Chapter 1
Pivot table fundamentals

In this chapter, you will:

• Learn why you should use a pivot table

• Learn when to use a pivot table

• Get to know the anatomy of a pivot table

• Peek at pivot tables behind the scenes

• Examine pivot table backward compatibility

Imagine that Excel is a large toolbox that contains different tools at your disposal. The pivot table is essentially one tool in your Excel toolbox. If a pivot table were indeed a physical tool that you could hold in your hand, a kaleidoscope would most accurately represent it.

When you look through a kaleidoscope at an object, you see that object in a different way. You can turn the kaleidoscope to move around the details of the object. The object itself doesn’t change, and it’s not connected to the kaleidoscope. The kaleidoscope is simply a tool you use to create a unique perspective on an ordinary object.

Think of a pivot table as a kaleidoscope that is pointed at a data set. When you look at a data set through a pivot table, you can see details in the data that you might not have noticed before. Furthermore, you can turn your pivot table to see your data from different perspectives. The data set itself doesn’t change, and it’s not connected to the pivot table. The pivot table is simply a tool you use to create a unique perspective on your data.

A pivot table enables you to create an interactive view of your data set, called a pivot table report. With a pivot table report, you can quickly and easily categorize your data into groups, summarize large amounts of data into meaningful information, and perform a variety of calculations in a fraction of the time it takes by hand. But the real power of a pivot table report is that you can use it to interactively drag and drop fields within your report, dynamically change your perspective, and recalculate totals to fit your current view.

Why you should use a pivot table

As a rule, what you do in Excel can be split into two categories:

• Calculating data

• Shaping (formatting) data

Although many built-in tools and formulas facilitate both tasks, using a pivot table is often the fastest and most efficient way to calculate and shape data. Let’s look at one simple scenario that illustrates this point.

You have just given your manager some revenue information by month, and he has predictably asked for more information. He adds a note to the worksheet and emails it back to you. As you can see in Figure 1-1, he would like you to add a line that shows credits by month.

FIGURE 1-1 Your manager predictably changes his request after you provide the first pass of a report.

To meet this new requirement, you run a query from your legacy system that provides the needed data. As usual, the data is formatted specifically to make you suffer. Instead of data by month, the legacy system provides detailed transactional data by day, as shown in Figure 1-2.

FIGURE 1-2 The data from the legacy system is by day instead of by month.

Your challenge is to calculate the total dollar amount of credits by month and shape the results into an extract that fits the format of the original report. The final extract should look like the data shown in Figure 1-3.

FIGURE 1-3 Your goal is to produce a summary by month and transpose the data to a horizontal format.

Creating the extract manually would take 18 mouse clicks and 3 keystrokes:

• Format dates to month: 3 clicks

• Create subtotals: 4 clicks

• Extract subtotals: 6 clicks, 3 keystrokes

• Transpose vertical to horizontal: 5 clicks

In contrast, creating the extract with a pivot table would take 9 mouse clicks:

• Create the pivot table report: 5 clicks

• Group dates into months: 3 clicks

• Transpose vertical to horizontal: 1 click

Both methods give you the same extract, which you can paste into the final report, as shown in Figure 1-4.

FIGURE 1-4 After adding credits to the report, you can calculate net revenue.

Using a pivot table to accomplish the task just described not only cuts down the number of actions by more than half but also reduces the possibility of human error. In addition, using a pivot table allows for the quick-and-easy shaping and formatting of the data.

This example shows that using a pivot table is not just about calculating and summarizing your data. Pivot tables can often help you do a number of tasks faster and better than conventional functions and formulas. For example, you can use pivot tables to instantly transpose large groups of data vertically or horizontally. You can use pivot tables to quickly find and count the unique values in your data. You can also use pivot tables to prepare your data to be used in charts.

The bottom line is that pivot tables can help you dramatically increase your efficiency and decrease your errors on a number of tasks you might have to accomplish with Excel. Pivot tables can’t do everything for you, but knowing how to use just the basics of pivot table functionality can take your data analysis and productivity to a new level.

When to use a pivot table

Large data sets, ever-changing impromptu data requests, and multilayered reporting are absolute productivity killers if you have to tackle them by hand. Going into hand-to-hand combat with one of these not only is time-consuming, but also opens up the possibility of an untold number of errors in your analysis. So how do you recognize when to use a pivot table before it’s too late?

Generally, a pivot table would serve you well in any of the following situations:

• You have a large amount of transactional data that has become increasingly difficult to analyze and summarize in a meaningful way.

• You need to find relationships and groupings within your data.

• You need to find a list of unique values for one field in your data.

• You need to find data trends using various time periods.

• You anticipate frequent requests for changes to your data analysis.

• You need to create subtotals that frequently include new additions.

• You need to organize your data into a format that’s easy to chart.

Anatomy of a pivot table

Because the anatomy of a pivot table is what gives it its flexibility and, indeed, its ultimate functionality, truly understanding pivot tables would be difficult without understanding their basic structure.

A pivot table is composed of four areas:

• Values area

• Rows area

• Columns area

• Filters area

The data you place in these areas defines both the utility and appearance of the pivot table.

You will go through the process of creating a pivot table in the next chapter, and the following sections prepare you for that by taking a closer look at the four pivot table areas and the functionality around them.

Values area

The Values area is shown in Figure 1-5. It is a large rectangular area below and to the right of the headings. In this example, the Values area contains a sum of the Revenue field.

FIGURE 1-5 The heart of the pivot table is the Values area. This area typically includes a total of one or more numeric fields.

The Values area is the area that calculates. This area is required to include at least one field and one calculation on that field. The data fields you drop here are those you want to measure or calculate. The Values area might include Sum Of Revenue, Count Of Units, and Average Of Price.

It is also possible to have the same field dropped in the Values area twice but with different calculations. For example, a marketing manager might want to see Minimum Of Price, Average Price, and Maximum Of Price.

Rows area

The Rows area, as shown in Figure 1-6, is composed of the headings that go down the left side of the pivot table.

FIGURE 1-6 The headings down the left side of the pivot table make up the Rows area of the pivot table.

Dropping a field into the Rows area displays the unique values from that field down the rows of the left side of the pivot table. The Rows area typically has at least one field, although it is possible to have no fields. The example earlier in the chapter where you needed to produce a one-line report of credits is an example where there are no row fields.

The types of data fields you would drop here include those you want to group and categorize—for example, Products, Names, and Locations.

Columns area

The Columns area is composed of headings that stretch across the top of columns in the pivot table. In the pivot table in Figure 1-7, the Month field is in the Columns area.

FIGURE 1-7 The Columns area stretches across the top of the columns. In this example, it contains the unique list of months in your data set.

Dropping fields into the Columns area would display your items in column-oriented perspective. The Columns area is ideal for showing trending over time. The types of data fields you would drop here include those you want to trend or show side by side—for example, Months, Periods, and Years.

Filters area

The Filters area is an optional set of one or more drop-downs at the top of the pivot table. In Figure 1-8, the Filters area contains the Region field, and the pivot table is set to show all regions.

FIGURE 1-8 Filter fields are great for quickly filtering a report. The Region drop-down menu in cell B1 enables you to print this report for one particular region manager.

Dropping fields into the Filters area would enable you to filter the data items in your fields. The Filters area is optional and comes in handy when you need to filter your results dynamically. The types of data fields you would drop here include those you want to isolate and focus on—for example, Regions, Line Of Business, and Employees.

Pivot tables behind the scenes

It’s important to know that pivot tables come with a few file space and memory implications for your system. To get an idea of what this means, let’s look at what happens behind the scenes when you create a pivot table.

When you initiate the creation of a pivot table report, Excel takes a snapshot of your data set and stores it in a pivot cache, which is a special memory subsystem where your data source is duplicated for quick access. Although the pivot cache is not a physical object you can see, you can think of it as a container that stores a snapshot of the data source.

Caution

Any changes you make to your data source are not picked up by your pivot table report until you take another snapshot of the data source or “refresh” the pivot cache. Refreshing is easy: Simply right-click the pivot table and click Refresh Data. You can also click the large Refresh button on the Options tab.

The benefit of working against the pivot cache and not your original data source is optimization. Any changes you make to the pivot table report, such as rearranging fields, adding new fields, or hiding items, are made rapidly and with minimal overhead.

Pivot table backward compatibility

Almost each new version of Excel introduces pivot table features that will not work in previous editions of Excel.

Slicers created in Excel 2010 or later will not work in Excel 2007 or earlier. Timelines created in Excel 2013 will not work in Excel 2010 or earlier.

There were major pivot table limit increases in Excel 2007. Table 1-1 illustrates the differences. If you have a pivot table with more than 256 columns or any other limit shown in the table, it cannot be used in Excel 2003.

TABLE 1-1 Pivot table limitations

Category	.xls Files	.xlsx Files
Number of row fields	Limited by available memory	1,048,576 (could be limited by available memory)
Number of column fields	256	16,384
Number of page fields	256	16,384
Number of data fields	256	16,384
Number of unique items in a single pivot field	32,500	1,048,576 (could be limited by available memory)
Number of calculated items	Limited by available memory	Limited by available memory
Number of pivot table reports on one worksheet	Limited by available memory	Limited by available memory

A word about compatibility

Excel provides a tool to identify any problems with backward compatibility. To check compatibility, select File, Info, Check For Issues, Check Compatibility, as shown in Figure 1-9.

Figure 1-9 Open the Check For Issues drop-down menu to find the Check Compatibility tool.

In the Compatibility Checker dialog box, use the Select Versions To Show drop-down menu to choose which version of Excel your coworkers might be using. The dialog box shows issues with your pivot tables (see Figure 1-10). Anything that is labeled “Significant Loss Of Functionality” should be corrected. Anything labeled “Minor Loss Of Fidelity” refers to formatting issues.

FIGURE 1-10 The Compatibility Checker alerts you about any compatibility issues before you save to a previous version of Excel.

Next steps

In the next chapter, you’ll learn how to prepare your data to be used by a pivot table. Chapter 2, “Creating a basic pivot table,” also walks through creating your first pivot table report using the Create PivotTable dialog box.