When you compile data in a list, you often need to answer questions such as “How much revenue did the West Coast office generate last month?” or “What was the average number of customers served at each office in each quarter last year?”
Excel’s PivotTables (Microsoft runs the words together, although you?ll find pivot tables in other spreadsheet programs) can provide those types of answers.
Do you want to group data by category? Use a PivotTable. Interested in comparing results by person, by quarter, or by category? Use a PivotTable. Need to answer questions that start with “How many?” or “How much?” PivotTables can do that, too. I?ll show you how to accomplish those tasks and more.
If you’d like to work through the examples I’ll present in this article, you can download my Excel spreadsheet.
What Is a PivotTable?
A PivotTable is an Excel tool for summarizing a list into a simple format. You create PivotTables from lists, as you define which fields should be arranged in columns, which fields should become rows, and what data you wish to summarize. You don’t have to use all of the data in a spreadsheet?just the data and the fields you need to answer your questions. Once you’ve created the table, you can then see the answer to your question. You can later reuse the PivotTable to answer different questions by rearranging it.
Create a PivotTable
Before creating a PivotTable, consider the questions you want to answer, or which information you wish to extract from your data. This step will determine how you should construct the table.
Let’s consider the data in this worksheet, and the question “How much did we earn from Development for each quarter in 2011?”
To create the PivotTable, click somewhere in the list of data, choose the Insert tab, and click PivotTable. Excel will automatically select the area containing the data, including the headings. If it does not select the area correctly, drag over the area to select it manually. Placing the PivotTable on a new sheet is best, so click New Worksheet for the location and then click OK.
You might become confused at this point, because if you’ve never created a PivotTable, nothing you see on this screen will look familiar. In reality, it?s simpler than it looks. The PivotTable Field List panel, as its name suggests, contains the fields from your list; all you need to do is to arrange them in the boxes at the foot of the panel. Once you?ve done that, the diagram on the left becomes your PivotTable.
In the PivotTable Field List on the right side of the screen, click the Quarter, Category, and Feescheckboxes; this is the data you need to answer the question “How much did we earn from Development for each quarter in 2011?” When you do this, Excel creates a PivotTable for you. The data might not be arranged exactly to your liking, but that’s an easy fix.
If you want to see the categories as columns and the quarters as rows, for instance, drag Quarter to the Row Labels box and drag Category to the Column Labels box. You can now read the answer to your question in the PivotTable, because column B now contains the results for fees earned from Development for the four quarters individually, as well as the grand total for the year.
Next Page: How to Format a PivotTable
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
Create a PivotChart Based on Your PivotTable
You can easily chart the data from your PivotTable; doing so creates a PivotChart that Excel automatically links to the PivotTable. Any changes you make in the chart’s organization will be reflected in the PivotTable, and vice versa.
To create a PivotChart, click anywhere in the PivotTable, choose Insert, and select a Column chart (a simple chart will work best). The PivotChart will appear in the sheet alongside the PivotTable, and it will display the data from the PivotTable.
When you later update the data in the PivotTable by using Slicers, or by changing the table layout, the chart will automatically change to match.
Drill Down to View the Data
PivotTables have a built-in drill-down feature. If you want to explore a particular value in your PivotTable, such as a total, double-click it. When you do, you’ll see a new sheet containing all the data that contributed to the value you double-clicked. This sheet allows you to analyze the data and understand it better.
Each time you double-click a value in a PivotTable, a new sheet appears in your workbook. It can get crowded with these extra sheets if you don’t remove them when you are finished with them. To do so, right-click a sheet’s tab and click Delete.
Use Other Calculations on Your PivotTable Data
By default, Excel sums the value fields in a PivotTable, but you can change this setting so that it makes other calculations, such as the maximum, minimum, and average. For instance, if you want to see the average of the values in the PivotTable, right-click an item in the data area, choose Summarize Values By, and click Average. The heading in the top left of the table will change to indicate that you are viewing the average values, and Excel will recalculate the PivotTable values to show averages. To undo the change and see the data totaled, right-click a value, choose Summarize Values By, and click Sum.
In this discussion I’ve merely scratched the surface of what you can achieve with PivotTables and PivotCharts in Excel. With knowledge of these basic tools, however, you can easily create a PivotTable to summarize and analyze your own data.
If you liked this article, you might also be interested in these stories:
How to Create Advanced Microsoft Excel Spreadsheets
Work Faster in Microsoft Excel: 10 Secret Tricks