The best way to understand the concept of a pivot table is to see one. Start with Figure 11-1, which shows a portion of the data used in creating the pivot table in this chapter.

Figure 11-1. This table is used to create a pivot table.

This table consists of a month's worth of new account information for a three–branch bank. The table contains 712 rows, and each row represents a new account. The table has the following columns:

The bank accounts table contains quite a bit of information. But in its current form, the data doesn't reveal much. To make the data more useful, you need to summarize it. Summarizing a table is essentially the process of answering questions about the data. Following are a few questions that may be of interest to the bank's management:

You can, of course, spend time sorting the data and creating formulas to answer these questions. Often, however, using a pivot table is a much better choice. Creating a pivot table takes only a few seconds, doesn't require a single formula, and produces a nice–looking report (with an optional chart). In addition, pivot tables are much less prone to error than creating formulas.

Figure 11-2 shows a pivot table created from the bank data. This pivot table shows the amount of new deposits, broken down by branch and account type. This particular summary is just one of dozens of summaries that you can produce from this data.

Figure 11-2. A simple pivot table.

Figure 11-3 shows another pivot table generated from the bank data. This pivot table uses a drop–down Report Filter for the Customer item (in row 1). In the figure, the pivot table displays the data only for Existing customers. (The user can also select New or All from the drop–down control.) Notice the change in the orientation of the table? For this pivot table, branches appear as column labels, and account types appear as row labels. This change, which took about five seconds to make, is another example of the flexibility of a pivot table.

Figure 11-3. A pivot table that uses a report filter.

A pivot table requires your data to be in the form of a rectangular database. You can store the database in either a worksheet range (which can be a table or just a normal range) or an external database file. Although Excel can generate a pivot table from any database, not all databases benefit.

Generally speaking, fields (or columns) in a database table consist of two types:

A single database table can have any number of data fields and category fields. When you create a pivot table, you usually want to summarize one or more of the data fields. Conversely, the values in the category fields appear in the pivot table as rows, columns, or filters.

Exceptions exist, however, and you may find Excel's pivot table feature useful even for databases that don't contain actual numerical data fields. For example, it's very easy to create a pivot table that displays the count of various items.

Figure 11-4 shows an example of an Excel range that is not appropriate for a pivot table. This range contains descriptive information about each value, but it's not set up as a table. In fact, this range resembles a pivot table summary.

Figure 11-5 shows the same data in a form that is appropriate for a pivot table. Notice that every data cell in the original range shown in Figure 11-4 is represented by a separate row.

Figure 11-4. This range is not appropriate for a pivot table.

Figure 11-5. The data in Figure 11-4, in a form that's appropriate for a pivot table.

If your data is in a worksheet range, select any cell in that range and then choose Insert

Figure 11-6. In the Create PivotTable dialog box, you tell Excel where the data is and where you want the pivot table.

Excel attempts to guess the range, based on the location of the active cell. If you're creating a pivot table from an external data source, you need to select that option and then click the Choose Connection button to specify the data source. If you're working with an external data source, the location of the active cell is not important.

Tip

If you're creating a pivot table from data in a worksheet, it's a good idea to first create a table for the range (by choosing Insert

Use the bottom section of the Create PivotTable dialog box to indicate the location for your pivot table. The default location is in a new worksheet, but you can specify any range in any worksheet, including the worksheet that contains the data. You can specify a single cell that will be the upper left corner of the pivot table.

Click the OK button, and Excel creates an empty pivot table and displays its PivotTable Field List, as shown in Figure 11-7.

Figure 11-7. Use the PivotTable Field List to build the pivot table.

Next, set up the actual layout of the pivot table by using the PivotTable Field List. You can do so by using either of these techniques:

Note

In previous versions of Excel, you could drag items from the field list directly into the appropriate area of the pivot table. This feature is still available, but it's turned off by default. To enable this feature, choose PivotTable Tools

The following steps create the pivot table presented earlier in this chapter (see the section "A Pivot Table Example"). For this example, I drag the items from the top of the PivotTable Field List to the areas in the bottom of the PivotTable Field List.

Figure 11-8. After a few simple steps, the pivot table shows a summary of the data.

Notice that the pivot table uses General number formatting. To change the number format used, select any value and choose PivotTable Tools

You can apply any of several built–in styles to a pivot table. Select any cell in the pivot table and choose PivotTable Tools

You also can use the controls in the PivotTable

The PivotTable Tools

Still more pivot table options are available in the PivotTable Options dialog box, shown in Figure 11-9. To display this dialog box, choose PivotTable Tools

Figure 11-9. The PivotTable Options dialog box.

Pivot table calculations

Pivot table data is most frequently summarized using a sum. However, you can display your data using a number of different summary techniques. Select any cell in the Values area of your pivot table and then choose PivotTable Tools

Use the Summarize By tab to select a different summary function. Your choices are Sum, Count, Average, Max, Min, Product, Count Numbers, StdDev, StdDevp, Var, and Varp.

To display your values in a different form, use the drop–down control on the Show Values As tab. Then, use the two list boxes (Base Field and Base Item) to refine your choice. Your choices are described in the following table.

Function	Result
Difference From	Displays data as the difference from the value of the Base Item in the Base Field
% Of	Displays data as a percentage of the value of the Base Item in the Base Field
% Difference From	Displays data as the percentage difference from the value of the Base Item in the Base Field
Running Total In	Displays the data for successive items in the Base Field as a running total
% Of Row	Displays the data in each row or category as a percentage of the total for the row or category
% Of Column	Displays all the data in each column or series as a percentage of the total for the column or series
% Of Total	Displays data as a percentage of the grand total of all the data or data points in the report
Index	Calculates data as follows: ((Value in Cell) x (Grand Total of Grand Totals)) / ((Grand Row Total) x (Grand Column Total))

After you've created a pivot table, it's easy to change it. For example, you can add further summary information by using the PivotTable Field List. Figure 11-10 shows the pivot table after I dragged a second field (OpenedBy) to the Row Labels section on the PivotTable Field List. In this example, each account type is broken down by whoever originally opened the account for the client.

Figure 11-10. Two fields are used for row labels.

Following are some tips on other pivot table modifications you can make:

Figure 11-11. The pivot table is filtered by date.

Copying a pivot table

A pivot table is very flexible, but it does have some limitations. For example, you can't add new rows or columns, change any of the calculated values, or enter formulas within the pivot table. If you want to manipulate a pivot table in ways not normally permitted, make a copy of it.

To copy a pivot table, select the entire table and choose Home

Note that the copied information is not a pivot table, and it is no longer linked to the source data. If the source data changes, your copied pivot table does not reflect these changes.

You can also copy the pivot table as a pivot table. Select the pivot table, and press Ctrl+C. Activate a different cell and press Ctrl+V. The result is a new pivot that is linked to the same data as the copied pivot table. You can then manipulate the new pivot table independently of the original pivot table.

If you're familiar with creating charts in Excel, you'll have no problem creating and customizing pivot charts. Most of Excel's charting features are available in a pivot chart. By the way, if you've used pivot charts in a previous version of Excel — and were frustrated by their limitations — you'll be pleased to know that pivot charts are improved significantly in Excel 2007.

Excel provides three ways to create a pivot chart:

A pivot chart cannot be a scatter chart, a bubble chart, or a stock chart. If you specify any of these chart types, Excel displays an error message.

Which method you choose is up to you. I usually create the pivot table first and then create a pivot chart from the pivot table. If you create the pivot table and pivot chart at the same time, the chart can get in the way.

Figure 11-12 shows Part of a table that tracks daily sales by region. The Date column contains dates for the entire year (excluding weekends), the Region field contains the region name (Eastern, Southern, or Western), and the Sales field contains the sales amount. The table contains 780 rows. Creating a chart from this table would be very difficult because the data arrangement isn't suitable for a chart.

Figure 11-12. This data will be used to create a pivot chart.

The first step is to create a pivot table to summarize the data. Figure 11-13 shows the pivot table. The Date field is in the Row Labels area, and the daily dates have been grouped into months. The Region field is in the Column Labels area. The Sales field is in the Values area (and it's summarized by summing).

Figure 11-13. This pivot table summarizes sales by region and by month.

The pivot table is certainly easier to interpret than the raw data, but the trends would be easier to spot on a chart.

To create a pivot chart, select any cell in the pivot table and choose PivotTable Tools

The chart makes it easy to see a strong upward sales trend for the Western division, a slight downward trend for the Southern division, and relatively flat sales for the Eastern division.

Figure 11-14. The pivot chart uses the data displayed in the pivot table.

When you select a pivot chart, the Ribbon displays a new context tab: PivotChart Tools. The commands in the Design, Layout, and Format tabs are virtually identical to those for a standard Excel chart, so you're in familiar territory. The commands in the Analyze tab contain a few commands specific to pivot table charts.

If you modify the underlying pivot table, the chart adjusts automatically to display the new summary data. Figure 11-15 shows the pivot chart after I changed the Date grouping to quarters.

Figure 11-15. If you modify the pivot table, the pivot chart is also changed.

What is the daily total new deposit amount for each branch?

Figure 11-16 shows the pivot table and pivot chart that answers this question.

The pivot chart is a stacked area chart.

Note that the pivot table can also be sorted by any column. For example, you can sort the Grand Total column in descending order to find out which day of the month had the largest amount of new funds. To sort, just right–click any cell in the column to sort and choose Sort from the shortcut menu. Note that this change to the pivot table will also change the pivot chart.

Figure 11-16. This pivot table shows daily totals for each branch.

How many accounts were opened at each branch, broken down by account type?

Figure 11-17 shows a pivot table that answers this question.

The most common summary function used in pivot tables is Sum. In this case, I changed the summary function to Count. To change the summary function to Count, right–click any cell in the Value area and choose Summarize Data By

Figure 11-17. This pivot table uses the Count function to summarize the data.

What if you wanted a second chart (a pie chart) to show the data in the Grand Total column? You can't create a pivot chart from only a portion of the pivot table. To create this pie chart, you need to remove the Branch field from the pivot table and move the AcctType field to the Row Labels section. If you would like to show both charts together, you must create a second pivot table (or make a copy of the original one) and generate the pie chart from the second pivot table. Figure 11-18 shows the pivot table and pivot chart that show the number of accounts by account type.

What's the dollar distribution of the different account types?

Figure 11-19 shows a pivot table that answers this question. For example, 253 of the new accounts were for an amount of $5, 000 or less.

Figure 11-18. This pivot table and pivot chart show the number of new accounts by type, across all branches.

Figure 11-19. This pivot table counts the number of accounts that fall into each value range.

This pivot table uses only one field: Amount.

When I initially added the Amount field to the Row Labels section, the pivot table showed a row for each unique dollar amount. I right–clicked one of the row labels and selected Group. Then I used Excel's Grouping dialog box to set up bins of $5, 000 increments.

The second instance of the Amount field (in the Values section) is summarized by Count. I right–clicked a value and chose Summarize Data By

Notice that the number of accounts for the larger dollar amounts is small. You may prefer to group these larger bins into a single bin of 20, 001 or more. To do so, right–click any cell in the Amount column and choose Group. In the Grouping dialog box, specify 1 for the Start At value, 20, 000 for the Ending At value, and 5, 000 for the By value. Figure 11-20 shows the result. Notice that Excel incorrectly labels the last column as >20001. Technically, it should be >20000.

Figure 11-20. The pivot table and pivot chart after changing the grouping parameters.

What types of accounts do tellers open most often?

Figure 11-21 shows that the most common account opened by tellers is a checking account.

Figure 11-21. This pivot table uses a report filter to show only the teller data.

This pivot table uses the OpenedBy field as a report filter and is showing the data only for tellers. I sorted the pivot table rows so that the largest value is at the top, and I also used conditional formatting to display data bars for the percentages.

="New Accounts By "&B1

Using a linked formula enables you to make the chart's title more descriptive. If I select a different value for the Report Filter field, the chart's title updates accordingly. Without the linked formula, the pivot chart's title would always display Accounts.

How does the Central branch compare to the other two branches?

Figure 11-22 shows a pivot table that sheds some light on this rather vague question. It simply shows how the Central branch compares to the other two branches combined.

I grouped the North County and Westside branches together and named the group Other Branches. The pivot table (and pivot chart) shows the amount, by account type.

Figure 11-22. This pivot table (and pivot chart) compares the Central branch with the other two branches combined.

In which branch do tellers open the most checking accounts for new customers?

Figure 11-23 shows a pivot table that answers this question. At the Central branch, tellers opened 23 checking accounts for new customers.

Figure 11-23. This pivot table uses three report filters.

This pivot table uses three report filters. The Customer field is filtered to show only New, the OpenedBy field is filtered to show only Teller, and the AcctType field is filtered to show only Checking.

The pivot table depicts the result graphically. Note that I used a linked formula for the chart's title to make the title more descriptive. The title is linked to cell F1, which contains the following formula that gets information from the three report filters:

=B1&" Customer "&B3&" Accounts By "&B2

The example in this section describes how to use a pivot table to create a frequency distribution chart, sometimes known as a histogram. This technique works with values and text.

Figure 11-24 shows a workbook with ratings from a survey item. Column A consists of the respondent number (from 1 to 192), and column B contains the rating. The objective is to create a frequency distribution chart that shows the number of each rating response. Note that column A is not actually required. A frequency distribution can be created from a single column.

Figure 11-24. A pivot chart can display the frequency of each response in column B.

To create the frequency distribution chart, follow these steps:

Figure 11-25 shows the pivot table and pivot chart. Because the items in the Values section are non–numeric, Excel defaults to the Count summary method — which is exactly what I want.

Figure 11-25. The pivot chart displays the frequency of each response

Notice that the Rating items are listed in alphabetical order in the pivot table, and on the category axis of the pivot chart. To change the order of the items, you can either

If the data consists of numerical values, you might want to create groups (or bins). To do so, right–click the field button in the pivot table and choose Group. Then specify the parameters for the grouping. Figure 11-26 shows a frequency distribution for numeric data, with the results grouped. Note that the pivot table displays a count of the values. By default, the pivot table will display the sum. You'll need to change this by right–clicking a value in the pivot table and choosing Summarize Data By

Figure 11-26. This pivot chart displays a frequency distribution for the data in column B, in groups of 10.

This section describes how to enable the user to determine which pivot table rows to display in a pivot chart. In other words, it's an interactive chart. Figure 11-27 shows a range of data — results from a customer survey. Each of the 450 customers responded to 14 survey items. Responses are coded as numbers:

Figure 11-27. This survey data will be converted to a pivot table.

The goal is to tabulate the survey results and display a chart that depicts the results for one or more of the survey items.

The first step is to create the pivot table. Follow these steps:

Next, create a pivot chart by selecting any cell in the pivot table and choosing PivotTable Tools

Figure 11-28 shows the pivot table and pivot chart.

Figure 11-28. This pivot chart displays the results for all survey items.

The pivot chart is not very usable because it shows too much information. To limit the amount of information in the chart, use either of these methods:

Using this filtering capability, the user can show the results for any single survey item, and even compare multiple items side by side. Figure 11-29 shows the pivot table and pivot chart after filtering the data to show three survey items.

Figure 11-29. The pivot chart shows the results for only three items.

In some situations, you might have data in multiple worksheets within a workbook. Figure 11-30, for example, shows a workbook that contains three sheets, named Yr2005, Yr2006, and Yr2007. Each sheet contains data for a year, and each has a similar four–column range of cells. This is a simple example. In actual practice, the sheets would probably contain much more data. Also, keep in mind that the data can come from different workbooks.

Figure 11-30. This workbook contains data in three worksheets.

You can create a pivot table to combine the information from these three sheets, although the procedure is certainly not obvious. This section describes how to create a pivot table and pivot chart to summarize the data in these three sheets.

Before Excel 2007, the PivotTable and PivotChart Wizard was used to created pivot tables. The Excel 2007 Ribbon interface has replaced this wizard. Unfortunately, the only way to create a pivot table from multiple data sources is to use this wizard. Here's how to add the missing PivotTable and PivotChart Wizard command to your Quick Access Toolbar (QAT):

Now that you have access to the missing wizard, follow these steps:

Figure 11-31 shows the pivot table and pivot chart after making these changes.

Figure 11-31. The pivot table and pivot chart that summarizes data on three worksheets.

Excel creates a clustered column chart, which is not appropriate because the data in the Ratio series is much smaller in magnitude (and not even visible in the chart). To fix this pivot chart, follow these steps:

Figure 11-32 shows the pivot chart after making these changes.

Figure 11-32. The pivot chart, after making some modifications.