Conventions Used in This Book

This book contains the following special elements.

NOTE

Notes provide additional information outside the main thread of the chapter discussion that might still be useful for you to know.

TIP

Tips provide you with quick workarounds and timesaving techniques to help you do your work more efficiently .

CAUTION

Cautions warn you about potential pitfalls you might encounter. Pay attention to these, because they alert you to problems that otherwise could cause you hours of frustration.

CASE STUDY

Case studies provide a real-world look at topics previously introduced in the chapter.

Chapter 1. Pivot Table Fundamentals

IN THIS CHAPTER

What Is a Pivot Table?

Why Should You Use a Pivot Table?

When Should You Use a Pivot Table?

The Anatomy of a Pivot Table

Pivot Tables Behind the Scenes

Limitations of Pivot Table Reports

What Is a Pivot Table?

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 the details of the object around. 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 your dataset. When you look at your dataset through a pivot table, you have the opportunity to see details in your data you may not have noticed before. Furthermore, you can turn your pivot table to see your data from different perspectives. The dataset itself doesn't change, and it's not connected to the pivot table. The pivot table is simply a tool you are using to create a unique perspective on your data.

A pivot table allows you to create an interactive view of your dataset. We call this view a pivot table report . With a pivot table report, you can quickly and easily categorize your data into groups, summa rize large amounts of data into meaningful information, and perform a wide 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 interactively drag and drop fields within your report, dynamically changing your perspective and recalculating totals to fit your current view.

Why Should You Use a Pivot Table?

As a rule, your dealings in Excel can be split into two categories: calculating data and shaping (formatting) data. Although there are many built-in tools and formulas that facilitate both of these, the pivot table is often the fastest and most efficient way to calculate and shape data.

Let's look at one simple scenario that will illustrate this point.

You have just given your manager some revenue information by month, and he has pre dictably asked for more information. He adds a note to the worksheet and emails it back to you. As shown 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 will provide 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 amount of credits by month and shape the results into an extract that will fit 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 into a horizontal format.

If you were to create the extract manually, it would take 25 mouse clicks and three keystrokes:

To change dates to month format: six clicks
To create subtotals: four clicks
To extract subtotals: 10 clicks, three keystrokes
To transpose the orientation from vertical to horizontal: five clicks

In contrast, creating the extract with a pivot table takes just 10 mouse clicks:

To create the pivot table report: five clicks
To group dates into months: four clicks
To transpose the orientation from vertical to horizontal: one click

Both methods give you the same extract, which can be pasted 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 this task cuts down the number of actions by more than half and reduces the possibility of human error. Over and above that, using a pivot table allows for the quick and easy shaping and formatting of the data.

What this example shows is 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 using 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 may have to accomplish with Excel. Pivot tables can't do everything, but knowing how to use just the basics of pivot table functionality can take your data analysis and productivity to a new level.