1. Pivot Table Fundamentals

Defining 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 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.

Why You Should Use a Pivot Table

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

Image Calculating data

Image 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.

Image

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.

Image

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.

Image

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:

Image Format dates to month: 3 clicks

Image Create subtotals: 4 clicks

Image Extract subtotals: 6 clicks, 3 keystrokes

Image Transpose vertical to horizontal: 5 clicks

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

Image Create the pivot table report: 5 clicks

Image Group dates into months: 3 clicks

Image 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.

Image

Figure 1.4 After adding credits to the report, you can calculate net revenue.

Advantages of Using a Pivot Table

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:

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

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

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

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

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

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

Image 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:

Image Values area

Image Rows area

Image Columns area

Image 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.

Image

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.

Image

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.

Image

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.

Image

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.

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

With Excel 2007, Microsoft introduced a dramatic increase in the number of rows and columns allowed in one worksheet. This increase in limits led to the creation of a new Excel file type: .xlsx. The.xlsx file type not only allows for more rows and columns than the legacy .xls files (used by Excel 2003 and prior versions), but also comes with limitation increases in many areas, including pivot tables.

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.

Image

Table 1.1 Pivot Table Limitations

A Word About Compatibility

As you can imagine, the extraordinary increases in pivot table limitations lead to some serious compatibility questions. For instance, what if you create a pivot table that contains more than 256 column fields and more than 32,500 unique data items? How are users with previous versions of Excel affected by this? Luckily, Excel allows for some precautionary measures that can help you avoid compatibility issues.

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).


Caution

Beware of the Convert option found under the Info section of the File menu. Although this command is designed to convert a previous file from Excel 2003 to Excel 2013, it actually deletes the Excel 2003 copy of the file.


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.

Image

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.


Note

For information on Excel’s compatibility tools, pick up Que Publishing’s Special Edition Using Microsoft Excel 2016, by Bill Jelen.


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 Pivot Table Wizard.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
3.128.200.140