|
Abstract:
|
Machine generated contents note: What Is a Pivot Table? -- Why Should You Use a Pivot Table? -- When Should You Use a Pivot Table? -- Anatomy of a Pivot Table -- Values Area -- Row Area -- Column Area -- Report Filter Area -- Pivot Tables Behind the Scenes -- Limitations of Pivot Table Reports -- A Word About Compatibility -- Next Steps -- Preparing Data for Pivot Table Reporting -- Ensure Data Is in a Tabular Layout -- Avoid Storing Data in Section Headings -- Avoid Repeating Groups as Columns -- Eliminate Gaps and Blank Cells in the Data Source -- Apply Appropriate Type Formatting to Fields -- Summary of Good Data Source Design -- Creating a Basic Pivot Table -- Adding Fields to the Report -- Adding Layers to a Pivot Table -- Rearranging a Pivot Table -- Creating a Report Filter -- Introducing Slicers -- Keeping Up with Changes in the Data Source -- Changes Have Been Made to Existing Data Sources -- Data Source's Range Has Expanded -- Sharing the Pivot Cache --Contents note continued: Side Effects of Sharing a Pivot Cache -- Saving Time with New Pivot Table Tools -- Deferring Layout Updates -- Starting Over with One Click -- Relocating a Pivot Table -- Next Steps -- 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 Layout Changes -- Using the New Compact Layout -- Using the Outline Form Layout -- Using the Traditional Tabular Layout -- Controlling Blank Lines, Grand Totals, Subtotals, and Other Settings -- Customizing the Pivot Table Appearance with Styles and Themes -- Customizing a Style -- Choosing a Default Style for Future Pivot Tables -- 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 --Contents note continued: Suppress Subtotals When You Have Many Row Fields -- Adding Multiple Subtotals for One Field -- Using Running Total, % of, Rank Options -- Tracking YTD Numbers with Running Total In -- Tracking Percent of Running Total -- Tracking Percent of Parent Item -- Display Change from a Previous Field -- Showing Rank -- Using % of to Compare One Line to Another Line -- Track Relative Importance with the Index Option -- Next Steps -- Grouping Pivot Fields -- Grouping Date Fields -- Including Years When Grouping by Months -- Grouping Date Fields by Week -- Grouping Two Date Fields in One Report -- Grouping Numeric Fields -- Ungrouping -- Looking at the PivotTable Field List -- Docking and Undocking the PivotTable Field List -- Rearranging the PivotTable Field List -- Using the Areas Section Drop-Downs -- Using the Fields Drop-Down -- Sorting in a Pivot Table -- Sorting Using the Sort Icons on the Options Tab -- Sorting Using the Field List Hidden Drop-Down --Contents note continued: Understanding the Effect of Layout Changes on AutoSort -- Using a Manual Sort Sequence -- Using a Custom List for Sorting -- Filtering the Pivot Table -- Using Filters in the Label Areas -- Filtering Using the Report Filter Area -- Adding Fields to the Report Filter Area -- Choosing One Item from a Report Filter -- Choosing Multiple Items from a Report Filter -- Quickly Selecting or Clearing All Items from a Filter -- Using Slicers -- Next Steps -- Introducing Calculated Fields and Calculated Items -- Method 1: Manually Add the Calculated Field to the Data Source -- Method 2: Use a Formula Outside the Pivot Table to Create the Calculated Field -- Method 3: Insert a Calculated Field Directly into the Pivot Table -- Creating Your First Calculated Field -- Creating Your First Calculated Item -- Understanding Rules and Shortcomings of Pivot Table Calculations -- Remembering the Order of Operator Precedence -- Using Cell References and Named Ranges --Contents note continued: Using Worksheet Functions -- Using Constants -- Referencing Totals -- Rules Specific to Calculated Fields -- Rules Specific to Calculated Items -- Managing and Maintaining Pivot Table Calculations -- Editing and Deleting Pivot Table Calculations -- Changing the Solve Order of Calculated Items -- Documenting Formulas -- Next Steps -- What Is a Pivot Chart...Really? -- Creating Your First Pivot Chart -- Keeping Pivot Chart Rules in Mind -- How Changes in Underlying Pivot Tables Affect Pivot Charts -- Placement of Data Fields in a Pivot Table Might Not Be Suited for a Pivot Chart -- A Few Formatting Limitations Still Exist in Excel 2010 -- Examining Alternatives to Using Pivot Charts -- Method 1: Turn the 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 to the Pivot Table as the Source Data -- Using Conditional Formatting with Pivot Tables --Contents note continued: Next Steps -- Using Multiple Consolidation Ranges -- Analyzing the Anatomy of a Multiple Consolidation Range Pivot Table -- The Row Field -- The Column Field -- The Value Field -- The Page Fields -- Redefining Your Pivot Table -- Building a Pivot Table Using External Data Sources -- Building a Pivot Table with Microsoft Access Data -- Building a Pivot Table with SQL Server Data -- Next Steps -- Sharing a Pivot Table with Other Versions of Office -- Compatibility Issues Between Excel 2007 and Excel 2010 -- Repeat All Labels Is Lost After Any Change in 2007 -- Excel 2007 Pivot Tables Work Fine in Excel 2010 -- Features Unavailable When a Legacy Pivot Table Is Opened in Excel 2010 -- Excel 2010 Compatibility Mode -- No Downgrade Path Available from Version 12 and Version 14 Pivot Tables -- Strategies for Sharing Pivot Tables -- Saving Pivot Tables to the Web -- Using Web Services in Excel 2003 -- Using Save As HTML in Excel 2010 Provides a Static Image --Contents note continued: Publishing Pivot Tables to Excel Services in Excel 2010 -- Viewing Your Excel 2010 Pivot Table on the SkyDrive -- Next Steps -- What Is OLAP? -- Connecting to an OLAP Cube -- Understanding the Structure of an OLAP Cube -- Understanding Limitations of OLAP Pivot Tables -- Creating Offline Cubes -- Breaking Out of the Pivot Table Mold with Cube Functions -- Next Steps -- Benefits and Drawbacks to PowerPivot -- Mega-Benefits of PowerPivot -- Moderate Benefits of PowerPivot -- Why Is This Free? -- Benefits of the Server Version of PowerPivot -- Drawbacks to Using PowerPivot -- Installing PowerPivot -- Building a PowerPivot Report -- 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 -- Slicers in PowerPivot -- Some Things Are Different -- Two Kinds of DAX Calculations -- DAX Calculations for Calculated Columns --Contents note continued: Using RELATED() to Base a Column Calculation on Another Table -- Using DAX to Create New Measures -- Count Distinct Using DAX -- When "Filter, Then Calculate" Does Not Work in DAX Measures -- Mix in Those Amazing Time Intelligence Functions -- Using PowerPivot To Access Named Sets For Asymmetric Reporting -- Other Notes -- Combination Layouts -- Report Formatting -- Refreshing PowerPivot Versus Refreshing Pivot Table -- Getting Your Data into PowerPivot with SQL Server -- Other Issues -- Next Steps -- 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 -- Next Steps -- Introducing VBA -- 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 --Contents note continued: Writing Code to Handle Any Size Data Range -- Using Super-Variables: Object Variables -- Using With and End With to Shorten Code -- Understanding Versions -- New in Excel 2010 -- Concepts Introduced in Excel 2007 -- 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 Move or Change Part of a Pivot Report -- Controlling Totals -- Determining Size of a Finished Pivot Table to Convert the Pivot Table to Values -- Pivot Table 201: Creating a Report Showing Revenue by Category -- Ensuring Tabular Layout Is Utilized -- Rolling Daily Dates Up to Years -- Eliminating Blank Cells -- Controlling the Sort Order with AutoSort -- Changing Default Number Format -- Suppressing Subtotals for Multiple Row Fields -- Copying Finished Pivot Table as Values to a New Workbook --Contents note continued: 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 -- Generating a Count Distinct -- 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 -- Filtering an OLAP Pivot Table Using Named Sets -- Formatting a Pivot Table -- Applying a Table Style -- Changing the Layout -- Applying a Data Visualization -- Next Steps -- Unique Solutions to Common Pivot Table Problems --Contents note continued: 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 Your Pivot Table -- Tip 7: Reduce the Size of Your Pivot Table Reports -- Tip 8: Create an Automatically Expanding Data Range -- Tip 9: Comparing Tables with a PivotTable -- Tip 10: AutoFilter a PivotTable -- Tip 11: Transposing a Data Set with a PivotTable -- Tip 12: Forcing Two Number Formats in a Pivot Table -- Tip 13: Creating 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 -- Next Steps -- Turning Off the Evil GetPivotData Problem -- Preventing GetPivotData by Typing the Formula --Contents note continued: 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 -- Next Steps.
Even if you've never created a pivot table before, this book will help you leverage all their amazing flexibility and analytical power. You'll even learn how to use the free PowerPivot add-in to create jaw-dropping business intelligence mashups that integrate data from virtually anywhere. --Learning advanced pivot table and pivot chart techniques has never been easier. You'll find simple, step-by-step instructions, real-world case studies, and even complete, easy pivot table recipes to solve 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 in seconds ---Instantly highlight your most (and least) profitable customers, products, or regions ---Use dynamic pivot charts 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 to create pivot tables from multiple data sources and worksheets ---Work with and analyze OLAP data ---Dynamically filter pivot tables with Excel 2010's brand-new Slicer --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áusaving 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 --Book Jacket.
|
