Headlines:
Applied Tips:
Additional Links:
applied tips : april 2007
microsoft excel : pivot tables explained
Excel is great for keeping lists of information, but sometimes these lists can be large and difficult to analyze.
For example, say you own a grocery store chain with stores in several states. You have sales data for each store, and you want to analyze the sales by region. Some of the stores are located in malls while others are stand-alone buildings. You'd like to see how the different types of stores compare.
Unfortunately, all this information is in a long Excel list that's hard to comprehend. Fortunately, a PivotTable can organize and summarize this information to help you analyze it quickly and easily.
What's a PivotTable?
A PivotTable is a dynamic table that extracts data from an Excel list or an outside source and organizes it in a summarized tabular format that's easy to analyze.
As you make changes to the Excel list data source, you can refresh the PivotTable to pull the updated list information.
Creating a PivotTable
To make a PivotTable, you first need a well-organized Excel list with column labels. You can also use an external database file or Internet information as your data source, but many of these sources will still require you to retrieve the data and put it in an Excel list format. Most of the time, you'll use an Excel list, so that's what we'll assume in this article.
Once you have your Excel list ready to go, click any cell in the list to start creating a PivotTable. Select Data > PivotTable and PivotChart Report (PivotTable Report in Excel 97) from the menu to open the PivotTable and PivotChart Wizard.
The PivotTable Wizard
Excel 2000, 2002, 2003: In Step 1 of the Wizard, make sure the "Microsoft Office Excel list or database" and "PivotTable" options are selected, then click the Next button.
In Step 2 of the Wizard, select the range of the data you want to include in the PivotTable. This range should include your whole Excel list, and it should already be correctly selected for you in the Range box. If it's not, select the correct range with your mouse. Then click Next.
Step 3 of the Wizard asks if you want to put the new PivotTable report in a new worksheet or the existing worksheet. Select "Existing worksheet" and click a blank cell in the worksheet near your list. This is where your PivotTable report will go.
When the PivotTable is inserted, design its layout. Click the Layout button to display the Layout screen.
Drag desired fields from the field list on the right onto the ROW and COLUMN areas of the diagram. Then drag fields that contain the data you want to display onto the DATA area.
Once you move a field into the DATA area, double-click it and select a calculation to be performed on the data. For example, if you choose Average, Excel will average the list data before it presents it in the PivotTable.
Let's walk through the layout of the grocery store PivotTable example. The goal of the grocery store PivotTable is to display the sales by region, while also comparing mall vs. stand-alone store sales.
To do this, we drag the Region field into the ROW area so that the different regions will appear in separate rows. Next, we drag the Store Type field into the COLUMN area so that we'll have a column for mall stores and one for stand-alone stores.
Finally, we drag the Sales field into the DATA area because the Sales numbers are what we want to organize and analyze. By default, the PivotTable calculates the sum of the Sales amounts by each region and store type, but by double-clicking on the Sales field, we can calculate something else, such as the average sales, instead.
To remove fields from the layout, drag them off the diagram. Once you like your layout, click OK and click the Finish button. The new PivotTable appears in your worksheet!
Excel 97: The Excel 97 Wizard works the same as the other versions, it just has an extra Wizard screen for the layout. You don't need to click the Layout button to open the layout screen because it appears on its own.
Working with PivotTable items
Once you've created a PivotTable, you can move an item by dragging its field button around the table. Simply drag it to a new position to create a new layout. To remove an item, click the field button and drag it off the PivotTable area.
To add an item in Excel 2002 and 2003, click the PivotTable to display the PivotTable Field List and drag the desired field onto the PivotTable area. In Excel 97 and 2000, reopen the Wizard by clicking the PivotTable to display the PivotTable toolbar. Click the PivotTable button list arrow and select PivotTable Wizard. Make changes to the layout in the Wizard, and click Finish.
Finally, if you change the source data, click the Refresh Data button on the PivotTable toolbar to refresh the PivotTable.
schedule a class on excel
Request an on-site Excel class from Applied Office. Sessions are priced per hour, not per person, and your employees will be shown how to create spreadsheets, charts, and use formulas that will save them a lot of time. Learn more here
upcoming workshops
Excel: Making it Look Good (Apr 11)
Discover how easy it is to create attractive spreadsheets that communicate your ideas with ease. Through the use of spacing, borders, font, and color, you'll observe how a simple spreadsheet can be made to be more easily understood. Learn how to create charts and adjust their appearance. More Information
The Magic of Excel's Pivot Tables (May 2)
One of Excel's best kept secrets is its ability to quickly summarize thousands of records in just a few clicks. The Pivot Table feature sorts, counts, and totals your data and lets you quickly review that data in a variety of ways. That long list of sales transactions can finally be turned into something useful! Basic experience with Excel is a must. More Information
quick reference card
Get the Quick Reference Card on Microsoft Excel! Download it for free and print it on your own printer. You might even want to laminate it.