SPECIAL OFFERS
Keep up with new releases and promotions. Sign up to hear from us.
Register your product to gain access to bonus material or receive a coupon.
This eBook includes the following formats, accessible from your Account page after purchase:
EPUB
The open industry format known for its reflowable content and usability on supported mobile devices.
PDF
The popular standard, used most often with the free Adobe® Reader® software.
This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.
A Web Edition is an online learning product that you view in your web browser while connected to the Internet. Some Web Editions include additional interactive features, such as videos, quizzes, or live code editing. Read the product description for more details. A Web Edition will be accessible from your Account page after purchase.
Viewing a Web Edition requires a web browser that fully supports HTML5. Supported platforms are:
A Web Edition is an online learning product that you view in your web browser while connected to the Internet. Some Web Editions include additional interactive features, such as videos, quizzes, or live code editing. Read the product description for more details. A Web Edition will be accessible from your Account page after purchase.
Viewing a Web Edition requires a web browser that fully supports HTML5. Supported platforms are:
This eBook includes the following formats, accessible from your Account page after purchase:
EPUB
The open industry format known for its reflowable content and usability on supported mobile devices.
PDF
The popular standard, used most often with the free Adobe® Reader® software.
This eBook requires no passwords or activation to read. We customize your eBook by discreetly watermarking it with your name, making it uniquely yours.
A Web Edition is an online learning product that you view in your web browser while connected to the Internet. Some Web Editions include additional interactive features, such as videos, quizzes, or live code editing. Read the product description for more details. A Web Edition will be accessible from your Account page after purchase.
Viewing a Web Edition requires a web browser that fully supports HTML5. Supported platforms are:
A Web Edition is an online learning product that you view in your web browser while connected to the Internet. Some Web Editions include additional interactive features, such as videos, quizzes, or live code editing. Read the product description for more details. A Web Edition will be accessible from your Account page after purchase.
Viewing a Web Edition requires a web browser that fully supports HTML5. Supported platforms are:
Excel® 2006 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
Customizing a Pivot Table in Excel 2016
Download the sample pages (includes Chapter 3 and Index)
&>
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
1 Pivot Table Fundamentals ............................................ 9
Defining a Pivot Table .................................................................9
Why You Should Use a Pivot Table .............................................10
When to Use a Pivot Table ......................................................12
Anatomy of a Pivot Table .........................................................12
Pivot Tables Behind the Scenes ..........................................14
Pivot Table Backward Compatibility .................................................15
Next Steps............................................................................17
2 Creating a Basic Pivot Table ........................................19
Preparing Data for Pivot Table Reporting ..............................................19
How to Create a Basic Pivot Table ......................................................24
Understanding the Recommended Pivot Table Feature...........................31
Using Slicers ......................................................................................33
Keeping Up with Changes in the Data Source .............................................39
Sharing the Pivot Cache .........................................40
Saving Time with New Pivot Table Tools ............................................41
Next Steps.......................................................................43
3 Customizing a Pivot Table ................................................45
Making Common Cosmetic Changes .................................................46
Making Report Layout Changes ..........................................52
Customizing a Pivot Table’s Appearance with Styles and Themes ......................................60
Changing Summary Calculations ....................................................63
Adding and Removing Subtotals ....................................................65
Changing the Calculation in a Value Field .......................................67
Next Steps...............................................................75
4 Grouping, Sorting, and Filtering Pivot Data ..........................77
Automatically Grouping Dates ...................................................77
Using the PivotTable Fields List ..............................................85
Sorting in a Pivot Table ................................................89
Filtering a Pivot Table: An Overview ...................................................95
Using Filters for Row and Column Fields .........................................96
Filtering Using the Filters Area ................................104
Next Steps......................................................................................112
5 Performing Calculations in Pivot Tables ...........................113
Introducing Calculated Fields and Calculated Items ............................................113
Creating a Calculated Field ...................................116
Creating a Calculated Item ...........................................124
Understanding the Rules and Shortcomings of Pivot Table Calculations ..................127
Managing and Maintaining Pivot Table Calculations ......................................131
Next Steps............................................................................134
6 Using Pivot Charts and Other Visualizations .........................135
What Is a Pivot Chart...Really? ..........................................................135
Creating a Pivot Chart ..........................................................136
Keeping Pivot Chart Rules in Mind ....................................................139
Examining Alternatives to Using Pivot Charts .......................................................145
Using Conditional Formatting with Pivot Tables ...............................................149
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 a Pivot Table Using External Data Sources .............................................168
Leveraging Power Query to Extract and Transform Data .............................................174
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
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
Adding Calculations to OLAP Pivot Tables .....................................................213
Next Steps...............................................................................222
10 Mashing Up Data with Power Pivot ...................................223
Understanding the Benefits and Drawbacks of Power Pivot and the Data Model ............223
Joining Multiple Tables Using the Data Model in Regular Excel 2016 ..............................226
Using the Power Pivot Add-in Excel 2016 Pro Plus .....................................234
Understanding Differences Between Power Pivot and Regular Pivot Tables ............................238
Using DAX Calculations .............................................................239
Next Steps.....................................................................................243
11 Dashboarding with Power View and 3D Map ..........................245
Preparing Data for Power View ....................................................245
Creating a Power View Dashboard .............................................................247
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
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
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
Understanding Versions .....................................................................295
Building a Pivot Table in Excel VBA ......................................296
Dealing with Limitations of Pivot Tables .................................................301
Pivot Table 201: Creating a Report Showing Revenue by Category ..............307
Calculating with a Pivot Table .............................................................................319
Using Advanced Pivot Table Techniques .......................................329
Using the Data Model in Excel 2016 ...................................................345
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
Tip 6: Add a Rank Number Field to a Pivot Table ...........................359
Tip 7: Reduce the Size of Pivot Table Reports .........................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
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
Using GetPivotData to Solve Pivot Table Annoyances .........................381
Conclusion .................................................................391
Index .......................................393
This book is part of Que's Content Update Program. As Microsoft updates features of Office 2016, sections of this book will be updated or new sections will be added to match the updates to the software. The updates will be delivered to you via a free Web Edition of this book, which can be accessed with any Internet connection from your account on quepublishing.com. For more information, visit quepublishing.com/CUP.