1. Pivot Table Fundamentals

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 have the opportunity to 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.

Calculating data

Shaping (formatting) data

Although many built-in tools and formulas facilitate both of these 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.

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.

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.

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.

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.

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.

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.

Figure 1.8 Filter fields are great for quickly filtering a report. The Region drop-down 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.

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.

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.

Table 1.1 highlights the pivot table limits in both .xls and .xlsx file types. Whereas some of these limitations remain constant, others are highly dependent on available system memory.

Table 1.1 Pivot Table Limitations

The first precautionary measure is Compatibility mode. Compatibility mode is a state that Excel automatically enters when opening an .xls file. When Excel is in Compatibility mode, it artificially takes on the limitations of Excel 2003. This means that while you are working with an .xls file, you cannot exceed any of the .xls file pivot table limitations shown in Table 1.1. This effectively prevents you from unwittingly creating a pivot table that is not compatible with previous versions of Excel. If you want to get out of Compatibility mode, you have to save the .xls file as one of Excel’s newer .xlsx or .xslm file formats (.xlsm files are macro-enabled Excel files).

The second precautionary measure is Excel’s Compatibility Checker. The Compatibility Checker is a built-in tool that checks for any compatibility issues when you try to save an Excel workbook as an .xls file. If your pivot table exceeds the bounds of .xls file limitations, the Compatibility Checker alerts you with a dialog similar to the one shown in Figure 1.9.

Figure 1.9 The Compatibility Checker alerts you about any compatibility issues before you save to a previous version of Excel.

With this dialog, Excel gives you the option of saving your pivot data as hard values in the new .xls file. If you choose to do so, the data from your pivot table is saved as hard values, but the pivot table object and the pivot cache are lost.