Excel 2013 Pivot Table Data Crunching

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  

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