Cover -- Title Page -- Copyright Page -- Dedication Page -- Acknowledgments -- About the Author -- About the Technical Editor -- Contents at a Glance -- Contents -- Introduction -- What Does This Book Cover? -- Who Should Read This Book -- Reader Support for This Book -- Companion Download Files -- How to Contact the Author -- Chapter 1 Preparing the Data for an Excel Pivot Table -- What Is Data? -- What the Data Should Look Like -- Types of Data That Can Be Used in Excel Pivot Tables -- Using Excel Data -- Importing Data from External Data Sources into Excel -- Importing Data from a Text/CSV File -- Importing Data from an Access Database -- Importing Data from a Web Page -- Connecting to an ODBC Database -- Importing Data from a Different Excel Workbook -- Refreshing the Data -- Using Power Query to Clean the Data -- Using the Queries & -- Connections Window -- Analyzing the Table -- Splitting a Column -- Merging Columns -- Changing Data Types -- Removing Columns and Rows -- Undoing Steps in Power Query -- Replacing Values -- Trimming Spaces from the Beginning and End of the Data in a Column -- Combining Tables Using the Append Query -- Combining Tables Using the Merge Query -- Creating Subtotals in Your Data Using the GroupBy Query -- Using Power Query to Create Calculations -- Calculating Age/Years of Service -- Using the Built-in Date Functions -- Using the Built-In Time Functions -- Using a Custom Column for Other Calculations -- Calculating a Line Total -- Calculating the Days to Ship -- Calculating the New Total -- Changing or Viewing an Existing Custom Column -- Using a Conditional Column for Calculations -- Calculating a Reorder -- Changing or Viewing an Existing Conditional Column -- Summary -- Chapter 2 Summarizing and Presenting Data with a Pivot Table -- What Is a Pivot Table? -- Making a Pivot Table from Scratch.

The PivotTable Fields Window -- Summarizing Data on One Field -- The Default Calculation in the Values Section -- Showing the Detail with a Drill Down -- Creating a Cross Tab or Cross Reference -- Adding More Layers of Detail to the Pivot Table -- Creating and Managing Groups -- Changing the Grouping Order -- Adding Groupings to Columns -- Creating Your Own Group -- Removing Your Own Group -- Using Recommended PivotTables and Analyzing Data -- Creating a Pivot Table Using Recommended PivotTables -- Creating a Pivot Table Using Analyze Data -- Making the Pivot Table Look Better -- Formatting Numbers -- Managing Subtotals -- Calculating Grand Totals -- Working with Blank Rows -- Changing the Layout -- Applying Styles -- Replacing Spaces and Other Options -- Using Conditional Formatting -- Summary -- Chapter 3 Using Calculations in Pivot Tables -- Using Built-In Calculations in Your Pivot Table -- Adding Calculation Fields to the Pivot Table -- Changing the Calculation Type of a Field -- Changing the Order of the Calculations -- The Calculations in the Summarize Values By Tab -- The Calculations in the Show Values As Tab -- Removing Calculations from the Pivot Table -- Delaying Calculations in a Pivot Table -- Changing the Way Errors Display in the Pivot Table -- Creating Custom Calculations -- Calculated Fields -- To Change or Delete a Calculated Field -- Calculated Items -- To Change or Delete a Calculated Item -- The Solve Order -- Documenting Your Calculated Items and Fields -- Limitations of Calculated Fields and Items -- Refreshing Values on the Pivot Table -- Manual Refresh -- Automatic Refresh -- Changing the Data Source -- Using Numbers from the Pivot Table in Other Calculations -- Enabling the GETPIVOTDATA Function -- Using the GETPIVOTDATA Function -- Summary -- Chapter 4 Sorting and Filtering the Pivot Table -- Sorting the Pivot Table.

Sorting by a Column -- Sorting by a Row -- Putting the Pivot Table in Ascending Order -- Creating Your Own Sort Order -- Creating Your Own Sort Order by Dragging Cells -- Creating Your Own Sort Order by Using a Custom List -- Sorting a Pivot Table with Multiple Fields -- Filtering the Pivot Table -- Filter by Selection -- Clearing the Filter -- Using AutoFilters -- Label Filters -- Value Filters -- Top 10 Filters -- Date Filters -- Showing Items with No Data -- Using the Filters Section -- Creating New Sheets from the Field in the Filters Section -- Using Slicers and Timelines -- Creating a Timeline -- Updating Multiple Pivot Tables Using Slicers and Timelines -- Removing a Slicer or a Timeline Window -- Summary -- Chapter 5 Making the Pivot Table More Visual with Charts -- Creating a Chart from a Pivot Table -- PivotChart Features -- The Design Tab and the Format Tab for a Chart -- Managing and Modifying Chart Elements -- The Chart Title -- Data Labels -- Changing the Number Format of the Data Labels -- Changing the Data Labels to Display Vertically -- Trendlines -- Adding a Trendline to a Chart -- Creating a Forecast from a Trendline -- Formatting the Numbers of the Y-Axis or X-Axis -- Creating and Managing a Pie Chart -- Changing the Data Labels on the Pie Chart to Percents -- Separating a Section from a Pie Chart -- Changing the Pie Chart to a Doughnut Chart -- Creating a Combo Chart -- Creating and Using Chart Templates -- Summary -- Chapter 6 Summarizing Data by Date and Time -- Summarizing a Pivot Table by the Built-In Date Periods -- Summarizing a Pivot Table by a Single Period of Time -- Summarizing a Pivot Table by More Than One Time Period -- Showing/Hiding the Totals for Each Time Period -- Expanding/Collapsing Time Periods in the Pivot Table -- Using Slicers to Filter the Pivot Table by Date and Time.

Displaying Earliest and Most Recent Dates -- Summarizing the Pivot Table by Number of Days -- Summarizing the Pivot Table Using Custom Date Calculations -- Summarizing the Pivot Table by Week -- Using the Day of the Week in a Pivot Table -- Using a Fiscal Period in a Pivot Table -- Summary -- Chapter 7 Creating a Pivot Table from Multiple Spreadsheets -- Creating a Pivot Table from Multiple Ranges Using the PivotTable Wizard -- Creating a Pivot Table Using the Data Model -- Adding, Changing, or Deleting Relationships -- Adding Additional Tables into the Data Model -- Creating and Managing Sets -- Changing or Deleting a Set -- Summary -- Chapter 8 Improving a Pivot Table with Power Pivot -- Activating the Power Pivot Add-In -- Using the Power Pivot Add-In -- Adding Tables to the Data Model -- Adding Formatted Excel Tables into the Data Model -- Adding Tables from Other Sources into the Data Model -- Joining Tables -- Changing, Deleting, and Managing the Relationships -- Creating a Pivot Table from the Power Pivot for Excel Window -- Adding Calculations to a Pivot Table Using the Data Model -- Adding a Calculated Column to a Table in the Data Model -- Adding a Measure into the Data Model -- Creating a KPI -- Summary -- Chapter 9 Pulling It All Together: Creating a Dashboard from Pivot Tables -- Looking at a Finished Dashboard -- Creating Your Own Dashboard -- Adding Charts to Your Dashboard -- Adding Slicers and Timelines to Your Dashboard -- Displaying Totals and Percent of Totals -- Working with Form Controls, Macros, and VBA Code -- Adding Finishing Touches to Your Dashboard -- Hiding Sheets -- Hiding the Pivot Table, Gridlines, Column Headings, and the Formula -- Summary -- Index -- EULA.