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 xxi

Introduction xxiii

Chapter 1 Preparing the Data for an Excel Pivot Table 1

What Is Data? 2

What the Data Should Look Like 3

Types of Data That Can Be Used in Excel Pivot Tables 6

Using Excel Data 7

Importing Data from External Data Sources into Excel 9

Importing Data from a Text/CSV File 10

Importing Data from an Access Database 13

Importing Data from a Web Page 13

Connecting to an ODBC Database 16

Importing Data from a Different Excel Workbook 17

Refreshing the Data 18

Using Power Query to Clean the Data 20

Using the Queries & Connections Window 21

Analyzing the Table 23

Splitting a Column 24

Merging Columns 25

Changing Data Types 26

Removing Columns and Rows 26

Undoing Steps in Power Query 28

Replacing Values 29

Trimming Spaces from the Beginning and End of the Data in a Column 30

Combining Tables Using the Append Query 31

Combining Tables Using the Merge Query 32

Creating Subtotals in Your Data Using the GroupBy Query 34

Using Power Query to Create Calculations 36

Calculating Age/Years of Service 37

Using the Built-in Date Functions 38

Using the Built-In Time Functions 39

Using a Custom Column for Other Calculations 40

Calculating a Line Total 40

Calculating the Days to Ship 41

Calculating the New Total 41

Changing or Viewing an Existing Custom Column 43

Using a Conditional Column for Calculations 44

Calculating a Reorder 44

Changing or Viewing an Existing Conditional Column 45

Summary 46

Chapter 2 Summarizing and Presenting Data with a Pivot Table 47

What Is a Pivot Table? 47

Making a Pivot Table from Scratch 48

The PivotTable Fields Window 50

Summarizing Data on One Field 53

The Default Calculation in the Values Section 55

Showing the Detail with a Drill Down 55

Creating a Cross Tab or Cross Reference 56

Adding More Layers of Detail to the Pivot Table 57

Creating and Managing Groups 57

Changing the Grouping Order 58

Adding Groupings to Columns 58

Creating Your Own Group 58

Removing Your Own Group 59

Using Recommended PivotTables and Analyzing Data 60

Creating a Pivot Table Using Recommended PivotTables 60

Creating a Pivot Table Using Analyze Data 61

Making the Pivot Table Look Better 62

Formatting Numbers 63

Managing Subtotals 63

Calculating Grand Totals 64

Working with Blank Rows 65

Changing the Layout 65

Applying Styles 67

Replacing Spaces and Other Options 68

Using Conditional Formatting 71

Summary 72

Chapter 3 Using Calculations in Pivot Tables 73

Using Built-In Calculations in Your Pivot Table 73

Adding Calculation Fields to the Pivot Table 74

Changing the Calculation Type of a Field 75

Changing the Order of the Calculations 78

The Calculations in the Summarize Values By Tab 78

The Calculations in the Show Values As Tab 79

Removing Calculations from the Pivot Table 86

Delaying Calculations in a Pivot Table 86

Changing the Way Errors Display in the Pivot Table 87

Creating Custom Calculations 89

Calculated Fields 89

To Change or Delete a Calculated Field 92

Calculated Items 93

To Change or Delete a Calculated Item 95

The Solve Order 96

Documenting Your Calculated Items and Fields 98

Limitations of Calculated Fields and Items 99

Refreshing Values on the Pivot Table 100

Manual Refresh 101

Automatic Refresh 102

Changing the Data Source 103

Using Numbers from the Pivot Table in Other Calculations 105

Enabling the GETPIVOTDATA Function 105

Using the GETPIVOTDATA Function 106

Summary 107

Chapter 4 Sorting and Filtering the Pivot Table 109

Sorting the Pivot Table 110

Sorting by a Column 110

Sorting by a Row 112

Putting the Pivot Table in Ascending Order 113

Creating Your Own Sort Order 113

Creating Your Own Sort Order by Dragging Cells 113

Creating Your Own Sort Order by Using a Custom List 114

Sorting a Pivot Table with Multiple Fields 119

Filtering the Pivot Table 121

Filter by Selection 122

Clearing the Filter 123

Using AutoFilters 123

Label Filters 125

Value Filters 127

Top 10 Filters 129

Date Filters 131

Showing Items with No Data 132

Using the Filters Section 134

Creating New Sheets from the Field in the Filters Section 136

Using Slicers and Timelines 137

Creating a Timeline 140

Updating Multiple Pivot Tables Using Slicers and Timelines 142

Removing a Slicer or a Timeline Window 143

Summary 143

Chapter 5 Making the Pivot Table More Visual with Charts 145

Creating a Chart from a Pivot Table 146

PivotChart Features 148

The Design Tab and the Format Tab for a Chart 151

Managing and Modifying Chart Elements 153

The Chart Title 154

Data Labels 156

Changing the Number Format of the Data Labels 156

Changing the Data Labels to Display Vertically 158

Trendlines 161

Adding a Trendline to a Chart 161

Creating a Forecast from a Trendline 162

Formatting the Numbers of the Y-Axis or X-Axis 164

Creating and Managing a Pie Chart 166

Changing the Data Labels on the Pie Chart to Percents 166

Separating a Section from a Pie Chart 168

Changing the Pie Chart to a Doughnut Chart 168

Creating a Combo Chart 169

Creating and Using Chart Templates 171

Summary 174

Chapter 6 Summarizing Data by Date and Time 175

Summarizing a Pivot Table by the Built-in Date Periods 176

Summarizing a Pivot Table by a Single Period of Time 176

Summarizing a Pivot Table by More Than One Time Period 180

Showing/Hiding the Totals for Each Time Period 180

Expanding/Collapsing Time Periods in the Pivot Table 181

Using Slicers to Filter the Pivot Table by Date and Time 181

Displaying Earliest and Most Recent Dates 184

Summarizing the Pivot Table by Number of Days 186

Summarizing the Pivot Table Using Custom Date Calculations 188

Summarizing the Pivot Table by Week 189

Using the Day of the Week in a Pivot Table 191

Using a Fiscal Period in a Pivot Table 194

Summary 197

Chapter 7 Creating a Pivot Table from Multiple Spreadsheets 199

Creating a Pivot Table from Multiple Ranges Using the PivotTable Wizard 200

Creating a Pivot Table Using the Data Model 205

Adding, Changing, or Deleting Relationships 210

Adding Additional Tables into the Data Model 211

Creating and Managing Sets 213

Changing or Deleting a Set 216

Summary 217

Chapter 8 Improving a Pivot Table with Power Pivot 219

Activating the Power Pivot Add-In 220

Using the Power Pivot Add-In 222

Adding Tables to the Data Model 223

Adding Formatted Excel Tables into the Data Model 224

Adding Tables from Other Sources into the Data Model 224

Joining Tables 227

Changing, Deleting, and Managing the Relationships 229

Creating a Pivot Table from the Power Pivot for Excel Window 230

Adding Calculations to a Pivot Table Using the Data Model 232

Adding a Calculated Column to a Table in the Data Model 232

Adding a Measure into the Data Model 234

Creating a KPI 236

Summary 239

Chapter 9 Pulling It All Together: Creating a Dashboard from Pivot Tables 241

Looking at a Finished Dashboard 242

Creating Your Own Dashboard 243

Adding Charts to Your Dashboard 244

Adding Slicers and Timelines to Your Dashboard 245

Displaying Totals and Percent of Totals 247

Working with Form Controls, Macros, and VBA Code 248

The Developer Tab 249

Adding Command Buttons 250

Saving a Workbook with a Macro 253

Adding a Combo Box 255

Adding Finishing Touches to Your Dashboard 260

Hiding Sheets 261

Hiding the Pivot Table, Gridlines, Column Headings, and the Formula 262

Summary 264

Index 265