Note: To work along with this tip, you can download the PivotTable Example file. The download file does not contain the PivotTables referred to below, it simply contains the source data so you can follow the steps to create the same PivotTables.
PivotTables are a useful way to quickly summarize data and display it in an easy to read format. Their biggest problem is that not everyone knows how to use them. Once you learn how to use PivotTables, though you’ll use them all the time.
In the PivotTable Example file, you’ll notice a list of salespeople, who have sold various widgets throughout the year, and for all different dollar amounts. You want a summary of sales by salesperson, location, date, and product.
First you need to get the data into a PivotTable.
- Click somewhere within the data, and click Data | PivotTable and PivotChart Report. If you’re using Excel 2007, click the Insert tab and select PivotTable.
- Ensure the selected range includes A1:F27, and place the PivotTable report in a new worksheet.
Now you have a blank PivotTable, with a list of available fields off on the right hand side of the screen.
We now want to find out which salesperson has sold the most. Drag the Salesperson field to the row area, and the Order Amount to the Data/Values area, depending on which verson of Excel you are using. You should see the following totals:
- Evans – $1,689
- Gordon – $2,008
- Jackson – $595
- Jones – $1,837
- Miller – $1,353
- Nolan – $1,649
- Smith – $3,197
- Snell – $1,131
- Stein – $1,358
To find out who sold the most by quarter, drag the Date field to the Column label area. The grand totals should be as follows:
- QTR 1 – $4,288
- QTR 2 – $3,213
- QTR 3 – $3,939
- QTR 4 – $3,377
To find out which location sold the most, replace the Salesperson field with the Location field in the row area. The grand totals by location should be as follows:
- Mid-West – $1,837
- Northeast – $4,550
- Northwest – $1,726
- Southeast – $3,338
- Southwest – $3,366
Finally, to find out which widget was the best selling one, replace the Location field with the Product field in the row area. The grand totals by product are as follows:
- Widget A – $2,508
- Widget B – $2,492
- Widget C – $1,527
- Widget D – $1,245
- Widget E – $1,561
- Widget F – $1,852
- Widget G – $1,512
- Widget H – $1,447
- Widget R – $673
You can also play around with the location of each field, by adding several fields to the row area, for example, to see which salesperson sold the most of a particular product. Basically, visualize how you want your report to look, and then drag your fields into place in order to get the information you want. Also, instead of summarizing the list based on the “Sum” of the dollar amounts, you can tell the PivotTable to count the number of items. This way you can count how many of Widget B Smith sold (two units).
As you can see a PivotTable can be a powerful tool when trying to summarize large amounts of data. Remember, a PivotTable is just as easy to set up if your data contains 30 or 30,000 rows of data. As long as they are formatted with a header row, and the data is in a consistent format, a PivotTable will have no problems creating useful reports.