eBook
Related collections and offers
Overview
CRUNCH ANY DATA, FROM ANY SOURCE, QUICKLY AND EASILY, WITH EXCEL 2013 PIVOT TABLES!
Use Excel 2013 pivot tables and pivot charts to produce powerful, dynamic reports in minutes instead of hours… understand exactly what’s going on in your business… take control, and stay in control!
Even if you’ve never created a pivot table before, this book will help you leverage all their amazing flexibility and analytical power. In just the first seven chapters, you learn how to generate complex pivot reports complete with drill-down capabilities and accompanying charts. Then, you go even further, discovering how to build a comprehensive, dynamic pivot table reporting system for any business task or function.
Learning advanced pivot table and pivot chart techniques for Excel 2013 or the newest Office 365 has never been easier. You’ll find simple, step-by-step instructions, real-world case studies, even complete, easy recipes for solving your most common business analysis problems.
• Create, customize, and change your pivot tables and pivot charts
• Transform gigantic data sets into crystal-clear summary reports
• Summarize and analyze data even faster with new Excel 2013 recommended pivot tables
• Instantly highlight your most (and least) profitable customers, products, or regions
• Quickly filter pivot tables using slicers
• Use dynamic dashboards using Power View to see exactly where your business stands right now
• Revamp analyses on the fly by simply dragging and dropping fields
• Build dynamic self-service reporting systems your entire team can use
• Use PowerPivot or the Data Model to create pivot tables from multiple data sources and worksheets
• Work with and analyze OLAP data, and much more
About MrExcel Library: Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these
books will
• Dramatically increase your productivity—saving you 50 hours a year or more
• Present proven, creative strategies for solving real-world problems
• Show you how to get great results, no matter how much data you have
• Help you avoid critical mistakes that even experienced users make
CATEGORY: Spreadsheets
COVERS: Microsoft Office Excel 2013
Product Details
| ISBN-13: | 9780133259339 |
|---|---|
| Publisher: | Pearson Education |
| Publication date: | 01/07/2013 |
| Series: | MrExcel Library |
| Sold by: | Barnes & Noble |
| Format: | eBook |
| Pages: | 432 |
| File size: | 69 MB |
| Note: | This product may take a few minutes to download. |
About the Author
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,500 episodes of his daily video podcast, Learn Excel from MrExcel. He is the author of 38 books about Microsoft Excel and writes the monthly Excel column for Strategic Finance magazine and his Excel tips appear regularly in CFO Excel Pro newsletter and CFO 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 near Akron, Ohio 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.
Table of Contents
Introduction
Chapter 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 Table
Values Area
Rows Area
Columns Area
Filters Area
Pivot Tables Behind the Scenes
Limitations of Pivot Table Reports
A Word About Compatibility
Next Steps
Chapter 2 Creating a Basic Pivot Table
Ensure Your Data Is in a Tabular Layout
Avoid Storing Data in Section Headings
Avoid Repeating Groups as Columns
Eliminate Gaps and Blank Cells in Your Data Source
Apply Appropriate Type Formatting to Your Fields
Summary of Good Data Source Design
Creating a Basic Pivot Table
Adding Fields to the Report
Adding Layers to Your Pivot Table
Rearranging Your Pivot Table
Creating a Report Filter
Understanding the Recommended PivotTables Feature
Using Slicers
Creating a Standard Slicer
Creating a Timeline Slicer
Keeping Up with Changes in Your Data Source
Changes Have Been Made to Your Existing Data Source
Your Data Source’s Range Has Been Expanded with the Addition of Rows or Columns
Sharing the Pivot Cache
Saving Time with New Pivot Table Tools
Deferring Layout Updates
Starting Over with One Click
Relocating Your Pivot Table
Next Steps
Chapter 3 Customizing a Pivot Table
Making Common Cosmetic Changes
Applying a Table Style to Restore Gridlines
Changing the Number Format to Add Thousands Separators
Replacing Blanks with Zeros
Changing a Field Name
Making Report Layout Changes
Using the New Compact Layout
Using the Outline Form Layout
Using the Traditional Tabular Layout
Controlling Blank Lines, Grand Totals, and Other Settings
Customizing the Pivot Table Appearance with Styles and Themes
Customizing a Style
Modifying Styles with Document Themes
Changing Summary Calculations
Understanding Why One Blank Cell Causes a Count
Using Functions Other Than Count or Sum
Adding and Removing Subtotals
Suppress Subtotals When You Have Many Row Fields
Adding Multiple Subtotals for One Field
Changing the Calculation in a Value Field
Showing Percentage of Total
Using % Of to Compare One Line to Another Line
Showing Rank
Tracking Running Total and Percent of Running Total
Display Change from a Previous Field
Tracking Percent of Parent Item
Track Relative Importance with the Index Option
Next Steps
Chapter 4 Grouping, Sorting, and Filtering Pivot Data
Grouping Pivot Fields
Grouping Date Fields
Including Years When Grouping by Months
Grouping Date Fields by Week
Ungrouping
Grouping Numeric Fields
Using the PivotTable Fields List
Docking and Undocking the PivotTable Fields List
Rearranging the PivotTable Fields List
Using the Areas Section Drop-Downs
Sorting in a Pivot Table
Sorting Customers into High-to-Low Sequence Based on Revenue
Using a Manual Sort Sequence
Using a Custom List for Sorting
Filtering the Pivot Table: An Overview
Using Filters for Row and Column Fields
Filtering Using the Check Boxes
Filtering Using the Search Box
Filtering Using the Label Filters
Filtering a Label Column Using Information in a Values Column
Creating a Top-Five Report Using the Top 10 Filter
Filtering Using the Date Filters in the Label Drop-Down
Filtering Using the Filters Area
Adding Fields to the Filters Area
Choosing One Item from a Filter
Choosing Multiple Items from a Report Filter
Replicating a Pivot Table Report for Each Item in a Filter
Filtering Using Slicers and Timelines
Using Timelines to Filter by Date
Driving Multiple Pivot Tables from One Set of Slicers
Next Steps
Chapter 5 Performing Calculations Within Your Pivot Tables
Introducing Calculated Fields and Calculated Items
Method 1: Manually Add the Calculated Field to Your Data Source
Method 2: Use a Formula Outside Your Pivot Table to Create the Calculated Field5
Method 3: Insert a Calculated Field Directly into Your Pivot Table
Creating Your First Calculated Field
Creating Your First Calculated Item
Understanding the Rules and Shortcomings of Pivot Table Calculations
Remembering the Order of Operator Precedence
Using Cell References and Named Ranges
Using Worksheet Functions
Using Constants
Referencing Totals
Rules Specific to Calculated Fields
Rules Specific to Calculated Items
Managing and Maintaining Your Pivot Table Calculations
Editing and Deleting Your Pivot Table Calculations
Changing the Solve Order of Your Calculated Items
Documenting Your Formulas
What’s Next
Chapter 6 Using Pivot Charts and Other Visualizations
What Is a Pivot Chart...Really?
Creating Your First Pivot Chart
Keeping Pivot Chart Rules in Mind
Changes in the Underlying Pivot Table Affect Your Pivot Chart
The Placement of Data Fields in Your Pivot Table Might Not Be Best Suited for Your Pivot Chart
A Few Formatting Limitations Still Exist in Excel 2013
Examining Alternatives to Using Pivot Charts
Method 1: Turn Your Pivot Table into Hard Values
Method 2: Delete the Underlying Pivot Table
Method 3: Distribute a Picture of the Pivot Chart
Method 4: Use Cells Linked Back to the Pivot Table as the Source Data for Your Chart
Using Conditional Formatting with Pivot Tables
Creating Custom Conditional Formatting Rules
What’s Next
Chapter 7 Analyzing Disparate Data Sources with Pivot Tables
Using Multiple Consolidation Ranges
Creating a Multiple Consolidation Pivot Table
Analyzing the Anatomy of a Multiple Consolidation Ranges Pivot Table
The Row Field
The Column Field
The Value Field
The Page Fields
Using the Internal Data Model
Building Out Your First Data Model
Managing Relationships in the Data Model
Adding a New Table to the Data Model
Removing a Table from the Data Model
Create a New Pivot Table Using the Data Model
Limitations of the Internal Data Model
Building a Pivot Table Using External Data Sources
Building a Pivot Table with Microsoft Access Data
Building a Pivot Table with SQL Server Data
What’s Next
Chapter 8 Sharing Pivot Tables with Others
Designing a Workbook as an Interactive Web Page
Sharing a Link to Your Web Workbook
Embedding Your Workbook in a Blog Post or Your Web Page
Sharing Pivot Tables with Other Versions of Office
Chapter 9 Working with and Analyzing OLAP Data
What Is OLAP?
Connecting to an OLAP Cube
Understanding the Structure of an OLAP Cube
Understanding the Limitations of OLAP Pivot Tables
Creating Offline Cubes
Breaking Out of the Pivot Table Mold with Cube Functions
Adding Calculations to Your OLAP Pivot Tables
Creating Calculated Measures
Creating Calculated Members
Managing Your OLAP Calculations
Performing What-If Analysis with OLAP Data
Next Steps
Chapter 10 Mashing Up Data with PowerPivot
Understanding the Benefits and Drawbacks of PowerPivot and the Data Model
Merge Data from Multiple Tables Without Using VLOOKUP
Import 100 Million Rows into Your Workbook
Create Better Calculations Using the DAX Formula Language
Other Benefits of the PowerPivot Data Model in All Editions of Excel
Benefits of the Full PowerPivot Add-In with Excel Pro Plus
Understanding the Limitations of the Data Model
Joining Multiple Tables Using the Data Model in Regular Excel 2013
Preparing Data for Use in the Data Model
Adding the First Table to the Data Model
Adding the Second Table and Defining a Relationship
Tell Me Again–Why Is This Better Than Doing a VLOOKUP?
Using QuickExplore
Creating a New Pivot Table from an Existing Data Model
Getting a Distinct Count
Using the PowerPivot Add-In from Excel 2013 Pro Plus
Enabling PowerPivot
Import a Text File
Add Excel Data by Copying and Pasting
Add Excel Data by Linking
Define Relationships
Add Calculated Columns Using DAX
Build a Pivot Table
Understanding Differences Between PowerPivot and Regular Pivot Tables
Two Kinds of DAX Calculations
DAX Calculations for Calculated Columns
Using RELATED() to Base a Column Calculation on Another Table
Using DAX to Create a Calculated Field in the Pivot Table
DAX Calculated Fields Implicitly Respect the Filters
Define a DAX Calculated Field
Is Unfilter Even a Word?
CALCULATE Is a Super-Enhanced Version of SUMIFS
Adding Fields to the Values Area Generates DAX Calculated Fields
Using a Calendar Table to Enable Time Intelligence Functions
Adding the Data to PowerPivot and Formatting It
PowerPivot Doesn’t Automatically Sort by Custom Lists
Create a PivotTable and Marvel at the Results
This Is a Discussion About Time Intelligence
Using Key Performance Indicators
Setting up a KPI Compared to an Absolute Value
Setting Up a KPI Compared to a Calculated Target Value
Other Notes About PowerPivot
Combination Layouts
Getting Your Data into PowerPivot with SQL Server
Other Issues
Next Steps
Chapter 11 Dashboarding with Power View
Preparing Your 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
Convert the Table to a Chart
Add Drill-Down to a Chart
To Begin a New Element, Drag a Field to a Blank Spot on the Canvas
Every Chart Point Is a Filter for Every Other Element
Adding a Real Slicer
The Filter Pane Can Be Confusing
Use Tile Boxes to Filter One or a Group of Charts
Replicating Charts Using Multiples
Showing Data on a Map
Using Table or Card View with Images
Changing the Calculation
Animating a Scatter Chart Over Time
Some Closing Tips on Power View
Animating Pivot Table Data on a Map
Next Steps
Chapter 12 Enhancing Your Pivot Table Reports with Macros
Why Use Macros with Your Pivot Table Reports?
Recording Your First Macro
Creating a User Interface with Form Controls
Altering a Recorded Macro to Add Functionality
What’s Next
Chapter 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
Writing Code to Handle Any Size Data Range
Using Super-Variables: Object Variables
Using With and End With to Shorten Code
Understanding Versions
Code for New Features Won’t Work in Previous Versions
Building a Pivot Table in Excel VBA
Adding Fields to the Data Area
Formatting the Pivot Table
Dealing with Limitations of Pivot Tables
Filling Blank Cells in the Data Area
Filling Blank Cells in the Row Area
Learning Why You Cannot Affect a Pivot Table by Inserting or Deleting Cells
Controlling Totals
Determining the Size of a Finished Pivot Table to Convert It to Values
Pivot Table 201: Creating a Report Showing Revenue by Category
Ensuring Table 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
Addressing Issues with Two or More Data Fields
Using Calculations Other Than Sum
Calculated Data Fields
Calculated Items
Calculating Groups
Using Show Values As to Perform Other Calculations
Using Advanced Pivot Table Techniques
Using AutoShow to Produce Executive Overviews
Using ShowDetail to Filter a Recordset
Creating Reports for Each Region or Model
Manually Filtering Two or More Items in a PivotField
Using the Conceptual Filters
Using the Search Filter
Setting up Slicers to Filter a Pivot Table
Using the Data Model in Excel 2013
Add Both Tables to the Data Model
Create a Relationship Between the Two Tables
Define the Pivot Cache and Build the Pivot Table
Add Model Fields to the Pivot Table
Add Numeric Fields to the Values Area
Putting It All Together
Next Steps
Chapter 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
Option 1: Implement the Repeat All Data Items Feature
Option 2: Use Excel’s Go To Special Functionality
Tip 6: Add a Rank Number Field to Your Pivot Table
Tip 7: Reduce the Size of Your Pivot Table Reports
Delete Your Source Data Tab
Tip 8: Create an Automatically Expanding Data Range
Tip 9: Compare Tables Using a Pivot Table
Tip 10: AutoFilter a Pivot Table
Tip 11: Transpose a Data Set with a Pivot Table
Step 1: Combine All Non-Column-Oriented Fields into One Dimension Field
Step 2: Create a Multiple Consolidation Ranges Pivot Table
Step 3: Double-Click the Grand Total Intersection of Row and Column
Step 4: Parse Your Dimension Column into Separate Fields
Tip 12: Force Two Number Formats in a Pivot Table
Tip 13: Create a Frequency Distribution with a Pivot Table
Tip 14: Use a Pivot Table to Explode a Data Set to Different Tabs
Tip 15: Use a Pivot Table to Explode a Data Set to Different Workbooks
What’s Next
Chapter 15 Dr. Jekyll and Mr. GetPivotData
Turning Off the Evil GetPivotData Problem
Preventing GetPivotData by Typing the Formula
GetPivotData Is Surely Evil–Turn It Off
Why Did Microsoft Force GetPivotData on Us?
Using GetPivotData to Solve Pivot Table Annoyances
Build an Ugly Pivot Table
Build the Shell Report
Using GetPivotData to Populate the Shell Report
Updating the Report in Future Months
9780789748751 TOC 12/18/2013