Pivot Table Data Crunching for Microsoft Office Excel 2007 (Business Solutions)

Product Description

This book consolidates all the best functionality of pivot tables into one guide that provides you with a meaningful tutorial, offering practical solutions to day-to-day problems. Within just the first 2 chapters, you will be creating basic pivot tables, increasing productivity, and producing reports in minutes instead of hours. Within the first 6 chapters, you will learn how to use pivot tables to quickly highlight your top 10 customers or bottom 5 products in profitability; quickly create analysis comparing sales this period to last period by product or region or both; easily summarize daily transactional data by month or quarter or year in a few mouse clicks-all without knowing any formulas!

By the end of the book, you will truly be a pivot table guru, automating pivot tables using VBA, creating pivot tables with external data in OLAP cubes, and even creating dynamic reporting systems so that your managers can answer their own queries with a few mouse clicks.

- Take advantage of the numerous pivot table recipes

- Create powerful summary reports in seconds

- Build ad-hoc query tools with ease

- Enhance your Executive Reporting

- Filter reports to top 10 customers or products

- Add dynamic charts to reports

- Quickly summarize daily data by month, quarter, or year

Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1

1------------- Pivot Table Fundamentals . . . . . . . . . . . . . . . . . . . . . . . . . . . .11

2------------- Creating a Basic Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . .21

3------------- Customizing a Pivot Table . . . . . . . . . . . . . . . . . . . . . . . . . . . .45

4------------- Controlling the Way You View Your Pivot Data . . . . . . . . . . . .83

5------------- Performing Calculations Within Your Pivot Tables . . . . . . . . 117

6------------- Using Pivot Charts and Other Visualizations . . . . . . . . . . . . .141

7------------- Analyzing Disparate Data Sources with Pivot Tables . . . . . . 167

8------------- Sharing Pivot Tables with Others . . . . . . . . . . . . . . . . . . . . . .189

9------------- Working with and Analyzing OLAP Data . . . . . . . . . . . . . . . . 201

10---------- Enhancing Your Pivot Table Reports with Macros . . . . . . . . .215

11---------- Using VBA to Create Pivot Tables . . . . . . . . . . . . . . . . . . . . . 231

12---------- Common Pivot Table Issues and Questions . . . . . . . . . . . . . .291

A------------ Finding Pivot Table Commands on the Ribbon . . . . . . . . . . . .315

Product Details

* Amazon Sales Rank: #13939 in Books
* Published on: 2007-01-05
* Original language: English
* Number of items: 1
* Binding: Paperback
* 360 pages

Editorial Reviews

About the Author

Bill Jelen is Mr. Excel! He is principal behind the leading Excel website, MrExcel.com. He honed his pivot table wizardry during a 12-year tenure as a financial analyst for a fast growing public computer firm. Armed with only a spreadsheet, he learned how to turn thousands of rows of transactional data into meaningful summaries in record time. He is an accomplished author of books on Excel and is a regular guest on “Call For Help” on TechTV Canada. As an Excel consultant, he has written Excel VBA solutions for hundreds of clients around the English-speaking world. His website hosts over 12 million page views annually.



Michael Alexander is a Microsoft Certified Application Developer (MCAD) with over 14 years experience developing business solutions with Microsoft Office, VBA, and .Net. He currently lives in Frisco, Texas, where he works as a senior program manager for a top technology firm. In his spare time, he runs a free tutorial site, www.datapigtechnologies.com, where he shares basic Access and Excel tips with intermediate users.

Excerpt. © Reprinted by permission. All rights reserved.
Introduction

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.

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