Home Page Icon
Home Page
Table of Contents for
Images
Close
Images
by Michael Alexander, Bill Jelen
Excel® 2016 Pivot Table Data Crunching
About This E-Book
Title Page
Copyright Page
Contents at a Glance
Contents
About the Authors
Dedication
Acknowledgments
We Want to Hear from You!
Reader Services
Introduction
What You Will Learn from This Book
What Is New in Excel 2016’s Pivot Tables
Skills Required to Use This Book
Invention of the Pivot Table
Sample Files Used in This Book
Conventions Used in This Book
Referring to Ribbon Commands
Special Elements
1. Pivot Table Fundamentals
Defining a Pivot Table
Why You Should Use a Pivot Table
Advantages of Using a Pivot Table
When to Use a Pivot Table
Anatomy of a Pivot Table
Values Area
Rows Area
Columns Area
Filters Area
Pivot Tables Behind the Scenes
Pivot Table Backward Compatibility
A Word About Compatibility
Next Steps
2. Creating a Basic Pivot Table
Preparing Data for Pivot Table Reporting
Ensuring That Data Is in a Tabular Layout
Avoiding Storing Data in Section Headings
Avoiding Repeating Groups as Columns
Eliminating Gaps and Blank Cells in the Data Source
Applying Appropriate Type Formatting to Fields
Summary of Good Data Source Design
How to Create a Basic Pivot Table
Adding Fields to a Report
Fundamentals of Laying Out a Pivot Table Report
Adding Layers to a Pivot Table
Rearranging a Pivot Table
Creating a Report Filter
Understanding the Recommended Pivot Table Feature
Using Slicers
Creating a Standard Slicer
Creating a Timeline Slicer
Keeping Up with Changes in the Data Source
Dealing with Changes Made to the Existing Data Source
Dealing with an Expanded Data Source Range Due to the Addition of Rows or Columns
Sharing the Pivot Cache
Saving Time with New Pivot Table Tools
Deferring Layout Updates
Starting Over with One Click
Relocating a Pivot Table
Next Steps
3. Customizing a Pivot Table
Making Common Cosmetic Changes
Applying a Table Style to Restore Gridlines
Changing the Number Format to Add Thousands Separators
Replacing Blanks with Zeros
Changing a Field Name
Making Report Layout Changes
Using the Compact Layout
Using the Outline Layout
Using the Traditional Tabular Layout
Controlling Blank Lines, Grand Totals, and Other Settings
Customizing a Pivot Table’s Appearance with Styles and Themes
Customizing a Style
Modifying Styles with Document Themes
Changing Summary Calculations
Understanding Why One Blank Cell Causes a Count
Using Functions Other Than Count or Sum
Adding and Removing Subtotals
Suppressing Subtotals with Many Row Fields
Adding Multiple Subtotals for One Field
Changing the Calculation in a Value Field
Showing Percentage of Total
Using % Of to Compare One Line to Another Line
Showing Rank
Tracking Running Total and Percentage of Running Total
Displaying a Change from a Previous Field
Tracking the Percentage of a Parent Item
Tracking Relative Importance with the Index Option
Next Steps
4. Grouping, Sorting, and Filtering Pivot Data
Automatically Grouping Dates
Undoing Automatic Grouping
Understanding How Excel 2016 Decides What to Group
Grouping Date Fields Manually
Including Years When Grouping by Months
Grouping Date Fields by Week
Grouping Numeric Fields
Using the PivotTable Fields List
Docking and Undocking the PivotTable Fields List
Rearranging the PivotTable Fields List
Using the Areas Section Drop-Downs
Sorting in a Pivot Table
Sorting Customers into High-to-Low Sequence Based on Revenue
Using a Manual Sort Sequence
Using a Custom List for Sorting
Filtering a Pivot Table: An Overview
Using Filters for Row and Column Fields
Filtering Using the Check Boxes
Filtering Using the Search Box
Filtering Using the Label Filters Option
Filtering a Label Column Using Information in a Values Column
Creating a Top-Five Report Using the Top 10 Filter
Filtering Using the Date Filters in the Label Drop-down
Filtering Using the Filters Area
Adding Fields to the Filters Area
Choosing One Item from a Filter
Choosing Multiple Items from a Filter
Replicating a Pivot Table Report for Each Item in a Filter
Filtering Using Slicers and Timelines
Using Timelines to Filter by Date
Driving Multiple Pivot Tables from One Set of Slicers
Next Steps
5. Performing Calculations in Pivot Tables
Introducing Calculated Fields and Calculated Items
Method 1: Manually Add a Calculated Field to the Data Source
Method 2: Use a Formula Outside a Pivot Table to Create a Calculated Field
Method 3: Insert a Calculated Field Directly into a Pivot Table
Creating a Calculated Field
Creating a Calculated Item
Understanding the Rules and Shortcomings of Pivot Table Calculations
Remembering the Order of Operator Precedence
Using Cell References and Named Ranges
Using Worksheet Functions
Using Constants
Referencing Totals
Rules Specific to Calculated Fields
Rules Specific to Calculated Items
Managing and Maintaining Pivot Table Calculations
Editing and Deleting Pivot Table Calculations
Changing the Solve Order of Calculated Items
Documenting Formulas
Next Steps
6. Using Pivot Charts and Other Visualizations
What Is a Pivot Chart...Really?
Creating a Pivot Chart
Understanding Pivot Field Buttons
Keeping Pivot Chart Rules in Mind
Changes in the Underlying Pivot Table Affect a Pivot Chart
Placement of Data Fields in a Pivot Table Might Not Be Best Suited for a Pivot Chart
A Few Formatting Limitations Still Exist in Excel 2016
Examining Alternatives to Using Pivot Charts
Method 1: Turn the Pivot Table into Hard Values
Method 2: Delete the Underlying Pivot Table
Method 3: Distribute a Picture of the Pivot Chart
Method 4: Use Cells Linked Back to the Pivot Table as the Source Data for the Chart
Using Conditional Formatting with Pivot Tables
An Example of Using Conditional Formatting
Preprogrammed Scenarios for Condition Levels
Creating Custom Conditional Formatting Rules
Next Steps
7. Analyzing Disparate Data Sources with Pivot Tables
Using the Internal Data Model
Building Out Your First Data Model
Managing Relationships in the Data Model
Adding a New Table to the Data Model
Removing a Table from the Data Model
Creating a New Pivot Table Using the Data Model
Limitations of the Internal Data Model
Building a Pivot Table Using External Data Sources
Building a Pivot Table with Microsoft Access Data
Building a Pivot Table with SQL Server Data
Leveraging Power Query to Extract and Transform Data
Power Query Basics
Understanding Query Steps
Refreshing Power Query Data
Managing Existing Queries
Understanding Column-Level Actions
Understanding Table Actions
Power Query Connection Types
Next Steps
8. Sharing Pivot Tables with Others
Designing a Workbook as an Interactive Web Page
Sharing a Link to a Web Workbook
Sharing with Power BI
Preparing Data for Power BI
Importing Data to Power BI
Building a Report in Power BI
Using Q&A to Query Data
Sharing Your Dashboard
Next Steps
9. Working with and Analyzing OLAP Data
Introduction to OLAP
Connecting to an OLAP Cube
Understanding the Structure of an OLAP Cube
Understanding the Limitations of OLAP Pivot Tables
Creating an Offline Cube
Breaking Out of the Pivot Table Mold with Cube Functions
Exploring Cube Functions
Adding Calculations to OLAP Pivot Tables
Creating Calculated Measures
Creating Calculated Members
Managing OLAP Calculations
Performing What-If Analysis with OLAP Data
Next Steps
10. Mashing Up Data with Power Pivot
Understanding the Benefits and Drawbacks of Power Pivot and the Data Model
Merging Data from Multiple Tables Without Using VLOOKUP
Importing 100 Million Rows into a Workbook
Creating Better Calculations Using the DAX Formula Language
Other Benefits of the Power Pivot Data Model in All Editions of Excel
Benefits of the Full Power Pivot Add-in with Excel Pro Plus
Understanding the Limitations of the Data Model
Joining Multiple Tables Using the Data Model in Regular Excel 2016
Preparing Data for Use in the Data Model
Adding the First Table to the Data Model
Adding the Second Table and Defining a Relationship
Tell Me Again—Why Is This Better Than Doing a VLOOKUP?
Creating a New Pivot Table from an Existing Data Model
Getting a Distinct Count
Using the Power Pivot Add-in Excel 2016 Pro Plus
Enabling Power Pivot
Importing a Text File Using Power Query
Adding Excel Data by Linking
Defining Relationships
Adding Calculated Columns Using DAX
Building a Pivot Table
Understanding Differences Between Power Pivot and Regular Pivot Tables
Using DAX Calculations
Using DAX Calculations for Calculated Columns
Using DAX to Create a Calculated Field in a Pivot Table
Filtering with DAX Calculated Fields
Defining a DAX Calculated Field
Using Time Intelligence
Next Steps
11. Dashboarding with Power View and 3D Map
Preparing Data for Power View
Creating a Power View Dashboard
Every New Dashboard Element Starts as a Table
Subtlety Should Be Power View’s Middle Name
Converting a Table to a Chart
Adding Drill-down to a Chart
Beginning a New Element by Dragging a Field to a Blank Spot on the Canvas
Filtering One Chart with Another One
Adding a Real Slicer
Understanding the Filters Pane
Using Tile Boxes to Filter a Chart or a Group of Charts
Replicating Charts Using Multiples
Showing Data on a Map
Using Images
Changing a Calculation
Animating a Scatter Chart over Time
Some Closing Tips on Power View
Analyzing Geographic Data with 3D Map
Preparing Data for 3D Map
Geocoding Data
Building a Column Chart in 3D Map
Navigating Through the Map
Labeling Individual Points
Building Pie or Bubble Charts on a Map
Using Heat Maps and Region Maps
Exploring 3D Map Settings
Fine-Tuning 3D Map
Animating Data over Time
Building a Tour
Creating a Video from 3D Map
Next Steps
12. Enhancing Pivot Table Reports with Macros
Why Use Macros with Pivot Table Reports
Recording a Macro
Creating a User Interface with Form Controls
Altering a Recorded Macro to Add Functionality
Inserting a Scrollbar Form Control
Next Steps
13. Using VBA to Create Pivot Tables
Enabling VBA in Your Copy of Excel
Using a File Format That Enables Macros
Visual Basic Editor
Visual Basic Tools
The Macro Recorder
Understanding Object-Oriented Code
Learning Tricks of the Trade
Writing Code to Handle a Data Range of Any Size
Using Super-Variables: Object Variables
Using With and End With to Shorten Code
Understanding Versions
Building a Pivot Table in Excel VBA
Adding Fields to the Data Area
Formatting the Pivot Table
Dealing with Limitations of Pivot Tables
Filling Blank Cells in the Data Area
Filling Blank Cells in the Row Area
Preventing Errors from Inserting or Deleting Cells
Controlling Totals
Converting a Pivot Table to Values
Pivot Table 201: Creating a Report Showing Revenue by Category
Ensuring That Tabular Layout Is Utilized
Rolling Daily Dates Up to Years
Eliminating Blank Cells
Controlling the Sort Order with AutoSort
Changing the Default Number Format
Suppressing Subtotals for Multiple Row Fields
Copying a Finished Pivot Table as Values to a New Workbook
Handling Final Formatting
Adding Subtotals to Get Page Breaks
Putting It All Together
Calculating with a Pivot Table
Addressing Issues with Two or More Data Fields
Using Calculations Other Than Sum
Using Calculated Data Fields
Using Calculated Items
Calculating Groups
Using Show Values As to Perform Other Calculations
Using Advanced Pivot Table Techniques
Using AutoShow to Produce Executive Overviews
Using ShowDetail to Filter a Recordset
Creating Reports for Each Region or Model
Manually Filtering Two or More Items in a Pivot Field
Using the Conceptual Filters
Using the Search Filter
Setting Up Slicers to Filter a Pivot Table
Using the Data Model in Excel 2016
Adding Both Tables to the Data Model
Creating a Relationship Between the Two Tables
Defining the Pivot Cache and Building the Pivot Table
Adding Model Fields to the Pivot Table
Adding Numeric Fields to the Values Area
Putting It All Together
Next Steps
14. Advanced Pivot Table Tips and Techniques
Tip 1: Force Pivot Tables to Refresh Automatically
Tip 2: Refresh All Pivot Tables in a Workbook at the Same Time
Tip 3: Sort Data Items in a Unique Order, Not Ascending or Descending
Tip 4: Turn Pivot Tables into Hard Data
Tip 5: Fill the Empty Cells Left by Row Fields
Option 1: Implement the Repeat All Data Items Feature
Option 2: Use Excel’s Go To Special Functionality
Tip 6: Add a Rank Number Field to a Pivot Table
Tip 7: Reduce the Size of Pivot Table Reports
Delete the Source Data Worksheet
Tip 8: Create an Automatically Expanding Data Range
Tip 9: Compare Tables Using a Pivot Table
Tip 10: AutoFilter a Pivot Table
Tip 11: Force Two Number Formats in a Pivot Table
Tip 12: Create a Frequency Distribution with a Pivot Table
Tip 13: Use a Pivot Table to Explode a Data Set to Different Tabs
Tip 14: Apply Restrictions on Pivot Tables and Pivot Fields
Pivot Table Restrictions
Pivot Field Restrictions
Tip 15: Use a Pivot Table to Explode a Data Set to Different Workbooks
Next Steps
15. Dr. Jekyll and Mr. GetPivotData
Avoiding the Evil GetPivotData Problem
Preventing GetPivotData by Typing the Formula
Simply Turning Off GetPivotData
Speculating on Why Microsoft Forced GetPivotData on Us
Using GetPivotData to Solve Pivot Table Annoyances
Building an Ugly Pivot Table
Building the Shell Report
Using GetPivotData to Populate the Shell Report
Updating the Report in Future Months
Conclusion
Index
Code Snippets
Search in book...
Toggle Font Controls
Playlists
Add To
Create new playlist
Name your new playlist
Playlist description (optional)
Cancel
Create playlist
Sign In
Email address
Password
Forgot Password?
Create account
Login
or
Continue with Facebook
Continue with Google
Sign Up
Full Name
Email address
Confirm Email Address
Password
Login
Create account
or
Continue with Facebook
Continue with Google
Prev
Previous Chapter
Images
Next
Next Chapter
Images
Add Highlight
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click
here
login for view all page.
Day Mode
Cloud Mode
Night Mode
Reset