Excel 2016 Pivot table data crunching

Description

Excel(R) 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. Contents......Page 5 What You Will Learn from This Book......Page 17 What Is New in Excel 2016’s Pivot Tables......Page 18 Skills Required to Use This Book......Page 19 Invention of the Pivot Table......Page 20 Conventions Used in This Book......Page 22 Special Elements......Page 23 Defining a Pivot Table......Page 25 Why You Should Use a Pivot Table......Page 26 Advantages of Using a Pivot Table......Page 27 Values Area......Page 28 Rows Area......Page 29 Pivot Tables Behind the Scenes......Page 30 Pivot Table Backward Compatibility......Page 31 A Word About Compatibility......Page 32 Next Steps......Page 33 Preparing Data for Pivot Table Reporting......Page 35 Avoiding Storing Data in Section Headings......Page 36 Avoiding Repeating Groups as Columns......Page 37 Summary of Good Data Source Design......Page 38 How to Create a Basic Pivot Table......Page 40 Adding Fields to a Report......Page 42 Fundamentals of Laying Out a Pivot Table Report......Page 43 Adding Layers to a Pivot Table......Page 44 Rearranging a Pivot Table......Page 45 Understanding the Recommended Pivot Table Feature......Page 47 Creating a Standard Slicer......Page 49 Creating a Timeline Slicer......Page 52 Dealing with an Expanded Data Source Range Due to the Addition of Rows or Columns......Page 55 Sharing the Pivot Cache......Page 56 Deferring Layout Updates......Page 57 Starting Over with One Click......Page 58 Next Steps......Page 59 3 Customizing a Pivot Table......Page 61 Making Common Cosmetic Changes......Page 62 Applying a Table Style to Restore Gridlines......Page 63 Changing the Number Format to Add Thousands Separators......Page 64 Replacing Blanks with Zeros......Page 65 Changing a Field Name......Page 67 Using the Compact Layout......Page 68 Using the Outline Layout......Page 70 Using the Traditional Tabular Layout......Page 71 Controlling Blank Lines, Grand Totals, and Other Settings......Page 73 Customizing a Pivot Table’s Appearance with Styles and Themes......Page 76 Customizing a Style......Page 77 Modifying Styles with Document Themes......Page 78 Understanding Why One Blank Cell Causes a Count......Page 79 Adding and Removing Subtotals......Page 81 Suppressing Subtotals with Many Row Fields......Page 82 Changing the Calculation in a Value Field......Page 83 Showing Percentage of Total......Page 86 Showing Rank......Page 87 Tracking Running Total and Percentage of Running Total......Page 88 Tracking the Percentage of a Parent Item......Page 89 Tracking Relative Importance with the Index Option......Page 90 Next Steps......Page 91 Automatically Grouping Dates......Page 93 Understanding How Excel 2016 Decides What to Group......Page 94 Grouping Date Fields Manually......Page 95 Including Years When Grouping by Months......Page 96 Grouping Date Fields by Week......Page 97 Grouping Numeric Fields......Page 98 Using the PivotTable Fields List......Page 101 Rearranging the PivotTable Fields List......Page 103 Using the Areas Section Drop-Downs......Page 104 Sorting Customers into High-to-Low Sequence Based on Revenue......Page 105 Using a Manual Sort Sequence......Page 108 Using a Custom List for Sorting......Page 109 Filtering a Pivot Table: An Overview......Page 111 Filtering Using the Check Boxes......Page 112 Filtering Using the Search Box......Page 113 Filtering Using the Label Filters Option......Page 114 Filtering a Label Column Using Information in a Values Column......Page 115 Creating a Top-Five Report Using the Top 10 Filter......Page 117 Filtering Using the Date Filters in the Label Drop-down......Page 119 Adding Fields to the Filters Area......Page 120 Replicating a Pivot Table Report for Each Item in a Filter......Page 121 Filtering Using Slicers and Timelines......Page 123 Using Timelines to Filter by Date......Page 125 Driving Multiple Pivot Tables from One Set of Slicers......Page 126 Next Steps......Page 128 Introducing Calculated Fields and Calculated Items......Page 129 Method 1: Manually Add a Calculated Field to the Data Source......Page 130 Method 2: Use a Formula Outside a Pivot Table to Create a Calculated Field......Page 131 Creating a Calculated Field......Page 132 Creating a Calculated Item......Page 140 Understanding the Rules and Shortcomings of Pivot Table Calculations......Page 143 Remembering the Order of Operator Precedence......Page 144 Rules Specific to Calculated Fields......Page 145 Editing and Deleting Pivot Table Calculations......Page 147 Changing the Solve Order of Calculated Items......Page 148 Documenting Formulas......Page 149 Next Steps......Page 150 What Is a Pivot ChartReally?......Page 151 Creating a Pivot Chart......Page 152 Understanding Pivot Field Buttons......Page 154 Placement of Data Fields in a Pivot Table Might Not Be Best Suited for a Pivot Chart......Page 155 A Few Formatting Limitations Still Exist in Excel 2016......Page 157 Method 1: Turn the Pivot Table into Hard Values......Page 161 Method 3: Distribute a Picture of the Pivot Chart......Page 162 Method 4: Use Cells Linked Back to the Pivot Table as the Source Data for the Chart......Page 163 An Example of Using Conditional Formatting......Page 165 Preprogrammed Scenarios for Condition Levels......Page 167 Creating Custom Conditional Formatting Rules......Page 168 Next Steps......Page 172 7 Analyzing Disparate Data Sources with Pivot Tables......Page 173 Building Out Your First Data Model......Page 174 Managing Relationships in the Data Model......Page 178 Adding a New Table to the Data Model......Page 179 Removing a Table from the Data Model......Page 181 Creating a New Pivot Table Using the Data Model......Page 182 Limitations of the Internal Data Model......Page 183 Building a Pivot Table Using External Data Sources......Page 184 Building a Pivot Table with Microsoft Access Data......Page 185 Building a Pivot Table with SQL Server Data......Page 187 Leveraging Power Query to Extract and Transform Data......Page 190 Power Query Basics......Page 191 Understanding Query Steps......Page 197 Managing Existing Queries......Page 199 Understanding Column-Level Actions......Page 201 Understanding Table Actions......Page 203 Power Query Connection Types......Page 204 Next Steps......Page 208 Designing a Workbook as an Interactive Web Page......Page 209 Sharing with Power BI......Page 212 Importing Data to Power BI......Page 213 Building a Report in Power BI......Page 215 Using Q&A to Query Data......Page 216 Next Steps......Page 218 Introduction to OLAP......Page 219 Connecting to an OLAP Cube......Page 220 Understanding the Structure of an OLAP Cube......Page 223 Understanding the Limitations of OLAP Pivot Tables......Page 224 Creating an Offline Cube......Page 225 Breaking Out of the Pivot Table Mold with Cube Functions......Page 227 Exploring Cube Functions......Page 228 Adding Calculations to OLAP Pivot Tables......Page 229 Creating Calculated Measures......Page 230 Creating Calculated Members......Page 233 Performing What-If Analysis with OLAP Data......Page 236 Next Steps......Page 238 Merging Data from Multiple Tables Without Using VLOOKUP......Page 239 Other Benefits of the Power Pivot Data Model in All Editions of Excel......Page 240 Understanding the Limitations of the Data Model......Page 241 Joining Multiple Tables Using the Data Model in Regular Excel 2016......Page 242 Preparing Data for Use in the Data Model......Page 243 Adding the First Table to the Data Model......Page 244 Adding the Second Table and Defining a Relationship......Page 245 Tell Me Again—Why Is This Better Than Doing a VLOOKUP?......Page 246 Getting a Distinct Count......Page 248 Enabling Power Pivot......Page 250 Importing a Text File Using Power Query......Page 251 Defining Relationships......Page 252 Building a Pivot Table......Page 253 Understanding Differences Between Power Pivot and Regular Pivot Tables......Page 254 Using DAX Calculations for Calculated Columns......Page 255 Defining a DAX Calculated Field......Page 256 Using Time Intelligence......Page 258 Next Steps......Page 259 Preparing Data for Power View......Page 261 Creating a Power View Dashboard......Page 263 Subtlety Should Be Power View’s Middle Name......Page 265 Converting a Table to a Chart......Page 266 Adding Drill-down to a Chart......Page 267 Filtering One Chart with Another One......Page 268 Adding a Real Slicer......Page 269 Understanding the Filters Pane......Page 270 Using Tile Boxes to Filter a Chart or a Group of Charts......Page 271 Replicating Charts Using Multiples......Page 272 Showing Data on a Map......Page 273 Using Images......Page 274 Animating a Scatter Chart over Time......Page 275 Preparing Data for 3D Map......Page 277 Geocoding Data......Page 278 Navigating Through the Map......Page 280 Using Heat Maps and Region Maps......Page 282 Exploring 3D Map Settings......Page 283 Fine-Tuning 3D Map......Page 284 Animating Data over Time......Page 285 Building a Tour......Page 286 Creating a Video from 3D Map......Page 287 Next Steps......Page 290 Why Use Macros with Pivot Table Reports......Page 291 Recording a Macro......Page 292 Creating a User Interface with Form Controls......Page 294 Altering a Recorded Macro to Add Functionality......Page 296 Inserting a Scrollbar Form Control......Page 297 Next Steps......Page 304 Enabling VBA in Your Copy of Excel......Page 305 Using a File Format That Enables Macros......Page 306 Visual Basic Tools......Page 307 Understanding Object-Oriented Code......Page 308 Writing Code to Handle a Data Range of Any Size......Page 309 Using Super-Variables: Object Variables......Page 310 Understanding Versions......Page 311 Building a Pivot Table in Excel VBA......Page 312 Adding Fields to the Data Area......Page 314 Formatting the Pivot Table......Page 315 Filling Blank Cells in the Data Area......Page 317 Controlling Totals......Page 318 Converting a Pivot Table to Values......Page 320 Pivot Table 201: Creating a Report Showing Revenue by Category......Page 323 Rolling Daily Dates Up to Years......Page 325 Eliminating Blank Cells......Page 327 Changing the Default Number Format......Page 328 Suppressing Subtotals for Multiple Row Fields......Page 329 Adding Subtotals to Get Page Breaks......Page 331 Putting It All Together......Page 333 Addressing Issues with Two or More Data Fields......Page 335 Using Calculations Other Than Sum......Page 337 Using Calculated Data Fields......Page 339 Using Calculated Items......Page 340 Calculating Groups......Page 342 Using Show Values As to Perform Other Calculations......Page 343 Using AutoShow to Produce Executive Overviews......Page 345 Using ShowDetail to Filter a Recordset......Page 348 Creating Reports for Each Region or Model......Page 350 Manually Filtering Two or More Items in a Pivot Field......Page 354 Using the Conceptual Filters......Page 355 Using the Search Filter......Page 358 Setting Up Slicers to Filter a Pivot Table......Page 359 Using the Data Model in Excel 2016......Page 361 Creating a Relationship Between the Two Tables......Page 362 Defining the Pivot Cache and Building the Pivot Table......Page 363 Adding Numeric Fields to the Values Area......Page 364 Putting It All Together......Page 365 Next Steps......Page 367 Tip 1: Force Pivot Tables to Refresh Automatically......Page 369 Tip 2: Refresh All Pivot Tables in a Workbook at the Same Time......Page 370 Tip 4: Turn Pivot Tables into Hard Data......Page 371 Option 1: Implement the Repeat All Data Items Feature......Page 372 Option 2: Use Excel’s Go To Special Functionality......Page 373 Tip 6: Add a Rank Number Field to a Pivot Table......Page 375 Delete the Source Data Worksheet......Page 376 Tip 9: Compare Tables Using a Pivot Table......Page 377 Tip 10: AutoFilter a Pivot Table......Page 379 Tip 11: Force Two Number Formats in a Pivot Table......Page 380 Tip 12: Create a Frequency Distribution with a Pivot Table......Page 382 Tip 13: Use a Pivot Table to Explode a Data Set to Different Tabs......Page 383 Pivot Table Restrictions......Page 384 Pivot Field Restrictions......Page 386 Tip 15: Use a Pivot Table to Explode a Data Set to Different Workbooks......Page 388 Next Steps......Page 389 15 Dr Jekyll and Mr GetPivotData......Page 391 Avoiding the Evil GetPivotData Problem......Page 392 Simply Turning Off GetPivotData......Page 395 Speculating on Why Microsoft Forced GetPivotData on Us......Page 396 Using GetPivotData to Solve Pivot Table Annoyances......Page 397 Building an Ugly Pivot Table......Page 398 Building the Shell Report......Page 401 Using GetPivotData to Populate the Shell Report......Page 403 Updating the Report in Future Months......Page 406 Conclusion......Page 407 A......Page 409 B......Page 411 C......Page 412 D......Page 415 E......Page 417 F......Page 418 G......Page 420 I......Page 421 M......Page 422 O......Page 424 P......Page 425 Q......Page 427 R......Page 428 S......Page 429 T......Page 431 V......Page 432 Z......Page 433