- 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
- 1. Pivot Table Fundamentals
- 2. Creating a Basic Pivot Table
- 3. Customizing a Pivot Table
- 4. Grouping, Sorting, and Filtering Pivot Data
- 5. Performing Calculations in Pivot Tables
- 6. Using Pivot Charts and Other Visualizations
- 7. Analyzing Disparate Data Sources with Pivot Tables
- 8. Sharing Pivot Tables with Others
- 9. Working with and Analyzing OLAP Data
- 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
- Using the Power Pivot Add-in Excel 2016 Pro Plus
- Understanding Differences Between Power Pivot and Regular Pivot Tables
- Using DAX Calculations
- Next Steps
- Understanding the Benefits and Drawbacks of Power Pivot and the Data Model
- 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
- 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
- Understanding Versions
- Building a Pivot Table in Excel VBA
- Dealing with Limitations of Pivot Tables
- 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
- Using Advanced Pivot Table Techniques
- Using the Data Model in Excel 2016
- 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
- Tip 6: Add a Rank Number Field to a Pivot Table
- Tip 7: Reduce the Size of Pivot Table Reports
- 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
- Tip 15: Use a Pivot Table to Explode a Data Set to Different Workbooks
- Next Steps
- 15. Dr. Jekyll and Mr. GetPivotData
- Index
- Code Snippets
Excel® 2016
Pivot Table Data Crunching
Bill Jelen
Michael Alexander
800 East 96th Street,
Indianapolis, Indiana 46240 USA
-
No Comment
..................Content has been hidden....................
You can't read the all page of ebook, please click here login for view all page.