Getting Great Results with Excel Pivot Tables, PowerQuery and PowerPivot

THOMAS FRAGALE has four decades of professional experience in computing. He is a Microsoft Certified Trainer, a Microsoft Certified Office Master, and a Microsoft Certified Expert in Word and Excel. He has trained over 50,000 people in online webinars, public seminars, and on-site training. Fragale’s passion is helping people get the most out of their computers. He has also taught at Fordham University.

• Foreword xxiIntroduction xxiiiChapter 1 Preparing the Data for an Excel Pivot Table 1What Is Data? 2What the Data Should Look Like 3Types of Data That Can Be Used in Excel Pivot Tables 6Using Excel Data 7Importing Data from External Data Sources into Excel 9Importing Data from a Text/CSV File 10Importing Data from an Access Database 13Importing Data from a Web Page 13Connecting to an ODBC Database 16Importing Data from a Different Excel Workbook 17Refreshing the Data 18Using Power Query to Clean the Data 20Using the Queries & Connections Window 21Analyzing the Table 23Splitting a Column 24Merging Columns 25Changing Data Types 26Removing Columns and Rows 26Undoing Steps in Power Query 28Replacing Values 29Trimming Spaces from the Beginning and End of the Data in a Column 30Combining Tables Using the Append Query 31Combining Tables Using the Merge Query 32Creating Subtotals in Your Data Using the GroupBy Query 34Using Power Query to Create Calculations 36Calculating Age/Years of Service 37Using the Built-in Date Functions 38Using the Built-In Time Functions 39Using a Custom Column for Other Calculations 40Calculating a Line Total 40Calculating the Days to Ship 41Calculating the New Total 41Changing or Viewing an Existing Custom Column 43Using a Conditional Column for Calculations 44Calculating a Reorder 44Changing or Viewing an Existing Conditional Column 45Summary 46Chapter 2 Summarizing and Presenting Data with a Pivot Table 47What Is a Pivot Table? 47Making a Pivot Table from Scratch 48The PivotTable Fields Window 50Summarizing Data on One Field 53The Default Calculation in the Values Section 55Showing the Detail with a Drill Down 55Creating a Cross Tab or Cross Reference 56Adding More Layers of Detail to the Pivot Table 57Creating and Managing Groups 57Changing the Grouping Order 58Adding Groupings to Columns 58Creating Your Own Group 58Removing Your Own Group 59Using Recommended PivotTables and Analyzing Data 60Creating a Pivot Table Using Recommended PivotTables 60Creating a Pivot Table Using Analyze Data 61Making the Pivot Table Look Better 62Formatting Numbers 63Managing Subtotals 63Calculating Grand Totals 64Working with Blank Rows 65Changing the Layout 65Applying Styles 67Replacing Spaces and Other Options 68Using Conditional Formatting 71Summary 72Chapter 3 Using Calculations in Pivot Tables 73Using Built-In Calculations in Your Pivot Table 73Adding Calculation Fields to the Pivot Table 74Changing the Calculation Type of a Field 75Changing the Order of the Calculations 78The Calculations in the Summarize Values By Tab 78The Calculations in the Show Values As Tab 79Removing Calculations from the Pivot Table 86Delaying Calculations in a Pivot Table 86Changing the Way Errors Display in the Pivot Table 87Creating Custom Calculations 89Calculated Fields 89To Change or Delete a Calculated Field 92Calculated Items 93To Change or Delete a Calculated Item 95The Solve Order 96Documenting Your Calculated Items and Fields 98Limitations of Calculated Fields and Items 99Refreshing Values on the Pivot Table 100Manual Refresh 101Automatic Refresh 102Changing the Data Source 103Using Numbers from the Pivot Table in Other Calculations 105Enabling the GETPIVOTDATA Function 105Using the GETPIVOTDATA Function 106Summary 107Chapter 4 Sorting and Filtering the Pivot Table 109Sorting the Pivot Table 110Sorting by a Column 110Sorting by a Row 112Putting the Pivot Table in Ascending Order 113Creating Your Own Sort Order 113Creating Your Own Sort Order by Dragging Cells 113Creating Your Own Sort Order by Using a Custom List 114Sorting a Pivot Table with Multiple Fields 119Filtering the Pivot Table 121Filter by Selection 122Clearing the Filter 123Using AutoFilters 123Label Filters 125Value Filters 127Top 10 Filters 129Date Filters 131Showing Items with No Data 132Using the Filters Section 134Creating New Sheets from the Field in the Filters Section 136Using Slicers and Timelines 137Creating a Timeline 140Updating Multiple Pivot Tables Using Slicers and Timelines 142Removing a Slicer or a Timeline Window 143Summary 143Chapter 5 Making the Pivot Table More Visual with Charts 145Creating a Chart from a Pivot Table 146PivotChart Features 148The Design Tab and the Format Tab for a Chart 151Managing and Modifying Chart Elements 153The Chart Title 154Data Labels 156Changing the Number Format of the Data Labels 156Changing the Data Labels to Display Vertically 158Trendlines 161Adding a Trendline to a Chart 161Creating a Forecast from a Trendline 162Formatting the Numbers of the Y-Axis or X-Axis 164Creating and Managing a Pie Chart 166Changing the Data Labels on the Pie Chart to Percents 166Separating a Section from a Pie Chart 168Changing the Pie Chart to a Doughnut Chart 168Creating a Combo Chart 169Creating and Using Chart Templates 171Summary 174Chapter 6 Summarizing Data by Date and Time 175Summarizing a Pivot Table by the Built-in Date Periods 176Summarizing a Pivot Table by a Single Period of Time 176Summarizing a Pivot Table by More Than One Time Period 180Showing/Hiding the Totals for Each Time Period 180Expanding/Collapsing Time Periods in the Pivot Table 181Using Slicers to Filter the Pivot Table by Date and Time 181Displaying Earliest and Most Recent Dates 184Summarizing the Pivot Table by Number of Days 186Summarizing the Pivot Table Using Custom Date Calculations 188Summarizing the Pivot Table by Week 189Using the Day of the Week in a Pivot Table 191Using a Fiscal Period in a Pivot Table 194Summary 197Chapter 7 Creating a Pivot Table from Multiple Spreadsheets 199Creating a Pivot Table from Multiple Ranges Using the PivotTable Wizard 200Creating a Pivot Table Using the Data Model 205Adding, Changing, or Deleting Relationships 210Adding Additional Tables into the Data Model 211Creating and Managing Sets 213Changing or Deleting a Set 216Summary 217Chapter 8 Improving a Pivot Table with Power Pivot 219Activating the Power Pivot Add-In 220Using the Power Pivot Add-In 222Adding Tables to the Data Model 223Adding Formatted Excel Tables into the Data Model 224Adding Tables from Other Sources into the Data Model 224Joining Tables 227Changing, Deleting, and Managing the Relationships 229Creating a Pivot Table from the Power Pivot for Excel Window 230Adding Calculations to a Pivot Table Using the Data Model 232Adding a Calculated Column to a Table in the Data Model 232Adding a Measure into the Data Model 234Creating a KPI 236Summary 239Chapter 9 Pulling It All Together: Creating a Dashboard from Pivot Tables 241Looking at a Finished Dashboard 242Creating Your Own Dashboard 243Adding Charts to Your Dashboard 244Adding Slicers and Timelines to Your Dashboard 245Displaying Totals and Percent of Totals 247Working with Form Controls, Macros, and VBA Code 248The Developer Tab 249Adding Command Buttons 250Saving a Workbook with a Macro 253Adding a Combo Box 255Adding Finishing Touches to Your Dashboard 260Hiding Sheets 261Hiding the Pivot Table, Gridlines, Column Headings, and the Formula 262Summary 264Index 265