How to Analyze Data Using Excel PivotTables

Format a PivotTable

You can format the data in a PivotTable so that it is easier to read.

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.

You can filter the PivotTable to show just the information you are interested in seeing.

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

You can add and use Slicers to select items to filter a PivotTable in a more visual way.

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

Shop ▾
arrow up Amazon Shop buttons are programmatically attached to all reviews, regardless of products' final review scores. Our parent company, IDG, receives advertisement revenue for shopping activity generated by the links. Because the buttons are attached programmatically, they should not be interpreted as editorial endorsements.
recommended for you

10 Word Table Secrets

Read more »

Subscribe to the Best of TechHive Newsletter