Make summarizing and reporting easy with Excel PivotTables

Excel’s PivotTable feature lets you organize and summarize data into a meaningful report format without changing the data set. Beyond reporting, PivotTables offer a quick and easy way to analyze information that might otherwise require specialized knowledge of functions and other features. In this article, I’ll show you the basics of creating a PivotTable. Once you’re familiar with the PivotTable feature, you might find it a great go-to solution for more than reporting.

You can download the example .xlsx or .xls file for your convenience. I’m using Excel 2013 on a Windows 7 system, and I’ll provide specific instructions for other versions when significantly different.

Easy start

A PivotTable is only as good as its data. To reduce problems, your data set should contain no blank rows or columns, no subtotals, and should be in tabular format. Each column should have a unique header (name). Once you’re satisfied that the source data is in good shape, consider designing a few simple reports using pen and paper. Doing so will help guide you later when you’re working in the PivotTable frame and facing lots of choices.

The good news is this: if you can click, you can create a PivotTable. You might need to tweak the results, but getting started is simple:

1. Select any cell in the data set, click the Insert tab, and then click PivotTable in the Tables group. If you’re still using Excel 2003, choose PivotTable and PivotChart Report from the Data menu to launch a wizard that will walk you through the process.
2. In the resulting dialog, check the range and specify whether you want the PivotTable in the existing worksheet or a new worksheet (Figure A).
   Figure A
   
3. Click OK.
4. When Excel displays the empty PivotTable frame (Figure B), start dragging columns from the field list to the sections below: Filters, Columns, Rows, and Values. (The names are slightly different in previous versions.)
   Figure B
   

Steps 1 through 3 are easy, and this article won’t repeat these steps as we work through examples. Step 4 requires specialized knowledge of your data and your reporting needs, and that’s where we’ll spend our time.

By default, the PivotTable frame is empty. However, you can rely on the following guidelines most of the time:

• Add descriptive (nonnumeric) values to the Rows area
• Add numeric fields you want to analyze to the Values area
• Add date and time fields to the Columns area

Counting

Summarizing data is the main purpose of a PivotTable, and a PivotTable can count values and text. Figure C shows a simple configuration that counts the number of orders by region; simply drag the Region field to the Rows and Values sections in the PivotTable Fields pane to the right. Because the Region field stores text, the Values section defaults to a simple count. In Excel 2003, you must use the Add To control to add fields to the frame.

Figure C

Count the number of orders for each region.

By dragging the Transaction Date field to the Columns section, you can offer more details by returning the monthly count. After dragging Transaction Date to the Columns section, do the following:

1. Right-click any of the date headers and choose Group (Figure D). In Excel 2003, choose Group and Show Detail, and then select Group.
   Figure D
   
2. In the resulting dialog, you can see the defaults: the first and last date in the data set and the Month option (Figure E).
   Figure E
   
3. Click OK without making any changes (Figure F).
   Figure F
   

Summing

Getting a total is just as simple as counting-but with a few more clicks, you can also show those subtotals as a percentage. Figure G shows a PivotTable that sums the Amount field by region.

Figure G

Grouped sales by region.

At this point, you could format the Amount field as currency, but instead, let’s show those values as percentages as follows:

1. Right-click any cell in the Sum of Amount column.
2. Choose Value Field Settings from the resulting submenu. Excel defaults to the Sum option.
3. Click the Show Values As tab. In Excel 2003, click Options.
4. From the Show values as drop-down, choose the % of Grand Total option. In 2007, choose % of total.
5. Click OK to see the results shown in Figure H.
   Figure H
   

By dragging the Personnel field to the Columns section, you can display the percentage per region for each salesperson, as shown in Figure I. Luke has the highest individual percentage at 8.83%, but Martha has the highest overall percentage at 34.39%. These are details you could glean from subtotals as well, but percentages often provide a different perspective, and this feature displays them for you quickly.

Figure I

Percentages by region for each salesperson.

Custom grouping

Earlier, we grouped records by month, based on the date values in Transaction Date. Groups won’t always be so generic. You might want to count or sum values within a range that isn’t represented in the natural data. For instance, let’s count the number of commission amount that fall without a few ranges: 0 to 99, 100 to 199, 200 to 299, and so on:

1. Drag Commission to the Columns section and drag Personnel to the Rows section.
2. Drag Commission to the Values section where it defaults to a sum function.
3. Right-click any of the values in the Rows Labels row and choose Group.
4. In the resulting dialog, specify the Starting at and Ending at values. Excel helps by defaulting to the most appropriate data values, but you can change them to accommodate larger or smaller ranges. In this case, enter 0, 400.
5. Enter an incrementing value in the By control. In this case, we want to specify incremental groups of 100 (Figure J).
   Figure J
   
6. Click OK to see the results shown in Figure K.
   Figure K
   

That’s a lot of return for such little work!

Worth noting

When you modify the data set, you must refresh the PivotTable to reflect those changes; the PivotTable isn’t dynamic. If you’re using Excel 2007 or later, you can convert your data set to a Table and build your PivotTable on that Table. When you use a Table as the data source for a PivotTable, Excel automatically updates the PivotTable as you update the data in the Table; your PivotTable is always in sync!

Creating a Table is simple:

1. Select any cell in the data set.
2. Click the Insert tab. In the Tables group, click Table (or, press [Ctrl]+[T]).
3. Before clicking OK, specify whether the data set has a header row. By default, Excel assumes the first row of data is a header row.
4. Click OK to convert the data set into a table.

If you’re using a Table, you can create the PivotTable using the insert tab or by clicking Summarize With PivotTable in the Tools group on the contextual Design tab. Using a Table as the data source can save you the trouble of remembering (or the embarrassment of forgetting) to update the PivotTable after modifying the source data.

We didn’t work with a Table data source in this article, but it’s important to know that you can and why you should consider doing so.

PivotTable solutions

PivotTables are worth taking the time to explore. We’ve reviewed only a few basic options, but it’s easy to see how useful they can be. There’s much more to this feature. The next time you’re faced with summarizing or reporting, try a PivotTable!

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.