Pivot Table Data Crunching for Microsoft Office Excel 2007

Read an Excerpt

IntroductionIntroduction

Pivot tables are the single most powerful feature in all of Excel. They 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-90s led to many incredible features, but none as powerful as the pivot table.

With a pivot table, you can take 1 million rows of transactional data and transform it 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 allow you to change your analysis on the fly by simply moving fields from one area of a report to another.

There is simply no other tool in Excel that gives you the flexibility and analytical power that pivot tables can give you.

What You Will Learn from This Book

It is widely agreed that close to 50% of Excel users leave 80% of Excel untouched. That is, most users don't 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 more than 12 years now, they remain one of the most underutilized tools in the entire Microsoft Office Suite. If you have picked up this book, you are savvy enough to have heard of pivot tables or even have used them on occasion. You have a sense that pivot tables have a power that you are not using, and you want to learn how to leverage that power to quickly increase your productivity.

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 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 2007's Pivot Tables

Microsoft streamlined the pivot table interface to make it easier to use. In the last six versions of Excel, you generally created and modified a pivot table by dragging field names around the worksheet. Excel provided subtle visual clues about where a dropped field would appear, but these clues were too subtle for most. If you accidentally dropped a text field in the data area instead of the row area, disaster would result. Now, in Excel 2007, you can build a pivot table by checking a few boxes. Excel's IntelliSense figures out the best location for the field. To modify the default, you can drag field names around the PivotTable Field List.

Also new in Excel 2007 is the easier interface for sorting and filtering fields in a pivot table. Whereas sorting was formerly hidden three levels deep in the menu system, it is now just one click away from the PivotTable Field List.

Formatting such as heat maps, data bars, banded rows, and columns are now available as icons on the Excel 2007 Ribbon.

Finally, Microsoft is pushing a high-end server product that allows many people to access data stored in pivot tables. It is no surprise that pivot tables play a key role in the interactivity of Excel Services for SharePoint.

Skills Required to Use This Book

We have created a reference that is comprehensive enough for hard-core analysts yet relevant to casual users of Excel. The bulk of the book covers how to use pivot tables in the Excel user interface. The final chapter describes how to create pivot tables in Excel's powerful VBA macro language. This means that any user who has a firm grasp of the basics (preparing data, copying, pasting, entering simple formulas) should have no problem understanding the concepts in this book.

Life Before Pivot Tables

Imagine that it is 1992. You are using Lotus 1-2-3 or Excel 4. You have thousands of rows of transactional data, as shown in Figure I.1. Your manager asks you to prepare a summary report showing revenue by region and product.

In 1992, preparing this report was a daunting task. It required superhuman spreadsheet skills that few could master. Here are the steps you needed to take:

1. You need to get a list of the unique regions in the dataset. Use the Advanced Filter command with Unique Records Only (see Figure I.2) to extract a list of the unique regions.
2. You need to get a list of the unique products in the dataset. Use the Advanced Filter command with Unique Records Only a second time to extract a list of the unique products.
3. You need to turn the list of products sideways so that it runs across the columns. Copy the list of unique models. Then choose Edit, Paste Special, Transpose to arrange the products as headings going across the report. You now have a skeleton of the report, as shown in Figure I.3.
4. You could use the DSUM function to total a column based on one criterion, but not based on two criteria. Therefore, you need to abandon typical functions and instead rely on an array formula. Before entering the array formula, set up two fields above the report to hold a sample region and sample model.
5. In the corner cell of the report, build an array formula to test whether the region column is North and the Model column is 4055T, and if so, add the corresponding row from the Revenue column. After typing this formula, remember to press Ctrl+Shift+Enter; otherwise, the formula will not work. The formula is shown in the formula bar in Figure I.4.

Tip - For a complete explanation of two-condition sums using array formulas, see http://www.MrExcel.com/tip031.shtml.

1. You know you're a hard-core data analyst if you can still imagine pressing the keystrokes for /Data Table 2 in Lotus 1-2-3. Figure I.5 shows the equivalent function in Excel. In Excel 2007, this command is found in Data, Data Tools, What If Analysis, Data Table.
2. Finally, after using two advanced filters and a Paste Special command, writing the hardest formula in the world, and then using the Data Table command, you have the result your manager is looking for, as shown in Figure I.6. If you could pull off this analysis in 10 minutes, you were doing an amazing job.

Now, if your manager takes a look at the report and asks you to add Market to the analysis, you are nearly back at square one and are looking at an additional 15 minutes to produce the new report.

The Invention of the Pivot Table

The concept that led to today's pivot table came from the halls of the Lotus Development Corporation with a revolutionary spreadsheet program called Lotus Improv. Improv was envisioned in 1986 by Pito Salas of the Advanced Technology Group at Lotus. Realizing that spreadsheets often have patterns of data, Pito concluded that if a user could build a tool that could recognize these patterns, then he could build enhanced data models. Lotus ran with the concept and started developing the next-generation spreadsheet.

Throughout 1987, Lotus demonstrated its new program to a few companies. In 1988, Steve Jobs saw the program and immediately wanted it developed for his upcoming NeXT computer platform. The program, finally named Lotus Improv, was eventually shipped in 1991 for the NeXT platform. A version for Windows was introduced in 1993.

The core concept behind Improv was that data, data views, and formulas should be encapsulated as separate entities and treated as different animals. For the first time in a spreadsheet program, a dataset was given a name that could be grouped into larger categories. This naming and grouping capability paved the way for the most powerful feature in Improv: rearranging data. With Improv, a user could define and store a set of categories and then change the view by simply dragging the category names with the mouse. The user could also create totals and group summaries.

Microsoft eventually picked up on this concept in its pivot table functionality in Excel 5. Years later, with the release of Excel 97, Microsoft offered users an enhanced pivot table wizard and key improvements to pivot table functionality, such as the capability to add calculated fields. Excel 97 also opened the pivot cache to developers, fundamentally changing the way pivot tables are created and managed. Microsoft introduced the pivot chart with Excel 2000, providing users a way to represent pivot tables graphically. Since Excel 2000, changes made to pivot tables have been mainly cosmetic, much to the chagrin of pivot table fans everywhere.

Life After Pivot Tables

You have 600,000 rows of transactional data, as discussed in the previous case study. Your manager asks you to prepare a summary report showing revenue by Region and Model. Luckily, you have pivot tables at your disposal. Here are the steps you would follow today:

1. Select a single cell in your dataset. Choose PivotTable from the Insert tab. Click OK. You are given a blank pivot table, as shown in Figure I.7.
2. From the Pivot Table Field List, click the Region check box. Excel adds it to the left side of the pivot table. Click the check box next to Product_Number. In the lower portion of the dialog box, drag Product_Description from the Row Labels section to the Column Labels section. Click the Sales_Amount field in the top of the Pivot Table Field List. After a total of six mouse clicks, you have the required report, as shown in Figure I.8.

If you are racing, you can actually create the report shown in Figure I.8 in exactly 10 seconds. This is an amazing accomplishment. Realistically, it would take you about 50 seconds at normal speed to create the report. If you are a spreadsheet wizard and are instead following the steps in the previous case study, the non–pivot table solution would take you at least 12 times longer.

Further, when your manager comes back with the request to add Market to the analysis, you need just seconds to check the Market field in the PivotTable Field List to add it to the report, as shown in Figure I.9.

Sample Files Used in This Book

All data files used throughout this book are available for download from http://www.mrexcel.com/pivotbookdata2007.html.

Conventions Used in This Book

This book follows certain conventions:

• Monospace—Text messages you see onscreen or code appears in a monospace font.
• Bold Monospace—Text you type appears in a bold, monospace font.
• Italic—New and important terms appear in italics.
• Initial Caps—Ribbon names, dialog box names, and dialog box elements are presented with initial capital letters so that you can identify them easily.

Office 2007 features a new interface called the Ribbon. The Ribbon in Excel 2007 is composed of several tabs labeled Home, Insert, Page Layout, and so on. When you click on the Page Layout tab, you see the icons available on the Page Layout tab.

When the active cell is inside a pivot table, two new tabs appear on the Ribbon. In the help files, Microsoft has been calling these tabs "PivotTable Tools Options" and "PivotTable Tools Design." For convenience, this book refers to these elements as the Options tab and the Design tab, respectively.

Within each Ribbon, the icons are arranged into logical groups. The name of each group is shown in a colored band at the bottom of the group. When referring to a specific icon, the book mentions the group name. To refer to the item in Figure I.10, this book says, "Click the Field Settings icon in the Active Field group of the Options tab."

In some cases, the Ribbon icon leads to a drop-down with additional choices. In these cases, the book lists the hierarchy of Ribbon, Group, Icon, Menu Choice, Submenu Choice. For example, in Figure I.11, the shorthand specifies "Choose Design, Layout, Blank Rows, Remove Blank Line After Each Item."

Special Elements

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 time-saving techniques to help you do your work more efficiently.

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

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

© Copyright Pearson Education. All rights reserved.