book

Excel 2013 Pivot Table Data Crunching

by Bill Jelen, Michael Alexander

January 2013

Beginner to intermediate

432 pages

12h 22m

English

Que

Read now

Unlock full access

What You Will Learn from This BookWhat Is New in Excel 2013’s Pivot TablesSkills Required to Use This BookInvention of the Pivot TableSample Files Used in This BookConventions Used in This Book

What Is a Pivot Table?Why Should You Use a Pivot Table?When Should You Use a Pivot Table?The Anatomy of a Pivot TablePivot Tables Behind the ScenesLimitations of Pivot Table ReportsNext Steps
Preparing Your Data for Pivot Table ReportingCreating a Basic Pivot TableUnderstanding the Recommended PivotTables FeatureUsing SlicersKeeping Up with Changes in Your Data SourceSharing the Pivot CacheSaving Time with New Pivot Table ToolsNext Steps
Making Common Cosmetic ChangesMaking Report Layout ChangesCustomizing the Pivot Table Appearance with Styles and ThemesChanging Summary CalculationsAdding and Removing SubtotalsChanging the Calculation in a Value FieldNext Steps
Grouping Pivot FieldsUsing the PivotTable Fields ListSorting in a Pivot TableFiltering the Pivot Table: An OverviewUsing Filters for Row and Column FieldsFiltering Using the Filters AreaFiltering Using Slicers and TimelinesNext Steps
Introducing Calculated Fields and Calculated ItemsCreating Your First Calculated FieldCreating Your First Calculated ItemUnderstanding the Rules and Shortcomings of Pivot Table CalculationsManaging and Maintaining Your Pivot Table CalculationsWhat’s Next
What Is a Pivot Chart...Really?Creating Your First Pivot ChartKeeping Pivot Chart Rules in MindExamining Alternatives to Using Pivot ChartsUsing Conditional Formatting with Pivot TablesCreating Custom Conditional Formatting RulesWhat’s Next
Using Multiple Consolidation RangesUsing the Internal Data ModelBuilding a Pivot Table Using External Data SourcesWhat’s Next
Designing a Workbook as an Interactive Web PageSharing Pivot Tables with Other Versions of Office
What Is OLAP?Connecting to an OLAP CubeUnderstanding the Structure of an OLAP CubeUnderstanding the Limitations of OLAP Pivot TablesCreating Offline CubesBreaking Out of the Pivot Table Mold with Cube FunctionsAdding Calculations to Your OLAP Pivot TablesNext Steps
Understanding the Benefits and Drawbacks of PowerPivot and the Data ModelJoining Multiple Tables Using the Data Model in Regular Excel 2013Using the PowerPivot Add-In from Excel 2013 Pro PlusUnderstanding Differences Between PowerPivot and Regular Pivot TablesTwo Kinds of DAX CalculationsUsing a Calendar Table to Enable Time Intelligence FunctionsUsing Key Performance IndicatorsOther Notes About PowerPivotNext Steps
Preparing Your Data for Power ViewCreating a Power View DashboardReplicating Charts Using MultiplesShowing Data on a MapUsing Table or Card View with ImagesChanging the CalculationAnimating a Scatter Chart Over TimeSome Closing Tips on Power ViewAnimating Pivot Table Data on a MapNext Steps
Why Use Macros with Your Pivot Table Reports?Recording Your First MacroCreating a User Interface with Form ControlsAltering a Recorded Macro to Add FunctionalityWhat’s Next
Enabling VBA in Your Copy of ExcelUsing a File Format That Enables MacrosVisual Basic EditorVisual Basic ToolsThe Macro RecorderUnderstanding Object-Oriented CodeLearning Tricks of the TradeUnderstanding VersionsBuilding a Pivot Table in Excel VBADealing with Limitations of Pivot TablesPivot Table 201: Creating a Report Showing Revenue by CategoryCalculating with a Pivot TableUsing Advanced Pivot Table TechniquesUsing the Data Model in Excel 2013Next Steps
Tip 1: Force Pivot Tables to Refresh AutomaticallyTip 2: Refresh All Pivot Tables in a Workbook at the Same TimeTip 3: Sort Data Items in a Unique Order Not Ascending or DescendingTip 4: Turn Pivot Tables into Hard DataTip 5: Fill the Empty Cells Left by Row FieldsTip 6: Add a Rank Number Field to Your Pivot TableTip 7: Reduce the Size of Your Pivot Table ReportsTip 8: Create an Automatically Expanding Data RangeTip 9: Compare Tables Using a Pivot TableTip 10: AutoFilter a Pivot TableTip 11: Transpose a Data Set with a Pivot TableTip 12: Force Two Number Formats in a Pivot TableTip 13: Create a Frequency Distribution with a Pivot TableTip 14: Use a Pivot Table to Explode a Data Set to Different TabsTip 15: Use a Pivot Table to Explode a Data Set to Different WorkbooksWhat’s Next
Turning Off the Evil GetPivotData ProblemUsing GetPivotData to Solve Pivot Table Annoyances

Content preview from Excel 2013 Pivot Table Data Crunching

Acknowledgments

Mike Alexander is the funniest guy doing live Excel seminars. I appreciate him as a coauthor on all four 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 Scott Ruble and Diego Oppenheimer for always being willing to answer a quick question. At MrExcel.com, thanks to Tracy Syrstad, Wei Jiang, Scott Pearson, Tyler Nash, and an entire community of people passionate about Excel. Thanks to VoG, Richard Schollar, Jerry JS411, Daniel Nieves, and Cindy Kredo. Loretta Yates at Pearson Education is the best editor ever. Bob Umlas keeps us on our toes as tech editor. ...