15. Using Pivot Tables to Analyze Data
A pivot table enables you to summarize thousands or millions of records of data to a one-page summary in just a few clicks.
Suppose you have 400,000 records of transactional data. It is easy for some people to look at this and figure out that it represents $x million. But to learn some things about the data, you need to do some more analysis to spot trends in the data. A pivot table enables you to analyze trends in data without having to worry about formulas.
By using a pivot table, it is possible to create a number of views of your data, including the following:
Breakdown of sales by product
Sales by month, this year versus last year
Percentage of sales by customer
Customers who bought XYZ product in the East region
Sales by product by month
Top five customers with products
Of course, these are just examples. You can use pivot tables to slice and dice your data in almost any imaginable way.
Pivot tables were introduced in Excel 95 and have been evolving ever since:
Excel 2016 includes a feature to automatically roll date fields up to months, quarters, and years.
The add-in formerly known as Power Map is now built in to Excel 2016 as 3D Maps.
Excel 2013 added a new entry point for pivot tables called Recommended Pivot Tables. This feature shows you various thumbnails of pivot tables before you begin creating one.
Excel 2013 added the capability to create a data model from several different tables. You can create a relationship between tables without using VLOOKUPs and base pivot tables on the model.
Timelines are a visual date filter introduced in Excel 2013. They join slicers, the visual filter introduced in Excel 2010. The best feature of timelines and slicers is the capability for them to drive multiple pivot tables built from the same data set.
Power View and PowerPivot are powerful add-ins for Excel 2016 that enhance pivot tables. If you are using Excel 2016 Pro Plus or Office 365 Pro Plus or later, you have access to these add-ins. PowerPivot enhances the ability to build multitable models and provides key performance indicators (KPIs) and the DAX formula language. Power View animates pivot charts over time.
Excel 2010 introduced new calculations such as Rank, Percent of Parent, and Running Percentage of Total.
Excel 2010 introduced the option to replace blanks in the outer row fields by repeating item labels from above.
Excel 2007 simplified the pivot table interface and added new filters.
Creating Your First Pivot Table
Pivot tables are best created from transactional data—that is, raw data files directly from your company’s IT department.
To create the best pivot tables, make sure your data follows these rules:
Ensure each column has a one-cell heading. Keep the headings unique; don’t use the same heading for two columns. If you need your headings to appear on two rows, type the first word, press Alt+Enter, and then type the second word.
If a column should contain numeric data, don’t allow blank cells in the column. Use zeros instead of blanks.
Do not use blank rows or blank columns.
If totals are embedded in your report, remove them.
The workbook should not be in Compatibility mode. Many pivot table features from Excel 2007–2013 are disabled if the workbook is in Compatibility mode.
If you add new data to the bottom of your data set each month, you should strongly consider converting your data set to a table using Ctrl+T. Pivot tables created from tables automatically pick up new rows pasted to the bottom of the tables after a refresh.
If your data has months spread across many columns, go back to the source software program to see if a different view of the data is available with months going down the rows.
For most of this chapter, the pivot tables shown in the figures are from the data set in Figure 15.1. This data set has two years of transactional data. There is a single text column of Customer. There is a single date column. Numeric columns include Quantity, Revenue, COGS, and Profit.
Browsing Ten “Recommended” Pivot Tables
You can save a few mouse clicks by starting with the Recommended Pivot Table dialog.
Select a single cell in your data. On the Insert tab, choose Recommended PivotTables. Excel displays a dialog with 10 pivot tables down the left side. Click each pivot table to see a preview of the pivot table in the dialog (see Figure 15.2). When you find one that is close, click OK to create that pivot table on a new worksheet.
Is it worthwhile to use the Recommended PivotTables dialog? The 10 suggestions are not perfect, but many near the top of the list are a great starting point. Provided that you want your pivot table to appear on a new worksheet, and provided that you are building a pivot table from a single table, then you lose nothing by using Insert, Recommended PivotTable, OK instead of Insert, PivotTable, OK. At the very least, you start with two common fields in your pivot table and are usually two mouse clicks closer to being finished with the pivot table.
The rules for choosing the recommended pivot tables are fairly complex. I believe some of the rules for deciding on the top 10 pivot tables are as follows:
If you have a numeric field with a label of Revenue, that field is always given priority and appears in the first few pivot tables.
If you use Sales instead of Revenue, Excel looks for a field called Profit.
If Excel does not recognize any of the numeric field headings, it looks for the field with the largest total or the field on the right for the first few recommended pivot tables.
Excel analyzes the text fields to determine the number of unique values for each field. The two fields with the fewest unique values are often suggested as the row fields in the first four pivot tables.
Three of the 10 pivot tables offer multiple numeric fields going across the report. At least one of those offers a Count or Average of one field.
The final three pivot tables might contain an attempt to offer a cross-tab report, with fields in Row and Column, or with two fields in the row field. This logic is the weakest. In 50+ experiments, the logical combinations of Customers and Products or Region and Product only appeared in 6% of the trials. Hopefully, the Excel team can refine this logic over time.
Starting with a Blank Pivot Table
The traditional method for creating a pivot table is to create a blank one. Choose one cell in your data. Select PivotTable from the Insert tab. Excel displays the Create PivotTable dialog, as shown in Figure 15.3.
This dialog confirms the range of your data. Provided you have no blank rows or blank columns, Excel normally gets this right. In Figure 15.3, the underlying data has been made into a table using Ctrl+T and renamed as Data. You could instead choose to use an external data source.
Using the Create PivotTable dialog, you have the choice of creating the pivot table on a new blank worksheet or in an existing location. You might decide to put the pivot table in J2 on this worksheet, or next to another existing pivot table or pivot chart if you plan on building a dashboard of several pivot tables.
You can build a pivot table from a relational model by checking the Add This Data to the Data Model check box. For details on building a pivot table from two or more tables, see Chapter 17, “Mashing Up Data with PowerPivot.”
Adding Fields to Your Pivot Table Using the Field List
If you started with a blank pivot table, you see a PivotTable Fields panel that looks like Figure 15.4. The graphic shown in columns A:C is a placeholder to indicate where the pivot table will appear after you choose some fields. The PivotTable Fields area has a list of fields from your original data set at the top and four drop zones at the bottom. To build your report, you add fields to the drop zones at the bottom.
The field list is generally docked to the right side of the Excel window. The figures in this book show the field list as undocked. To undock the field list, drag the title bar away from the edge of the window. It is hard to redock the field list. You have to grab the left side of the title bar and drag the field list more than 50% off the right side of the Excel window.
If you built your pivot table using the Recommended PivotTable dialog, you already see a few fields in the drop zones and a few fields in the report. Figure 15.5 shows the initial pivot table and field list when you choose Sum of Revenue by Region.
Figure 15.5 If you choose a recommended pivot table, the first few fields are added to the pivot table.
Changing the Pivot Table Report by Using the Field List
If you are starting with Figure 15.4, check the Region, Product, and Revenue fields. If you are starting with Figure 15.5, check the Product field.
When you check a text or date field, that field automatically moves to the ROWS drop zone in the PivotTable Fields list. When you check a numeric field, that field moves to the VALUES drop zone and is changed to Sum of Field.
By choosing Region, Product, and Revenue, you see Sum of Revenue by region and product, as shown in Figure 15.6.
You can further customize the pivot table by moving fields around in the drop zones. For example, drag the Region field so it is below the Product field in the ROWS drop zone. The report updates to show Region within Product, as shown in Figure 15.7.
Figure 15.7 Drag the Region field to appear after the Product field in the ROWS drop zone to change the report.
Drag the Region field from the ROWS drop zone to the COLUMNS drop zone, and you have a crosstab report, as shown in Figure 15.8.
Dealing with the Compact Layout
If you’ve been using pivot tables for many versions of Excel, you have to wonder about the bizarre layout of the pivot table in Figure 15.6. The totals appear at the top of each group instead of at the bottom. Two fields, Region and Product, appear in column A. Collapse buttons appear next to the regions.
This is a report layout called Compact Form. Introduced in Excel 2007, it is beautiful if you plan to present your pivot table in an interactive touch-screen kiosk complete with slicers. However, if you plan to reuse the results of the pivot table, the Compact Form is horrible. Every pivot table you create in the Excel interface starts with Compact Form. Here is how to go back to the Tabular Form layout:
1. Make sure that the active cell is inside the pivot table.
2. Go to the Design tab in the ribbon. Open the Report Layout drop-down. Select Show in Tabular Form. As shown in Figure 15.9, the totals move back to the bottom of each region. Also, Product moves to column B.
3. Open the Report Layout drop-down and select Repeat All Item Labels. This eliminates the blanks in column A of the pivot table, as shown in Figure 15.10. This is a feature that has been badly needed in Excel for 15 years. It was finally added to Excel 2010.
Rearranging a Pivot Table
The drop zone sections of the PivotTable Fields list box are as follows:
Filter—You use this section to limit the report to only certain criteria. This section is virtually replaced by the slicer feature.
To learn more about filtering pivot tables, see Chapter 16, “Using Slicers and Filtering a Pivot Table.”
Rows—This section is for fields that appear on the left side of the table. By default, all text fields move here when you select the check boxes in the top of the field list.
Columns—This section is for fields that stretch along the top rows of columns of your table. Old database geeks refer to this as a crosstab report.
Values—This section is for all the numeric fields that are summarized in the table. By default, most fields are automatically summed, but you can change the default calculation to an average, minimum, maximum, or other calculation.
You can add fields to a drop zone by dragging from the top of the PivotTable Fields list to a drop zone or by dragging from one drop zone to another. To remove a field from a drop zone, drag the field from the drop zone to outside of the PivotTable Fields list.
Finishing Touches: Numeric Formatting and Removing Blanks
After you arrange your data in the report, you want to consider formatting the numeric fields. For example, the pivot table in Figure 15.11 has Customer and Product in the ROWS drop zone, Region in COLUMNS, and Revenue in VALUES. It would be helpful if the numbers were formatted with commas as thousands separators. Also, consider changing the words Sum of Revenue to something less awkward, such as Total Revenue or even Revenue.
Follow these steps to apply a numeric format to the Revenue field:
1. Select one cell that contains a revenue amount. If you look on the Analyze tab, you see a box that reports the active field. By choosing a cell with Revenue, the Active Field box indicates that Sum of Revenue is the active field.
2. Click the Field Settings icon in the Active Field group of the Analyze tab. Excel displays the Value Field Settings dialog.
3. The label for this field appears in the Custom Name box at the top of the dialog. Change Sum of Revenue by typing the word Revenue followed by a space. Note that the space is critical. You cannot use just the word Revenue without a space because this would create a duplicate field name.
4. Click the Number Format button in the bottom of the Value Field Settings dialog. Excel displays the familiar Number tab of the Format Cells dialog.
5. Select the Number category. Select 0 decimal places. Add a thousands separator. Click OK to close the Format Cells dialog. Click OK to close the Value Field Settings dialog.
Figure 15.12 shows the new number format applied to the pivot table, along with the Field Settings icon and the Value Field Settings dialog.
Caution
Avoid the temptation to format cells B5:F113 using the formatting commands on the Home tab. This formats the current pivot table, but as you continue to add fields to the pivot table later, the shape of the pivot table will change and the Home tab formatting will not stick. When you go through the process previously described, the Revenue field will continue to have the correct format.
Notice the blank cells in the values area of the pivot table. For example, the blank cell in C5 of the pivot table means that there are no records in the data set where data2impact bought product ABC in the Central region. You would probably rather have zeros in those cells instead of blanks. You will perform the following steps so often that you will wonder why Microsoft did not make this the default choice:
1. Select any one cell inside the pivot table.
2. On the Analyze tab, select the Options icon on the left side of the ribbon.
3. On the Layout & Format tab of the PivotTable Options dialog, type 0 next to For Empty Cells Show.
4. Click OK. Excel fills in the empty cells with zeros.
Four Things You Have to Know When Using Pivot Tables
Pivot tables are the greatest invention in spreadsheets. However, you have to understand the following four issues, presented in order of importance.
Your Pivot Table Is in Manual Calculation Mode Until You Click Refresh!
Most people are shocked to learn that changes to underlying data do not appear in a pivot table. After all, you change a cell in Excel, and all the formulas derived from the cell automatically change. You would think that the same should hold true for pivot tables, but it does not. Pivot tables are fast because the original data from the worksheet is loaded into a pivot cache in memory. Until you click the Refresh icon on the Analyze ribbon, Excel does not pick up the changes to the underlying data.
One Blank Cell in a Value Column Causes Excel to Count Instead of Sum
Suppose your data set has thousands of rows of data. For any reason, if one of the revenue cells happens to be blank, this completely confuses Excel. There can be 999,999 cells with numbers and one blank cell, but Excel no longer realizes that the Revenue column is a numeric column. When you add Revenue to the pivot table, Excel decides to count the number of rows instead of summing the revenue. To correct the problem, you have two choices:
Delete the pivot table, fill the blanks in the original data with zeros, and re-create the pivot table.
Select one cell that contains Count of Revenue. Select the Field Settings icon, and then change from Count to Sum in that dialog.
If You Click Outside the Pivot Table, All the Pivot Table Tools Disappear
If your field list disappeared and the Options and Design tabs are missing, it is likely that you clicked outside of the pivot table.
I’ve had the argument with Microsoft that because nothing is on the worksheet other than the pivot table, I am still looking at the pivot table even when I click outside of the pivot table. I continue to lose this argument, however. If the field list disappears and the tabs are gone, click back inside your pivot table.
You Cannot Change, Move a Part of, or Insert Cells in a Pivot Table
Many times, pivot tables get you very close to the final report you want, and you just want to insert a row or move one bit of the table. You cannot do this. If you try, you will be greeted with the ubiquitous message: “We can’t make this change for the selected cells because it will affect a PivotTable.” This is a fair limitation. After all, Excel needs to figure out how to redraw the table when you move something in the field list.
The solution is to copy the entire pivot table and then use Paste Values to convert the report to regular Excel data. You can either put this on a new worksheet or paste the entire table back over itself. If you go to a new worksheet, you can continue to modify the original pivot table. If you paste values over the original worksheet, the pivot table converts to a range, and you cannot pivot it further.
Calculating and Roll-ups with Pivot Tables
Pivot tables offer many more calculation options than those shown so far in this chapter. One of the most amazing features is the automatic capability to roll daily dates up to months, quarters, and years.
Grouping Daily Dates to Months and Years
Good pivot tables start with good transactional data. Invariably, that transactional data is stored with daily dates instead of monthly summaries. Excel 2016 automatically does roll-ups to show daily data as months, quarters, and years.
To produce a summary by month, quarter, and year, follow these steps:
1. Start with data that spans more than one year or one full year from January 1 to December 31. Build a pivot table with daily dates going down the row field, Region in the columns, and Sum of Revenue in the value area. Excel 2016 has already converted your daily dates to years, as shown in Figure 15.13. Note that the ROWS area shows Years, Quarters, and Date. All three of these fields are virtual roll-ups created by Excel 2016.
2. Right-click one of the year values. Select Expand/Collapse, Expand to Date. Excel reveals data for quarters and month as shown in Figure 15.14. Due to a logic flaw dating back 15 years, Excel does not automatically show totals by quarter or year.
3. To add quarter totals, choose the Qtr1 cell in A6. Click Field Settings and change the Subtotals setting from None to Automatic. Repeat with the Year cell in A5. You now have the report shown in Figure 15.15.
The automatic grouping of daily dates is new in Excel 2016. If you need to show daily dates instead of the roll-up, you might not like this feature. There are three ways to turn off this feature.
Immediately after adding a date field to the pivot table, press Ctrl+Z to undo. This undoes the grouping of the dates.
Select any cell that contains a year, quarter, month. Choose Ungroup Field.
To disable the Auto Group functionality on both native and data model Pivot Tables and Pivot Charts, you can add a new DWORD (32-bit) Value registry key: HKEY_CURRENT_USERSoftwareMicrosoftOffice16.0ExcelOptionsDateAutoGroupingDisabled. After adding the key, enter the Edit mode for the key to set its value data to “1”. The new key is effective immediately; there is no need to reload Excel.
Tip
Excel looks at the span of dates or times to figure out how AutoGroup should work. If your data spans a short period within one month, AutoGroup does not take any action. If your data spans several months but does not fall outside of one year, AutoGroup groups to months.
Caution
It is common to have January 1 or December 31 off as a holiday. If your company has no sales on January 1, your sales data might span from January 2 to December 31. Unfortunately, the logic built in to Excel 2016 does not recognize this as a full year, and Auto Group will not roll the data up to quarters and years. You can override the Auto Group choices by selecting one date cell and choosing Group Field.
For an interesting alternative to the report in Figure 15.15, follow these steps:
1. Uncheck the Region field to remove Region from the report.
2. Drag the Years field from the ROWS area to the COLUMNS area.
You now have a pivot table that provides totals by month and quarter and compares years going across the report (see Figure 15.16). Notice that your pivot table field list includes three fields related to dates: The years and quarters fields are virtual fields. The original Date field includes the months. This was a brilliant design decision on Microsoft’s part because it allows years and months to be pivoted to different sections of the pivot table.
Adding Calculations Outside the Pivot Table
Figure 15.17 shows % Growth instead of Grand Total in column D. However, after you group the dates in the pivot table, you are prevented from adding a calculated item inside the pivot table, so you have to turn back to regular Excel to provide the % Growth column.
Figure 15.17 The % Growth column is a regular formula outside the pivot table, formatted to look like it is part of the pivot table.
However, it is not simple for Excel to create that column. In particular, step 2 trips up most people. Follow these steps:
1. Select one cell in the pivot table. Go to the Design tab and choose Grand Totals, On For Columns Only. This command removes the Grand Total column.
2. In cell E5, type =D5/C5-1. You really have to type this formula! Do not touch the mouse or the arrow keys while you are building the formula, or you will be stung by the GetPivotData bug.
3. Format cell E5 as a percentage with one decimal place.
4. Double-click the fill handle in E5 to copy the formula down to all rows.
Changing the Calculation of a Field
By default, a numeric column will be added to the pivot table with a default calculation of Sum. Excel offers 10 other calculations, such as Average, Count, Max, and Min. Excel 2010 added several new calculations and did a great job of bringing the old calculations to the forefront by adding the Show Values As drop-down to the Pivot Table Options tab.
For this section, the figures start with a completely new pivot table. You can follow along with these steps:
1. Delete the worksheet that contains the pivot table from the previous examples. This clears the pivot cache from memory.
2. Select one cell on the Data worksheet.
3. Choose Insert, Pivot Table.
4. Add a check next to the Region, Product, and Revenue fields. Check Quantity and Profit. You end up with a default pivot table showing Sum of Revenue, Sum of Quantity, and Sum of Profit, as shown in Figure 15.18.
In column C, you would like a count of the number of records. Follow these steps to change column C from Sum of Quantity:
1. Select one cell in the C3:C16 range (that is, any cell that contains quantity or the heading above those cells).
2. On the Analyze tab, choose Field Settings. Excel displays the Value Field Settings dialog.
3. In the Value Field Settings dialog, choose Count instead of Sum.
4. In the Custom Name field, type Count of Orders or any other name that makes sense to you.
5. Click OK. Column C now shows a count of records instead of a sum (see Figure 15.19).
To change column D to show average profit, follow these steps:
1. Choose one cell in Sum of Profit column.
2. Click the Field Settings icon.
3. Change the calculation to Average.
4. Change the Custom Name field to Avg Profit.
5. Click the Number Format button.
6. Choose Currency with two decimal places.
7. Click OK twice to close the Format Cells and the Value Field Settings dialogs. Excel now shows Avg Profit in column D.
You can use a similar method to change to any of the 11 calculations offered in the Summarize Values By tab.
That’s not all—there are more ways to show the values, as discussed in the next section.
Showing Percentage of Total Using Show Value As Settings
In addition to the 11 ways to summarize values, Excel 2016 offers 14 calculation options on the second tab of the Value Field Settings dialog. To experiment with these 14 calculations, drag the Revenue field to the VALUES drop zone two more times. Follow these steps:
1. Select a cell in column E of the pivot table. This is the second revenue column. Choose the Field Settings icon in the Analyze tab. Select the second tab in the Value Field Settings dialog. Choose % of Column Total from the drop-down. Change the Custom Name to % of Total. Click OK.
2. Select a cell in column F of the pivot table. This is the third revenue column. Choose the Field Settings icon in the Analyze tab. Select the second tab in the Value Field Settings dialog. Choose % of Parent Row Total from the drop-down. Change the Custom Name to % of Parent. Click OK.
Figure 15.20 shows the result. In row 5, the $766,469 of revenue in B5 is 11.43% of the grand total revenue. E5 shows 11.43%. The calculation in F5 shows that the revenue in B5 is 32.26% of the Central region revenue shown in B4.
Showing Running Totals and Rank
Other options in the Show Values As drop-down include running totals and a ranking. These work best when there is only one field in the row area.
Delete the worksheet that contains the existing pivot table. Build a new pivot table with Customer in the ROWS area. Drag Revenue six times to the VALUES area.
Initially, the customers are sorted alphabetically. Open the Row Labels drop-down in cell A3. Choose More Sort Options. In the Sort (Customer) dialog, choose Descending (Z to A) By. In the drop-down, choose Sum of Revenue. Click OK. The pivot table shows the largest customers at the top.
To change the calculation in each column, follow these steps:
1. Choose cell B3. Click Field Settings. Change the Custom Name to Revenue with a leading or trailing space. Click Number Format. Choose Currency with 0 decimal places. Click OK twice.
2. Choose cell C3. Click Field Settings. On the Show Values As tab, choose Running Total In. In the Base Field list, choose Customer. Change the Custom Name to Accum. Total. Click Number Format. Choose Currency with 0 decimal places. Click OK twice.
3. Choose cell D3. Click Field Settings. On the Show Values As tab, choose % Running Total In. In the Base Field list, choose Customer. Change the Custom Name to Accum %. Click Number Format. Choose Percentage with 1 decimal place. Click OK twice.
4. Choose cell E3. Click Field Settings. On the Show Values As tab, choose Rank Largest to Smallest. In the Base Field list, choose Customer. Change the Custom Name to Rank. Click OK.
5. Choose cell F3. Click Field Settings. On the Show Values As tab, choose % of Column Total. Change the Custom Name to % of Total. Click Percentage 1 decimal place. Click OK twice.
6. Choose cell G3. Click Field Settings. On the Show Values As tab, choose % Of. In the Base Field list, choose Customer. In the Base Item field, you can choose (previous), (next), or a specific customer. Because the largest customer is leanexcelbooks.com, choose that customer as the Base Item setting. Change the Custom Name to % of Top. Choose the Number Format button. Click Percentage 1 decimal place. Click OK twice.
The resulting pivot table in Figure 15.21 shows examples of the 14 Show Values As options. Note that many of the options require the choice of a base field. A few also require that you select a base item.
Using a Formula to Add a Field to a Pivot Table
The previous examples took an existing field and used the Show Values As setting to change how the data is presented in the pivot table. In this example, you learn how to add a brand-new calculated field to the pivot table. Follow these steps:
1. Select one of the numeric cells in the pivot table.
2. On the Analyze tab in the ribbon, choose Fields, Items, & Sets. Choose Calculated Field from the drop-down. (If this option is grayed out, choose a cell in the value area of the pivot table.) Excel displays the Insert Calculated Field dialog. The default field name of Field 1 and the default formula of =0 appear in the dialog.
3. Type a new name, such as GP%.
4. The Formula field starts out as an equal sign, a space, and then a zero. You have to click in this field and press backspace to remove the zero.
5. Build the formula by double-clicking Profit, typing a slash, and then double-clicking Revenue. The dialog box should look like Figure 15.22. Click OK.
6. The headings for calculated fields always appear strange. Select a cell in column H and choose Field Settings. Change the Custom Name from Sum of GP% to GP% with a leading or trailing space. Change the Number Format to Percentage with 1 decimal. Click OK twice. The final pivot table is shown in Figure 15.23.
Formatting a Pivot Table
Excel offers a PivotTable Styles gallery on the Design tab. Instead, if you try to format individual cells in a pivot table, you will experience frustration. After you rearrange the pivot table, your manual formatting will be lost.
The PivotTable Styles gallery on the Design tab contains 73 built-in styles for a pivot table. The 73 styles are further modified by using the four check boxes for Banded Rows, Banded Columns, Row Headers, and Column Headers. Multiply that by the 20 color themes on the Page Layout tab, and you have 23,260 different styles. Multiply by the three report layouts, two options for blank rows, Grand Totals On or Off for Rows or Columns, Subtotals Above or Below, and you have more than a million styles available for your pivot table.
You can also build new styles. For example, if you would like the banded rows to be two rows tall, you can design a style for that.
To format a pivot table, select Banded Rows, Row Headers, and Column Headers from the Design tab of the ribbon. Then open the Styles gallery. Figure 15.24 shows some of the choices available in the gallery.
Finding More Information on Pivot Tables
Chapter 16, covers slicers and other ways to filter a pivot table.
Chapter 17 covers creating pivot tables from multiple tables using PowerPivot.
Chapter 24, “Using 3D Maps,” covers creating a pivot table on a map using 3D Maps.
For more information on pivot tables, check out my other book on the subject: Excel 2016 Pivot Table Data Crunching (Que, ISBN 978-0-7897-5629-9), coauthored by Mike Alexander.