- 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
About the Authors
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.
-
No Comment