- 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
Acknowledgments
Mike Alexander is the funniest guy doing live Excel seminars. I appreciate him as a coauthor on all five editions of this book, which has earned the #1 spot on the Amazon Computer Book bestseller list (for 54 minutes one day in January). Rob Collie of PowerPivotPro.com keeps me up to speed on PowerPivot. At Microsoft, thanks to Aviv Ezrachi and Ben Rampson for always being willing to answer quick questions. At MrExcel.com, thanks to an entire community of people who are passionate about Excel. Finally, thanks to my wife, Mary Ellen, for her support during the writing process.
—Bill Jelen
Thanks to Bill Jelen for deciding to coauthor this book with me many editions ago. His knowledge of Excel still blows me away to this day. My deepest thanks to the professionals at Pearson Education for all the hours of work put into bringing this book to life. Thanks also to Bob Umlas, whose technical editing has helped us make numerous improvements to the examples and text in this book. Finally, a special thank you goes to the wife and kids for putting up with all the time I spent locked away on this project.
—Mike Alexander
-
No Comment