How to use PivotTables, Excel's data tool du jour
Businesses large and small routinely amass large amounts of data into Excel spreadsheets. That's fine, but once you've got all that data, how can you better understand it and massage it?
Here's one way: Take advantage of the "PivotTable" functionality built into Microsoft Excel, included in all versions of Microsoft Office.
With PivotTables, you can quickly turn rows of data, such as sales information or inventory figures, into summary reports. Once you have the summary, you can then change the format to create other reports or summaries.
For example, let's say that you have a table that summarizes sales data by customer. If the table includes information on sales data by region, sales rep, and product, you can literally drag the "customer" data off of the table and drag another category, such as sales rep or product data, onto the table.
Bill Jelen, an Excel expert who's been messing around with spreadsheets and software since the early days of Lotus 1-2-3, calls PivotTables "amazingly useful."
"The PivotTable allows you to take two hours of incredibly arcane database functions and in literally about 20 seconds create very powerful summary reports," Jelen says.
Jelen runs a remarkably detailed Web site, MrExcel.com (www.mrexcel.com), dedicated to information and solutions about Excel. The site offers more than 500 articles about Excel, as well as a message board with more than 75,000 searchable messages.
One thing that's interesting about the tables is how many people apparently don't know about them. Jelen notes that a survey at his Web site indicated that about 60% of Excel users don't even use PivotTables.
He showed me how you can take 50,000 rows of data and, with fewer than 10 mouse clicks, create tables that summarize sales by customer, by item, or by region.
Jelen started by just walking me through the process of creating a PivotTable showing revenue for a fictional company by product and region, as follows:
1. Open the PivotExample.xls worksheet that we're linking you to here. This worksheet contains fictitious sales data.
2. Select any one cell in the dataset. Cell A2 is fine.
3. From the Data menu at top, select PivotTable and PivotChart Report.
4. In Step 1 of the wizard, Excel guesses your data is in an Excel list. This is right, so click Next.
5. In Step 2 of the wizard, Excel correctly guesses that your data is in A1:G564. Click Next again.
6. Assuming you have Excel 2000 or newer, the next step is called Step 3 of 3. Click the Layout button in the lower left corner. If you happen to have Excel 97, go ahead to Step 6.
7. In the Layout dialogue, you have all of your field names as grey buttons on the right side.You are allowed to drag the buttons to one of four places on the left side: Row, Column, Data, or Page.
8. Drag the Revenue box and drop it in the Data area of the dialog. It will then change to read "Sum of Revenue." Drag the Region and drop it in the Row section. Drag Product and drop it in the Column section. Your dialog should look like this (below). If it does, click OK.
9. Back in the Wizard, click the Finish button to put the PivotTable on a new sheet.
10. Presto! The 563 rows were summarized on a new sheet called Sheet1.
"The beautiful thing is that once you have your summaries, you can easily reorganize or 'pivot' the data by clicking on one of the headings and moving it to another part of the report," Jelen says. "The whole thing is just excellent for ad hoc reporting and breaking down and summarizing information in any number of ways."
Here's how to reorganize our data to replace the "Region" field with the "Customer" field, still using the PivotTable in our example:
1. In Excel 2002 or newer, you will see a list of available fields in the PivotTable Field List task pane. Click on the Customer field and drag it to the line between column A and column B as shown.Secret trick: Look just below the mouse arrow. Do you see the icon that looks like the PivotTable layout screen? It is subtle, but one section will light up in blue to tell you where are going to drop the field. In this case, the blue area is on the left side, under "Region." If you accidentally dropped this field in the data area, you would have a mess. If you're already looking at a mess, hit Ctrl+Z to undo it.
2. Now, take a look at the new Customer report you've created. Down in row 32, you still see the total for Central. This would be a nice report for a sales manager, for example.
3. The goal here, remember, is to get rid of the region field. So click on the gray region field in the PivotTable and drag it outside of the PivotTable. When you see the dark red "X" underneath what you are dragging, you know you are removing the field.Click here:Drag to the right of your numbers:
You also can use PivotTables to create your own business equivalent of a Top 10 list. Using the table's advanced options, you can take your data summary and then limit it to showing only the top 10 or top five customers, products, sales regions, and so on. If you're in a business where 90% of your profits or sales come from only 10% of your customers, this can be a tremendously effective way of quickly identifying your most important clients or products, etc.
Again using our sample worksheet, here's how:
1. Double click the Customer field in cell A4
2. Click the Advanced button
3. You will actually change five items on this dialog.After these five changes, the report looks like this (below):Click OK to close Advanced Options and then OK to close the Field dialogue.
4. Your report shows just the top five customers, sorted high-to-low.
Note that the customer field is in blue to alert you that the table is filtered to show only some customers.
All of this pretty much scratches the surface of what you can do with PivotTables. For example, have you ever wondered where a number in a summary came from? With the PivotTable, you can double-click on the mystery number for any cell in the table, and up will pop all the data that went into that number. People who work with Excel way more than I do tell me that, for some reason, this little feature isn't explained in any easy-to-find official Excel guide.
For more information on using pivot tables, check out the Excel portal page on Microsoft Office Online, as well as Jelen's MrExcel site, www.mrexcel.com.