IN THIS CHAPTER
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.
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.
Here are the PivotTable Fields task pane settings I used for this pivot table:
Count
.Figure 34.2 shows the pivot table after making some additional changes.
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:
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).
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.
When a field contains numbers, dates, or times, Excel can create groups automatically. The two examples in this section demonstrate automatic grouping.
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.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.
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.
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.9 shows another view of the data, grouped by quarter and by year.
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.
Here are the settings I used for this pivot table:
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).
The pivot table is simple:
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.
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:
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.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).
The examples that follow create
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:
=Sales/'Units Sold'
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 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.
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:
To create a calculated item to compute the commission for January, February, and March, follow these steps:
=10%*(Jan+Feb+Mar)
Qtr2 Commission: = 11%*(Apr+May+Jun)
Qtr3 Commission: = 12%*(Jul+Aug+Sep)
Qtr4 Commission: = 12.5%*(Oct+Nov+Dec)
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.
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.
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.
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.
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.
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.
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.
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.
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.
Excel provides several ways to create a pivot chart:
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.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.
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.
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.
Keep in mind these points when using pivot charts:
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.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.
I created three calculated fields to display additional information:
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.
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.
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.
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.
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:
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.
The next step is to set up the relationships among 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.
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.
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