Excel® 2016 PIVOT TABLE DATA CRUNCHING CRUNCH DATA FROM ANY SOURCE, QUICKLY AND EASILY, WITH EXCEL 2016 PIVOT TABLES!     Use Excel 2016 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours… understand exactly what’s going on in your business… take control, and stay in control! Even if you’ve never created a pivot table before, this book will help you leverage all their amazing flexibility and analytical power. Drawing on more than 40 combined years of Excel experience, Bill Jelen and Michael Alexander offer practical “recipes” for solving real business problems, help you avoid common mistakes, and present tips and tricks you’ll find nowhere else! • Create, customize, and change pivot tables • Transform huge data sets into clear summary reports • Analyze data faster with Excel 2016’s new recommended pivot tables • Instantly highlight your most profitable customers, products, or regions • Quickly import, clean, and shape data with Power Query vBuild geographical pivot tables with Power Map • Use Power View dynamic dashboards to see where your business stands • Revamp analyses on the fly by dragging and dropping fields • Build dynamic self-service reporting systems • Combine multiple data sources into one pivot table • Use Auto grouping to build date/time-based pivot tables faster vCreate data mashups with Power Pivot • Automate pivot tables with macros and VBA     About MrExcel Library   Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will • Dramatically increase your productivity—saving you 50 hours a year or more • Present proven, creative strategies for solving real-world problems • Show you how to get great results, no matter how much data you have • Help you avoid critical mistakes that even experienced users make   Bill Jelen is MrExcel, the world’s #1 spreadsheet wizard. Jelen hosts MrExcel.com, the premier Excel solutions site, with more than 20 million page views annually. A Microsoft MVP for Excel, his best-sellers include Excel 2016 In Depth. Michael Alexander, Microsoft Certified Application Developer (MCAD) and Microsoft MVP, is author of several books on advanced business analysis with Excel and Access. He has more than 15 years of experience developing Office solutions.   CATEGORY:   Spreadsheets COVERS:   Microsoft Excel 2016

Table of Contents:
&> Introduction What You Will Learn from This Book .................................................................1 What Is New in Excel 2016’s Pivot Tables ...............................................................2 Skills Required to Use This Book .........................................................................3 Invention of the Pivot Table........................................................................4 Sample Files Used in This Book ...........................................................6 Conventions Used in This Book ............................................................6      Referring to Versions ..............................................................................7      Referring to Ribbon Commands.................................................7      Special Elements .....................................................................7 1 Pivot Table Fundamentals ............................................ 9 Defining a Pivot Table .................................................................9 Why You Should Use a Pivot Table .............................................10      Advantages of Using a Pivot Table .........................................11 When to Use a Pivot Table ......................................................12 Anatomy of a Pivot Table .........................................................12      Values Area ...........................................................................12      Rows Area........................................................................................13      Columns Area ...................................................................14      Filters Area ........................................................................14 Pivot Tables Behind the Scenes ..........................................14 Pivot Table Backward Compatibility .................................................15      A Word About Compatibility .................................................16 Next Steps............................................................................17 2 Creating a Basic Pivot Table ........................................19 Preparing 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 ........................................22 How 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.......................................................31 Understanding the Recommended Pivot Table Feature...........................31 Using Slicers ......................................................................................33      Creating a Standard Slicer ............................................................33      Creating a Timeline Slicer ...............................................................36 Keeping 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 ..............39 Sharing the Pivot Cache .........................................40 Saving Time with New Pivot Table Tools ............................................41      Deferring Layout Updates ......................................................41      Starting Over with One Click ..................................................42      Relocating a Pivot Table .................................................43 Next Steps.......................................................................43 3 Customizing a Pivot Table ................................................45 Making 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 .....................................................51 Making 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 ....................................57 Customizing a Pivot Table’s Appearance with Styles and Themes ......................................60      Customizing a Style .................................................................61      Modifying Styles with Document Themes ........................................62 Changing Summary Calculations ....................................................63      Understanding Why One Blank Cell Causes a Count ..............................63      Using Functions Other Than Count or Sum ...............................65 Adding and Removing Subtotals ....................................................65      Suppressing Subtotals with Many Row Fields ...........................................66      Adding Multiple Subtotals for One Field .......................................67 Changing 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 ...................................74 Next Steps...............................................................75 4 Grouping, Sorting, and Filtering Pivot Data ..........................77 Automatically 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 ............................................82 Using 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 ..................................88 Sorting 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 ..................................................93 Filtering a Pivot Table: An Overview ...................................................95 Using 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 .........................................103 Filtering 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 .......................................110 Next Steps......................................................................................112 5 Performing Calculations in Pivot Tables ...........................113 Introducing 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 ............................116 Creating a Calculated Field ...................................116 Creating a Calculated Item ...........................................124 Understanding 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 ...........................................................131 Managing and Maintaining Pivot Table Calculations ......................................131      Editing and Deleting Pivot Table Calculations ......................................131      Changing the Solve Order of Calculated Items .........................................132      Documenting Formulas ........................................................133 Next Steps............................................................................134 6 Using Pivot Charts and Other Visualizations .........................135 What Is a Pivot Chart...Really? ..........................................................135 Creating a Pivot Chart ..........................................................136      Understanding Pivot Field Buttons ..............................................138 Keeping 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 ..................................141 Examining 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 .............147 Using Conditional Formatting with Pivot Tables ...............................................149      An Example of Using Conditional Formatting ...........................................149      Preprogrammed Scenarios for Condition Levels ........................................151 Creating Custom Conditional Formatting Rules .............................................152 Next Steps...................................................................................................156 7 Analyzing Disparate Data Sources with Pivot Tables ................................157 Using 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 ...............................................................167 Building 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 ......................................................171 Leveraging 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 ......................................................188 Next Steps..........................................................................................192 8 Sharing Pivot Tables with Others ........................................193 Designing a Workbook as an Interactive Web Page .......................................................193 Sharing a Link to a Web Workbook ................................................................196 Sharing 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 ..............................................................202 Next Steps........................................................................202 9 Working with and Analyzing OLAP Data ..........................203 Introduction to OLAP .............................................................................203 Connecting to an OLAP Cube .....................................................204 Understanding the Structure of an OLAP Cube ...................................................207 Understanding the Limitations of OLAP Pivot Tables .........................................208 Creating an Offline Cube ...................................................209 Breaking Out of the Pivot Table Mold with Cube Functions ................................211      Exploring Cube Functions ............................................................212 Adding 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 .....................................220 Next Steps...............................................................................222 10 Mashing Up Data with Power Pivot ...................................223 Understanding 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 .................................225 Joining 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 ...........................................................232 Using 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 ....................................................................237 Understanding Differences Between Power Pivot and Regular Pivot Tables ............................238 Using 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 .............................................................242 Next Steps.....................................................................................243 11 Dashboarding with Power View and 3D Map ..........................245 Preparing Data for Power View ....................................................245 Creating 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 .....................................255 Replicating Charts Using Multiples .................................................256 Showing Data on a Map......................................................257 Using Images ..........................................................................258 Changing a Calculation ..........................................................................259 Animating a Scatter Chart over Time .............................................259 Some Closing Tips on Power View ......................................................261 Analyzing 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...................................................271 Next Steps.........................................................................274 12 Enhancing Pivot Table Reports with Macros ................275 Why Use Macros with Pivot Table Reports ................................275 Recording a Macro ..................................................................276 Creating a User Interface with Form Controls .................................278 Altering a Recorded Macro to Add Functionality.................................280      Inserting a Scrollbar Form Control ............................................281 Next Steps......................................................288 13 Using VBA to Create Pivot Tables.................................289 Enabling VBA in Your Copy of Excel .....................................289 Using a File Format That Enables Macros ..................................290 Visual Basic Editor .................................................................291 Visual Basic Tools .........................................................291 The Macro Recorder ..................................................................292 Understanding Object-Oriented Code ................................................292 Learning 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 ................................................295 Understanding Versions .....................................................................295 Building a Pivot Table in Excel VBA ......................................296      Adding Fields to the Data Area ..............................................................298      Formatting the Pivot Table ..........................................................299 Dealing 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 .................................................304 Pivot 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 .........................................................317 Calculating 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 ...................................327 Using 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 .............................................343 Using 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 ......................................................349 Next Steps....................................................................351 14 Advanced Pivot Table Tips and Techniques .....................353 Tip 1: Force Pivot Tables to Refresh Automatically........................................353 Tip 2: Refresh All Pivot Tables in a Workbook at the Same Time ...................................354 Tip 3: Sort Data Items in a Unique Order, Not Ascending or Descending ....................355 Tip 4: Turn Pivot Tables into Hard Data .............................................355 Tip 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 ..............................357 Tip 6: Add a Rank Number Field to a Pivot Table ...........................359 Tip 7: Reduce the Size of Pivot Table Reports .........................360      Delete the Source Data Worksheet ......................................360 Tip 8: Create an Automatically Expanding Data Range .................................361 Tip 9: Compare Tables Using a Pivot Table ..........................361 Tip 10: AutoFilter a Pivot Table .............................................363 Tip 11: Force Two Number Formats in a Pivot Table ..................................364 Tip 12: Create a Frequency Distribution with a Pivot Table ..................................366 Tip 13: Use a Pivot Table to Explode a Data Set to Different Tabs .................... 367 Tip 14: Apply Restrictions on Pivot Tables and Pivot Fields .........................................368      Pivot Table Restrictions ......................................................368      Pivot Field Restrictions ...................................................370 Tip 15: Use a Pivot Table to Explode a Data Set to Different Workbooks .....................372 Next Steps..................................................................373 15 Dr. Jekyll and Mr. GetPivotData ...................................................375 Avoiding 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 ............................380 Using 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 ..................................390 Conclusion .................................................................391 Index .......................................393