Getting Great Results with Excel Pivot Tables, PowerQuery and PowerPivot E-Book

Thomas Fragale

• Herausgeber: John Wiley & Sons
• Kategorie: Wissenschaft und neue Technologien
• Serie: Tech Today
• Sprache: Englisch

Seitenzahl: 360

Veröffentlichungsjahr: 2024

Beschreibung

Get more out of your data with step-by-step tutorials for the Excel features you need to know Excel is still the most popular tool for organizing and analyzing data, and today's professionals are expected to have a high degree of fluency with it. Complex Excel tools like Pivot Tables, PowerQuery, and PowerPivot can help you manage and report on data the way you need to. Getting Great Results with Excel Pivot Tables, PowerQuery and PowerPivot offers a fresh look at how these tools can help you. Author and Microsoft Certified Trainer Thomas Fragale breaks down the topics into easy-to-use steps and screenshots, so you'll be able to put your advanced Excel skills into practice right away. Using Pivot Tables, PowerQuery, and PowerPivot, you can import, sort, transform, summarize, and present your data, all without having to be a programmer. This book takes the technical jargon out of using these features, so you can do your job more efficiently, bring value to your teams, and advance your career. The plain-English instructions inside will help anyone learn to get quick, meaningful results from your data, without having a degree in computing. * Get easy-to-understand walkthroughs for analyzing data and creating dashboards in Microsoft Excel * Learn how to organize data in Excel and use advanced features to find patterns and insights * Summarize any kind of data faster and easier, leaving you more time for other tasks * Turn raw numbers into new knowledge, reports, and charts that tell coworkers and customers what they need to know This book is great for anybody who has tons of raw data and needs to make sense of it. Managers, salespeople, finance professionals, marketers--along with anyone else who works with large amounts of data--will love this quick and easy guide to Pivot Tables, PowerQuery, and PowerPivot.

Leseprobe

Table of Contents

Cover

Table of Contents

Title Page

Foreword

Introduction

What Does This Book Cover?

Who Should Read This Book

Reader Support for This Book

CHAPTER 1: Preparing the Data for an Excel Pivot Table

What Is Data?

Importing Data from External Data Sources into Excel

Using Power Query to Clean the Data

Using Power Query to Create Calculations

Summary

CHAPTER 2: Summarizing and Presenting Data with a Pivot Table

What Is a Pivot Table?

Making a Pivot Table from Scratch

Using Recommended PivotTables and Analyzing Data

Making the Pivot Table Look Better

Summary

CHAPTER 3: Using Calculations in Pivot Tables

Using Built‐In Calculations in Your Pivot Table

Creating Custom Calculations

Refreshing Values on the Pivot Table

Using Numbers from the Pivot Table in Other Calculations

Summary

CHAPTER 4: Sorting and Filtering the Pivot Table

Sorting the Pivot Table

Filtering the Pivot Table

Summary

CHAPTER 5: Making the Pivot Table More Visual with Charts

Creating a Chart from a Pivot Table

Managing and Modifying Chart Elements

Creating and Managing a Pie 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 the Pivot Table Using Custom Date Calculations

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

Summary

CHAPTER 8: Improving a Pivot Table with Power Pivot

Activating the Power Pivot Add‐In

Using the Power Pivot Add‐In

Summary

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

Looking at a Finished Dashboard

Creating Your Own Dashboard

Working with Form Controls, Macros, and VBA Code

Adding Finishing Touches to Your Dashboard

Summary

Index

Copyright

Dedication

Acknowledgments

About the Author

About the Technical Editor

End User License Agreement

List of Tables

Chapter 1

Table 1.1: Ways to break down a date field

Table 1.2: Ways to break down a time field

Chapter 2

Table 2.1: The PivotTable Fields window sections

Table 2.2: Report Layout menu options

Table 2.3: PivotTable Options

Chapter 3

Table 3.1: Calculations available to summarize values

Table 3.2: The Calculations in the Show Values As tab of the Value Field Se...

Chapter 4

Table 4.1: Examples of wildcards in label filters

Chapter 5

Table 5.1: Chart elements

Chapter 6

Table 6.1: Date/Time functions in Excel

Table 6.2: Fiscal periods based on fiscal start date of July 1, 2023

Table 6.3: The table used in the VLOOKUP formulas to calculate fiscal perio...

Chapter 8

Table 8.1: Common fields to join between the tables

Chapter 9

Table 9.1: Form controls

List of Illustrations

Chapter 1

Figure 1.1: An example of good data

Figure 1.2: An example of bad data

Figure 1.3: Selecting the Find icon

Figure 1.4: Clicking Options

Figure 1.5: The Alignment tab in the Find Format dialog box

Figure 1.6: Nonformatted data

Figure 1.7: Formatted table

Figure 1.8: Formatting data as a table

Figure 1.9: Importing data

Figure 1.10: The data being imported

Figure 1.11: Using the first row as headers

Figure 1.12: The Navigator window when importing data from an Access databas...

Figure 1.13: Importing data from a web page

Figure 1.14: Importing data from an ODBC data source

Figure 1.15: Importing multiple sheets from a different Excel workbook

Figure 1.16: Refreshing the table automatically

Figure 1.17: Displaying the Queries & Connections window

Figure 1.18: Allowing Power Query to see normal Excel data

Figure 1.19: Analyzing the data using Power Query

Figure 1.20: Splitting a column

Figure 1.21: Merging columns

Figure 1.22: Changing data types

Figure 1.23: Removing blank rows

Figure 1.24: Undoing steps in Power Query

Figure 1.25: Replacing values using Power Query

Figure 1.26: Trimming leading and trailing spaces

Figure 1.27: Using the Append Query

Figure 1.28: Using the Merge Query

Figure 1.29: Completing the Merge Query

Figure 1.30: Using the GroupBy Query to create subtotals

Figure 1.31: Calculating age using Power Query

Figure 1.32: Line total calculation

Figure 1.33: Days to Ship calculation

Figure 1.34: New total calculation

Figure 1.35: Viewing or changing a custom column

Figure 1.36: Add Conditional Column window

Figure 1.37: Viewing or changing a calculated column

Chapter 2

Figure 2.1: Creating a pivot table from a table or range

Figure 2.2: New pivot table

Figure 2.3: Showing the field list

Figure 2.4: Rearranging the PivotTable Fields window

Figure 2.5: Side‐by‐side view of the PivotTable Fields window

Figure 2.6: Dragging a field to the Rows section

Figure 2.7: Summarizing one field

Figure 2.8: Drilling down

Figure 2.9: A cross reference

Figure 2.10: Creating groupings

Figure 2.11: Changed grouping order

Figure 2.12: Adding a column grouping

Figure 2.13: Creating your own group

Figure 2.14: Recommended PivotTables feature

Figure 2.15: The Analyze Data dialog box

Figure 2.16: Number formats

Figure 2.17: Displaying or modifying subtotals

Figure 2.18: Displaying or hiding grand totals

Figure 2.19: Displaying blank rows

Figure 2.20: Report layout options

Figure 2.21: Pivot table styles

Figure 2.22: A pivot table with banded rows

Figure 2.23: PivotTable Options dialog box

Figure 2.24: Conditional Formatting options

Figure 2.25: Conditional Formatting settings

Chapter 3

Figure 3.1: Adding another calculation to a pivot table

Figure 3.2: Changing a number's calculation by right‐clicking a number in th...

Figure 3.3: The Value Field Settings dialog box

Figure 3.4: Pivot table with a sum and a count

Figure 3.5: The Show Values As tab of the Value Field Settings dialog box

Figure 3.6: The Sum and the % of Total in the pivot table

Figure 3.7: Creating the Difference From calculation

Figure 3.8: The Sum and the Difference From calculations in the pivot table...

Figure 3.9: Creating the Running Total calculation

Figure 3.10: The Sum and the Running Total

Figure 3.11: Creating the Rank calculation

Figure 3.12: The Sum and the Rank

Figure 3.13: The Defer Layout checkbox

Figure 3.14: The PivotTable Options dialog box

Figure 3.15: A pivot table with the errors showing as 0

Figure 3.16: The Insert Calculated Field dialog box

Figure 3.17: The Sum and the Sales Tax

Figure 3.18: The Sum, Sales Tax, and the Line Total

Figure 3.19: Changing or deleting a Calculated Field

Figure 3.20: The Insert Calculated Item dialog box

Figure 3.21: The Calculated Item in the pivot table

Figure 3.22: Changing or deleting a Calculated Item

Figure 3.23: Before changing the Solve Order

Figure 3.24: The Solve Order dialog box

Figure 3.25: After changing the Solve Order

Figure 3.26: Result of using the List Formulas feature

Figure 3.27: Before the refresh

Figure 3.28: After the refresh

Figure 3.29: The Data tab of the PivotTable Options dialog box

Figure 3.30: The Change PivotTable Data Source dialog box

Figure 3.31: The Commission Amt now displays in the PivotTable Fields window...

Figure 3.32: Enabling the GETPIVOTDATA function

Figure 3.33: The GETPIVOTDATA formula

Chapter 4

Figure 4.1: Pivot table sorted by customer name

Figure 4.2: Pivot table sorted by the Grand Total column

Figure 4.3: The Sort By Value dialog box

Figure 4.4: Pivot table sorted across a row

Figure 4.5: Positioning the mouse pointer so you can drag a cell

Figure 4.6: Pivot table sorted by dragging a row or column

Figure 4.7: The Excel Options dialog box

Figure 4.8: The Edit Custom Lists button

Figure 4.9: The Custom Lists dialog box

Figure 4.10: The Sort dialog box

Figure 4.11: The More Sort Options dialog box

Figure 4.12: Pivot table sorted using a custom list

Figure 4.13: The Sort dialog box

Figure 4.14: The More Sort Options dialog box

Figure 4.15: Pivot table sorted by employee last name and by value of each s...

Figure 4.16: Filter by selection options

Figure 4.17: Pivot table filtered by selection

Figure 4.18: Sort and filter menu

Figure 4.19: Pivot table filtered using an AutoFilter

Figure 4.20: The Label Filters menu

Figure 4.21: The Label Filter dialog box

Figure 4.22: Pivot table filtered using Label Filters

Figure 4.23: The Value Filters menu

Figure 4.24: The Value Filter dialog box

Figure 4.25: Pivot table filtered using Value Filters

Figure 4.26: The Top 10 Filter dialog box

Figure 4.27: Pivot table filtered using a Top 10 filter

Figure 4.28: The Date Filters menu

Figure 4.29: The Date Filter dialog box

Figure 4.30: Pivot table filtered using a Date Filter

Figure 4.31: Layout & Print tab of the Field Settings dialog box

Figure 4.32: Pivot table showing items with no data

Figure 4.33: Moving a field into the Filters section

Figure 4.34: Choosing a value from the Filters section

Figure 4.35: Pivot table filtered using the Filters section

Figure 4.36: The Show Report Filter Pages dialog box

Figure 4.37: Sheets made from using Show Report Filter Pages

Figure 4.38: The Insert Slicers dialog box

Figure 4.39: Slicer window for the Shipper field

Figure 4.40: The Slicer Settings dialog box

Figure 4.41: The Format Slicer window

Figure 4.42: The Insert Timelines dialog box

Figure 4.43: Timeline window for the OrderDate field

Figure 4.44: The Format Timeline window

Figure 4.45: Report Connections dialog box

Chapter 5

Figure 5.1: The Insert Chart dialog box

Figure 5.2: A chart that is selected

Figure 5.3: The PivotTable Analyze tab

Figure 5.4: The PivotChart Analyze tab

Figure 5.5: The PivotChart Fields window

Figure 5.6: Using AutoFilters to sort and filter the chart

Figure 5.7: A chart with AutoFilters turned on

Figure 5.8: A chart with AutoFilters turned off

Figure 5.9: The menu for the Field Buttons icon on the PivotChart Analyze ta...

Figure 5.10: Quick Layouts on the Design tab

Figure 5.11: A chart before using Switch Row/Column

Figure 5.12: A chart after using Switch Row/Column

Figure 5.13: The Format tab for charts

Figure 5.14: The Format window on the right

Figure 5.15: A chart with some chart elements

Figure 5.16: A chart with a chart title

Figure 5.17: Data Label options

Figure 5.18: A chart with data labels

Figure 5.19: The Format Data Labels window

Figure 5.20: Scrolling to the bottom of the Format Data Labels window

Figure 5.21: A chart with currency data labels

Figure 5.22: The Chart Styles menu

Figure 5.23: The chart displaying the second chart style

Figure 5.24: Changing the text direction

Figure 5.25: The chart showing rotated data labels

Figure 5.26: The Add Trendline dialog box

Figure 5.27: A chart with a trendline

Figure 5.28: Trendline types

Figure 5.29: The Format Trendline window

Figure 5.30: A chart with a trendline with a forecast

Figure 5.31: Changing the display units

Figure 5.32: A chart showing thousands in the y‐axis

Figure 5.33: A pie chart

Figure 5.34: The Format Data Labels window

Figure 5.35: A pie chart with percents as data labels

Figure 5.36: A pie chart with a separated slice

Figure 5.37: A doughnut chart

Figure 5.38: Creating a combo chart using the Insert Chart dialog box

Figure 5.39: A combo chart

Figure 5.40: The Save Chart Template dialog box

Figure 5.41: Choosing a template from the Insert Chart dialog box

Figure 5.42: A chart made from a chart template

Chapter 6

Figure 6.1: Pivot table summarized by employee last name

Figure 6.2: Pivot table summarized by year

Figure 6.3: The Grouping dialog box

Figure 6.4: Pivot table summarized by month

Figure 6.5: Pivot table summarized by last name and month

Figure 6.6: Pivot table summarized by month and last name

Figure 6.7: Pivot table summarized by year and month

Figure 6.8: The Expand/Collapse options

Figure 6.9: Pivot table collapsed by year

Figure 6.10: Additional slicers

Figure 6.11: Month, quarter, and year slicers

Figure 6.12: The Slicer Settings dialog box

Figure 6.13: Dragging a date field into the Values section

Figure 6.14: The Value Field Settings dialog box

Figure 6.15: The most recent order date for each customer

Figure 6.16: The Grouping dialog box

Figure 6.17: Pivot table showing summary by a number of days

Figure 6.18: The Change PivotTable Data Source dialog box

Figure 6.19: Pivot table displaying the week number

Figure 6.20: Days of the week

Figure 6.21: Pivot table displaying day names

Figure 6.22: Pivot table showing fiscal month

Chapter 7

Figure 7.1: The 2021 company budget data (sheet 2021 from the sample workboo...

Figure 7.2: The 2022 company budget data (sheet 2022 from the sample workboo...

Figure 7.3: The 2023 company budget data (sheet 2023 from the sample workboo...

Figure 7.4: The PivotTable and PivotChart Wizard dialog box

Figure 7.5: Adding the PivotTable and PivotChart Wizard to the Quick Access ...

Figure 7.6: Step 2b of the PivotTable and PivotChart Wizard

Figure 7.7: A pivot table made with the Multiple Consolidation Ranges option...

Figure 7.8: The common fields between the sheets

Figure 7.9: The PivotTable from Table or Range dialog box

Figure 7.10: Creating a pivot table using the data model

Figure 7.11: Pivot table using fields from two tables

Figure 7.12: The Create Relationship dialog box

Figure 7.13: A pivot table using fields from two tables with a relationship...

Figure 7.14: The Manage Relationships window

Figure 7.15: A pivot table with fields from three tables

Figure 7.16: A pivot table with fields from three tables with relationships...

Figure 7.17: A pivot table with two fields in the Rows section

Figure 7.18: The New Set dialog box

Figure 7.19: A pivot table with a set

Figure 7.20: The Set Manager dialog box

Chapter 8

Figure 8.1: The Add‐ins section of the Excel Options dialog box

Figure 8.2: The COM Add‐ins dialog box

Figure 8.3: The Power Pivot ribbon

Figure 8.4: The Power Pivot for Excel window

Figure 8.5: The Table Import Wizard dialog box

Figure 8.6: Connecting to a database

Figure 8.7: The diagram view, before tables are linked

Figure 8.8: The diagram view, after tables are linked

Figure 8.9: The Edit Relationship window

Figure 8.10: Available templates when creating a pivot table from the Power ...

Figure 8.11: A pivot table made from the Power Pivot for Excel window

Figure 8.12: A table with a calculated column in the Power Pivot for Excel w...

Figure 8.13: The Measure dialog box

Figure 8.14: The Insert Function dialog box

Figure 8.15: A pivot table with a measure

Figure 8.16: The Manage Measures dialog box

Figure 8.17: The Key Performance Indicator (KPI) dialog box

Figure 8.18: A pivot table with a KPI, before formatting

Figure 8.19: An expanded KPI

Figure 8.20: The pivot table with a KPI, after formatting

Figure 8.21: The Manage KPIs dialog box

Chapter 9

Figure 9.1: A sample dashboard

Figure 9.2: Pivot tables and charts

Figure 9.3: Pivot tables, charts, slicers, and timeline

Figure 9.4: A dashboard with totals

Figure 9.5: Excel Options, Customize Ribbon settings

Figure 9.6: The ribbon of the Developer tab

Figure 9.7: Form controls

Figure 9.8: The Assign Macro dialog box

Figure 9.9: The Record Macro dialog box

Figure 9.10: The Microsoft Excel dialog box

Figure 9.11: The Save As dialog box

Figure 9.12: The lists for the combo box

Figure 9.13: The Format Object dialog box

Figure 9.14: The Microsoft Visual Basic for Applications window

Figure 9.15: The Microsoft Visual Basic for Applications window with the com...

Figure 9.16: The Microsoft Visual Basic for Applications window with the VBA...

Figure 9.17: The Protect Structure and Windows dialog box

Figure 9.18: The Protection tab of the Format Cells dialog box

Figure 9.19: The View tab

Figure 9.20: The Protect Sheet dialog box

Guide

Cover

Table of Contents

Title Page

Copyright

Dedication

Acknowledgments

About the Author

About the Technical Editor

Foreword

Introduction

Begin Reading

Index

End User License Agreement

Pages

iii

xxi

xxii

xxiii

xxiv

xxv

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

219

220

221

222

223

224

225

226

227

228

229

230

231

232

233

234

235

236

237

238

239

240

241

242

243

244

245

246

247

248

249

250

251

252

253

254

255

256

257

258

259

260

261

262

263

264

265

266

267

268

269

270

271

272

273

274

275

iv

v

vii

ix

xi

276

Getting Great Results with Excel® Pivot Tables, PowerQuery, and PowerPivot®

Thomas Fragale

Microsoft Certified Trainer

Foreword

In 2002, Steven Spielberg released the film Minority Report. I watched mesmerized as the lead detective swiped and shifted data midair from one holographic screen to another. He used this technology to find the would-be killer before he committed the crime.

Obviously, this was science fiction, but the film's premise left me wondering: can the speed of technology allow businesses to prevent potential disasters before they happen?

I began my career providing technical training. As I worked with more and more organizations across the United States and globally, I realized that software skills training was helpful and important but didn't really solve what I saw as the larger problem: employees needed a way to make data-based decisions that would drive positive outcomes. The data needed to be current and easy to access.

As a solution, I created the “efficiency audit,” which helped teams look at information in fresh ways. We identified what data was important now and what might be important in the future. We located and eliminated inefficient duplications of effort. Data warehouses were built. Using customized dashboards, users accessed fact-based analysis from this shared treasure trove of up-to-date data, tailored to their needs. Decision-making became easier, faster, and more accurate.

This is when I first met Tom Fragale. Introduced by a mutual friend, we quickly connected over our shared passion for all things tech and teaching. It was an easy decision to collaborate with Tom on training and database design projects. That business relationship morphed into a strong friendship that I'm proud to say continues some 20 years later.

When Tom reached out to share the news that he had been asked to write this book, I was thrilled.

New data becomes available constantly. For data analytics to be successful, employees need to be able to access data and data models on their own—easily and quickly.

Enter Microsoft pivot tables. Microsoft Excel is accessible, familiar, and relatively easy to use. Pivot tables are the hidden powerhouse of the program. If you know how, you can quickly analyze large and small bodies of data. This data can reside inside or outside of Excel. Pivot tables are flexible and interactive. You can flip, swipe, and move data as easily as it was done in that scene in Minority Report (minus the holographic screens!).

In Getting Great Results with Excel Pivot Tables, PowerQuery, and PowerPivot, Tom shares his specialized knowledge of data modeling and design. He understands the powerful applications for this tool. Additionally, he is a highly experienced and skilled instructor. Tom thoughtfully includes the example files he uses in the book. Download them to practice hands-on as you go through each chapter. Getting Great Results with Excel Pivot Tables, PowerQuery, and PowerPivot methodically walks you through each step of the “how-to” while explaining the “why” and sharing best practices. Before you know it, you will be able quickly access the stories your data wants to tell you.

I'm glad his book has found its way into your hands. It's a resource you'll refer to over and over.

—Media Baldwin

CEO of Diversified Seminars

Introduction

There used to be a time when a company's IT team would manage all of the data and create the necessary charts and reports from that data, based on the user's needs. But now, for most companies, those days are long gone!

In today's world, we are all overwhelmed by huge amounts of data from many different sources. On top of that, our co-workers, bosses, vendors, and customers are asking us for all kinds of reports and charts from that data. They also want it to be flexible so they can change the parameters and have it produce instant results. Oh, and by the way, they needed it all yesterday! Sort it this way, filter it that way, show it month by month, etc. It is a daunting and time-consuming task, to say the least! Plus, you have all the other responsibilities for your job as well.

Microsoft Excel has great tools to help you manage your data and to report on that data the way you want to see it. Using pivot tables, Power Query, and PowerPivot, you can pull in data from many sources. You can also clean up and prepare your data, summarize your data in many ways, analyze your data by adding formulas, create visually appealing and interactive dashboards, and get real results from your data. You can do all of this without having to be a programmer. This book will show how to use these tools in an easy-to-follow step-by-step format, backed up with screenshots and real-life examples.

What Does This Book Cover?

An Excel pivot table is an amazing tool that helps you summarize your data just about any way you want. I tried to make this book as thorough as possible so you can really see the depth of the pivot tables, and you can use them to help summarize your own data.

This book has been made to be as easy to follow as possible. Each chapter has specific topics, step-by-step instructions, and screenshots to guide you along.

All the sample files that are mentioned in this book and featured in the screenshots are accessible online so that you can learn the skills using the downloadable sample files and then apply the skills to your own data.

Each chapter has its own sample files and is independent of the other chapters so you can go right to the chapter you want and start to benefit from its information right away.

Chapter 1

: Preparing the Data for an Excel Pivot Table

 This chapter shows you how to structure your data so that you can create a pivot table from your data. You will see how to import data from other data sources such as text/CSV files, Access databases, websites, ODBC databases, and others. You will also learn how to clean up your data using Power Query.

Chapter 2

: Summarizing and Presenting Data with a Pivot Table 

This chapter will show you how to get your pivot table started so you can start summarizing your data.

Chapter 3

: Using Calculations in Pivot Tables

 This chapter teaches you how to use the many built-in calculations of a pivot table and how to create your own calculations in a pivot table.

Chapter 4

: Sorting and Filtering the Pivot Table

 This chapter demonstrates the many ways that a pivot table can be sorted and filtered, including slicers and timelines, so you can get the exact results you are looking for.

Chapter 5

: Making the Pivot Table More Visual with Charts

 This chapter will show you how to create and manage charts that come from a pivot table.

Chapter 6

: Summarizing Data by Date and Time

 This chapter teaches you how to summarize your data by year, quarter, month, day, hour, minute, second, or any combination. It will also show you how to use Excel's built-in calculations to summarize the pivot table by other date and time ranges.

Chapter 7

: Creating a Pivot Table from Multiple Spreadsheets 

This chapter displays how to make a consolidated pivot table from multiple sheets that are structured in a similar way, and it will also show you how to make a pivot table from sheets that can be linked together on common fields, creating a data model.

Chapter 8

: Improving the Pivot Table with Power Pivot

 This chapter will demonstrate how to take the data model even further with Power Pivot. It will also show you how to create Data Analysis Expression (DAX) formulas when the pivot table is created from the data model.

Chapter 9

: Pulling It All Together: Creating a Dashboard from Pivot Tables

 This chapter will show how you can use charts, slicers, timelines, calculations, and form controls to create user-friendly, dynamic, interactive dashboards from pivot tables.

Who Should Read This Book

This book is great for anybody who has tons of raw data and needs to summarize and report on the data in many ways and is looking for quick and easy ways to do that. This can include managers, salespeople, administrative staff, office workers, and people who work in the following professions: accounting, finance, marketing, billing, teaching, purchasing, government, inventory, medical, scientific, engineering, advertising, education, banking, military, and really any other profession that uses huge amounts of data. Anybody who would benefit from the reports made from the pivot tables would also benefit from reading this book.

Reader Support for This Book

If you need help, refer to the following sections.

Companion Download Files

The example files used in this book can be found at and downloaded from www.wiley.com/go/GGRXL_PivotTables. Each chapter of the book indicates which workbook to use from the sample files.

How to Contact the Author

We appreciate your input and questions about this book or about possible speaking/training engagements. Email me at [email protected] or find me on LinkedIn at www.linkedin.com/in/tomfragale.

CHAPTER 1Preparing the Data for an Excel Pivot Table

If you are like most office workers, you probably have tons of data coming from all directions that you somehow must summarize and make it all make sense. Maybe it is endless lists of sales, bills, invoices, customers, vendors, employees, benefits, payments, orders, products, inventory, collections, books, charges, or countless other possible lists. Additionally, it seems that the lists of information come from all different sources, and it never ends.

When I started in IT a long time ago, it was pretty much up to the IT team to gather all the data and then make reports and charts from the data. At the time, programming languages such as COBOL, Fortran, Basic, Pascal, ColdFusion, VBA, dBase, FoxPro, and others were used to write long, complex programs that would open the data file, go through the file record by record, clean up the data if necessary, accumulate totals, and then finally generate the reports or charts that were asked for. It was a time-consuming process that was prone to errors and many other challenges.

For most companies, those days are long gone. Now it is up to you, the individual, or the people you work with to gather all the data from different sources and make some kind of sense out of it. Somehow you are expected to know how to sort, filter, summarize, chart, and report on the data for the next staff meeting to show something meaningful from the data. Oh, and, by the way, the meeting is this afternoon! No pressure. It's only your job, your career, your life!

What are you going to do?

Relax, it's going to be OK. This whole book is designed to help you make sense of all that data, and you don't even have to be a programmer to get great results from your data. This book will show how to create and manage a pivot table, which is a powerful reporting tool built into Excel. A pivot table can take in huge amounts of data, and it allows you to summarize your data just about any way you want, all without you having to be on the IT team. By using the steps in this book, you really should be able to get great results from your data by using pivot tables.

NOTE  This chapter will present a number of examples. To get the most from these examples, you can download sample files from www.wiley.com/go/GGRXL_PivotTables . The examples throughout the chapter will note which book file is being used.

What Is Data?

For the purposes of this book, data can be defined as a list of rows of information or transactions that have a common theme. The data itself could represent any number of lists of information. It could be names of customers, employees, teachers, students, grades, bills, invoices, inventory, sales, credits, debits, investments, addresses, cities, countries, and so on and so on. The data could be any list of items that people keep track of. It could be a short list with just a few rows, or it could be a long list with hundreds of thousands of rows. Microsoft Excel spreadsheets can hold more than 1,048,000 rows down and more than 16,000 columns across. Larger databases, going into the hundreds of millions of rows, can be managed in Power Pivot, which is discussed in Chapter 8, “Improving Your Pivot Table with Power Pivot.” Each row in the list within the Excel worksheet list is a separate transaction or record. Each column is a different field of information.

What the Data Should Look Like

For the data to be used in a pivot table, the data has to be set up in a certain way so it will be optimized for the pivot table. If necessary, Power Query, which is discussed later in this chapter, along with other traditional Excel techniques, can be used to clean the data. Figure 1.1 shows an example of “good” data, and Figure 1.2 shows an example of “bad” data. The following is a list of ways the data should be structured so it is ready to be made into a pivot table:

It is important that the column headings or field names appear on the top row of the data. The row that contains the field names is also called the

header row

.

There should be only one row for the column headings. Excel will use the top row of the list as the headers.

If the data you want to use does not have a header row, then you should take the time to manually insert a row at the top of the data and give each column a meaningful name.

The header row does not necessarily have to be row 1 of the Excel spreadsheet, but it does have to be the first row of the list of data. Similarly, the first column of information does not have to be in column A of the Excel spreadsheet; it just has to be the first column of the list.

If there is anything above the header row, then there has to be at least one completely blank row before the header row, and if there is anything before the first column of data in the list, there has to be at least one blank column before the first column of data in the list.

After the header row, there should be no entirely blank rows until the bottom of the data, and there should be no entirely blank columns until the right side of the list. The reason for this is when the pivot table is first made, Excel will automatically select a range of data, and it will stop at the next completely blank row it finds above the current cell and also below the current cell, and the next completely blank column it finds to the left of the current cell and to the right of the current cell.

There can be blank cells here and there, but an entire row or column should not be blank within the list of data.

Each separate transaction should take up only one row of the Excel spreadsheet.

Each column should have just one piece of information, and it should be consistent all the way up and down the column. A column called Country, for example, should contain only names of countries.

The column should have the same data type, for example being all text, numbers, or dates within the same column.

The list of data should not contain any merged cells. A merged cell is when more than one cell is combined into one big cell. These merged cells really get in the way of a successful pivot table.

Figure 1.1: An example of good data

Figure 1.2: An example of bad data

NOTE Checking for Merged Cells

The following is a quick way to see if your list of data has any merged cells:

Click one of the cells within the data range.

Use the Ctrl+A keyboard shortcut to select the entire range of data.

Click the Home tab.

Click the Find icon, as shown in

Figure 1.3

.

Figure 1.3: Selecting the Find icon

In the Find And Replace dialog box, click Options. This will show a list of options, as shown in

Figure 1.4

.

Figure 1.4: Clicking Options

Click Format. This will display the Find Format dialog box, as shown in

Figure 1.5

.

Figure 1.5: The Alignment tab in the Find Format dialog box

Click the Alignment tab.

Select the Merge Cells option so that it is checked.

Click OK.

Click Find All. This will highlight any merged cells within the list.

Close the Find And Replace dialog box.

If any merged cells were found within the list of the data, then highlight the list of data, right-click the range, and then use the Format Cells option to unmerge the cells within the list.

Types of Data That Can Be Used in Excel Pivot Tables

The data for a pivot table can potentially come from many sources. The data can already be in Excel, or it can come from external sources. Data external to Excel would eventually have to be imported into an Excel worksheet. If there are more than 1,048,000 rows in the table, however, it would have to be imported into Power Pivot instead, which will be discussed in Chapter 8, “Improving Your Pivot Table with Power Pivot”. The following are the most popular data sources that can be imported into Excel and then made into a pivot table:

An Excel workbook

A text or CSV file

An XML dataset

A JSON dataset

A PDF file (not preferable, but it could possibly work if it is the only thing available)

A SQL Server database

An Access database

An Oracle database

An IBM DB2 database

A MySQL database

A PostgreSQL database

A Sybase database

A Teradata database

A SAP HANA database

Data from Azure

Data from PowerBI

Data from Dataverse

Data from Dataflows

A SharePoint list

Microsoft Exchange

Microsoft Dynamics 365

Salesforce

A website that has a table of data

An OData feed

A Hadoop file

An ODBC data source

An OLEDB data source

There are other possible data sources, but these are the main ones that are being used. Additionally, most of the widely used databases are ODBC compliant, OLE compliant, or both, so that almost every popular database can be a source of data that can be imported into Excel and then used as the main data for a pivot table. If the database you are using is not listed here and it is not ODBC compliant or OLE compliant, there's a pretty good chance that the database program you are using has a way to export the data either into a text/CSV file or even an Excel spreadsheet, so there should still be a way to get that data into Excel so that you can use it for your pivot tables. By using the newly improved Power Query, you can import data directly from all of the previous data sources into Excel. Power Query is an excellent tool that is built into Excel that allows you to import data from other sources, clean the data, and also enhance the data in many ways, some of which will be demonstrated in this chapter.

Using Excel Data

It is likely that you already have data in your existing Excel workbooks that is ready to be made into a pivot table. That's a great start! The data can be just plain, unformatted data, also called raw data, or it can be a formatted table, also called a table in Excel. Figure 1.6 shows an example of plain, unformatted data. Figure 1.7 shows an example of a formatted table in Excel.

Either one will be a great source for your pivot table, although there are advantages to using a formatted table as the source for your pivot table such as the following:

A formatted table automatically expands to include the new columns or rows when they are added. These new columns and rows would then carry over to the pivot table as well. On the other hand, if more rows or columns were added to a list of data that is not a formatted table, the data range that the pivot table uses may have to be manually expanded.

Figure 1.6: Nonformatted data

Figure 1.7: Formatted table

Formatted tables can be used to make a pivot table from more than one sheet by joining the tables on common fields, a technique that is covered in

Chapter 7

, “Creating a Pivot Table from Multiple Spreadsheets,” and in

Chapter 8

, “Improving Your Pivot Table with Power Pivot”.

A formatted table is already structured to be used in a pivot table in almost all cases. A list of data that is not a formatted table may have to be cleaned up before being used in a pivot table.

A formatted table will automatically eliminate any merged cells that were previously within the data and will not allow any new merged cells to be placed within the table.

Sort and filter are facilitated in formatted tables by having a drop-down list on the top of each column.

The headers on the formatted table will stay on the top of each column as you scroll down. This does not happen in unformatted lists unless you use Excel's Freeze Panes command, which can be found on the View tab within Excel.

A formatted table is ready to be used with Power Pivot.

The formatted table will assist when you create formulas.

You can create a formatted table from unformatted data by doing the following:

Click a cell within your data.

Click the Home tab.

Click the Format as Table icon on the ribbon. This will display a gallery of table styles, as shown in

Figure 1.8

.

Figure 1.8: Formatting data as a table

Choose one of the styles. A Create Table dialog box will be displayed.

Make sure the My table has headers check box is selected.

Click OK.

Your list is now a formatted table.

Importing Data from External Data Sources into Excel