Pivot Table Data Crunching

by Jelen, Bill; Alexander, Michael

• ISBN13: 9780789734358
• ISBN10: 0789734354
• Edition: 1st
• Format: Paperback
• Copyright: 2005-06-21
• Publisher: Que Publishing

Summary

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 Crunchingoffers a comprehensive review of all the functionalities of Pivot Tables from authorBill Jelen, otherwise known asMr. Excelfrom www.mrexcel.com, andMichael 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, makingPivot Table Data Crunchinga beneficial resource for all levels of Excel users.

Author Biography

Bill Jelen is Mr. Excel! He is principal behind the leading Excel website, MrExcel.com Michael Alexander is a Microsoft Certified Application Developer (MCAD) with more than 13 years' experience developing business solutions with Microsoft Office, VBA, and .NET

Table of Contents

Introduction	1	(8)
Pivot Table Fundamentals	9	(8)
What Is a Pivot Table?	9	(1)
Why Should You Use a Pivot Table?	10	(1)
When Should You Use a Pivot Table?	11	(1)
The Anatomy of a Pivot Table	12	(2)
Data Area	12	(1)
Row Area	13	(1)
Column Area	13	(1)
Page Area	14	(1)
Pivot Tables Behind the Scenes	14	(1)
Limitations of Pivot Table Reports	15	(1)
Next Steps	15	(2)
Creating a Basic Pivot Table	17	(18)
Preparing Your Data for Pivot Table Reporting	17	(5)
Ensure Your Data Is in a Tabular Layout	18	(1)
Use Unique Headings That Occupy Only a Single Row of Data	18	(1)
Avoid Storing Data in Section Headings	19	(1)
Avoid Repeating Groups as Columns	19	(1)
Eliminate Gaps and Blank Cells in Your Data Source	20	(1)
Apply Appropriate Type Formatting to Your Fields	20	(1)
Summary of Good Data Source Design	20	(1)
Cleaning Up Data for Pivot Table Analysis	21	(1)
Creating a Basic Pivot Table	22	(11)
Introduction to the PivotTable Wizard	23	(2)
Drag Fields to the Report	25	(1)
Adding Fields to the Pivot Table	26	(1)
Rearranging the Pivot Table	27	(1)
Revenue by Market and Model	28	(4)
Watch the Mouse Pointer to Learn Where You Are Dropping a Field	32	(1)
Redisplay the Pivot Table Field List	32	(1)
Redisplay the Pivot Table Toolbar	32	(1)
Activate the PivotTable Wizard	33	(1)
Keeping Up with Changes in Your Data Source	33	(1)
Changes Have Been Made to Your Existing Data Source	33	(1)
Your Data Source's Range Has Been Expanded with the Addition of Rows or Columns	34	(1)
Next Steps	34	(1)
Customizing Fields in a Pivot Table	35	(20)
The Need to Customize	35	(1)
Displaying the PivotTable Field Dialog Box	35	(2)
Customizing Field Names	37	(1)
Applying Numeric Formats to Data Fields	38	(1)
Changing Summary Calculations	39	(2)
One Blank Cell Causes a Count	39	(1)
Using Functions Other Than Count or Sum	39	(2)
Adding and Removing Subtotals	41	(2)
Suppress Subtotals When You Have Many Row Fields	41	(1)
Adding Multiple Subtotals for One Field	42	(1)
Using Running Total Options	43	(10)
Display Change from Year to Year with Difference From	44	(2)
How Much Does Each Line of Business Contribute to the Total?	46	(1)
Seasonality Reports	46	(3)
Revenue by Line of Business Report	49	(4)
Next Steps	53	(2)
Formatting Your Pivot Table Report	55	(16)
Using AutoFormat	55	(3)
Applying Your Own Style	58	(3)
Setting Table Options	61	(9)
Grand Totals for Columns	62	(1)
Grand Totals for Rows	63	(1)
AutoFormat Table	63	(1)
Subtotal Hidden Page Items	63	(1)
Merged Labels	63	(1)
Preserve Formatting	63	(1)
Repeat Item Labels on Each Printed Page	64	(1)
Mark Totals with*	65	(1)
Page Layout	65	(1)
For Error Values Show	66	(1)
For Empty Cells Show	67	(1)
Set Print Titles	67	(1)
Formatting a PivotTable	68	(2)
Next Steps	70	(1)
Controlling the Way You View Your Pivot Data	71	(30)
Showing and Hiding Options	71	(8)
The Basics of Hiding an Item	71	(2)
Showing All Items Again	73	(1)
Showing or Hiding Most Items	73	(2)
Hiding or Showing Items Without Data	75	(2)
Hiding or Showing Items in a Page Field	77	(1)
Showing or Hiding Items in a Data Field	78	(1)
Sorting in a Pivot Table	79	(9)
Sorting Using the Advanced Options Dialog Box	80	(2)
Note the Effect of Layout Changes on AutoSort	82	(1)
Sorting Using the Manual Method	83	(2)
Sorting Using the Sorting Buttons on the Standard Toolbar	85	(3)
Producing Top 10 Reports	88	(3)
Grouping Pivot Fields	91	(8)
Grouping Date Fields	91	(2)
When Grouping by Months, Include Years	93	(1)
Grouping Date Fields by Week	94	(1)
Grouping Two Date Fields in One Report	95	(1)
Order Lead-Time Report	96	(1)
Grouping Numeric Fields	97	(1)
Grouping Text Fields	97	(2)
Grouping and Ungrouping	99	(1)
Next Steps	99	(2)
Performing Calculations Within Your Pivot Tables	101	(22)
Introducing Calculated Fields and Calculated Items	101	(3)
Method 1: Manually Add the Calculated Field to Your Data Source	102	(1)
Method 2: Use a Formula Outside of Your Pivot Table to Create the Calculated Field	103	(1)
Method 3: Insert a Calculated Field Directly into Your Pivot Table	103	(1)
Creating Your First Calculated Field	104	(7)
Summarizing Next Year's Forecast	107	(4)
Creating Your First Calculated Item	111	(6)
Creating a Mini-Dashboard	113	(4)
Rules and Shortcomings of Pivot Table Calculations	117	(3)
Order of Operator Precedence	117	(1)
Cell References and Named Ranges	118	(1)
Worksheet Functions	118	(1)
Constants	118	(1)
Referencing Totals	118	(1)
Rules Specific to Calculated Fields	118	(2)
Rules Specific to Calculated Items	120	(1)
Managing and Maintaining Your Pivot Table Calculations	120	(2)
Editing and Deleting Your Pivot Table Calculations	120	(1)
Changing the Solve Order or Your Calculated Items	121	(1)
Documenting Your Formulas	121	(1)
Next Steps	122	(1)
Creating and Using Pivot Charts	123	(18)
What Is a Pivot Chart Really?	123	(1)
Creating Your First Pivot Chart	123	(3)
Rules and Limitations of Pivot Charts	126	(9)
Pivot Chart Layout Optimization	127	(2)
Scatter, Bubble, and Stock Charts Off Limits	129	(1)
Limitations on Element Size and Location	129	(1)
Certain Customizations Aren't Permanent	129	(1)
Create a Dynamic Year-Over-Year Chart	130	(5)
Alternatives to Using Pivot Charts	135	(4)
Avoiding Overhead	135	(1)
Avoid the Formatting Limitations of Pivot Charts	136	(3)
Next Steps	139	(2)
Using Disparate Data Sources for Your Pivot Table	141	(14)
Working with Disparate Data Sources	141	(1)
Using Multiple Consolidation Ranges	142	(4)
The Anatomy of a Multiple Consolidation Range Pivot Table	146	(5)
The Row Field	146	(1)
The Column Field	146	(1)
The Value Field	147	(1)
The Page Fields	147	(1)
Redefining Your Pivot Table	148	(1)
Consolidate and Analyze Eight Datasets	148	(3)
Creating a Pivot Table from an Existing Pivot Table	151	(2)
Next Steps	153	(2)
Using External Data Sources for Your Pivot Table	155	(16)
Building a Pivot Table Using
External Data Sources	155	(1)
Working Around Excel's Data Management Limitations	155	(1)
About MS Query	156	(1)
Analyze a Dataset with More Than 83,000 Records with a Pivot Table	157	(4)
Importing and Using External Data Without the PivotTable Wizard	161	(1)
Creating Dynamic PivotTable Reporting Systems	161	(7)
Create a Standalone Dynamic Pivot Table Reporting System	162	(6)
Pivot Table Data Options	168	(1)
Next Steps	169	(2)
Leveraging the Power of OLAP Cubes	171	(14)
Defining OLAP?	171	(1)
Benefits of OLAP Cubes	171	(1)
Introduction to Data Warehouses and OLAP Cubes	172	(3)
Operational Data	172	(1)
Warehousing Your Data	173	(1)
Enter the Cube	174	(1)
Cubes Offer Prebuilt Data Views	174	(1)
Connecting to an OLAP Cube	175	(3)
Make the Connection to a Local Cube	175	(2)
Make the Connection to a Server Cube	177	(1)
Working with an OLAP Pivot Table	178	(3)
Arranging the Data	178	(1)
Drilling Into the Cube	179	(1)
Using Page Fields	180	(1)
Comparing OLAP Cubes' Pivot Tables to Excel Data	181	(2)
OLAP Handles More Data, Faster	181	(1)
Dimensions or Measures	181	(1)
OLAP Measures Are Already Grouped	182	(1)
Drill-Through of OLAP Data	182	(1)
Calculated Fields with OLAP	182	(1)
Other Pivot Table Features Operate the Same	182	(1)
Other Considerations When Using OLAP Cubes	183	(1)
Viewing an OLAP Cube Online	183	(1)
Writing Back to a Cube	183	(1)
Setting Actions in a Cube	183	(1)
Combining Cubes	183	(1)
Building a Local Cube	183	(1)
Next Steps	184	(1)
Enhancing Your Pivot Table Reports with Macros	185	(14)
Why Use Macros with Your Pivot Table Reports?	185	(1)
Recording Your First Macro	186	(1)
Creating a User Interface with Form Controls	187	(2)
Altering a Recorded Macro to Add Functionality	189	(8)
Synchronize Two Pivot Tables with One Combo Box	193	(4)
Next Steps	197	(2)
Using VBA to Create Pivot Tables	199	(46)
Introduction to VBA	199	(2)
Enable VBA in Your Copy of Excel	199	(1)
Visual Basic Editor	199	(1)
Visual Basic Tools	200	(1)
The Macro Recorder	201	(1)
Understanding Object-Oriented Code	201	(1)
Tricks of the Trade	201	(2)
Write Code to Handle Any Size Data Range	201	(2)
Use Super-Variables --- Object Variables	203	(1)
Versions	203	(1)
Build a Pivot Table in Excel VBA	203	(7)
Getting a Sum Instead of a Count	205	(2)
Cannot Move or Change Part of a Pivot Report	207	(1)
Size of a Finished Pivot Table	207	(3)
Revenue by Model for a Product Line Manager	210	(4)
Eliminate Blank Cells in the Data Area	212	(1)
Control the Sort Order with AutoSort	212	(1)
Default Number Format	212	(1)
Suppress Subtotals for Multiple Row Fields	213	(1)
Suppress Grand Total for Rows	214	(1)
Handle Additional Annoyances	214	(6)
New Workbook to Hold the Report	214	(1)
Summary on a Blank Report Worksheet	215	(1)
Fill Outline View	216	(1)
Final Formatting	216	(1)
Add Subtotals	217	(1)
Put It All Together	218	(2)
Issues with Two or More Data Fields	220	(6)
Calculated Data Fields	222	(2)
Calculated Items	224	(2)
Summarize Date Fields with Grouping	226	(4)
Group by Week	228	(2)
Advanced Pivot Table Techniques	230	(9)
AutoShow Feature to Produce Executive Overviews	230	(3)
ShowDetail to Filter a Recordset	233	(2)
Create Reports for Each Region or Model	235	(3)
Manually Filter Two or More Items in a PivotField	238	(1)
Control the Sort Order Manually	239	(1)
Sum, Average, Count, Min, Max, and More	239	(1)
Report Percentages	240	(3)
Percentage of Total	240	(1)
Percentage Growth from Previous Month	241	(1)
Percentage of a Specific Item	241	(1)
Running Total	241	(1)
Special Considerations for Excel 97	242	(1)
Next Steps	243	(2)
A Solutions to Common Questions and Issues with Pivot Tables	245	(18)
What does ``The PivotTable field name is not valid'' mean?	246	(1)
Problem	246	(1)
Solution	246	(1)
When I refresh my pivot table, my data disappears	246	(1)
Problem	246	(1)
Solution	246	(1)
When I try to group a field, I get an error message	246	(1)
Problem	246	(1)
Solution	247	(1)
Why can't I group my month fields into quarters?	247	(2)
Problem	247	(1)
Solution	248	(1)
My pivot table is showing the same data item twice	249	(1)
Problem	249	(1)
Solution	249	(1)
Why are deleted data items still showing up in the page field?	250	(1)
Problem	250	(1)
Solution	250	(1)
When I type a formula referencing a pivot table, I cannot copy the formula down	251	(1)
Problem	251	(1)
Solution	251	(1)
How can I sort data items in a unique order that is not ascending or descending?	251	(1)
Problem	251	(1)
Solution	252	(1)
How do I turn my pivot table into hard data?	252	(1)
Problem	252	(1)
Solution	252	(1)
Is there an easy way to fill the empty cells left by row fields?	252	(2)
Problem	252	(1)
Solution	253	(1)
Is there an easy way to fill the empty cells left by row fields in multiple columns?	254	(2)
Problem	254	(1)
Solution	255	(1)
How do I add a rank number field to my pivot table?	256	(2)
Problem	256	(1)
Solution	256	(2)
Why does my pivot chart exclude months for certain data items?	258	(1)
Problem	258	(1)
Solution	258	(1)
Can I create a pivot chart on the same sheet as my pivot table?	259	(1)
Problem	259	(1)
Solution	259	(1)
How can I turn my pivot table report into an interactive web page?	259	(4)
Problem	259	(1)
Solution	260	(3)
Index	263

Excerpts

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: 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. 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. You need to turn the list of models sideways so that it runs across the columns. Copy the lis