Chapter 34: Analyzing Data
with Pivot Tables

In This Chapter

Creating a pivot table from non-numeric data

Grouping items in a pivot table

Creating a calculated field or a calculated item in a pivot table

Understanding the new Date Model feature

Creating an attractive report using a pivot table

The previous chapter introduces pivot tables. There, I present several examples to demonstrate the types of pivot table summaries that you can generate from a set of data.

This chapter continues the discussion and explores the details of creating effective pivot tables. Creating a basic pivot table is very easy, and the examples in this chapter demonstrate additional pivot table features that you may find helpful. I urge you to try these techniques with your own data. If you don't have suitable data, use the files available on this book's website.

Working with Non-Numeric Data

Most pivot tables are created from numeric data, but pivot tables are also useful with some types of non-numeric data. Because you can't sum non-numbers, this technique involves counting.

Figure 34.1 shows a table and a pivot table generated from the table. The table is a list of 400 employees, along with their location and gender. As you can see, the table has no numeric values, but you can create a useful pivot table that counts the items rather than sums them. The pivot table cross-tabulates the Location field by the Sex field for the 400 employees and shows the count for each combination of location and gender.

Figure 34.1

This table doesn't have any numeric fields, but you can use it to generate a pivot table, shown next to the table.

Here are the PivotTable Fields task pane settings I used for this pivot table:

• The Sex field is used for the Columns.

• The Location field is used for the Rows.

• Location is also used for the Values and is summarized by Count.

• The pivot table has the field headers turned off, by using the Field Headers toggle control in the PivotTable Tools ⇒ Analyze ⇒ Show group.

Figure 34.2 shows the pivot table after making some additional changes:

Figure 34.2

The pivot table, after making a few changes.

• I added a second instance of the Location field to the Values section. To display percentages, I right-clicked a value in that column and chose Show Values As ⇒ Percent of Column Total.

• I changed the field names in the pivot table to Ct and Pct.

• I selected a pivot table style that makes it easier to distinguish the columns.

Grouping Pivot Table Items

One of the most useful features of a pivot table is the ability to combine items into groups. You can group items that appear in the Rows or Columns section in the PivotTable Fields task pane. Excel offers two ways to group items:

• Manually: After creating the pivot table, select the items to be grouped and then choose PivotTable Tools ⇒ Analyze ⇒ Group ⇒ Group Selection. Or you can select the items, right-click, and choose Group from the shortcut menu.

• Automatically: If the items are numeric (or dates), use the Grouping dialog box to specify how you would like to group the items. Select any single item and then choose PivotTable Tools ⇒ Analyze ⇒ Group ⇒ Group Field. Or right-click a single item and choose Group from the shortcut menu. In either case, the Grouping dialog box appears. Use this dialog box to specify how to group the items.

A manual grouping example

Figure 34.3 shows the pivot table example from the previous sections, with two groups created from the Row Labels. To create the first group, I held down the Ctrl key while I selected Arizona, California, and Washington. Then I right-clicked and chose Group from the shortcut menu. Then I selected the three other states and created a second group. I replaced the default group names (Group 1 and Group 2) with more meaningful names (Western Region and Eastern Region).

Figure 34.3

A pivot table with two groups.

You can create any number of groups and even create groups of groups.

Excel provides a number of options for displaying a pivot table, and you may want to experiment with these options when you use groups. These commands are on the PivotTable Tools ⇒ Design tab of the Ribbon. There are no rules for choosing a particular option. The key is to try a few and see which makes your pivot table look the best. In addition, try various options in the PivotTable Tools ⇒ Design tab. Often, the style that you choose can greatly enhance readability.

Figure 34.4 shows pivot tables using various options for displaying subtotals, grand totals, and styles.

Automatic grouping examples

When a field contains numbers, dates, or times, Excel can create groups automatically. The two examples in this section demonstrate automatic grouping.

Grouping by date

Figure 34.5 shows a portion of a simple table with two fields: Date and Sales. This table has 730 rows and covers the dates between January 1, 2012, and December 31, 2013. The goal is to summarize the sales information by month.

Figure 34.4

Pivot tables with options for subtotals and grand totals.

Figure 34.5

You can use a pivot table to summarize the sales data by month.

Figure 34.6 shows part of a pivot table created from the data. The Date field is in the Rows section, and the Sales field is in the Values section. Not surprisingly, the pivot table looks exactly like the input data because the dates have not been grouped.

Figure 34.6

The pivot table, before grouping by month.

To group the items by month, select any date and choose PivotTable Tools ⇒ Analyze ⇒ Group ⇒ Group Field (or right-click and choose Group from the shortcut menu). The Grouping dialog box, shown in Figure 34.7, appears. Excel supplies values for the Starting At and Ending At fields. The values cover the entire range of data, and you can change them if you like.

Figure 34.7

Use the Grouping dialog box to group pivot table items by dates.

In the By list box, select Months and Years and verify that the starting and ending dates are correct for your data. Click OK. The Date items in the pivot table are grouped by years and by months, as shown in Figure 34.8.

Figure 34.8

The pivot table, after grouping by month and year.

Figure 34.9 shows another view of the data, grouped by quarter and by year.

Grouping by time

Figure 34.10 shows a set of data in columns A:B. Each row is a reading from a measurement instrument, taken at one-minute intervals throughout an entire day. The table has 1,440 rows, each representing one minute. The pivot table summarizes the data by hour.

Figure 34.9

This pivot table shows sales by quarter and by year.

Figure 34.10

This pivot table is grouped by Hours.

Here are the settings I used for this pivot table:

• The Values area has three instances of the Reading field and each instance displays a different summary method (Average, Minimum, and Maximum). To change the summary method for a column, right-click any cell in the column and choose the Summarize Values By and then appropriate option.

• The Time field is in the Rows section, and I used the Grouping dialog box to group by Hours.

Creating a Frequency Distribution

Excel provides a number of ways to create a frequency distribution (see Chapter 13), but none of these methods is easier than using a pivot table.

Figure 34.11 shows part of a table of 221 students and the test score for each. The goal is to determine how many students are in each ten-point range (1–10, 11–20, and so on).

Figure 34.11

Creating a frequency distribution for these test scores is simple.

The pivot table is simple:

• The Score field is in the Rows section (grouped).

• Another instance of the Score field is in the Values section (summarized by Count).

The Grouping dialog box that generated the bins specified that the groups start at 1, end at 100, and are incremented by 10.

Figure 34.12 show the frequency distribution of the test scores, along with a pivot chart (see “Creating Pivot Charts,” later in this chapter). I filtered the Scores so the pivot table (and chart) do not show the <1 category and the >101 category.

Figure 34.12

The pivot table and pivot chart show the frequency distribution for the test scores.

Creating a Calculated Field or Calculated Item

Perhaps the most confusing aspect of pivot tables is calculated fields versus calculated items. Many pivot table users simply avoid dealing with calculated fields and items. However, these features can be useful, and they really aren't that complicated once you understand how they work.

First, some basic definitions:

• A calculated field: A new field created from other fields in the pivot table. If your pivot table source is a worksheet table, an alternative to using a calculated field is to add a new column to the table and create a formula to perform the desired calculation. A calculated field must reside in the Values area of the pivot table. You can't use a calculated field in the Columns area, in the Rows area, or in the Filter area.

• A calculated item: Uses the contents of other items within a field of the pivot table. If your pivot table source is a worksheet table, an alternative to using a calculated item is to insert one or more rows and write formulas that use values in other rows. A calculated item must reside in the Columns area, Rows area, or Filters area of a pivot table. You can't use a calculated item in the Values area.

The formulas used to create calculated fields and calculated items aren't standard Excel formulas. In other words, you don't enter the formulas into cells. Rather, you enter these formulas in a dialog box, and they're stored along with the pivot table data.

The examples in this section use the worksheet table shown in Figure 34.13. The table consists of five columns and 48 rows. Each row describes monthly sales information for a particular sales representative. For example, Amy is a sales rep for the North region, and she sold 239 units in January for total sales of $23,040.

Figure 34.14 shows a pivot table created from the data. This pivot table shows Sales (Values area), cross-tabulated by Month (Rows area) and by SalesRep (Columns area).

Figure 34.13

This data demonstrates calculated fields and calculated items.

Figure 34.14

This pivot table was created from the sales data.

The examples that follow create

• A calculated field, to compute average sales per unit

• Four calculated items, to compute the quarterly sales commission

Creating a calculated field

Because a pivot table is a special type of range, you can't insert new rows or columns within the pivot table, which means that you can't insert formulas to perform calculations with the data in a pivot table. However, you can create calculated fields for a pivot table. A calculated field consists of a calculation that can involve other fields.

A calculated field is basically a way to display new information (derived from other fields) in a pivot table. It's an alternative to creating a new column field in your source data. In many cases, you may find it easier to insert a new column in the source range with a formula that performs the desired calculation. A calculated field is most useful when the data comes from a source that you can't easily manipulate — such as an external database.

In the sales example, suppose that you want to calculate the average sales amount per unit. You can compute this value by dividing the Sales field by the Units Sold field. The result shows a new field (a calculated field) for the pivot table.

Use the following procedure to create a calculated field that consists of the Sales field divided by the Units Sold field:

1. Select any cell within the pivot table.

2. Choose PivotTable Tools ⇒ Analyze ⇒ Calculations ⇒ Fields, Items & Sets ⇒ Calculated Field. The Insert Calculated Field dialog box appears.

3. Enter a descriptive name in the Name box and specify the formula in the Formula box (see Figure 34.15). The formula can use worksheet functions and other fields from the data source. For this example, the calculated field name is Average Unit Price, and the formula is

=Sales/'Units Sold'

4. Click Add to add this new field.

5. Click OK to close the Insert Calculated Field dialog box.

Figure 34.15

The Insert Calculated Field dialog box.

After you create the calculated field, Excel adds it to the Values area of the pivot table (and it also appears in the PivotTable Fields task pane). You can treat it just like any other field, with one exception: You can't move it to the Rows, Columns, or Filters areas. It must remain in the Values area.

Figure 34.16 shows the pivot table after adding the calculated field. The new field displayed Sum of Average Unit Price, but I shortened this label to Avg Price.

Figure 34.16

This pivot table uses a calculated field.

Inserting a calculated item

The preceding section describes how to create a calculated field. Excel also enables you to create a calculated item for a pivot table field. Keep in mind that a calculated field can be an alternative to adding a new field (column) to your data source. A calculated item, on the other hand, is an alternative to adding a new row to the data source — a row that contains a formula that refers to other rows.

In this example, you create four calculated items. Each item represents the commission earned on the quarter's sales, according to the following schedule:

• Quarter 1: 10% of January, February, and March sales

• Quarter 2: 11% of April, May, and June sales

• Quarter 3: 12% of July, August, and September sales

• Quarter 4: 12.5% of October, November, and December sales

To create a calculated item to compute the commission for January, February, and March, follow these steps:

1. Move the cell pointer to the Row Labels or Column Labels area of the pivot table and choose PivotTable Tools ⇒ Analyze  ⇒ Calculations ⇒ Fields, Items & Sets ⇒ Calculated Item. The Insert Calculated Item dialog box appears.

2. Enter a name for the new item in the Name field and specify the formula in the Formula field (see Figure 34.17). The formula can use items in other fields, but it can't use worksheet functions. For this example, the new item is named Qtr1 Commission, and the formula appears as follows:

=10%*(Jan+Feb+Mar)

3. Click Add.

4. Repeat Steps 2 and 3 to create three additional calculated items:

Qtr2 Commission: = 11%*(Apr+May+Jun)

Qtr3 Commission: = 12%*(Jul+Aug+Sep)

Qtr4 Commission: = 12.5%*(Oct+Nov+Dec)

5. Click OK to close the dialog box.

Figure 34.17

The Insert Calculated Item dialog box.

After you create the calculated items, they appear in the pivot table. Figure 34.18 shows the pivot table after adding the four calculated items. Notice that the calculated items are added to the end of the Month items. You can rearrange the items by selecting the cell and dragging its border. Another option is to create two groups: one for the sales numbers, and one for the commission calculations. Figure 34.19 shows the pivot table after creating the two groups and adding subtotals.

Figure 34.18

This pivot table uses calculated items for quarterly totals.

Figure 34.19

The pivot table, after creating two groups and adding subtotals.

Filtering Pivot Tables with Slicers

A Slicer is an interactive control that makes it easy to filter data in a pivot table. Figure 34.20 shows a pivot table with three Slicers. Each Slicer represents a particular field. In this case, the pivot table is displaying data for existing customers, opened by tellers at the Central branch.

Figure 34.20

Using Slicers to filter the data displayed in a pivot table.

The same type of filtering can be accomplished by using the field labels in the pivot table, but Slicers are intended for those who might not understand how to filter data in a pivot table. Slicers can also be used to create an attractive and easy-to-use interactive “dashboard.”

To add one or more Slicers to a worksheet, start by selecting any cell in a pivot table. Then choose Insert ⇒ Filter ⇒ Slicer. The Insert Slicers dialog box appears, with a list of all fields in the pivot table. Place a check mark next to the Slicers you want, and then click OK.

Slicers can be moved and resized, and you can change the look. To remove the effects of filtering by a particular Slicer, click the icon in the Slicer's upper-right corner.

To use a Slicer to filter data in a pivot table, just click a button. To display multiple values, press Ctrl while you click the buttons in a Slicer. Press Shift and click to select a series of consecutive buttons.

Figure 34.21 shows a pivot table and a pivot chart. Two Slicers are used to filter the data (by state and by month). In this case, the pivot table and pivot chart show only the data for Kansas, Missouri, and New York, for the months of January through March. Slicers provide a quick and easy way to create an interactive chart.

Figure 34.21

Using Slicers to filter a pivot table by state and by month.

Filtering Pivot Tables with a Timeline

A Timeline is conceptually similar to a Slicer, but this control is designed to simplify time-based filtering in a pivot table.

A Timeline is relevant only if your pivot table has a field that's formatted as a date. This feature does not work with times. To add a Timeline, select a cell in a pivot table and choose Insert ⇒ Filter ⇒ Timeline. A dialog box appears listing all date-based fields. If your pivot table doesn't have a field formatted as a date, Excel displays an error.

Figure 34.22 shows a pivot table created from the data in columns A:E. This pivot table uses a Timeline, set to allow date filtering by quarters. Click a button that corresponds to the quarter you want to view, and the pivot table is updated immediately. To select a range of quarters, press Shift while you click the buttons. Other filtering options (selectable from the drop-down in the upper-right corner) are Year, Month, and Day. In the figure, the pivot table displays data from the first two quarters of 2012.

Figure 34.22

Using a Timeline to filter a pivot table by date.

You can, of course, use both Slicers and a Timeline for a pivot table. A Timeline has the same type of formatting options as Slicers, so you can create an attractive interactive dashboard that simplifies pivot table filtering.

Referencing Cells within a Pivot Table

After you create a pivot table, you may want to create formulas that reference one or more cells within a pivot table. Figure 34.23 shows a simple pivot table that displays income and expense information for three years. In this pivot table, the Month field is hidden, so the pivot table shows the year totals.

Figure 34.23

The formulas in column F reference cells in the pivot table.

Column F contains formulas, and this column is not part of the pivot table. These formulas calculate the expense-to-income ratio for each year. I created these formulas by pointing to the cells. You may expect to see this formula in cell F3:

=D3/C3

In fact, the formula in cell F5 is

=GETPIVOTDATA(“Sum of Expenses”,$B$2,”Year”,2010)/GETPIVOTDATA(“Sum of Income”,$B$2,”Year”,2010)

When you use the pointing technique to create a formula that references a cell in a pivot table, Excel replaces those simple cell references with a much more complicated GETPIVOTDATA function. If you type the cell references manually (instead of pointing to them), Excel doesn't use the GETPIVOTDATA function. The reason? Using the GETPIVOTDATA function helps ensure that the formula will continue to reference the intended cells if the pivot table layout is changed.

Figure 34.24 shows the pivot table after expanding the years to show the month detail. As you can see, the formulas in column F still show the correct result even though the referenced cells are in a different location. Had I used simple cell references, the formula would return incorrect results after expanding the years.

Figure 34.24

After expanding the pivot table, formulas that use the GETPIVOTDATA function continue to display the correct result.

Creating Pivot Charts

A pivot chart is a graphical representation of a data summary displayed in a pivot table. If you're familiar with creating charts in Excel, you'll have no problem creating and customizing pivot charts. All Excel charting features are available in a pivot chart.

 I cover charting in Chapters 19 and 20.

Excel provides several ways to create a pivot chart:

• Select any cell in an existing pivot table and then choose PivotTable Tools ⇒ Analyze ⇒ Tools ⇒ PivotChart.

• Select any cell in an existing pivot table and then choose Insert ⇒ Charts ⇒ PivotChart.

• Choose Insert ⇒ Charts ⇒ PivotChart ⇒ PivotChart. If the cell pointer is not within a pivot table, Excel prompts you for the data source and creates a pivot chart.

• Choose Insert ⇒ Charts ⇒ Pivot Chart ⇒ PivotChart & PivotTable. Excel prompts you for the data source and creates a pivot table and a pivot chart. This command is available only when the cell pointer is not within a pivot table.

A pivot chart example

Figure 34.25 shows part of a table that tracks daily sales by region. The Date field 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.

Figure 34.25

This data will be used to create a pivot chart.

Although you can create a pivot chart without a pivot table, I find it much easier to create a pivot table first. Figure 34.26 shows the pivot table. The Date field is in the Rows area, and the daily dates have been grouped into months. The Region field is in the Columns area. The Sales field is in the Values area.

Figure 34.26

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 in a chart.

To create a pivot chart, select any cell in the pivot table and choose PivotTable Tools ⇒ Analyze ⇒ Tools ⇒ PivotChart. The Insert Chart dialog box appears, from which you can choose a chart type. For this example, select a Line With Markers chart and then click OK. Excel creates the pivot chart shown in Figure 34.27. The chart makes it easy to see an upward sales trend for the Western division, a downward trend for the Southern division, and relatively flat sales for the Eastern division.

A pivot chart includes field buttons that let you filter the chart's data. To remove some or all of the field buttons, select the pivot chart and use the Field Buttons control in the PivotChart Tools ⇒ Analyze ⇒ Show/Hide group.

Figure 34.27

The pivot chart uses the data displayed in the pivot table.

When you select a pivot chart, the Ribbon displays a new contextual tab: PivotChart Tools. The commands in the Design and Format tabs are virtually identical to those for a standard Excel chart, so you can manipulate the pivot chart any way you like.

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

Figure 34.28

If you modify the pivot table, the pivot chart is also changed.

More about pivot charts

Keep in mind these points when using pivot charts:

• A pivot table and a pivot chart are joined in a two-way link. If you make structural or filtering changes to one, the other is also changed.

• When you activate a pivot chart, the PivotTable Fields task pane changes to the PivotChart Fields task pane. In this task pane, Legend (Series) replaces the Columns area, and Axis (Category) replaces the Rows area.

• The field buttons in a pivot chart contain the same controls as the pivot chart's field headers. These controls allow you to filter the data that's displayed in the pivot table and pivot chart. If you make changes to the pivot chart using these buttons, those changes are also reflected in the pivot table.

• If you have a pivot chart linked to a pivot table and you delete the underlying pivot table, the pivot chart remains. The pivot chart's Series formulas contain the original data, stored in arrays.

• By default, pivot charts are embedded in the sheet that contains the pivot table. To move the pivot chart to a different worksheet (or to a Chart sheet), choose PivotChart Tools ⇒ Analyze ⇒ Actions ⇒ Move Chart.

• You can create multiple pivot charts from a pivot table, and you can manipulate and format the charts separately. However, all the charts display the same data.

• A normal chart, when selected, displays the icons to the right: Chart Elements, Chart Styles, and Chart Filters. A pivot chart does not display the Chart Filters icon.

• Slicers and Timelines also work with pivot charts. See the examples earlier in this chapter.

• Don't forget about themes. You can choose Page Layout ⇒ Themes ⇒ Themes to change the workbook theme, and your pivot table and pivot chart will both reflect the new theme.

Another Pivot Table Example

The pivot table example in this section demonstrates some useful ways to work with pivot tables.

Figure 34.29 shows part of a table with 3,144 data rows, one for each county in the United States. The fields are

Figure 34.29

This table contains data for each county in the United States.

• County: The name of the county

• State Name: The state of the county

• Region: The region (Roman numeral ranging from I to X)

• Census 2000: The population of the county, according to the 2000 Census

• Census 1990: The population of the county, according to the 1990 Census

• LandArea: The area, in square miles (excluding water-covered area)

• WaterArea: The area, in square miles, covered by water

Figure 34.30 shows a pivot table created from the county data. The pivot table uses the Region and State Name fields for the Rows section and uses Census 2000 and Census 1990 in the Values section.

Figure 34.30

This pivot table was created from the county data.

I created three calculated fields to display additional information:

• Change (displayed as Pop Change): The difference between Census 2000 and Census 1990

• Pct Change (displayed as Pct Pop Change): The population change expressed as a percentage of the 1990 population

• Density (displayed as Pop/Sq Mile): The population per square mile of land

Figure 34.31

This worksheet lists calculated fields and items for the pivot table.

This pivot table is sorted on two columns. The main sort is by Region, and states within each region are sorted alphabetically. To sort, just select a cell that contains a data point to be included in the sort. Right-click and choose from the shortcut menu.

Sorting by Region requires some additional effort because Roman numerals are not in alphabetical order. Therefore, I had to create a custom list. To create a custom sort list, access the Excel Options dialog box, select the Advanced tab, and click Edit Custom Lists. Click New List, type your list entries, and click Add. Figure 34.32 shows the custom list I created for the region names.

Figure 34.32

This custom list ensures that the Region names are sorted correctly.

Producing a Report with a Pivot Table

By using a pivot table, you can convert a huge table of data into an attractive printed report. Figure 34.33 shows a small portion of a pivot table that I created from a table that has more than 67,000 rows of data. This data happens to be my digital music collection, and each row contains information about a single music file: the genre, the artist name, the track name, the album name, the duration, and the file size. I chose this example because it's the largest file I have that's actually interesting.

Figure 34.33

Part of a 161-page pivot table report.

The pivot table report created from this data is 161 pages long, and it took about five minutes to set up (and only a little longer to fine-tune it).

Here's a quick summary of how I created this report:

1. I selected a cell in the table and chose Insert ⇒ Tables ⇒ PivotTable. The Create PivotTable dialog box appeared.

2. I clicked OK to accept the default settings.

3. In the new worksheet, I used the PivotTable Fields task pane and dragged the following fields to the Rows area: Genre, Artist, and Album.

4. I dragged the following fields to the Values area: Title, Size, and Duration.

5. I used the Data Field Settings dialog box to summarize Title as Count, Size as Sum, and Duration as Sum.

6. I wanted the information in the Size column to display in megabytes (not kilobytes), so I formatted the column using this custom number format:

###,###, “Mb”;;

7. I wanted the information in the Duration column to display as hours, minutes, and seconds, so I formatted the column using this custom number format:

[h]:mm:ss;;

8. I edited the column headings. For example, I replaced Count of Track with Tracks.

9. I changed the layout to outline format by choosing PivotTable Tools ⇒ Design ⇒ Layout ⇒ Report Layout ⇒ Show in Outline Form.

10. I turned off the field headers by choosing PivotTable Tools ⇒ Analyze ⇒ Show ⇒ Field Headers.

11. I turned off the buttons by choosing PivotTable Tools ⇒ Analzye ⇒ Show ⇒ +/– Buttons.

12. I applied a built-in style by choosing PivotTable Tools ⇒ Design ⇒ PivotTable Styles.

13. I went into Page Layout view and adjusted the column widths so that the report would fit horizontally on the page.

14. I added to Slicers (Genre and Artist) to make it easy to display specific music types.

Note that you can expand and contract fields. For example, to hide the albums under each artist, select any artist and choose PivotTable Tools ⇒ Analyze ⇒ Active Field ⇒ Collapse Field. To hide all the artist names (and just display the genres), select any genre cell and choose PivotTable Tools ⇒ Analyze ⇒ Active Field ⇒ Collapse Field. Use the Expand Field command to unhide hidden fields.

Using the Data Model

So far, this chapter has focused exclusively on pivot tables that are created from a single table of data. A feature called the Data Model brings new power to pivot charts. With the Data Model, you can use multiple tables of data in a single pivot table. You'll need to create one or more “table relationships” so the data can be tied together.

Figure 34.34 shows parts of three tables that are in a single workbook (each sheet is in its own worksheet and is shown in a separate window). The tables are named Orders, Customers, and Regions. The Orders table contains information about product orders. The Customers table contains information about the company's customers. The Regions table contains a region identifier for each state.

Figure 34.34

These three tables will be used for a pivot table, using the Data Model.

Notice that the Orders and Customers tables have a CustomerID column in common, and the Customers and Regions tables have a State column in common. The common columns will be used to form relationship among the tables.

Notice that these are “one-to-many” relationships. For every row in the Orders table, there is exactly one corresponding row in the Customers table, and that row is determined by the CustomerID column. Similarly, for every row in the Customers table, there is exactly one corresponding row in the Regions table, and that row is determined by the State column.

For this example, the goal is to summarize sales by state, by region, and by year. Notice that the sales and date information is in the Order table, the state information is in the Customers table, and the region names are in the Regions table. Therefore, all three tables will be used for this pivot table.

Start by creating a pivot table (in a new worksheet) from the Orders table. Follow these steps:

1. Select any cell within the table and choose Insert ⇒ Tables ⇒ Pivot Tables. The Create PivotTable dialog box appears.

2. Select the Add This Data to the Data Model check box. Notice that the PivotTable Fields task pane is a bit different when you're working with the Data Model. The task pane contains two tabs: Active and All. The Active tab lists only the Orders table. The All tab lists all the tables in the workbook. To make things easier, activate the PivotTable Fields task pane, right-click the Customers table, and choose Show in Active Tab. Then do the same for the Regions table.

Figure 34.35 shows the Active tab of the PivotTable Fields task pane, with all three tables expanded to show their column headers. Notice that I also changed the configuration of this task pane by using the drop-down Tools control. I chose Fields Section and Areas Section Side-by-Side.

The next step is to set up the relationships among the tables.

3. Choose PivotTable Tools ⇒ Analyze ⇒ Calculations ⇒ Relationships. The Manage Relationships dialog box appears.

4. Click the New button. The Create Relationship dialog box appears.

5. For the Table, specify Orders, and for the Foreign Column specify Customer ID; for the Related Table, specify Customers and for the Related Column (Primary) specify CustomerID (see Figure 34.36).

Figure 34.35

The PivotTable Fields task pane, with three active tables.

Figure 34.36

Creating a relationship between two tables.

6. Click OK to return to the Manage Relationships dialog box.

7. Click New again and set up a relationship between the Customers table and the Regions table. Both will use the State column. The Manage Relationships dialog box will now show two relationships.

8. With the table relationship established, it's simply a matter of dragging the field names to the appropriate section of the PivotTable Fields task pane:

• Drag the Total field to the Values area.

• Drag the Year field to the Columns area.

• Drag the Region field to the Rows area.

• Drag the StateName field to the Rows area.

Figure 34.37 shows part of the pivot table. I added two slicers to enable filtering the table by customers who are on the mailing list, and by product.

Figure 34.37

The pivot table, after adding two slicers.

Learning More about Pivot Tables

The two pivot table chapters in this book provide a good introduction, and most users should have enough knowledge to create and modify pivot tables and pivot charts. But these chapters barely scratch the surface. Excel's pivot table feature could easily be the topic for an entire book.

Two relevant topics I haven't covered are

• Using external data sources: All the examples in this book use data stored in an Excel workbook. You can also create pivot tables from external databases.

• The PowerPivot add-in: Enables you to integrate large external databases and create “business intelligence” reports and dashboards. PowerPivot works independently of Excel's built-in pivot table features.