book

Excel 2013 Pivot Table Data Crunching

Name: Excel 2013 Pivot Table Data Crunching
ISBN: 9780133259414

by Bill Jelen, Michael Alexander

January 2013

Beginner to intermediate

432 pages

12h 22m

English

Que

Read now

Unlock full access

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 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

1. Pivot Table Fundamentals
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
2. Creating a Basic Pivot Table
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
3. Customizing a Pivot Table
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
4. Grouping, Sorting, and Filtering Pivot Data
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
5. Performing Calculations Within Your Pivot Tables
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
6. Using Pivot Charts and Other Visualizations
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
7. Analyzing Disparate Data Sources with Pivot Tables
Using Multiple Consolidation RangesUsing the Internal Data ModelBuilding a Pivot Table Using External Data SourcesWhat’s Next
8. Sharing Pivot Tables with Others
Designing a Workbook as an Interactive Web PageSharing Pivot Tables with Other Versions of Office
9. Working with and Analyzing OLAP Data
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
10. Mashing Up Data with PowerPivot
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
11. Dashboarding with Power View
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
12. Enhancing Your Pivot Table Reports with Macros
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
13. Using VBA to Create Pivot Tables
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
14. Advanced Pivot Table Tips and Techniques
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
15. Dr. Jekyll and Mr. GetPivotData
Turning Off the Evil GetPivotData ProblemUsing GetPivotData to Solve Pivot Table Annoyances
Index

Content preview from Excel 2013 Pivot Table Data Crunching

15. Dr. Jekyll and Mr. GetPivotData

In This Chapter

Turning Off the Evil GetPivotData Problem

Using GetPivotData to Solve Pivot Table Annoyances

This chapter shows you a technique that solves many annoying pivot table problems. If you have been using pivot tables for a while, you might have run into the following problems:

• Formatting tends to be destroyed when you refresh your pivot table. Numeric formats are lost. Column widths go away.

• There is no easy way to build an asymmetric pivot table. Named sets are one way, but they are only available in a pivot table model, not in a regular pivot cache pivot table.

• Excel cannot remember a template. If you frequently have to re-create a pivot table, you must redo the groupings, calculated fields, ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 9780133259414Purchase book

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Excel 2013 Pivot Table Data Crunching

by Bill Jelen, Michael Alexander

15. Dr. Jekyll and Mr. GetPivotData

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.