Data Visualization with Excel Dashboards and Reports E-Book

Dick Kusleika

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

Seitenzahl: 373

Veröffentlichungsjahr: 2021

Beschreibung

Large corporations like IBM and Oracle are using Excel dashboards and reports as a Business Intelligence tool, and many other smaller businesses are looking to these tools in order to cut costs for budgetary reasons. An effective analyst not only has to have the technical skills to use Excel in a productive manner but must be able to synthesize data into a story, and then present that story in the most impactful way. Microsoft shows its recognition of this with Excel. In Excel, there is a major focus on business intelligence and visualization. Data Visualization with Excel Dashboards and Reports fills the gap between handling data and synthesizing data into meaningful reports. This title will show readers how to think about their data in ways other than columns and rows. Most Excel books do a nice job discussing the individual functions and tools that can be used to create an "Excel Report". Titles on Excel charts, Excel pivot tables, and other books that focus on "Tips and Tricks" are useful in their own right; however they don't hit the mark for most data analysts. The primary reason these titles miss the mark is they are too focused on the mechanical aspects of building a chart, creating a pivot table, or other functionality. They don't offer these topics in the broader picture by showing how to present and report data in the most effective way. What are the most meaningful ways to show trending? How do you show relationships in data? When is showing variances more valuable than showing actual data values? How do you deal with outliers? How do you bucket data in the most meaningful way? How do you show impossible amounts of data without inundating your audience? In Data Visualization with Excel Reports and Dashboards, readers will get answers to all of these questions. Part technical manual, part analytical guidebook; this title will help Excel users go from reporting data with simple tables full of dull numbers, to creating hi-impact reports and dashboards that will wow management both visually and substantively. This book offers a comprehensive review of a wide array of technical and analytical concepts that will help users create meaningful reports and dashboards. After reading this book, the reader will be able to: * Analyze large amounts of data and report their data in a meaningful way * Get better visibility into data from different perspectives * Quickly slice data into various views on the fly * Automate redundant reporting and analyses * Create impressive dashboards and What-If analyses * Understand the fundamentals of effective visualization * Visualize performance comparisons * Visualize changes and trends over time

Leseprobe

Table of Contents

Cover

Title Page

Introduction

What Does This Book Cover?

Companion Download Files

How to Contact the Publisher

Part I: Display Data on a Dashboard

CHAPTER 1: Dashboard Basics

Determining When to Use a Dashboard

Establishing User Requirements

Assembling the Data

Building the Dashboard

Formatting the Dashboard

CHAPTER 2: Dashboard Case Studies

Monitoring Progress

Displaying Key Performance Indicators

Reporting Financial Information

CHAPTER 3: Organizing Data for Dashboards

Separating Data Layers

Working with External Data

Transforming Data in Power Query

Part II: Visualization Primer

CHAPTER 4: The Fundamentals of Effective Visualization

Creating an Effective Visualization

Driving Meaning with Color

Focusing Attention on Text

Showing Insights with Charts

CHAPTER 5: Non-chart Visualizations

Understanding Custom Number Formats

Using Icons

Creating Sparklines

CHAPTER 6: Using Shapes to Create Infographics

Working with Shapes

Framing Data with Shapes

Creating Custom Infographics

Adding Other Illustrations

Part III: Tell a Story with Visualization

CHAPTER 7: Visualizing Performance Comparisons

Single Measurements

Column Charts

Bullet Charts

Clustered Column Charts

Funnel Charts

XY Charts

Bubble Charts

Dot Plot Charts

CHAPTER 8: Visualizing Parts of a Whole

Pie Charts

Doughnut Charts

Waffle Charts

Sunburst Charts

Histograms

Treemap Charts

Waterfall Charts

CHAPTER 9: Visualizing Changes Over Time

Line Charts

Column Charts with Variances

Combination Charts

Line Charts with Differences

Side-by-Side Box Plots

Animated Charts

Chart Automation

Index

Copyright

Dedication

About the Author

About the Technical Editor

Acknowledgments

End User License Agreement

List of Tables

Chapter 1

Table 1.1: Custom formats for large numbers

Chapter 2

Table 2.1: New recruiting cost columns

Table 2.2: Financial metric formulas

Chapter 5

Table 5.1: Format codes for displaying parts of a date

List of Illustrations

Chapter 1

Figure 1.1: Data shown raw, aggregated, and analyzed and presented

Figure 1.2: Raw data for a PivotTable

Figure 1.3: A PivotTable

Figure 1.4: The PivotTable Fields task pane

Figure 1.5: Nested fields in the Columns area

Figure 1.6: The PivotTable Options dialog box

Figure 1.7: The Field Settings dialog box

Figure 1.8: A PivotTable with no grand totals or subtotals

Figure 1.9: The VLOOKUP function

Figure 1.10: The XLOOKUP function

Figure 1.11: The INDEX and MATCH functions

Figure 1.12: The SUMPRODUCT function

Figure 1.13: Filter values with SUMPRODUCT

Figure 1.14: An array formula to find the largest value

Figure 1.15: A column chart based on a table

Figure 1.16: The Create Table dialog

Figure 1.17: The table expands for new data.

Figure 1.18: The chart adjusts for new data.

Figure 1.19: Excel's formula entry drop-down

Figure 1.20: Options for table formula completion

Figure 1.21: Data that is lumped together in a cell

Figure 1.22: The Convert Text to Columns Wizard

Figure 1.23: Excel splits the data on the delimiter.

Figure 1.24: Data split into separate columns

Figure 1.25: Data with duplicate records

Figure 1.26: The Remove Duplicates dialog

Figure 1.27: Excel displays the number of deleted values.

Figure 1.28: A dashboard with different sized elements

Figure 1.29: A dashboard of the same chart type

Figure 1.30: A trendline that adds little information

Figure 1.31: A trendline helps when data varies.

Figure 1.32: The Format Trendline task pane

Figure 1.33: Too much formatting detracts from the chart's message.

Figure 1.34: Clean and simple formatting keeps the message front and center....

Figure 1.35: Excel's color pallet

Figure 1.36: Excel's theme gallery

Figure 1.37: Data labels can be used to highlight unusual variations.

Figure 1.38: Formatted large numbers in the value axis

Figure 1.39: The Number section of the Format Axis task pane

Chapter 2

Figure 2.1: A rough layout of the dashboard

Figure 2.2: Collecting data for hours and tasks

Figure 2.3: Collecting data for code commits

Figure 2.4: Collecting data for overall progress

Figure 2.5: The Select Data Source dialog

Figure 2.6: An intermediate hours bar chart

Figure 2.7: Move the Actual series to the secondary axis.

Figure 2.8: Overlapping bar charts

Figure 2.9: Bullet charts with elements removed

Figure 2.10: Two bullet charts positioned on the dashboard

Figure 2.11: Create a PivotChart on the Dashboard worksheet.

Figure 2.12: The initial PivotChart for commits by sprint

Figure 2.13: Filter the PivotTable to hide zeros.

Figure 2.14: The completed Code Commits by Sprint line chart

Figure 2.15: Create the PivotTable data for the Commits by Developer chart....

Figure 2.16: Filter the PivotChart for the current sprint.

Figure 2.17: The first four visual elements

Figure 2.18: The initial phases bar chart and table

Figure 2.19: Reverse the order of the categories.

Figure 2.20: A stacked bar chart to simulate a Gantt chart

Figure 2.21: Add the green formatting rule.

Figure 2.22: Conditionally formatting a cell for green or red

Figure 2.23: The final project progress dashboard

Figure 2.24: A rough layout of the HR dashboard

Figure 2.25: Recruiting cost Data provided by HR

Figure 2.26: An employee list with separation reasons

Figure 2.27: The new hire log

Figure 2.28: The initial recruiting cost column chart and data

Figure 2.29: The final recruiting cost column chart

Figure 2.30: Choose the clustered column chart with year in the category axi...

Figure 2.31: The chart with only two series

Figure 2.32: Change the Cost per Applicant series to a line chart.

Figure 2.33: The two recruiting cost charts

Figure 2.34: The PivotTable Fields task pane for the Gender chart

Figure 2.35: Doughnut charts for the diversity hire KPIs

Figure 2.36: Building the PivotChart for the 90-Day Quit Rate chart

Figure 2.37: Change the aggregate to count the EmployeeID field.

Figure 2.38: The final 90-Day Quit Rate chart

Figure 2.39: The voluntary separation chart filtered

Figure 2.40: Sort the PivotChart by Count of EmployeeID.

Figure 2.41: The final Voluntary Separations bar chart

Figure 2.42: The final Average Days to Fill Vacancies chart

Figure 2.43: Rename a chart with the Name box.

Figure 2.44: Insert a module into the project.

Figure 2.45: The final HR dashboard

Figure 2.46: A rough layout of the financial dashboard

Figure 2.47: Balance sheet and income statement data

Figure 2.48: Reversing signs via Paste Special ➪ Multiply

Figure 2.49: The initial waterfall chart

Figure 2.50: Set the totals data points.

Figure 2.51: The Current Year Income Statement waterfall chart

Figure 2.52: Stage the data for the gauge charts.

Figure 2.53: The initial current ratio doughnut chart

Figure 2.54: Resize the plot area to fill More of the chart area.

Figure 2.55: Set the fill colors for each data point.

Figure 2.56: The Current Ratio gauge chart

Figure 2.57: Four balance sheet ratio gauge charts

Figure 2.58: Gross and net margin calculations

Figure 2.59: The six-chart gauge panel

Figure 2.60: The five-year revenue chart

Figure 2.61: Three five-year income statement charts

Figure 2.62: Pasting a new series to an existing chart

Figure 2.63: Point the series name to a cell.

Figure 2.64: Six trend charts for financial data

Figure 2.65: The financial data dashboard

Chapter 3

Figure 3.1: The three data layers

Figure 3.2: A well formatted data file

Figure 3.3: A poorly formatted data file

Figure 3.4: Data formatted as delimited and fixed width

Figure 3.5: The Get Data drop-down on the Ribbon

Figure 3.6: The import delimiter choices

Figure 3.7: The text file in a table

Figure 3.8: The import screen for comma delimited

Figure 3.9: The legacy Text Import Wizard

Figure 3.10: Include legacy import wizards on the Ribbon in Excel Options.

Figure 3.11: The Import Data dialog

Figure 3.12: Sales transaction data in an Excel file

Figure 3.13: Import an Excel file from the Ribbon.

Figure 3.14: The Power Query Navigator window

Figure 3.15: Excel data imported and the Queries & Connections task pane

Figure 3.16: Excel's warning when adding an existing data set to the Data Mo...

Figure 3.17: Enable the Power Pivot add-in in Excel.

Figure 3.18: The Power Pivot interface

Figure 3.19: Creating a relationship in Power Pivot

Figure 3.20: Power Pivot's Diagram View shows relationships.

Figure 3.21: Create a PivotTable based on the Data Model.

Figure 3.22: A PivotTable based on the Data Model shows all data sets.

Figure 3.23: A PivotTable summing sales by salesperson's last name

Figure 3.24: The Power Query Navigator dialog for importing data from an Acc...

Figure 3.25: The Orders table from the Northwind database

Figure 3.26: Select multiple data sets in the Navigator dialog.

Figure 3.27: Existing Access relationships are already created in the Data M...

Figure 3.28: Loading a single table from the Queries & Connections task pane...

Figure 3.29: The Import Data dialog

Figure 3.30: Create a PivotTable from the Data Model.

Figure 3.31: A PivotTable based on the Data Model

Figure 3.32: Power Query selects all related tables.

Figure 3.33: Selecting related tables creates all the relationships in the D...

Figure 3.34: Excel's dialog for entering the SQL Server name

Figure 3.35: The Navigator dialog for SQL Server

Figure 3.36: Power Pivot's Diagram View for the SalesOrderHeader and SalesOr...

Figure 3.37: Click the Transform Data button on the Navigator dialog.

Figure 3.38: The Power Query Editor

Figure 3.39: The Advanced Editor

Figure 3.40: Remove a column from a query.

Figure 3.41: Remove rows from a query.

Figure 3.42: Filter orders with January order dates.

Figure 3.43: Edit a step by clicking the gear icon.

Figure 3.44: Changing the shipped date to a date data type

Figure 3.45: Changing to an improper data type results in errors.

Figure 3.46: Editing a step in the formula bar

Figure 3.47: Replace values in a column.

Figure 3.48: Multiplying the Shipping Fee column by a fixed value

Figure 3.49: Round Up rounds to the nearest higher whole number.

Figure 3.50: Statistics functions return a single value.

Figure 3.51: The Split Column By Delimiter dialog

Figure 3.52: Splitting a value by digit to non-digit

Figure 3.53: Adding columns from examples

Figure 3.54: Power Query uses an example to determine how to split the remai...

Figure 3.55: The new column is added to the end of the data set.

Chapter 4

Figure 4.1: Scale a worksheet to fit on a single page.

Figure 4.2: The Scale tool displays the actual scaling percentage.

Figure 4.3: An out-of-balance dashboard due to one dense element

Figure 4.4: Eliminating clutter makes the chart easier to understand.

Figure 4.5: A chart with too much data is hard to read.

Figure 4.6: A chart with three layers of complexity

Figure 4.7: Five-year revenue in three chart types

Figure 4.8: A typical stock chart

Figure 4.9: Darker colors represent higher values.

Figure 4.10: Use color to highlight a data point.

Figure 4.11: A bar chart of office supply subcategories

Figure 4.12: Grouping a bar chart with color

Figure 4.13: A small number of legend entries are easy to remember.

Figure 4.14: Merging the legend and chart title

Figure 4.15: A legend and data labels serve the same function.

Figure 4.16: Showing first and last data labels provides context.

Figure 4.17: An anomalous data point labeled for clarity

Figure 4.18: A chart to mine data to find anomalies

Figure 4.19: A pie chart with too many data points

Figure 4.20: A bar chart can show more data points.

Figure 4.21: A 100% stacked bar chart focuses on the components.

Figure 4.22: A stacked bar shows the components and the total.

Figure 4.23: A scatter plot shows the relationship between two variables.

Chapter 5

Figure 5.1: A one-section number format applies to all numeric conditions an...

Figure 5.2: The second section governs negative numbers.

Figure 5.3: The third section controls zero values.

Figure 5.4: The fourth section affects text entries.

Figure 5.5: The # character shows only digits that exist.

Figure 5.6: Characters in a number format can cause numbers to not line up....

Figure 5.7: The underscore character helps align values.

Figure 5.8: Use an asterisk to align a currency symbol.

Figure 5.9: Select the Custom category to see how a built-in category is con...

Figure 5.10: The Color Scales gallery on the Ribbon

Figure 5.11: A range with the Green - Yellow - Red Color Scale applied

Figure 5.12: The Conditional Formatting Rules Manager

Figure 5.13: The Edit Formatting Rule dialog

Figure 5.14: A 2-Color Scale formatting rule

Figure 5.15: Two ranges with the Percent and Percentile format styles applie...

Figure 5.16: Use the same color for Minimum and Maximum to highlight extreme...

Figure 5.17: Data Bars applied to a range

Figure 5.18: The New Formatting Rule dialog

Figure 5.19: The Negative Value and Axis Settings dialog

Figure 5.20: Negative Data Bars extending to the left and differently colore...

Figure 5.21: A directional Icon Set applied to a range

Figure 5.22: The New Formatting Rule dialog for Icon Sets

Figure 5.23: The Icon drop-down allows you to create custom Icon Sets.

Figure 5.24: Three types of Sparklines

Figure 5.25: The Create Sparklines dialog

Figure 5.26: A newly inserted Sparkline group is selected

Figure 5.27: The Edit Sparkline Data dialog allows you to change the source ...

Figure 5.28: Use the Clear tool to delete individual Sparklines or the whole...

Figure 5.29: Change the group's source data in the Edit Sparklines dialog.

Figure 5.30: The Hidden and Empty Cell Settings dialog

Figure 5.31: Change a Sparkline's color using the Style Gallery.

Figure 5.32: Change a Sparkline's thickness with the Weight tool.

Figure 5.33: Comparing the General and Date Axis Types

Figure 5.34: Sparklines with positive and negative values will show an axis...

Figure 5.35: Comparing the scaling options for Sparklines

Chapter 6

Figure 6.1: Insert a shape from the Shapes Gallery.

Figure 6.2: Insert a shape by clicking and dragging.

Figure 6.3: Selecting a shape displays its sizing handles.

Figure 6.4: Hold down the Ctrl key when resizing to maintain the center posi...

Figure 6.5: Excel names each shape inserted.

Figure 6.6: Change a shape's style from the Shape Styles Theme Style gallery...

Figure 6.7: Change the gradient with a preset gradient from the gallery.

Figure 6.8: Use the alignment tools to help aligning shapes.

Figure 6.9: A simple gauge chart from a financial dashboard

Figure 6.10: Position a rectangle across the top of the chart.

Figure 6.11: Position a triangle below the rectangle.

Figure 6.12: Position another triangle on the other side of the chart.

Figure 6.13: Use a formula to show text in a shape.

Figure 6.14: Combined shapes to frame a chart title

Figure 6.15: Replacing the chart title and legend with shapes

Figure 6.16: An exploded view of the binder tab chart

Figure 6.17: An invisible chart title keeps the other elements from filling ...

Figure 6.18: Make the text box larger than necessary to ensure the text alig...

Figure 6.19: You can rename shapes to better manage them.

Figure 6.20: Use the Selection task pane to make selecting shapes easier.

Figure 6.21: Hold down the Ctrl key to select multiple shapes in the Selecti...

Figure 6.22: Group shapes to work with them as a single unit.

Figure 6.23: House sales for a real estate office

Figure 6.24: Format the shapes to complement the chart.

Figure 6.25: Group shapes to make them easier to move.

Figure 6.26: Some shapes have additional sizing points.

Figure 6.27: Use additional sizing points to create custom shapes.

Figure 6.28: Use edit points to further customize a shape.

Figure 6.29: Right-click on a segment to modify it.

Figure 6.30: Right-click on an edit point to modify it.

Figure 6.31: The dialog for inserting icons and other images

Figure 6.32: Limit the icons using the search box

Figure 6.33: Search in the Cutout People section.

Figure 6.34: Add a text box to the sign.

Figure 6.35: Resize and rotate the text box to better fit the sign.

Figure 6.36: Wrap the text within the text box.

Figure 6.37: Make the text box transparent.

Chapter 7

Figure 7.1: A single percentage in a dashboard

Figure 7.2: A single percentage showing change

Figure 7.3: Total sales dollars shown as raw and rounded

Figure 7.4: Bar charts are column charts shown horizontally.

Figure 7.5: Start the values axis at zero.

Figure 7.6: Grouping categories reduces noise in your chart.

Figure 7.7: Table headings for Q1 sales

Figure 7.8: Product categories for Q1 sales

Figure 7.9: A completed Q1 sales table

Figure 7.10: A column chart for Q1 sales

Figure 7.11: A typical bullet chart

Figure 7.12: Bullet chart data

Figure 7.13: A clustered bar chart

Figure 7.14: Changing the axis for one series

Figure 7.15: The Gap Width setting on the Format Data Series task pane

Figure 7.16: Bullet chart after gap width and color adjustments

Figure 7.17: Hide an axis using the on-object chart customization buttons.

Figure 7.18: The completed bullet chart

Figure 7.19: A stacked column chart and a clustered column chart

Figure 7.20: Defects per facility per quarter

Figure 7.21: Clustered column chart of production defects

Figure 7.22: Clustered column chart with data labels

Figure 7.23: A funnel chart

Figure 7.24: Sales process data

Figure 7.25: Steve's initial funnel chart

Figure 7.26: Nonselected data points are lighter.

Figure 7.27: Adding color to a funnel chart

Figure 7.28: A funnel chart for Steve and one for all salespeople

Figure 7.29: An XY chart with random values

Figure 7.30: An XY chart (left) compared to a line chart (right)

Figure 7.31: Daily temperature and sales data

Figure 7.32: An XY chart comparing temperature to sales

Figure 7.33: Separated units sold data

Figure 7.34: Smoothie type vs. temperature

Figure 7.35: A bubble chart

Figure 7.36: Home mortgage data

Figure 7.37: Add branch names to the data labels.

Figure 7.38: Mortgages by branch

Figure 7.39: A dot plot chart of bird observations

Figure 7.40: Raw data for units produced

Figure 7.41: Dummy series for the category axis

Figure 7.42: Staging area for X values

Figure 7.43: Staging area for Y values

Figure 7.44: A column chart with the dummy series

Figure 7.45: Adding a new series

Figure 7.46: Changing the series chart type

Figure 7.47: Setting the X and Y series values

Figure 7.48: Setting the X and Y value for the next series

Figure 7.49: All series have been added to the chart.

Figure 7.50: Adding an axis title

Figure 7.51: The formatted chart

Figure 7.52: Four charts aligned vertically

Figure 7.53: A zoomed-out view of the staging areas

Figure 7.54: The final four dot plot charts

Chapter 8

Figure 8.1: A pie chart

Figure 8.2: Grouping smaller data points

Figure 8.3: Placing text inside a doughnut chart

Figure 8.4: Sales dollars by region

Figure 8.5: The Insert Chart dialog box

Figure 8.6: The on-chart Chart Styles button

Figure 8.7: Sales by Region doughnut chart

Figure 8.8: A waffle chart

Figure 8.9: Retail outlets by fuel type

Figure 8.10: Employee benefit use data

Figure 8.11: The 10x10 grid of formulas

Figure 8.12: Set the border color to dark blue.

Figure 8.13: Create the conditional formatting rule.

Figure 8.14: The conditionally formatted range

Figure 8.15: The first two waffle charts

Figure 8.16: Employee benefits waffle charts

Figure 8.17: A sunburst chart

Figure 8.18: A sunburst chart with an incomplete hierarchy

Figure 8.19: Data layout for a sunburst chart

Figure 8.20: Time-motion study results

Figure 8.21: Excel's initial sunburst chart

Figure 8.22: A histogram chart

Figure 8.23: The Format Axis task pane for a histogram

Figure 8.24: A histogram formatted by category

Figure 8.25: A partial list of tickets for a month

Figure 8.26: Histogram of restaurant tickets

Figure 8.27: Custom bins on a histogram

Figure 8.28: A treemap chart

Figure 8.29: A treemap with an incomplete hierarchy

Figure 8.30: Displaying labels with the Banner option

Figure 8.31: Insurance policy data by class and type

Figure 8.32: Insurance policy breakdown treemap

Figure 8.33: A waterfall chart

Figure 8.34: A waterfall chart to show the change in sales

Figure 8.35: A waterfall chart of a mortgage loan

Figure 8.36: Income statement data

Figure 8.37: The waterfall chart with totals set

Figure 8.38: The final income statement waterfall chart

Figure 8.39: The income statement waterfall chart with percentage

Chapter 9

Figure 9.1: A line chart

Figure 9.2: The Format Data Series task pane for a line chart

Figure 9.3: Use transparency to highlight a series.

Figure 9.4: A line chart with arrows

Figure 9.5: Sales data by product category

Figure 9.6: The Insert Line Or Area Chart drop-down

Figure 9.7: The initial line chart

Figure 9.8: The onscreen dialog

Figure 9.9: The completed line chart

Figure 9.10: Date staging areas for projected line chart

Figure 9.11: The chart's Paste Special dialog

Figure 9.12: Newly pasted series on the line chart

Figure 9.13: A line chart with projected sales

Figure 9.14: A column chart with variances

Figure 9.15: House closings by month

Figure 9.16: Data staging area for a column chart with variances

Figure 9.17: A clustered column chart of house sales

Figure 9.18: The Paste Special dialog for charts

Figure 9.19: The Change Chart Type dialog

Figure 9.20: The variance series added to the column chart

Figure 9.21: Error bars on the variance series

Figure 9.22: The Custom Error Bars dialog

Figure 9.23: The formatted Increase series

Figure 9.24: The formatted variance series

Figure 9.25: The final column chart with variances

Figure 9.26: A column and line combination chart

Figure 9.27: Related data at different scales on the same axis

Figure 9.28: Freight data by month

Figure 9.29: Insert a combination chart

Figure 9.30: Deadhead miles added to the data

Figure 9.31: The Paste Special dialog

Figure 9.32: The completed combination chart

Figure 9.33: A Line chart with differences

Figure 9.34: Weekly revenue data

Figure 9.35: The initial line chart

Figure 9.36: The on-chart Chart Elements tool

Figure 9.37: The chart with positive differences

Figure 9.38: The Custom Error Bars dialog for the Prior series

Figure 9.39: The Final Line Chart with Differences

Figure 9.40: A side-by-side box plot

Figure 9.41: The Format Data Series task pane for a box plot

Figure 9.42: Salaries data

Figure 9.43: The initial box plot

Figure 9.44: Renaming a series

Figure 9.45: The final box plot

Figure 9.46: A partial data set for a PivotChart

Figure 9.47: A PivotChart with no data

Figure 9.48: A basic PivotChart

Figure 9.49: Field button options

Figure 9.50: Changing how dates are grouped in a PivotChart

Figure 9.51: Annual revenue data

Figure 9.52: The staging area for five years of data

Figure 9.53: A dynamic chart changes as the data changes.

Figure 9.54: The Format Control dialog for a scroll bar

Figure 9.55: A slider that animates a line chart

Figure 9.56: A new code window in the Visual Basic Editor

Figure 9.57: A line chart with newly pasted series

Figure 9.58: Two formatted projection series

Figure 9.59: A line chart and panel chart comparison

Figure 9.60: A chart with source data

Figure 9.61: Eight panel charts

Figure 9.62: The final panel chart

Guide

Cover

Table of Contents

Begin Reading

Pages

iii

xxi

xxii

xxiii

1

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

87

88

89

90

91

92

93

94

95

96

97

98

99

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

127

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

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

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

203

205

206

207

208

209

210

211

212

213

214

215

216

217

218

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

265

266

267

268

269

270

271

272

273

274

275

276

277

278

279

280

281

282

283

284

285

286

287

288

289

290

291

292

293

294

295

296

297

298

299

300

301

302

303

304

305

306

307

308

309

310

311

312

313

314

315

316

317

318

319

320

321

322

323

324

325

326

iv

v

vii

ix

xi

327

Data Visualization with Excel® Dashboards and Reports

Dick Kusleika

Introduction

Businesses are collecting and storing more data than ever before. It's not just very large businesses either. Small and medium-sized businesses have unprecedented access to data and storage. It's management's job to use that data in decision making, but they simply can't consume all of it in its raw form. Business intelligence (BI) is the process of turning raw data into useful information.

BI has been around in some form for a long time. But recently the increase in quality and accessibility of BI tools have increased its popularity. These tools, coupled with a new widespread availability of data, have fueled an environment where it seems that everyone is creating dashboards.

Excel is becoming the standard for BI tools (if it's not already). Microsoft has invested heavily in the BI tools built in to Excel and some that are outside Excel. They have created the PowerBI family of tools (PowerQuery, PowerPivot, and PowerBI) and have added many more chart types than were available just a few versions ago.

What was once highly specialized software soon became a feature in Excel and available to anyone. In the past, you may have needed an IT project to get the data and the tools to create a dashboard. Now, you likely have it all on your computer already. And at the center of those tools is Excel, a program you probably already have regardless of the size of your business.

Maybe you've been wanting to create a dashboard but never thought you had the skills. Or maybe management has asked you to create one. This book will guide you through Excel's data visualization features from shapes to conditional formatting to charts. I include several realistic case studies so you can see how a business question can turn into a chart or dashboard.

What Does This Book Cover?

The chapters in this book are divided into three parts. In Part I, I discuss dashboards as a whole, including three case studies that result in a full dashboard. Part II focuses on how to get the most of out of the individual elements that make up a dashboard and introduces you to some non-chart data visualization elements. In Part III, I discuss individual charts in detail and provide case studies for many different chart types.

Chapter 1

: Dashboard Basics

   This chapter covers the very basics of dashboarding, including when a dashboard is appropriate and the big-picture steps for building and formatting a dashboard.

Chapter 2

: Dashboard Case Studies

   This chapter includes three case studies. Each case study provides background for the business need, the details around the request for a dashboard, and the construction of the dashboard elements.

Chapter 3

: Organizing Data for Dashboards

   This chapter is all about data. It covers best practices for organizing your data into layers. I also discuss several external data sources and how to get them into Excel.

Chapter 4

: The Fundamentals of Effective Visualization

   This chapter is for users who are new to creating visualizations. In it, I cover what makes an effective visualization, how to use elements like color and text, and how to choose a chart type for the data you want to present.

Chapter 5

: Non-chart Visualizations

   Not all dashboard elements are charts. In this chapter, I discuss visualization features in Excel, and dive deeply into custom number formatting.

Chapter 6

: Using Shapes to Create Infographics

   This chapter covers the basics of shapes in Excel. It also covers how you can use shapes to frame your data in interesting ways.

Chapter 7

: Visualizing Performance Comparisons

   This chapter discusses all the chart types that are appropriate for comparing performance data, including case studies for many of the chart types.

Chapter 8

: Visualizing Parts of a Whole

   This chapter includes sections for chart types that you use when you want to tell a story about how component parts make up a whole. It also includes several case studies with step-by-step instructions.

Chapter 9

: Visualizing Changes over Time

   This chapter reviews the chart types for displaying data that changes over time. In addition to the case studies, it includes a section on how to control charts with the Visual Basic for Applications programming language.

Companion Download Files

As you work through the examples in this book, the workbooks and supporting files you need are all available for download from www.wiley.com/go/datavizwithexcel/.

How to Contact the Publisher

If you believe you've found a mistake in this book, please bring it to our attention. At John Wiley & Sons, we understand how important it is to provide our customers with accurate content, but even with our best efforts an error may occur.

In order to submit your possible errata, please email it to our Customer Service Team at [email protected] with the subject line “Possible Book Errata Submission.”

Part IDisplay Data on a Dashboard

Chapter 1:

Dashboard Basics

Chapter 2:

Dashboard Case Studies

Chapter 3:

Organizing Data for Dashboards

CHAPTER 1Dashboard Basics

In This Chapter

Determining When to Use a Dashboard

Establishing User Requirements

Assembling the Data

Building the Dashboard

Formatting the Dashboard

Dashboards have never been more popular. We have more data available to us all the time and better visualization tools than ever before. At its core, a dashboard is a collection of charts. But it's much more than that. If you put some charts on a page, you would technically have a dashboard, but perhaps not a very good one. Creating a good dashboard takes some preparation, knowledge, and skill. In this chapter, I introduce you to dashboards and the concepts, skills, and best practices you'll need to create them.

Determining When to Use a Dashboard

Dashboards are used to present data. Data can be thought to be at various stages: raw, aggregated, analyzed, and presented. The stage your data is in depends on where it comes from and what you plan to do with it. There are many levels of aggregation and an infinite number of ways to analyze or present data. For example, an invoice is an aggregation of invoice lines and a sales report is an aggregation of invoices. Relative to an invoice, the invoice lines are raw data but relative to the sales report, the invoices are the raw data. Figure 1.1 shows data in its various stages.

Figure 1.1: Data shown raw, aggregated, and analyzed and presented

Raw data is data that hasn't been processed. It can be transactions that come out of an accounting system, sales information from a point of sale, or readings from a measuring device like tank levels or temperatures. If you're starting with raw data, you will have to do some aggregating and possibly some analyzing before it's ready for a dashboard.

NOTE

A workbook containing the charts in the figures for this chapter is named Chapter1Figures.xls x and can be found on this book's companion website at www.wiley.com/go/datavizwithexcel/.

Aggregated data has been grouped and summarized in some way. A report of units produced by month sums the units produced each week or each day. And that may be a sum of units produced by shifts for a day. In many cases, dashboard builders start with aggregated data.

Dashboards tell a story about the underlying data. Analyzing data is determining what stories the data tells and which of those stories is worth telling. Analyzing is more than just drawing conclusions from the data. It's also understanding the nature of the data and what questions the data raises. It's common during data analysis to have to take a step back and aggregate the data in a different way.

Finally, there's the presentation stage, where dashboards live. The dashboard building process can start at any stage. If you get the source data from a data analyst, the story to tell may have already been determined and it's just a matter of presenting that story in an effective way. Conversely, if you start with raw data, you'll need to first aggregate and then analyze the data to make those determinations.

Dashboards are constantly evolving. At one time they were only static visuals telling one story. Now, dashboards include self-service business intelligence (BI) tools that either tell multiple stories or allow the users to find the meaning in the data themselves. With Microsoft's Power BI tool and its integration into Excel with Power Pivot and Power Query, self-service BI is becoming more mainstream and accessible.

CROSS-REFERENCE

Power Pivot, Power Query, and Power BI are introduced in Chapter 3, “Organizing Data for Dashboards.”

What Is a Dashboard?

A dashboard is one or more visual elements that tell a story about related data. A report that aggregates data isn't a dashboard because it's not telling a story. That's typically called a report or table, although these terms are often used to mean the same thing. For our purposes, a dashboard must contain visual elements and not just a list of data.

The story is the most important aspect of a dashboard. It comes from analyzing the data to determine what's important about it. Key performance indicators (KPIs) are commonly displayed on dashboards. KPIs are ready-made stories for your dashboard to tell. I briefly discuss KPIs in the next section. A common pitfall in dashboard building is to start with a conclusion. The person requesting the dashboard may have an agenda or preconceived notion of what that data should say. But the data should drive the story, not the other way around. Try to reframe the conclusion as a question. If someone wants you to create a dashboard that shows that sales decreased because of bad weather, you can turn that into a question like “How does average daily temperature correlate with daily sales?” or “How much do we sell on rainy days vs. sunny days?”

The underlying data on a dashboard is related, but how it's related depends on who's looking at it. A member of the Human Resources department's dashboard might use data related to employee retention like hiring rate, firing rate, layoffs, voluntary terminations, and retirements. The human resources manager may have a dashboard that's a level above, such as more aggregated employee retention data along with payroll costs and benefit engagement. The person in charge of all administration in a company would look at human resources data next to finance, accounting, and legal data. At the top level of a company, data from administration, operations, and research and development is related.

Key Performance Indicators

How KPIs are determined and what makes a good one is well beyond the scope of this book. An organization's leaders will develop KPIs based on what they know about the organization. If you're running a for-profit business, net income is an important measurement and you don't have to analyze the data to know that it's something you'll want to look at. KPIs are unique to each organization, but similar organizations will have similar KPIs. Finance departments are interested in net income, free cash flow, and working capital. And manufacturers are interested in units produced and line utilization.

Establishing User Requirements

Don't start building a dashboard until you have a plan. Just like building a house, if you start without a plan, you may have to tear it down and start over. To make your plan, start by finding out what the end users need. There are at least three users you'll want to talk to before you begin: the person requesting the dashboard, the person providing the data, and the end user. All these users may be one person, and that person may even be you.

Get as much detail as you can from the person requesting the dashboard. If they have a general idea of what they want, now is the time to probe for details to get a clear picture. As I mentioned in the previous section, the requester might be starting with a conclusion in mind. Try to turn that conclusion into a question or series of questions so you're on the same page.

Questions about the source data are sometimes overlooked but shouldn't be. Find out where the data is coming from and if it's already been aggregated or analyzed. Depending on your project, you may want to try to get the data in as raw a form as possible in case you have to change direction once you get started. It's a lot easier to aggregate raw data in a different way but almost impossible to disaggregate it.

Determine if the data is coming from inside or outside of the organization, who maintains it, and how often it's updated. Financial data from an accounting system may only be available monthly or quarterly. Other types of data, like data from a point of sale, may be able to be queried in real time.

If you don't have the data you need, your dashboard project might turn into two projects: a data-collection project and a dashboard project. You may find that not only is the data not readily available, it doesn't exist at all. If the organization doesn't track defects from the production line, there may be no way to get historical data. In that case, you could set up a system to start tracking the data you need, which would delay how quickly a dashboard could be created. Having this conversation early in the project helps set the expectations of all the stakeholders.

Types of End Users

You can divide end users by how they intend to use the dashboard to get a better understanding of how to construct it. Monitors use dashboards to see the state of an organization or project at a given time. You use your car's dashboard to monitor speed, fuel levels, and trouble alerts. Deciders use dashboards to determine if they should take one action, another action, or no action at all. A production manager might use a dashboard of sales and line utilization data to determine if a third shift is necessary.

Planners are people at the highest levels of an organization that determine the direction of the organization. They are looking at broader trends, and the actions they take are more policy based. Planners might look at operating results by division to determine how to allocate resources for the next five years. Presenters use dashboards to present information. A dashboard presented at a shareholder meeting may be used to simply give shareholders information they don't see day to day.

There is a lot of overlap in these categories of users. Someone monitoring a project, for example, will certainly take action if the information dictates it. And shareholders might change the direction of a company by changing management if they don't like the information presented. Know your audience, and in some cases your audience's audience, so you can provide the right level of information.

Determine how often the dashboard will be created. If you're creating a dashboard to show the effects of the Olympics on a city's finances, you'll probably only do that one time. For one-off dashboards, you don't have to be as concerned about data maintenance and how efficiently you can build it.

For periodic or real-time dashboards, make sure the data availability lines up with how often you will be publishing. Also, invest more of your time in automation for dashboards you will be publishing more. Real-time dashboards have to be fully automated. Dashboards you publish annually can be less so.