Microsoft Excel 2016 BIBLE (2016)
Part V
Analyzing Data with Excel
Chapter 34
Analyzing Data with Pivot Tables
IN THIS CHAPTER
1. Creating a pivot table from nonnumeric data
2. Grouping items in a pivot table
3. Creating a calculated field or a calculated item in a pivot table
4. Understanding the Data Model feature
5. Creating an attractive report using a pivot table
The previous chapter introduced pivot tables. There, I presented 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 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 Nonnumeric Data
Most pivot tables are created from numeric data, but pivot tables are also useful with some types of nonnumeric data. Because you can't sum nonnumbers, 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 (in range E2:H10) 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.
A workbook that demonstrates the pivot table created from nonnumeric data is available on this book's website at www.wiley.com/go/excel2016bible. The file is named employee list.xlsx
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.
Note
The Employee field is not used. This example uses the Location field for the Values section, but you can actually use any of the three fields because the pivot table is displaying a count.
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.
Note
If you plan on creating multiple pivot tables that use different groupings, make sure you read the sidebar “Multiple Groups from the Same Data Source.”
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 in the pivot table. 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. 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.
Figure 34.4 Pivot tables with options for subtotals and grand totals.
A workbook that contains these grouping examples is available on this book's website at www.wiley.com/go/excel2016bible. The file is named grouping examples.xlsx.
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 731 rows and covers the dates between January 1, 2015, and December 31, 2016. The goal is to summarize the sales information by month.
Figure 34.5 You can use a pivot table to summarize the sales data by month.
A workbook demonstrating how to group pivot table items by date is available on this book's website at www.wiley.com/go/excel2016bible. The file is grouping sales by date.xlsx.
Figure 34.6 shows part of a pivot table (in Columns D:E) 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.
Note
If you select only Months in the By list box in the Grouping dialog box, months in different years combine. For example, the January item would display the sum of sales for 2015 and 2016.
Figure 34.9 shows another view of the data, grouped by quarter and by year.
Figure 34.9 This pivot table shows sales by quarter and by year.
Multiple Groups from the Same Data Source
If you create multiple pivot tables from the same data source, you may have noticed that grouping a field in one pivot table affects the other pivot tables. Specifically, all the other pivot tables automatically use the same grouping. Sometimes, this is exactly what you want. Other times, it's not at all what you want. For example, you might like to see two pivot table reports: one that summarizes data by month and year, and another that summarizes the data by quarter and year.
Grouping affects other pivot tables because all the pivot tables are using the same pivot table “cache.” Unfortunately, there is no direct way to force a pivot table to use a new cache. But there is a way to trick Excel into using a new cache. The trick involves giving multiple range names to the source data.
For example, name your source range Table1, and then give the same range a second name: Table2. The easiest way to name a range is to use the Name box, to the left of the Formula bar. Select the range, type a name in the Name box, and press Enter. Then, with the range still selected, type a different name, and press Enter. Excel will display only the first name, but you can verify that both names exist by choosing Formulas Define Names Name Manager.
When you create the first pivot table, specify Table1 as the Table/Range. When you create the second pivot table, specify Table2 as the Table/Range. Each pivot table will use a separate cache, and you can create groups in one pivot table, independent of the other pivot table.
You can use this trick with existing pivot tables. Make sure that you give the data source a different name. Then select the pivot table and choose PivotTable Tools Analyze Data Change Data Source. In the Change PivotTable Data Source dialog box, type the new name that you gave to the range. This will cause Excel to create a new pivot cache for the pivot table.
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 (in columns D:G) summarizes the data by hour.
Figure 34.10 This pivot table is grouped by hours.
This workbook, named time-based grouping.xlsx, is available on this book's website at www.wiley.com/go/excel2016bible
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, “Creating Formulas That Count and Sum”), 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.
This workbook, named frequency distribution.xlsx, is available on this book's website at www.wiley.com/go/excel2016bible.
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.
Note
By default, Excel does not display items with a count of zero. In this example, no test scores are less than 21, so the 1–10 and 11–20 items were hidden when I created the pivot table. To force the display of empty bins, right-click any cell and choose Field Settings from the shortcut menu. In the Field Settings dialog box, click the Layout & Print tab, and select Show Items with No Data.
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.
Note
This example uses the Excel Grouping dialog box to create the groups automatically. If you don't want to group in equal-sized bins, you can create your own groups. For example, you may want to assign letter grades based on the test score. Select the rows for the first group, right-click, and then choose Group from the shortcut menu. Repeat these steps for each additional group. Then replace the default group names with more meaningful names.
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 the way 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 into 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 5 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.13 This data demonstrates calculated fields and calculated items.
A workbook demonstrating calculated fields and items is available on this book's website at www.wiley.com/go/excel2016bible. The file is named calculated fields and items.xlsx.
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.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. Acalculated 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'
Figure 34.15 The Insert Calculated Field dialog box.
4. Click Add to add this new field.
5. Click OK to close the Insert Calculated Field dialog box.
Note
You can create the formula manually by typing it or by double-clicking items in the Fields list box. Double-clicking an item transfers it to the Formula field. Because the Units Sold field contains a space, Excel adds single quotes around the field name.
After you create the calculated field, Excel adds it to the Values area of the pivot table. (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 mustremain 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.
Tip
The formulas that you develop can also use worksheet functions, but the functions can't refer to cells or named ranges.
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
Note
Modifying the source data to obtain this information would require inserting 16 new rows, each with formulas (four formulas for each sales rep). So, for this example, creating four calculated items may be an easier task.
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)
Figure 34.17 The Insert Calculated Item dialog box.
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.
Note
A calculated item, unlike a calculated field, does not appear in the PivotTable Fields task pane. Only fields appear in the field list.
Caution
If you use a calculated item in your pivot table, you may need to turn off the Grand Total display for columns to avoid double counting. In this example, the Grand Total includes the calculated items, so the commission amounts are included with the sales amounts. To turn off Grand Totals, choose PivotTable Tools Design Layout Grand Totals.
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 (manually): 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.
A Reverse Pivot Table
The Excel Pivot Table feature creates a summary table from a list. But what if you want to perform the opposite operation? Often, you may have a two-way summary table, and it would be convenient if the data were in the form of a normalized list.
In the accompanying figure, range A1:E13 contains a summary table with 48 data points. Notice that this summary table is similar to a pivot table. Column G:I shows part of a 48-row table that was derived from the summary table. In other words, every value in the original summary table is converted to a row, which also contains the region name and month. This type of table is useful because it can be sorted and manipulated in other ways. And you can create a pivot table from this transformed table.
The companion website contains a workbook, reverse pivot.xlsm, which has a VBA macro that will convert any two-way summary table into a three-column normalized table.
Another way to perform this type of transformation is to use Get & Transform. See Chapter 38, “Working with Get & Transform,” for an example.
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 representing a particular field. In this case, the pivot table is displaying data for new and existing customers, opened by tellers at the North County 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 “clear filter” 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.
New
Excel 2016 adds a new icon to slicers. Click the Multi-Select icon, and you don't need to press Ctrl to select multiple values in a slicer. This feature is intended primarily for touch-screen users.
Figure 34.21 shows a pivot table and a pivot chart. TwosSlicers 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.
This workbook, named pivot table slicers.xlsx, is available on this book's website at www.wiley.com/go/excel2016bible
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 2015.
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 it. 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.
This workbook, named pivot table referencing.xlsx, is available on this book's website.
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 F3 is
=GETPIVOTDATA("Sum of Expenses",$B$2,"Year",2014)/GETPIVOTDATA("Sum of Income",$B$2,"Year",2014)
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. Why use the GETPIVOTDATA function? 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.
Caution
Using the GETPIVOTDATA function has one potential problem: the data that it retrieves must be visible. If you modify the pivot table so that the value used by GETPIVOTDATA is no longer visible, the formula returns an error.
Tip
If, for some reason, you want to prevent Excel from using the GETPIVOTDATA function when you point to pivot table cells when creating a formula, choose PivotTable Tools Analyze PivotTable Options Generate GetPivot Data. This command is a toggle.
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 Chapter 19, “Getting Started Making Charts,” and Chapter 20, “Learning Advanced Charting.”
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.
This workbook, named sales by region pivot chart.xlsx, is available on this book's website at www.wiley.com/go/excel2016bible.
Figure 34.26 shows a pivot table created from this data. 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.
Figure 34.27 The pivot chart uses the data displayed in the pivot table.
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.
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
This workbook, named county data.xlsx, is available on this book's website at www.wiley.com/go/excel2016bible.
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
Tip
To view (or document) calculated fields and calculated items in a pivot table, choose PivotTable Tools Analyze Calculations Fields, Items & Sets List Formulas. Excel inserts a new worksheet with information about your calculated fields and items. Figure 34.31 shows an example.
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.
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 tables. 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.
Note
The Data Model was introduced in Excel 2013, so workbooks that use this feature are not compatible with previous versions.
Figure 34.33 shows parts of three tables that are in a single workbook. (Each table 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.33 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 relationships among the tables.
These relationships are “one-to-many.” 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.
The example in this section is available on this book's website at www.wiley.com/go/excel2016bible. The workbook is named data model.xlsx.
Note
A pivot table created using the Data Model has some restrictions, as opposed to a pivot table created from a single table. Most notably, you can't create groups. In addition, you can't create calculated fields or calculated items.
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 to generate 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 Table. 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, click All, 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.34 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.
Figure 34.34 The PivotTable Fields task pane, with three active tables.
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 (see Figure 34.35).
Figure 34.35 Creating a relationship between the tables.
Excel determined the relationships based on the field names. If your tables uses different field names, you can specify the relationships manually, using the New button.
4. Click Close to close the Manage Relationships dialog box.
Note
If you don't set up the table relationships in advance, Excel will prompt you to do so when you add a field to the pivot table that's from a different table than you started with.
5. 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 (from the Orders table) to the Values area.
· Drag the Year field (from the Orders table) to the Columns area.
· Drag the Region field (from the Regions table) to the Rows area.
· Drag the StateName field (from the Regions table) to the Rows area.
Figure 34.36 shows part of the pivot table. I added two slicers to enable filtering the table by customers who are on the mailing list, and filtering by product.
Figure 34.36 The pivot table, after adding two slicers.
Tip
When you create a pivot chart using the Data Model, you can convert the pivot table to formulas. Select any cell in the pivot table and choose PivotTable Tools Analyze Calculations OLAP Tools Convert to Formulas. The pivot table is replaced by cells that use formulas. These formulas use CUBEMEMBER and CUBEVALUE functions. Although the range is no longer a pivot table, the formulas update when the data changes.
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: This 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. This add-in works only with enterprise versions of Excel 2016.
<p><strong><u>Figure 33.14</u></strong> This pivot table shows new account totals by day of the week.</p>
<p>· The Weekday field is in the Rows section.</p>
<p>· The Amount field is in the Values section and is summarized by Sum.</p>
<p>I added conditional formatting data bars to make it easier to see how the days compare. As you see, the largest deposit days are Fridays.</p>
<p><img id="Рисунок 623" src="excel_9.files/image268.jpg" alt="image" width="104" height="56" border="0" />See <u>Chapter 21</u>, “Visualizing Data Using Conditional Formatting,” for more information about conditional formatting.</p>
<p><strong>How many accounts were opened at each branch, broken down by account type?</strong></p>
<p><u>Figure 33.15</u> shows a pivot table that answers this question.</p>
<p><img id="Рисунок 622" src="excel_9.files/image506.jpg" alt="A pivot table presenting a summary of CD, Checking, IRA, and Savings in Central, North County, and Westside branches. Grand totals per branch is on column F and grand totals per account type on row 7." width="574" height="185" border="0" /></p>
<p><strong><u>Figure 33.15</u></strong> This pivot table uses the Count function to summarize the data.</p>
<p>· The AcctType field is in the Columns section.</p>
<p>· The Branch field is in the Rows section.</p>
<p>· The Amount field is in the Values section and is summarized by Count.</p>
<p>So far, all the pivot table examples have used the Sum summary function. In this case, I changed the summary function to Count. To change the summary function to Count, right-click any cell in the Values area and choose Summarize Values By <img id="Рисунок 621" src="excel_9.files/image298.jpg" alt="image" width="16" height="12" border="0" /> Count from the shortcut menu.</p>
<p><strong>What's the dollar distribution of the different account types?</strong></p>
<p><u>Figure 33.16</u> shows a pivot table that answers this question. For example, 253 (or 35.53%) of the new accounts were for an amount of $5,000 or less.</p>
<p><img id="Рисунок 620" src="excel_9.files/image507.jpg" alt="Image described by caption and surrounding text." width="333" height="345" border="0" /></p>
<p><strong><u>Figure 33.16</u></strong> This pivot table counts the number of accounts that fall into each value range.</p>
<p>This pivot table is unusual because it uses only one field: Amount.</p>
<p>· The Amount field is in the Rows section (grouped, to show dollar ranges).</p>
<p>· The Amount field is also in the Values section and is summarized by Count.</p>
<p>· A third instance of the Amount field is the Values section, summarized by Count and summarized by Percent of Column Total.</p>
<p>When I initially added the Amount field to the Rows section, the pivot table showed a row for each unique dollar amount. To group the values, I right-clicked one of the Row labels and chose Group from the shortcut menu. Then I used the Grouping dialog box to set up bins of $5,000 increments. Note that the Grouping dialog box does not appear if you select more than one Row label.</p>
<p>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 <img id="Рисунок 619" src="excel_9.files/image300.jpg" alt="image" width="16" height="12" border="0" /> Count from the shortcut menu.</p>
<p>I added another instance of Amount to the Values section, and I set it up to display the percentage. I right-clicked a value in column C and chose Show Values As <img id="Рисунок 618" src="excel_9.files/image298.jpg" alt="image" width="16" height="12" border="0" /> % of Column Total. This option is also available in the Show Values As tab of the Value Field Settings dialog box.</p>
<p><strong>What types of accounts do tellers open most often?</strong></p>
<p>The pivot table in <u>Figure 33.17</u> shows that the most common account opened by tellers is a checking account.</p>
<p><img id="Рисунок 617" src="excel_9.files/image508.jpg" alt="Image described by caption and surrounding text." width="444" height="197" border="0" /></p>
<p><strong><u>Figure 33.17</u></strong> This pivot table uses a filter to show only the teller data.</p>
<p>· The AcctType field is in the Rows section.</p>
<p>· The OpenedBy field is in the Filters section.</p>
<p>· The Amount field is in the Values section (summarized by Count).</p>
<p>· A second instance of the Amount field is in the Values section (summarized by % of Column Total).</p>
<p>This pivot table uses the OpenedBy field as a filter and is showing the data only for tellers. I sorted the rows so that the largest value is at the top, and I used conditional formatting to display data bars for the percentages.</p>
<p><img id="Рисунок 616" src="excel_9.files/image268.jpg" alt="image" width="104" height="56" border="0" />See <u>Chapter 21</u> for more information about conditional formatting.</p>
<p><strong>In which branch do tellers open the most checking accounts for new customers?</strong></p>
<p><u>Figure 33.18</u> shows a pivot table that answers this question. At the Central branch, tellers opened 23 checking accounts for new customers.</p>
<p><img id="Рисунок 615" src="excel_9.files/image509.jpg" alt="A pivot table with three filters: Customer filter is set to New, OpenedBy filter to Teller, and Acct Type filter to Checking." width="321" height="214" border="0" /></p>
<p><strong><u>Figure 33.18</u></strong> This pivot table uses three filters.</p>
<p>· The Customer field is in the Filters section.</p>
<p>· The OpenedBy field is in the Filters section.</p>
<p>· The AcctType field is in the Filters section.</p>
<p>· The Branch field is in the Rows section.</p>
<p>· The Amount field is in the Values section, summarized by Count.</p>
<p>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.</p>
<p><strong>Learning More</strong></p>
<p>The examples in this chapter should give you an appreciation for the power and flexibility of Excel pivot tables. The next chapter digs a bit deeper and covers some advanced features — with lots of examples.</p>