Excel 2016 Pivot Table Data Crunching (includes Content Update Program)

Series
Que
Author
Bill Jelen / Michael Alexander  
Publisher
QUE Publishing
Cover
Softcover
Edition
1
Language
English
Total pages
432
Pub.-date
November 2015
ISBN13
9780789756299
ISBN
0789756293
Related Titles


Product detail

Product Price CHF Available  
9780789756299
Excel 2016 Pivot Table Data Crunching (includes Content Update Program)
44.50 approx. 7-9 days

Description

A practical, realistic, easy-to-understand guide to Excel's most powerful and underutilized features: PivotTables and PivotCharts. Even for students who have never created a pivot table before, this book will help them 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 learners avoid common mistakes, and present tips and tricks found nowhere else.

Features

  • Fully updated for Excel 2016, including the powerful new Recommended Pivot Table features
  • Teaches PivotTables, PivotCharts, and PowerPivot through real-world scenarios that make intuitive sense to users at all levels
  • Includes proven PivotTable recipes for instant on-the-job results
  • Helps Excel users avoid common pitfalls and mistakes
  • Part of the popular MrExcel's Library series, edited by Excel legend Bill Jelen

New to this Edition

What will be added/deleted to this edition of the manuscript? 

New chapters on Power Query & Power Map.
Updated screen shots throughout
Approximately 40% of the content will change
New features being covered include Power Query, Power Map
There will also be new accompanying sample files

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

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,900 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 49 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance 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 in Merritt Island, Florida, 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.