Pivot Table Data Crunching - Softcover

Jelen, Bill; Alexander, Michael

Softcover

ISBN 10: 0789734354 ISBN 13: 9780789734358

Publisher: Que Pub, 2005

Synopsis

Become a savvy Microsoft Excel user. Pivot tables are a great feature in Excel that help you organize and analyze data, but not many Excel users know how to use pivot tables. Pivot Table Data Crunching offers a comprehensive review of all the functionalities of Pivot Tables from author Bill Jelen, otherwise known as Mr. Excel from www.mrexcel.com, and Michael Alexander, a Microsoft Certified Application Developer. The authors' practical scenarios and real-world advice demonstrate the benefits of Pivot Tables and how to avoid the common pitfalls of every day data crunching. Each solution presented in the book can be accomplished with resources available in the Excel interface, making Pivot Table Data Crunching a beneficial resource for all levels of Excel users.

About this title

About the Author

Bill Jelen is the principal behind the leading Excel website, MrExcel.com. As an Excel consultant, he has written Excel VBA solutions for hundreds of clients around the English-speaking world. His website hosts more than 12 million page views annually. Prior to founding MrExcel.com, Bill spent 12 years "in the trenches," working as a financial analyst for the finance, marketing, accounting, and operations departments of a $500 million public company. His duties included turning large amounts of mainframe data into meaningful reports. Working initially with Lotus 1-2-3 and then Excel, Bill honed techniques to take massive amounts of data and produce meaningful reports in record time. Bill is the author of seven books on Microsoft Excel.

Michael Alexander is a Microsoft Certified Application Developer (MCAD) with more than 13 years' experience consulting and developing office solutions. Michael started his career in consulting and development at the White House Communications Agency in Washington D.C., later parleying his experience with VBA and VB into a successful career in the private sector, developing middleware and reporting solutions for a wide variety of industries. He currently lives in Plano, Texas, where he heads an analytical services group for a $700 million company. In his spare time he runs a free tutorial site, http://www.datapigtechnologies.com, where he shares basic Access and Excel tips with intermediate users.

© Copyright Pearson Education. All rights reserved.

Excerpt. © Reprinted by permission. All rights reserved.

INTRODUCTION

INTRODUCTION

In this introduction

• What You Will Learn from This Book

• Skills Required to Use This Book

• The Invention of the Pivot Table

• Conventions Used in This Book

Pivot tables are the single most powerful feature in all of Excel. They came along during the 90s 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 65,000 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 dragging 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 upon that close to 50% of Excel users leave 80% of Excel untouched. That is to say that 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 10 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 there is power in pivot tables 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.

Skills Required to Use This Book

We have created a reference that is comprehensive enough for hardcore analysts, yet relevant to casual users of Excel. The bulk of the book will cover how to use pivot tables in the Excel user interface. The final chapter includes information on 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 40,000 rows of transactional data, as shown in Figure I.1. Your manager asks you to prepare a summary report showing revenue by region and model.

Figure I.1
As a financial analyst in 1992, your job is to produce a summary from this 40,000 row dataset.

In 1992, this was a daunting task. It required superhuman spreadsheet skills that few could master. Here are the steps you would need 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.

Figure I.2
Even today, the Advanced Filter command is not a lot of fun to use.

1. You need to get a list of the unique models in the dataset. Do a second Advanced Filter with Unique Records Only to extract a list of the unique models.

2. You need to turn the list of models sideways so that it runs across the columns. Copy the list of unique models. Then, do a Edit, Paste Special, Transpose to arrange the models as headings going across the report. You now have a skeleton of the report, as shown in Figure I.3.

Figure I.3
After a second Advanced Filter command and Edit, Paste Special, Transpose, you have this skeleton of the final report. There is still a long way to go.

1. The DSUM function could be used to total a column based on one criteria, 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 a sample model.

2. 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 up the corresponding row from the Revenue column. After typing this formula, remember to press Ctrl+Shift+Enter or else 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.

Figure I.4
With the array formula in the corner of the report, you are ready to use the not-so-intuitive Data Table 2 command.

1. You know you're a hardcore data analyst if you can still imagine hitting the keystrokes for /Data Table 2 in Lotus 1-2-3. Figure I.5 shows the equivalent function in Excel. The Table command on the Data menu will allow you to set up a table.

Figure I.5
The Data Table command replicates the formula in the top-left corner of the table, but replaces two references in the formula with the headings at the top and left of the report.

1. Finally, after using two advanced filters, a Paste Special, 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 this analysis off in 10 minutes, you were doing an amazing job.

Figure I.6
After 10 minutes displaying knowledge of obscure spreadsheet commands, you have produced the needed report.

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 one could build a tool that could recognize these patterns, then one could build enhanced data models. Lotus ran with the concept and started developing the next-generation spreadsheet.

Throughout 1987, Lotus demoed 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.

Pito Salas, inventor of the pivot table concept, is always working on cutting-edge products at http://www.salas.com.

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 ability to add calculated fields. Excel 97 also opened up 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 40,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 Report from the Data menu. Click Finish. You are given a blank pivot table, as shown in Figure I.7.

Figure I.7
After three mouse clicks, you have a blank pivot table report. Three more mouse clicks to go.

1. From the pivot table field list, drag Region and drop it where it reads "Drop Row Fields Here." Drag the Model field where it reads "Drop Column Fields Here." Drag the Revenue field where it reads "Drop Data Items Here." After a total of six mouse clicks, you have the required report, as shown in Figure I.8.

Figure I.8
Drag three headings to the report, and Excel calculates your report.

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, it takes just seconds to drag the Market field from the field list and drop it on the report, as shown in Figure I.9.

Figure I.9
Creating a new report with the Market field is as simple as dragging the field to the report.

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.

© Copyright Pearson Education. All rights reserved.

Edition details

• Publisher: Que Pub
• Publication date: 2005
• ISBN 10: 0789734354
• ISBN 13: 9780789734358
• Binding: Paperback
• Language: English
• Edition number: 1
• Number of pages: 274