Line Graph using Row summations

Question

I'm having some trouble in Excel producing a line graph for the following data:

+------+--------+-----------+-----------+
|  Id  |  Type  |   Owner   |   Date    |
+------+--------+-----------+-----------+
| 5415 | Type A | Bob       | 6/25/2013 |
|  540 | Type B | Bob       | 6/25/2013 |
| 4412 | Type A | Bob       | 6/25/2013 |
| 5542 | Type A | Bob       | 6/25/2013 |
| 5531 | Type A | Bob       | 6/26/2013 |
| 7055 | Type A | Bob       | 6/26/2013 |
| 7056 | Type A | Bob       | 6/27/2013 |
| 3482 | Type A | Marmaduke | 6/27/2013 |
+------+--------+-----------+-----------+

I would like to graph the number of rows for Owner Bob of Type A across each day in one line, and in another line graph the number of rows for Owner Bob of Type B in another line.

So in this case, it would produce one line on the chart corresponding to the sum of "Type A" rows for Bob on each day, and one line on the chart corresponding to the sum of the "Type B" rows for Bob on each day.

From this example excel file, the chart have a "Type A" line indicating:

• Hor Axis Pt: 06/25, Ver Axis Pt: 3 (3 Excel rows with Type A)
• Hor Axis Pt: 06/26, Ver Axis Pt: 2 (2 Excel rows with Type A)
• Hor Axis Pt: 06/27, Ver Axis Pt: 1 (1 Excel row with Type A)

The chart would also have a second line "Type B" indicating:

• Hor Axis Pt: 06/25, Ver Line Pt: 1 (1 Excel row with Type B)
• Hor Axis Pt: 06/26, Ver Line Pt: 0 (0 Excel rows with Type B)
• Hor Axis Pt: 06/27, Ver Line Pt: 0 (0 Excel row with Type B)

So the Type A line would have three points moving in a downward direction. The Type B line would have three points also moving downward. The owner Marmaduke would be completely ignored.

So the chart's horizontal X-axis would be the date: 06/25 -- 06/26 -- 06/27

I'm using Microsoft Excel 2007. Any ideas?

Answer 1

This is a perfect scenario for using a pivot table and pivot chart. You can get exactly what you want with just a few clicks. If you're unfamiliar pivot tables, a Google search will lead you to several tutorials.

For this case, you'll want to insert a pivot table (from the Insert ribbon). Select your entire table (headers and all) as the data you want to analyze.

Once you have inserted the pivot table, you can drag and drop your fields to the appropriate roles. Do the following:

• Drag Owner to Report Filter.
• Drag Type to Column Labels.
• Drag Date to Row Labels.
• Drag Id to Values. Click Sum of Id in the Values list and select Value Field Settings. Choose to summarize the value field by Count. Click OK.

Now your pivot table is set up. You can now filter by owner above the pivot table. Set the filter to show only values for Bob.

Right-click inside the pivot table and choose Pivot Table Options.... In the options menu, set the table to show 0 for empty cells.

Finally, you can create a pivot chart line graph for this table. Click on the pivot table and choose Pivot Chart from the Pivot Table Tools > Options ribbon. Choose the line graph style you want and you've got it.

I've inserted the pivot table on the same sheet with the data here just for illustration. It may be better to insert your table and chart on a separate sheet.