Excel 2016 Pivot table data crunching

&>
IntroductionWhat You Will Learn from This Book .................................................................1What Is New in Excel 2016\'s Pivot Tables ...............................................................2Skills Required to Use This Book .........................................................................3Invention of the Pivot Table........................................................................4Sample Files Used in This Book ...........................................................6Conventions Used in This Book ............................................................6 Referring to Versions ..............................................................................7 Referring to Ribbon Commands.................................................7 Special Elements .....................................................................71 Pivot Table Fundamentals ............................................ 9Defining a Pivot Table .................................................................9Why You Should Use a Pivot Table .............................................10 Advantages of Using a Pivot Table .........................................11When to Use a Pivot Table ......................................................12Anatomy of a Pivot Table .........................................................12 Values Area ...........................................................................12 Rows Area........................................................................................13 Columns Area ...................................................................14 Filters Area ........................................................................14Pivot Tables Behind the Scenes ..........................................14Pivot Table Backward Compatibility .................................................15 A Word About Compatibility .................................................16Next Steps............................................................................172 Creating a Basic Pivot Table ........................................19Preparing Data for Pivot Table Reporting ..............................................19 Ensuring That Data Is in a Tabular Layout..........................................20 Avoiding Storing Data in Section Headings ...................................20 Avoiding Repeating Groups as Columns .............................................21 Eliminating Gaps and Blank Cells in the Data Source ...............................22 Applying Appropriate Type Formatting to Fields .......................................22 Summary of Good Data Source Design ........................................22How to Create a Basic Pivot Table ......................................................24 Adding Fields to a Report .......................................................26 Fundamentals of Laying Out a Pivot Table Report ........................................27 Adding Layers to a Pivot Table ...........................................................28 Rearranging a Pivot Table ...........................................................29 Creating a Report Filter.......................................................31Understanding the Recommended Pivot Table Feature...........................31Using Slicers ......................................................................................33 Creating a Standard Slicer ............................................................33 Creating a Timeline Slicer ...............................................................36Keeping Up with Changes in the Data Source .............................................39 Dealing with Changes Made to the Existing Data Source ............................39 Dealing with an Expanded Data Source Range Due to the Addition of Rows or Columns ..............39Sharing the Pivot Cache .........................................40Saving Time with New Pivot Table Tools ............................................41 Deferring Layout Updates ......................................................41 Starting Over with One Click ..................................................42 Relocating a Pivot Table .................................................43Next Steps.......................................................................433 Customizing a Pivot Table ................................................45Making Common Cosmetic Changes .................................................46 Applying a Table Style to Restore Gridlines ............................................47 Changing the Number Format to Add Thousands Separators ....................................48 Replacing Blanks with Zeros .........................................49 Changing a Field Name .....................................................51Making Report Layout Changes ..........................................52 Using the Compact Layout .............................................52 Using the Outline Layout ...................................................54 Using the Traditional Tabular Layout ...........................................55 Controlling Blank Lines, Grand Totals, and Other Settings ....................................57Customizing a Pivot Table\'s Appearance with Styles and Themes ......................................60 Customizing a Style .................................................................61 Modifying Styles with Document Themes ........................................62Changing Summary Calculations ....................................................63 Understanding Why One Blank Cell Causes a Count ..............................63 Using Functions Other Than Count or Sum ...............................65Adding and Removing Subtotals ....................................................65 Suppressing Subtotals with Many Row Fields ...........................................66 Adding Multiple Subtotals for One Field .......................................67Changing the Calculation in a Value Field .......................................67 Showing Percentage of Total .........................................................70 Using % Of to Compare One Line to Another Line ......................................71 Showing Rank ..........................................................................71 Tracking Running Total and Percentage of Running Total .............................72 Displaying a Change from a Previous Field ..........................................................73 Tracking the Percentage of a Parent Item ..............................................73 Tracking Relative Importance with the Index Option ...................................74Next Steps...............................................................754 Grouping, Sorting, and Filtering Pivot Data ..........................77Automatically Grouping Dates ...................................................77 Undoing Automatic Grouping ...........................................78 Understanding How Excel 2016 Decides What to Group ...................................78 Grouping Date Fields Manually ....................................................79 Including Years When Grouping by Months ....................................80 Grouping Date Fields by Week ...............................................81 Grouping Numeric Fields ............................................82Using the PivotTable Fields List ..............................................85 Docking and Undocking the PivotTable Fields List ................................87 Rearranging the PivotTable Fields List...................................87 Using the Areas Section Drop-Downs ..................................88Sorting in a Pivot Table ................................................89 Sorting Customers into High-to-Low Sequence Based on Revenue ..................89 Using a Manual Sort Sequence ..............................................92 Using a Custom List for Sorting ..................................................93Filtering a Pivot Table: An Overview ...................................................95Using Filters for Row and Column Fields .........................................96 Filtering Using the Check Boxes ..................................................96 Filtering Using the Search Box ......................................................97 Filtering Using the Label Filters Option ......................................98 Filtering a Label Column Using Information in a Values Column ..........................99 Creating a Top-Five Report Using the Top 10 Filter ...........................................101 Filtering Using the Date Filters in the Label Drop-down .........................................103Filtering Using the Filters Area ................................104 Adding Fields to the Filters Area .............................................104 Choosing One Item from a Filter ...................................................105 Choosing Multiple Items from a Filter ....................................................105 Replicating a Pivot Table Report for Each Item in a Filter ............................................105 Filtering Using Slicers and Timelines .........................................107 Using Timelines to Filter by Date .....................................................109 Driving Multiple Pivot Tables from One Set of Slicers .......................................110Next Steps......................................................................................1125 Performing Calculations in Pivot Tables ...........................113Introducing Calculated Fields and Calculated Items ............................................113 Method 1: Manually Add a Calculated Field to the Data Source ..........................114 Method 2: Use a Formula Outside a Pivot Table to Create a Calculated Field .............................115 Method 3: Insert a Calculated Field Directly into a Pivot Table ............................116Creating a Calculated Field ...................................116Creating a Calculated Item ...........................................124Understanding the Rules and Shortcomings of Pivot Table Calculations ..................127 Remembering the Order of Operator Precedence ......................................128 Using Cell References and Named Ranges ...........................................129 Using Worksheet Functions ....................................................................129 Using Constants ...........................................................................129 Referencing Totals ..........................................................................129 Rules Specific to Calculated Fields ......................................................129 Rules Specific to Calculated Items ...........................................................131Managing and Maintaining Pivot Table Calculations ......................................131 Editing and Deleting Pivot Table Calculations ......................................131 Changing the Solve Order of Calculated Items .........................................132 Documenting Formulas ........................................................133Next Steps............................................................................1346 Using Pivot Charts and Other Visualizations .........................135What Is a Pivot Chart...Really? ..........................................................135Creating a Pivot Chart ..........................................................136 Understanding Pivot Field Buttons ..............................................138Keeping Pivot Chart Rules in Mind ....................................................139 Changes in the Underlying Pivot Table Affect a Pivot Chart .................................139 Placement of Data Fields in a Pivot Table Might Not Be Best Suited for a Pivot Chart .....................139 A Few Formatting Limitations Still Exist in Excel 2016 ..................................141Examining Alternatives to Using Pivot Charts .......................................................145 Method 1: Turn the Pivot Table into Hard Values ...................................145 Method 2: Delete the Underlying Pivot Table ..........................................146 Method 3: Distribute a Picture of the Pivot Chart ....................................146 Method 4: Use Cells Linked Back to the Pivot Table as the Source Data for the Chart .............147Using Conditional Formatting with Pivot Tables ...............................................149 An Example of Using Conditional Formatting ...........................................149 Preprogrammed Scenarios for Condition Levels ........................................151Creating Custom Conditional Formatting Rules .............................................152Next Steps...................................................................................................1567 Analyzing Disparate Data Sources with Pivot Tables ................................157Using the Internal Data Model ..................................................158 Building Out Your First Data Model ............................................................158 Managing Relationships in the Data Model ......................................162 Adding a New Table to the Data Model .................................................163 Removing a Table from the Data Model ...................................................165 Creating a New Pivot Table Using the Data Model ........................................166 Limitations of the Internal Data Model ...............................................................167Building a Pivot Table Using External Data Sources .............................................168 Building a Pivot Table with Microsoft Access Data....................................................169 Building a Pivot Table with SQL Server Data ......................................................171Leveraging Power Query to Extract and Transform Data .............................................174 Power Query Basics..................................................................175 Understanding Query Steps ..........................................................181 Refreshing Power Query Data ....................................................183 Managing Existing Queries ................................................................183 Understanding Column-Level Actions ............................................185 Understanding Table Actions ..........................................................187 Power Query Connection Types ......................................................188Next Steps..........................................................................................1928 Sharing Pivot Tables with Others ........................................193Designing a Workbook as an Interactive Web Page .......................................................193Sharing a Link to a Web Workbook ................................................................196Sharing with Power BI ...............................................................................196 Preparing Data for Power BI ...........................................................197 Importing Data to Power BI .........................................................................197 Building a Report in Power BI ...................................................199 Using Q&A to Query Data ........................................................200 Sharing Your Dashboard ..............................................................202Next Steps........................................................................2029 Working with and Analyzing OLAP Data ..........................203Introduction to OLAP .............................................................................203Connecting to an OLAP Cube .....................................................204Understanding the Structure of an OLAP Cube ...................................................207Understanding the Limitations of OLAP Pivot Tables .........................................208Creating an Offline Cube ...................................................209Breaking Out of the Pivot Table Mold with Cube Functions ................................211 Exploring Cube Functions ............................................................212Adding Calculations to OLAP Pivot Tables .....................................................213 Creating Calculated Measures ............................................................214 Creating Calculated Members ..................................................217 Managing OLAP Calculations ............................................................220 Performing What-If Analysis with OLAP Data .....................................220Next Steps...............................................................................22210 Mashing Up Data with Power Pivot ...................................223Understanding the Benefits and Drawbacks of Power Pivot and the Data Model ............223 Merging Data from Multiple Tables Without Using VLOOKUP ..................................223 Importing 100 Million Rows into a Workbook .....................................................224 Creating Better Calculations Using the DAX Formula Language ...........................224 Other Benefits of the Power Pivot Data Model in All Editions of Excel .........................224 Benefits of the Full Power Pivot Add-in with Excel Pro Plus ...................................225 Understanding the Limitations of the Data Model .................................225Joining Multiple Tables Using the Data Model in Regular Excel 2016 ..............................226 Preparing Data for Use in the Data Model ..................................227 Adding the First Table to the Data Model ...................................................228 Adding the Second Table and Defining a Relationship ......................................229 Tell Me Again-Why Is This Better Than Doing a VLOOKUP? ..............................230 Creating a New Pivot Table from an Existing Data Model ....................................232 Getting a Distinct Count ...........................................................232Using the Power Pivot Add-in Excel 2016 Pro Plus .....................................234 Enabling Power Pivot ....................................................................234 Importing a Text File Using Power Query ............................................235 Adding Excel Data by Linking ..................................................................236 Defining Relationships ...........................................................................236 Adding Calculated Columns Using DAX ......................................................237 Building a Pivot Table ....................................................................237Understanding Differences Between Power Pivot and Regular Pivot Tables ............................238Using DAX Calculations .............................................................239 Using DAX Calculations for Calculated Columns ................................................239 Using DAX to Create a Calculated Field in a Pivot Table ...........................240 Filtering with DAX Calculated Fields ...............................................240 Defining a DAX Calculated Field ..................................................240 Using Time Intelligence .............................................................242Next Steps.....................................................................................24311 Dashboarding with Power View and 3D Map ..........................245Preparing Data for Power View ....................................................245Creating a Power View Dashboard .............................................................247 Every New Dashboard Element Starts as a Table ................................................249 Subtlety Should Be Power View\'s Middle Name .........................................249 Converting a Table to a Chart .............................................................250 Adding Drill-down to a Chart ........................................251 Beginning a New Element by Dragging a Field to a Blank Spot on the Canvas ..............252 Filtering One Chart with Another One ...............................................252 Adding a Real Slicer ..............................................................................253 Understanding the Filters Pane ..................................................................254 Using Tile Boxes to Filter a Chart or a Group of Charts .....................................255Replicating Charts Using Multiples .................................................256Showing Data on a Map......................................................257Using Images ..........................................................................258Changing a Calculation ..........................................................................259Animating a Scatter Chart over Time .............................................259Some Closing Tips on Power View ......................................................261Analyzing Geographic Data with 3D Map ................................................261 Preparing Data for 3D Map ............................................261 Geocoding Data .........................................................................262 Building a Column Chart in 3D Map ...............................................264 Navigating Through the Map.......................................................264 Labeling Individual Points ....................................................................266 Building Pie or Bubble Charts on a Map............................266 Using Heat Maps and Region Maps ........................................266 Exploring 3D Map Settings ............................................................267 Fine-Tuning 3D Map .....................................................................268 Animating Data over Time ........................................................269 Building a Tour ................................................................................270 Creating a Video from 3D Map...................................................271Next Steps.........................................................................27412 Enhancing Pivot Table Reports with Macros ................275Why Use Macros with Pivot Table Reports ................................275Recording a Macro ..................................................................276Creating a User Interface with Form Controls .................................278Altering a Recorded Macro to Add Functionality.................................280 Inserting a Scrollbar Form Control ............................................281Next Steps......................................................28813 Using VBA to Create Pivot Tables.................................289Enabling VBA in Your Copy of Excel .....................................289Using a File Format That Enables Macros ..................................290Visual Basic Editor .................................................................291Visual Basic Tools .........................................................291The Macro Recorder ..................................................................292Understanding Object-Oriented Code ................................................292Learning Tricks of the Trade ........................................................293 Writing Code to Handle a Data Range of Any Size ..............................293 Using Super-Variables: Object Variables .................................................294 Using With and End With to Shorten Code ................................................295Understanding Versions .....................................................................295Building a Pivot Table in Excel VBA ......................................296 Adding Fields to the Data Area ..............................................................298 Formatting the Pivot Table ..........................................................299Dealing with Limitations of Pivot Tables .................................................301 Filling Blank Cells in the Data Area ....................................................301 Filling Blank Cells in the Row Area ............................................302 Preventing Errors from Inserting or Deleting Cells ...............................302 Controlling Totals ................................................................302 Converting a Pivot Table to Values .................................................304Pivot Table 201: Creating a Report Showing Revenue by Category ..............307 Ensuring That Tabular Layout Is Utilized....................................309 Rolling Daily Dates Up to Years ........................................309 Eliminating Blank Cells .............................................................311 Controlling the Sort Order with AutoSort .........................................312 Changing the Default Number Format ................................................312 Suppressing Subtotals for Multiple Row Fields ...............................313 Handling Final Formatting .............................................................................315 Adding Subtotals to Get Page Breaks ..............................................315 Putting It All Together .........................................................317Calculating with a Pivot Table .............................................................................319 Addressing Issues with Two or More Data Fields ..................................319 Using Calculations Other Than Sum ............................................................321 Using Calculated Data Fields ......................................................323 Using Calculated Items .................................................................324 Calculating Groups ..........................................................................326 Using Show Values As to Perform Other Calculations ...................................327Using Advanced Pivot Table Techniques .......................................329 Using AutoShow to Produce Executive Overviews .........................................329 Using ShowDetail to Filter a Recordset ..............................................332 Creating Reports for Each Region or Model ................................................334 Manually Filtering Two or More Items in a Pivot Field .....................................338 Using the Conceptual Filters .................................................339 Using the Search Filter .....................................................................342 Setting Up Slicers to Filter a Pivot Table .............................................343Using the Data Model in Excel 2016 ...................................................345 Adding Both Tables to the Data Model ..................................................346 Creating a Relationship Between the Two Tables ............................................346 Defining the Pivot Cache and Building the Pivot Table ........................................347 Adding Model Fields to the Pivot Table ........................................................348 Adding Numeric Fields to the Values Area ......................................348 Putting It All Together ......................................................349Next Steps....................................................................35114 Advanced Pivot Table Tips and Techniques .....................353Tip 1: Force Pivot Tables to Refresh Automatically........................................353Tip 2: Refresh All Pivot Tables in a Workbook at the Same Time ...................................354Tip 3: Sort Data Items in a Unique Order, Not Ascending or Descending ....................355Tip 4: Turn Pivot Tables into Hard Data .............................................355Tip 5: Fill the Empty Cells Left by Row Fields .........................................356 Option 1: Implement the Repeat All Data Items Feature ..................................356 Option 2: Use Excel\'s Go To Special Functionality ..............................357Tip 6: Add a Rank Number Field to a Pivot Table ...........................359Tip 7: Reduce the Size of Pivot Table Reports .........................360 Delete the Source Data Worksheet ......................................360Tip 8: Create an Automatically Expanding Data Range .................................361Tip 9: Compare Tables Using a Pivot Table ..........................361Tip 10: AutoFilter a Pivot Table .............................................363Tip 11: Force Two Number Formats in a Pivot Table ..................................364Tip 12: Create a Frequency Distribution with a Pivot Table ..................................366Tip 13: Use a Pivot Table to Explode a Data Set to Different Tabs .................... 367Tip 14: Apply Restrictions on Pivot Tables and Pivot Fields .........................................368 Pivot Table Restrictions ......................................................368 Pivot Field Restrictions ...................................................370Tip 15: Use a Pivot Table to Explode a Data Set to Different Workbooks .....................372Next Steps..................................................................37315 Dr. Jekyll and Mr. GetPivotData ...................................................375Avoiding the Evil GetPivotData Problem ..........................................................376 Preventing GetPivotData by Typing the Formula..................................379 Simply Turning Off GetPivotData ..................................................379 Speculating on Why Microsoft Forced GetPivotData on Us ............................380Using GetPivotData to Solve Pivot Table Annoyances .........................381 Building an Ugly Pivot Table .............................................382 Building the Shell Report ..............................................................385 Using GetPivotData to Populate the Shell Report ..........................387 Updating the Report in Future Months ..................................390Conclusion .................................................................391Index .......................................393