PivotTables and PivotCharts with Excel 2003
Author: jason
Date: 2007-04-30 06:36:35
Category: Technical

So you've got a list of data. You need a report by the end of the day. You need to make pretty graphs. Has that happened before? Have you built the graphs by categorizing and counting each unique value by hand? If you are using Microsoft Excel 2003 you can use PivotTables and PivotCharts.

First thing to do is insert a row at the top of your data. This will be the header row.
Highlight the first row by clicking the 1 on the left.
At the top of the screen click Insert, Rows.
Add a description for each row.

Note: in my case I need to save this CSV file as a XLS file to make/store PivotTables and PivotCharts in it.
At the top click File - Save As.
In the "Save as type" dropdown find and select "Microsoft Office Excel Workbook (*.xls)"

If you don't already have a blank worksheet, I recommend inserting one.
Right-click the title (in this case bk_download) of the bottom worksheet and select Insert.
Ensure Worksheet is highlighted and click OK.
At the top of the blank worksheet click Data - PivotTable and PivotChart Report.

After the "PivotTable and PivotChart Report" window opens click PivotChart report (with PivotTable report)

The "PivotTable and PivotChart Wizard Step 2 of 3" window should open. This window is where we will specify what rows and columns the PivotTable will use to build our PivotChart.

Click back to the other worksheet (in this case bk_download).
Highlight the rows and columns that you want in the PivotTable.
Make sure you include the header row in the range.
Hold the Shift or Control key to select multiple columns/rows.
Click Finish.
If you get errors make sure you aren?t selecting empty rows/columns and you are including a header row.

You'll now be able to drag your header items to the PivotTable

I'm going to drag "Category" to the bottom and ?Amount? to the middle (in the gray) from the right side.

If you don't like the column graph you can right-click the graph and click Chart Type. You'll be able to choose: Column, Bar, Line, Pie, XY (Scatter), Area, Doughnut, Radar, Surface, Bubble, Stock, Cylinder, Cone, or Pyramid.

Also you can double-click the gray field "Count of Amount" to change the summary in the graph. At the moment the bars only represent a count of each item. It can be changed to reflect a sum, average, minimum, maximum, etc... Also the data can be shown as normal, difference, percentage, etc...

That is a basic overview of PivotTables and PivotCharts in Excel 2003. I haven't had a chance to preview Excel 2007, but I hear its easier to make graphs and charts with it.

jason @ jasonthomasfrance.com - www.masterstationlog.com - copyright 2009