Graphing by time in Excel
I have a data set (~2000 rows) with the following form:
+---------------------+-----+-----+-----+-----+-----+
| Datetime | n1 | n2 | n3 | n4 | tot |
+---------------------+-----+-----+-----+-----+-----+
| 2008-08-02 12:25:00 | 20 | 5 | 2 | 3 | 30 |
+---------------------+-----+-----+-----+-----+-----+
I'd like to graph this data using a Stacked Area chart in Excel 2003. Where I'm having difficulty is I'd like to present the chart on a scale of each hour. Could someone please provide the steps for producing a Stacked Area chart using time scale?
Here are the steps you need to follow. I'm using a Pivot Chart, as suggested by Longhorn213. While you have the table selected:
- Select Data -> PivotTable and PivotChart Report ...
- Select the Radio Button for PivotChart report (with PivotTable report)
Hit the Finish button You should now have two new tabs in the spreadsheet. One will have an empty chart and the other will have an empty pivot table. Select the sheet with the empty pivot table. Then:
Drag the Date field from the Pivot Table Field List to the area in the pivot table marked Drop Row Fields Here You should now see EVERY time stamp listed in the row.
Right click on the Date header in the Row and select Group and Show Detail -> Group
Highlight that you want to group by Hours and then hit the OK button Note that if your data spans several days/months, you MUST select both Days and Months in addition to Hours to further order the data.
Drag the n1 field into the area marked Drop Data Items Here
Drag the n2 field over top of the n1 data and drop it This will cause a new row header called Data to appear beside the Date header.
Drag the new Data header to the area marked Total and drop it there You now have the basic structure of the table that you want.
Drag the n3 and n4 fields into the data area, completing the table you want Now you can switch back over to the new chart sheet that was created. You should see the default type of chart with all your data.
Right click on the Chart Area and select Chart Type...
- Select the type of chart you want
And we're done! I'll check back later and clarify anything you need.