How to Analyze Data Using Excel PivotTables
Format a PivotTable
Although the PivotTable answers the question, it could be formatted more neatly. To format the numerical data, right-click a value and select Number Format.
Over in the Category list, choose Number, and then set up your desired number format; in my example, I added a thousand separator and set decimal places to zero. Click OK, and Excel formats the data?all of the numbers in the table?accordingly. To make the PivotTable itself more attractive, click inside it, choose PivotTable Tools > Design, and click a style in the PivotTable Styles Gallery.
Answer Multiple Questions With One PivotTable
Having created a PivotTable, you can now use that same table to answer other questions. For example, to answer the question "How much did Davidson earn for Development for each quarter?" click in the table to redisplay the PivotTable Field List, and click Consultant to add it to the Row Labels box. If the PivotTable Field List disappears from the screen, right-click inside the PivotTable and click Show Field List.
Even though you can read the answer already in the PivotTable, you can also filter the PivotTable to hide unwanted data. Select one of the consultants' names, and then click the Row Labels drop-down arrow in the PivotTable. Deselect the checkboxes for all but Davidson. If you see the quarters listed in the drop-down box, go back and select a consultant before clicking the drop-down arrow.
Now choose one of the categories (Development or Support), click the Column Labels drop-down arrow, and deselect all but Development. This action filters the PivotTable so that it shows only the information that answers the question. In this way, you can use your created PivotTable over and over again to answer all the questions you have about your data.
Perform Smarter Filtering With Slicers
If you have Excel 2010, you can use the new Slicers tool to make your PivotTable even easier to work with. Slicers let you select the data to show in the PivotTable in a more visual way. To add a Slicer, click inside the PivotTable, and choose PivotTable Tools > Options > Insert Slicer. Click the checkbox for each Slicer to add to the worksheet, and click OK. In my case, I added them for Quarter, Category, and Consultant.
To filter the PivotTable, click an item in the Slicer to see that data in the PivotTable. To view multiple items at once, Ctrl-click each item you want to see. The items you're viewing will be colored in the Slicer. When the icon in the top right of a Slicer is active, it indicates that the Slicer is filtering the data in the PivotTable; click the icon to remove that filter.
You can format Slicers by selecting a Slicer, choosing Slicer Tools > Options, and then clicking a Slicer Style to use for each of the Slicers in turn.
Update a PivotTable When Data Changes
If you make any changes to the data in the original list from which you created the PivotTable, you need to update the PivotTable to reflect these changes. To do so, just right-click the PivotTable and click Refresh.
Next Page: Create a PivotChart Based on Your PivotTable