Table of Contents

Introduction Chapter 1 Pivot Table Fundamentals What Is a Pivot Table? Why Should You Use a Pivot Table? When Should You Use a Pivot Table? The Anatomy of a Pivot Table Values Area Rows Area Columns Area Filters Area Pivot Tables Behind the Scenes Limitations of Pivot Table Reports A Word About Compatibility Next Steps Chapter 2 Creating a Basic Pivot Table Ensure Your Data Is in a Tabular Layout Avoid Storing Data in Section Headings Avoid Repeating Groups as Columns Eliminate Gaps and Blank Cells in Your Data Source Apply Appropriate Type Formatting to Your Fields Summary of Good Data Source Design Creating a Basic Pivot Table Adding Fields to the Report Adding Layers to Your Pivot Table Rearranging Your Pivot Table Creating a Report Filter Understanding the Recommended PivotTables Feature Using Slicers Creating a Standard Slicer Creating a Timeline Slicer Keeping Up with Changes in Your Data Source Changes Have Been Made to Your Existing Data Source Your Data Source's Range Has Been Expanded with the Addition of Rows or Columns Sharing the Pivot Cache Saving Time with New Pivot Table Tools Deferring Layout Updates Starting Over with One Click Relocating Your Pivot Table Next Steps Chapter 3 Customizing a Pivot Table Making Common Cosmetic Changes Applying a Table Style to Restore Gridlines Changing the Number Format to Add Thousands Separators Replacing Blanks with Zeros Changing a Field Name Making Report Layout Changes Using the New Compact Layout Using the Outline Form Layout Using the Traditional Tabular Layout Controlling Blank Lines, Grand Totals, and Other Settings Customizing the Pivot Table Appearance with Styles and Themes Customizing a Style Modifying Styles with Document Themes Changing Summary Calculations Understanding Why One Blank Cell Causes a Count Using Functions Other Than Count or Sum Adding and Removing Subtotals Suppress Subtotals When You Have Many Row Fields Adding Multiple Subtotals for One Field Changing the Calculation in a Value Field Showing Percentage of Total Using % Of to Compare One Line to Another Line Showing Rank Tracking Running Total and Percent of Running Total Display Change from a Previous Field Tracking Percent of Parent Item Track Relative Importance with the Index Option Next Steps Chapter 4 Grouping, Sorting, and Filtering Pivot Data Grouping Pivot Fields Grouping Date Fields Including Years When Grouping by Months Grouping Date Fields by Week Ungrouping Grouping Numeric Fields Using the PivotTable Fields List Docking and Undocking the PivotTable Fields List Rearranging the PivotTable Fields List Using the Areas Section Drop-Downs Sorting in a Pivot Table Sorting Customers into High-to-Low Sequence Based on Revenue Using a Manual Sort Sequence Using a Custom List for Sorting Filtering the Pivot Table: An Overview Using Filters for Row and Column Fields Filtering Using the Check Boxes Filtering Using the Search Box Filtering Using the Label Filters Filtering a Label Column Using Information in a Values Column Creating a Top-Five Report Using the Top 10 Filter Filtering Using the Date Filters in the Label Drop-Down Filtering Using the Filters Area Adding Fields to the Filters Area Choosing One Item from a Filter Choosing Multiple Items from a Report Filter Replicating a Pivot Table Report for Each Item in a Filter Filtering Using Slicers and Timelines Using Timelines to Filter by Date Driving Multiple Pivot Tables from One Set of Slicers Next Steps Chapter 5 Performing Calculations Within Your Pivot Tables Introducing Calculated Fields and Calculated Items Method 1: Manually Add the Calculated Field to Your Data Source Method 2: Use a Formula Outside Your Pivot Table to Create the Calculated Field5 Method 3: Insert a Calculated Field Directly into Your Pivot Table Creating Your First Calculated Field Creating Your First Calculated Item Understanding the Rules and Shortcomings of Pivot Table Calculations Remembering the Order of Operator Precedence Using Cell References and Named Ranges Using Worksheet Functions Using Constants Referencing Totals Rules Specific to Calculated Fields Rules Specific to Calculated Items Managing and Maintaining Your Pivot Table Calculations Editing and Deleting Your Pivot Table Calculations Changing the Solve Order of Your Calculated Items Documenting Your Formulas What's Next Chapter 6 Using Pivot Charts and Other Visualizations What Is a Pivot Chart...Really? Creating Your First Pivot Chart Keeping Pivot Chart Rules in Mind Changes in the Underlying Pivot Table Affect Your Pivot Chart The Placement of Data Fields in Your Pivot Table Might Not Be Best Suited for Your Pivot Chart A Few Formatting Limitations Still Exist in Excel 2013 Examining Alternatives to Using Pivot Charts Method 1: Turn Your Pivot Table into Hard Values Method 2: Delete the Underlying Pivot Table Method 3: Distribute a Picture of the Pivot Chart Method 4: Use Cells Linked Back to the Pivot Table as the Source Data for Your Chart Using Conditional Formatting with Pivot Tables Creating Custom Conditional Formatting Rules What's Next Chapter 7 Analyzing Disparate Data Sources with Pivot Tables Using Multiple Consolidation Ranges Creating a Multiple Consolidation Pivot Table Analyzing the Anatomy of a Multiple Consolidation Ranges Pivot Table The Row Field The Column Field The Value Field The Page Fields Using the Internal Data Model Building Out Your First Data Model Managing Relationships in the Data Model Adding a New Table to the Data Model Removing a Table from the Data Model Create a New Pivot Table Using the Data Model Limitations of the Internal Data Model Building a Pivot Table Using External Data Sources Building a Pivot Table with Microsoft Access Data Building a Pivot Table with SQL Server Data What's Next Chapter 8 Sharing Pivot Tables with Others Designing a Workbook as an Interactive Web Page Sharing a Link to Your Web Workbook Embedding Your Workbook in a Blog Post or Your Web Page Sharing Pivot Tables with Other Versions of Office Chapter 9 Working with and Analyzing OLAP Data What Is OLAP? Connecting to an OLAP Cube Understanding the Structure of an OLAP Cube Understanding the Limitations of OLAP Pivot Tables Creating Offline Cubes Breaking Out of the Pivot Table Mold with Cube Functions Adding Calculations to Your OLAP Pivot Tables Creating Calculated Measures Creating Calculated Members Managing Your OLAP Calculations Performing What-If Analysis with OLAP Data Next Steps Chapter 10 Mashing Up Data with PowerPivot Understanding the Benefits and Drawbacks of PowerPivot and the Data Model Merge Data from Multiple Tables Without Using VLOOKUP Import 100 Million Rows into Your Workbook Create Better Calculations Using the DAX Formula Language Other Benefits of the PowerPivot Data Model in All Editions of Excel Benefits of the Full PowerPivot Add-In with Excel Pro Plus Understanding the Limitations of the Data Model Joining Multiple Tables Using the Data Model in Regular Excel 2013 Preparing Data for Use in the Data Model Adding the First Table to the Data Model Adding the Second Table and Defining a Relationship Tell Me Again-Why Is This Better Than Doing a VLOOKUP? Using QuickExplore Creating a New Pivot Table from an Existing Data Model Getting a Distinct Count Using the PowerPivot Add-In from Excel 2013 Pro Plus Enabling PowerPivot Import a Text File Add Excel Data by Copying and Pasting Add Excel Data by Linking Define Relationships Add Calculated Columns Using DAX Build a Pivot Table Understanding Differences Between PowerPivot and Regular Pivot Tables Two Kinds of DAX Calculations DAX Calculations for Calculated Columns Using RELATED() to Base a Column Calculation on Another Table Using DAX to Create a Calculated Field in the Pivot Table DAX Calculated Fields Implicitly Respect the Filters Define a DAX Calculated Field Is Unfilter Even a Word? CALCULATE Is a Super-Enhanced Version of SUMIFS Adding Fields to the Values Area Generates DAX Calculated Fields Using a Calendar Table to Enable Time Intelligence Functions Adding the Data to PowerPivot and Formatting It PowerPivot Doesn't Automatically Sort by Custom Lists Create a PivotTable and Marvel at the Results This Is a Discussion About Time Intelligence Using Key Performance Indicators Setting up a KPI Compared to an Absolute Value Setting Up a KPI Compared to a Calculated Target Value Other Notes About PowerPivot Combination Layouts Getting Your Data into PowerPivot with SQL Server Other Issues Next Steps Chapter 11 Dashboarding with Power View Preparing Your Data for Power View Creating a Power View Dashboard Every New Dashboard Element Starts as a Table Subtlety Should Be Power View's Middle Name Convert the Table to a Chart Add Drill-Down to a Chart To Begin a New Element, Drag a Field to a Blank Spot on the Canvas Every Chart Point Is a Filter for Every Other Element Adding a Real Slicer The Filter Pane Can Be Confusing Use Tile Boxes to Filter One or a Group of Charts Replicating Charts Using Multiples Showing Data on a Map Using Table or Card View with Images Changing the Calculation Animating a Scatter Chart Over Time Some Closing Tips on Power View Animating Pivot Table Data on a Map Next Steps Chapter 12 Enhancing Your Pivot Table Reports with Macros Why Use Macros with Your Pivot Table Reports? Recording Your First Macro Creating a User Interface with Form Controls Altering a Recorded Macro to Add Functionality What's Next Chapter 13 Using VBA to Create Pivot Tables Enabling VBA in Your Copy of Excel Using a File Format That Enables Macros Visual Basic Editor Visual Basic Tools The Macro Recorder Understanding Object-Oriented Code Learning Tricks of the Trade Writing Code to Handle Any Size Data Range Using Super-Variables: Object Variables Using With and End With to Shorten Code Understanding Versions Code for New Features Won't Work in Previous Versions Building a Pivot Table in Excel VBA Adding Fields to the Data Area Formatting the Pivot Table Dealing with Limitations of Pivot Tables Filling Blank Cells in the Data Area Filling Blank Cells in the Row Area Learning Why You Cannot Affect a Pivot Table by Inserting or Deleting Cells Controlling Totals Determining the Size of a Finished Pivot Table to Convert It to Values Pivot Table 201: Creating a Report Showing Revenue by Category Ensuring Table Layout Is Utilized Rolling Daily Dates Up to Years Eliminating Blank Cells Controlling the Sort Order with AutoSort Changing the Default Number Format Suppressing Subtotals for Multiple Row Fields Copying a Finished Pivot Table as Values to a New Workbook Handling Final Formatting Adding Subtotals to Get Page Breaks Putting It All Together Calculating with a Pivot Table Addressing Issues with Two or More Data Fields Using Calculations Other Than Sum Calculated Data Fields Calculated Items Calculating Groups Using Show Values As to Perform Other Calculations Using Advanced Pivot Table Techniques Using AutoShow to Produce Executive Overviews Using ShowDetail to Filter a Recordset Creating Reports for Each Region or Model Manually Filtering Two or More Items in a PivotField Using the Conceptual Filters Using the Search Filter Setting up Slicers to Filter a Pivot Table Using the Data Model in Excel 2013 Add Both Tables to the Data Model Create a Relationship Between the Two Tables Define the Pivot Cache and Build the Pivot Table Add Model Fields to the Pivot Table Add Numeric Fields to the Values Area Putting It All Together Next Steps Chapter 14 Advanced Pivot Table Tips and Techniques Tip 1: Force Pivot Tables to Refresh Automatically Tip 2: Refresh All Pivot Tables in a Workbook at the Same Time Tip 3: Sort Data Items in a Unique Order Not Ascending or Descending Tip 4: Turn Pivot Tables into Hard Data Tip 5: Fill the Empty Cells Left by Row Fields Option 1: Implement the Repeat All Data Items Feature Option 2: Use Excel's Go To Special Functionality Tip 6: Add a Rank Number Field to Your Pivot Table Tip 7: Reduce the Size of Your Pivot Table Reports Delete Your Source Data Tab Tip 8: Create an Automatically Expanding Data Range Tip 9: Compare Tables Using a Pivot Table Tip 10: AutoFilter a Pivot Table Tip 11: Transpose a Data Set with a Pivot Table Step 1: Combine All Non-Column-Oriented Fields into One Dimension Field Step 2: Create a Multiple Consolidation Ranges Pivot Table Step 3: Double-Click the Grand Total Intersection of Row and Column Step 4: Parse Your Dimension Column into Separate Fields Tip 12: Force Two Number Formats in a Pivot Table Tip 13: Create a Frequency Distribution with a Pivot Table Tip 14: Use a Pivot Table to Explode a Data Set to Different Tabs Tip 15: Use a Pivot Table to Explode a Data Set to Different Workbooks What's Next Chapter 15 Dr. Jekyll and Mr. GetPivotData Turning Off the Evil GetPivotData Problem Preventing GetPivotData by Typing the Formula GetPivotData Is Surely Evil-Turn It Off Why Did Microsoft Force GetPivotData on Us? Using GetPivotData to Solve Pivot Table Annoyances Build an Ugly Pivot Table Build the Shell Report Using GetPivotData to Populate the Shell Report Updating the Report in Future Months 9780789748751 TOC 12/18/2013

About the Author

Bill Jelen, Excel MVP and the host of MrExcel.com, has been using spreadsheets since 1985, and he launched the MrExcel.com website in 1998. Bill was a regular guest on Call for Help with Leo Laporte and has produced more than 1,500 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 38 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance magazine and his Excel tips appear regularly in CFO Excel Pro newsletter and CFO Magazine. Before founding MrExcel.com, Bill Jelen 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. He lives near Akron, Ohio with his wife, Mary Ellen. Mike Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Excel. He has more than 15 years of experience consulting and developing Office solutions. Mike has been named a Microsoft MVP for his ongoing contributions to the Excel community. In his spare time, he runs a free tutorial site, www.datapigtechnologies.com, where he shares basic Access and Excel tips to the Office community.