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: #14265 in Books
• Published on: 2007-01-05
• Original language: English
• Number of items: 1
• Binding: Paperback
• 360 pages

Features

• ISBN13: 9780789736017
• Condition: NEW
• Notes: Brand New from Publisher. No Remainder Mark.
• Click here to view our Condition Guide and Shipping Prices

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

---

Customer Reviews

Buidence from the Experts
Pivot Tables are one of the most powerful yet least used features of Excel. The author's MrExcel web site conducted a survey that showed that less than 42% of Excell users make use of pivot tables. I believe his estimate is high. Only experts are likely to visit his web site. I don't believe that 42% of the people who use Excel have even heard of pivot tables.

I further think that part of the problem is the name Pivot Table. This is primarily a Microsoft term (Microsoft has trademarked the word PivotTable - with no space), but the original functionality came from Lotus Improv.

It's foolish for me to try to explain pivot tables here when the book exists. Let me just say that if your task involves taking a lot of data that would make a lot more sense to you or your boss if it were summarized in various ways, Pivot Tables are for you, and the authors of this book are recognized experts in the field.

Note that the book has been brought up to date to cover Excel 2007. There are a number of changes in 2007 that are distinct improvements over previous versions but that require you to do things that are a bit different while the overall functionality remains basically the same.

Great stuff
This book helped tremendously during my early stages using PT, without a doubt I would recommend this book to someone who just started to incorporate Pivot Table into their work. One recommendation, It would be nice if a CD with all the work examples came with the book, unfortunately you have to download the example files from a website.

It does make Pivot Table EASY!
I have no previous experience of either Pivot Table or Office 2007. However, by following the instructions of this book and keep practicing, I don't feel pivot table is so difficult as I did before. Highly recommend this book.