Introduction
The pivot table is the single most powerful tool in all of Excel. Pivot tables came along during the 1990s, when Microsoft and Lotus were locked in a bitter battle for dominance of the spreadsheet market. The race to continually add enhanced features to their respective products during the mid-1990s led to many incredible features, but none as powerful as the pivot table.
With a pivot table, you can transform one million rows of transactional data into a summary report in seconds. If you can drag a mouse, you can create a pivot table. In addition to quickly summarizing and calculating data, pivot tables enable you to change your analysis on the fly by simply moving fields from one area of a report to another.
No other tool in Excel gives you the flexibility and analytical power of a pivot table.
What you will learn from this book
It is widely agreed that close to 60 percent of Excel customers leave 80 percent of Excel untouched—that is, most people do not tap into the full potential of Excel’s built-in utilities. Of these utilities, the most prolific by far is the pivot table. Despite the fact that pivot tables have been a cornerstone of Excel for almost 20 years, they remain one of the most underutilized tools in the entire Microsoft Office suite.
Having picked up this book, you are savvy enough to have heard of pivot tables—and you have perhaps even used them on occasion. You have a sense that pivot tables provide a power that you are not using, and you want to learn how to leverage that power to increase your productivity quickly.
Within the first two chapters, you will be able to create basic pivot tables, increase your productivity, and produce reports in minutes instead of hours. Within the first seven chapters, you will be able to output complex pivot reports with drill-down capabilities and accompanying charts. By the end of the book, you will be able to build a dynamic pivot table reporting system.
What is new in Excel 2019’s pivot tables
Luckily, Microsoft continues to invest heavily in business intelligence (BI), and pivot tables are the front end that let you access the new features. Some of the features added to Excel 2019 pivot tables include the following:
You can specify default settings for all future pivot tables. If you want all pivot tables to start in Tabular layout instead of Compact layout, you can specify this. Do you prefer Classic layout or do you always replace empty cells with zero? Visit File, Options, Data, Pivot Table Defaults to change the settings for all future pivot tables.
The automatic date grouping introduced in Excel 2016 pivot tables can now be turned off. The setting is found in File, Options, Data. The feature became unpopular after the release of Excel 2016.
Pivot tables will be doing more summing and less counting. Previously, if you had a column with revenue and a few empty cells, the pivot table would choose to Count instead of Sum. In Excel 2019, a mix of empty cells and numeric cells will be treated like a numeric column and will default to Sum instead of Count.
Power Pivot is now included in all Windows versions of Excel 2019 and Office 365. During the tenure of Office 2013–2016, only parts of Power Pivot were available. Although some of the Power Pivot functionality was available if you chose Add This Data To The Data Model while creating a pivot table, you can now access the Power Pivot tab in the ribbon. This allows you to create and view relationships in a diagram view. You can choose to sort one column by another column.
With Power Pivot in Excel 2019, your pivot tables benefit from any new functions added to the DAX formula language. If you ever wanted to do Median in a pivot table or even report Text fields in the Values area, the new DAX functions such as MEDIAN and CONCATENATEX allow this to happen.
Power View is being de-emphasized. Microsoft is replacing Power View with Power BI Desktop. You will find it difficult to locate the Power View icons in Excel 2019.
If you are using Office 365, you will have access to the Insights command on the Insert tab. This command uses artificial intelligence to suggest 30 interesting charts or pivot tables that can be created from your data.
Case Study: Life Before Pivot Tables
Say that your manager asks you to create a one-page summary of a sales database. He would like to see total revenue by region and product. Suppose you do not know how to use pivot tables. You will have to use dozens of keystrokes or mouse clicks to complete this task.
First, you have to build the outline of the report:
Copy the Product column to a blank section of the worksheet.
Select Data, Remove Duplicates to eliminate the duplicates.
Delete the Product heading.
Copy the unique list of products and then use Paste Special Transpose to turn the list sideways.
Delete the vertical list of products.
Copy the Region column to a blank section of the worksheet.
Select Data, Remove Duplicates to remove the duplicates.
Delete the Region heading.
Cut and paste the products so they appear to the left of and below the regions.
At this point, with 27 mouse clicks or keystrokes, you’ve built the shell of the final report, but there are no numbers inside yet (see Figure I-1).
Next, you need to build the relatively new SUMIFS function to total the revenue for the intersection of a region and product. As shown in Figure I-2, the formula =SUMIFS($G$2:$G$564,$C$2:$C$564,L$1,$B$2:$B$564,$K2) does the trick. It takes 52 characters plus the Enter key to finish the formula, but I managed to enter the formula in 36 clicks or keystrokes by using some clever navigation tricks I’ve learned over the years.
If you are adept at using the Fill handle, you need just two more mouse drags to copy the formula to the rest of the table.
Enter the heading Total for the total row and for the total column. You can do this in nine keystrokes if you type the first heading, press Ctrl+Enter to stay in the same cell, and then use Copy, select the cell for the second heading, and use Paste.
If you select K1:P6 and press Alt+= (that is, Alt and the equal sign key), you can add the total formula in three keystrokes.
With this method, which takes 77 clicks or keystrokes, you end up with a nice summary report, as shown in Figure I-3. If you could pull this off in 5 or 10 minutes, you would probably be fairly proud of your Excel prowess; there are some good tricks among those 77 operations.
You hand the report to your manager. Within a few minutes, he comes back with one of the following requests, which will certainly cause a lot of rework:
Could you put products down the side and regions across the top?
Could you show me the same report for only the manufacturing customers?
Could you show profit instead of revenue?
Could you copy this report for each of the customers?
Invention of the pivot table
When the actual pivot table was invented is in dispute. The Excel team coined the term pivot table, which appeared in Excel in 1993. However, the concept was not new. Pito Salas and his team at Lotus were working on the pivot table concept in 1986 and released Lotus Improv in 1991. Before then, Javelin offered functionality similar to that of pivot tables.
The core concept behind a pivot table is that the data, formulas, and data views are stored separately. Each column has a name, and you can group and rearrange the data by dragging field names to various positions on the report.
Case Study: Life After Pivot Tables
Say that you’re tired of working so hard to remake reports every time your manager wants a change. You’re in luck: You can produce the same report as in the last case study but use a pivot table instead. Excel 2019 offers you 10 thumbnails of recommended pivot tables to get you close to the goal. Follow these steps:
Click the Insert tab of the ribbon.
Click Recommended PivotTables. The first recommended item is Revenue By Region (see Figure I-4).
Click OK to accept the first pivot table.
Drag the Product field from the PivotTable Fields list to the Columns area (see Figure I-5).
Clear Field Headers on the right side of the ribbon.
With just five clicks of the mouse, you have the report shown in Figure I-6.
In addition, when your manager comes back with a request like the ones near the end of the prior case study, you can easily use the pivot table to make the changes. Here’s a quick overview of the changes you’ll learn to make in the chapters that follow:
Could you put products down the side and regions across the top? (This change will take you 10 seconds: Drag Product to Rows and Region to Columns.)
Could you show me the same report for only the manufacturing customers? (15 seconds: Select Insert Slicer, Sector; click OK; click Manufacturing.)
Could you show profit instead of revenue? (10 seconds: Clear the check box for Revenue, select the check box for Profit.)
Could you copy this report for each of the customers? (30 seconds: Move Customer to Report Filter, open the tiny drop-down menu next to the Options button, choose Show Report Filter Pages, click OK.)
Excel 2019 versus Office 365
The title of this book says Excel 2019. In reality, no one should be buying Office 2019. Instead, it is likely that you are renting Office 365. As of May 2018, Office 2019 and Office 365 were nearly equivalent.
Here’s why the authors hope that you did not purchase Office 2019: The features in Office 2019 will not change. The features in Office 365 will constantly evolve and improve. The people who have been using Office 365 have enjoyed the new features described earlier in this chapter one to two years before the people who purchased Office 2019.
Because the title of this book says Excel 2019, the screenshots show the Excel 2019 ribbon. However, anyone using Office 365 will have a new look to the ribbon.
Microsoft reduced the number of colors in the Office 365 ribbon. The selected Ribbon tab is now underlined instead of being a different color. Compare the Excel 2019 Insert tab in Figure I-7 with the Office 365 Insert tab in Figure I-8.
Microsoft is experimenting with a new ribbon that goes back to a single row like the Office 2013 menus. As of the summer of 2018, that new ribbon will debut with Outlook and Excel Online and only for Office 365 customers. It remains to be seen if that ribbon ever makes it to the desktop version of Excel.
New features in Office 365
After this book is printed, the Excel team will release new features for Excel. Anyone with an Office 365 subscription will receive the new features. People with Excel 2019 will not get the new features.
For any new features introduced to Office 365, the owners of Excel 2019 will have to purchase Office 2022 in order to access those features.
But what is even worse, some features introduced will be marked as Office 365 Exclusive features. Those features will never be available to customers who buy the perpetual versions of Office, such as Office 2016, Office 2019, or Office 2022.
One such feature introduced in early 2018 is the Insights feature. This feature will send up to 250,000 cells of your data to a Microsoft server where artificial intelligence looks for trends in your data. The first few results from Insights are very similar to the Insert Recommended PivotTable logic introduced in Excel 2013 and shown previously in Figure I-4.
However, if you click the More link, Insights often provides two to three dozen suggestions. Most suggestions will insert a pivot table and a pivot chart. In the first preview of Insights, Excel will look for: rank, evenness, trend, composite signal, attribution, outstanding top two, monotonicity (always increasing or always decreasing), and unimodality (having a single peak data point).
When I used the data set from this Introduction with Insights in Office 365, Microsoft suggested 32 different possible results. One is shown in Figure I-9.
If you regularly purchased every new version of Office, it will cost less to rent Office 365 at $10 a month than to pay $399 for the perpetual version of Office every 36 months. Office 365 offers more features than Excel 2019. Unless your buying pattern is to only buy every second or third version of Office, upgrading to Office 365 makes sense.
Who this book is for
This book is a comprehensive-enough reference for hard-core analysts yet relevant to casual users of Excel.
We assume that you are comfortable navigating in Excel and that you have some large data sets that you need to summarize.
How this book is organized
The bulk of the book covers how to use pivot tables in the Excel user interface. Chapter 10, “Unlocking features with the Data Model and Power Pivot,” delves into the Power Pivot window. Chapter 13, “Using VBA to create pivot tables,” describes how to create pivot tables in Excel’s powerful VBA macro language. Anyone who has a firm grasp of basics such as preparing data, copying, pasting, and entering simple formulas should not have a problem understanding the concepts in this book.
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
System requirements
You need the following software and hardware to build and run the code samples for this book:
Microsoft Excel running on a Windows computer. (Yes, Excel runs on an iPad, on an Android tablet, and in a browser, but none of those are going to support creation of pivot tables anytime soon.) For people using Excel on a Mac, some of the basic pivot table concepts will apply. Power Query and Power Pivot will not run on a Mac.
Support and feedback
The following sections provide information on errata, book support, feedback, and contact information.
Stay in touch
Let’s keep the conversation going! We’re on Twitter:
http://twitter.com/MicrosoftPress
Errata, updates, and book support
We’ve made every effort to ensure the accuracy of this book and its companion content. You can access updates to this book—in the form of a list of submitted errata and their related corrections—at the following page:
MicrosoftPressStore.com/Excel2019PivotTable/errata
If you discover an error that is not already listed, please submit it to us at the same page.
If you need additional support, email Microsoft Press Book Support at microsoftpresscs@pearson.com.
Please note that product support for Microsoft software and hardware is not offered through the previous addresses. For help with Microsoft software or hardware, go to http://support.microsoft.com.