About the companion content
The download files for this book include all of the data sets used to produce the book, so you can practice the concepts in the book. You can download this book’s companion content from the following page:
MicrosoftPressStore.com/Excel2019PivotTable/downloads
Pivot tables have only one hard rule pertaining to the data source: The data source must have column headings, which are labels in the first row of the data that describe the information in each column. Without column headings, you cannot create a pivot table report.
Originally called Power Query was later rebranded as Get & Transform Data.
You can also press the shortcut to start a pivot table: Press and release Alt, press and release N, and then press and release V.
Note the presence of another option in the Create PivotTable dialog box shown in Figure 2-13: the Add This Data To The Data Model option. You would select this option if you were trying to consolidate multiple data sources into a single pivot table.
If clicking on the pivot table does not activate the PivotTable Fields list, you can manually activate it by right-clicking anywhere inside the pivot table and selecting Show Fields list. You can also click anywhere inside the pivot table and then choose the large Fields List icon on the Analyze tab under PivotTable Tools in the ribbon.
Before you start dropping fields into the various areas, answer two questions:
• “What am I measuring?”
• “How do I want to see it?”
The answer to the first question tells you which fields in your data source you need to work with, and the answer to the second question tells you where to place the fields.
How Does Excel Know Where Your Fields Go?
As you’ve just experienced, the PivotTable Fields list interface enables you to add fields to your pivot table by simply selecting the check box next to each field name. Excel automatically adds the selected fields to the pivot table. But how does Excel know which area to use for a field you select? The answer is that Excel doesn’t really know which area to use, but it makes a decision based on data type. Here’s how it works: When you select a check box next to a field, Excel evaluates the data type for that field. If the data type is numeric, Excel places the field into the Values area; otherwise, Excel places the field into the Rows area. This placement obviously underlines the importance of correctly assigning the data types for your fields.
If you have someone who clears out cells by mashing down the spacebar several times instead of using the Delete key, Excel will treat those spaces as text. A mix of numbers and text in a column will cause Excel to default to counting the column instead of summing.
The action of updating your pivot cache by taking another snapshot of your data source is called refreshing your data. There are two reasons you might have to refresh your pivot table report:
• Changes have been made to your existing data source.
• Your data source’s range has been expanded with the addition of rows or columns.
Note that if you format your pivot table source data as a table using Home, Format As Table or Ctrl+T, the Pivot Table Source range will automatically expand as the data grows. You will still have to click Refresh to pick up the new rows.
In legacy versions of Excel, when you created a pivot table using a data set that was already being used in another pivot table, Excel actually gave you the option of using the same pivot cache. However, Excel 2019 does not give you such an option.
Instead, each time you create a new pivot table in Excel 2019, Excel automatically shares the pivot cache. Most of the time, this is beneficial: You can link as many pivot tables as you want to the same pivot cache with a negligible increase in memory and file size.
On the flip side, when you group one pivot table by month and year, all the pivot tables are grouped in a similar fashion. If you want one pivot table by month and another pivot table by week, you have to force a separate pivot cache. You can force Excel to create a separate pivot cache by taking the following steps:
1. Press Alt+D, release, and then press P to launch the PivotTable Wizard.
2. Click the Next button to get past the first screen of the wizard.
3. On the second screen, select the range for your pivot table and click the Next button.
4. Excel displays a wordy message saying that you can use less memory if you click Yes. Instead, click No.
5. On the next screen, click the Finish button.
At this point, you have a blank pivot table that pulls from its own pivot cache.
It’s important to note that there are a few side effects to sharing a pivot cache. For example, suppose you have two pivot tables using the same pivot cache. Certain actions affect both pivot tables:
• Refreshing your data—You cannot refresh one pivot table and not the other. Refreshing affects both tables.
• Adding a calculated field—If you create a calculated field in one pivot table, your newly created calculated field shows up in the PivotTable Fields list of the other pivot table.
• Adding a calculated item—If you create a calculated item in one pivot table, it shows in the other as well.
• Grouping or ungrouping fields—Any grouping or ungrouping you perform affects both pivot tables. For instance, suppose you group a date field in one pivot table to show months. The same date field in the other pivot table is also grouped to show months.
Although none of these side effects are critical flaws in the concept of sharing a pivot cache, it is important to keep them in mind when determining whether using a pivot table as your data source is the best option for your situation.
If you find yourself always making the same changes to a pivot table, consider making that change in the pivot table defaults.
Follow these steps to change this setting for the current pivot table:
1. Right-click any cell in the pivot table and choose PivotTable Options.
2. On the Layout & Format tab in the Format section, type 0 next to the field labeled For Empty Cells Show (see Figure 3-5).
FIGURE 3-5 Enter a zero in the For Empty Cells Show box to replace the blank cells with zero.
1. Click OK to accept the change.
The result is that the pivot table is filled with zeros instead of blanks.
When you attempt to expand the innermost field, Excel offers to add a new innermost field.
Caution
When you arrange several pivot tables vertically, as in Figure 3-19, you’ll notice that changes in one pivot table change the column widths for the entire column, often causing #### to appear in the other pivot tables. By default, Excel changes the column width to AutoFit the pivot table but ignores anything else in the column. To turn off this default behavior, right-click each pivot table and choose PivotTable Options. In the first tab of the Options dialog box, the second-to-last check box is AutoFit Column Widths On Update. Clear this check box.
As you’ve seen in these pages, I rarely use the Compact form for a pivot table. I use Pivot Table Defaults to make sure my pivot tables start in Tabular layout instead of Compact layout.
Although slicers are now the darlings of the pivot table report, the good old-fashioned report filter can still do one trick that slicers cannot do. Say you have created a report that you would like to share with the industry managers. You have a report showing customers with revenue and profit. You would like each industry manager to see only the customers in their area of responsibility.
Follow these steps to quickly replicate the pivot table:
1. Make sure the formatting in the pivot table looks good before you start. You are about to make several copies of the pivot table, and you don’t want to format each worksheet in the workbook, so double-check the number formatting and headings now.
2. Add the Sector field to the Filters area. Leave the Sector filter set to (All).
3. Select one cell in the pivot table so that you can see the Analyze tab in the ribbon.
4. Find the Options button in the left side of the Analyze tab. Next to the Options tab is a drop-down menu. Don’t click the big Options button. Instead, open the drop-down menu (see Figure 4-31).
FIGURE 4-31 Click the tiny drop-down arrow next to the Options button.
5. Choose Show Report Filter Pages. In the Show Report Filter Pages dialog box, you see a list of all the fields in the report area. Because this pivot table has only the Sector field, this is the only choice (see Figure 4-32).
FIGURE 4-32 Select the field by which to replicate the report.
6. Click OK and stand back.
Excel inserts a new worksheet for every item in the Sector field. On the first new worksheet, Excel chooses the first sector as the filter value for that sheet. Excel renames the worksheet to match the sector. Figure 4-33 shows the new Consulting worksheet, with neighboring tabs that contain Museums, Retail, Training, and Utilities.
FIGURE 4-33 Excel quickly adds one page per sector.
If you have daily dates that include an entire year or that fall in two or more years, Excel 2019 groups the daily dates to include years, quarters, and months. If you need to report by daily dates, you will have to select any date cell, choose Group Field, and add Days.
A calculated field is a data field you create by executing a calculation against existing fields in the pivot table. Think of a calculated field as a virtual column added to your data set. This column takes up no space in your source data, contains the data you define with a formula, and interacts with your pivot data as a field—just like all the other fields in your pivot table.
A measure is a calculated field created in a Data Model pivot table using the Data Analysis Expressions (DAX) formula language.
A calculated item is a data item you create by executing a calculation against existing items within a data field. Think of a calculated item as a virtual row of data added to your data set. This virtual row takes up no space in your source data and contains summarized values based on calculations performed on other rows in the same field. Calculated items interact with your pivot data as data items—just like all the other items in your pivot table.
The order of operations for Excel is as follows:
• Evaluate items in parentheses.
• Evaluate ranges (