Pivot Table Data Crunching: Microsoft Excel 2016
November 2015
The world’s best-selling book on Excel Pivot Tables, now in a fifth edition!
I am excited that Mike Alexander and I have updated out best-selling Pivot Table book. New topics include Power Pivot, the Data Model, and Power Map.
Buy Now »- 432 Pages
- Publisher: Que Publishing
- ISBN: 978-0789756299
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
-
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 .31
- 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
-
