Data Visualization with Excel Dashboards and Reports

by Dick Kusleika

Overview

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

Product Details

ISBN-13:	9781119698722
Publisher:	Wiley
Publication date:	02/17/2021
Pages:	352
Sales rank:	632,501
Product dimensions:	7.30(w) x 9.10(h) x 0.90(d)

About the Author

DICK KUSLEIKA is a 12-year Microsoft Excel MVP and the principal contributor at the Daily Dose of Excel Blog.

Table of Contents

Introduction xxi

Part I Display Data on a Dashboard 1

Chapter 1 Dashboard Basics 3

Determining When to Use a Dashboard 3

What Is a Dashboard? 5

Key Performance Indicators 6

Establishing User Requirements 6

Types of End Users 7

Assembling the Data 8

PivotTables 8

The GETTTVOTDATA Worksheet Function 13

Worksheet Functions 14

The VLOOKUP Function 14

The XLOOKUP Function 15

The INDEX and MATCH Functions 16

The SUMPRODUCT Function 17

Array Formulas 19

Tables 20

Structured Table Referencing 23

Text to Columns 24

Removing Duplicates 26

Building the Dashboard 28

Organizing Elements 28

Varying Elements 30

Showing Trends 31

Formatting the Dashboard 33

Number Formats 36

Chapter 2 Dashboard Case Studies 39

Monitoring Progress 39

Case Study: Monitoring a Software Project 40

Planning and Layout 40

Collecting the Data 42

Building the Visual Elements 43

Laying Out the Dashboard 54

Displaying Key Performance Indicators 55

Case Study: Human Resources KPIs 55

Planning and Layout 56

Collecting the Data 57

Building the Visual Elements 58

Laying Out the Dashboard 69

Reporting Financial information 72

Case Study: Financial Information and Ratios 72

Planning and Layout 72

Collecting the Data 73

Building the Visual Elements 75

Laying Out the Dashboard 83

Chapter 3 Organizing Data for Dashboards 87

Separating Data Layers 87

Source Data Layer 89

Staging and Analysis Layer 90

Presentation Layer 91

Working with External Data 92

Power Query vs. Power Pivot 92

Text Files 92

Excel Files 98

Access Databases 105

SQL Server Databases 111

Transforming Data in Power Query 114

Managing Columns and Rows 116

Transforming Columns 119

Transforming Data Types 119

Transforming Numbers 121

Splitting Columns 123

Part II Visualization Primer 127

Chapter 4 The Fundamentals of Effective Visualization 129

Creating an Effective Visualization 129

Keep It to a Single Screen 130

Make It Attractive 131

Tell the Story Quickly 131

Make the Story Consistent with the Data 133

Choose the Proper Chart 135

Driving Meaning with Color 137

How to Use Color 137

Varying Color as Data Values Vary 137

Using Sharp Contrast to Highlight Data 138

Grouping Data with Color 139

Tips on Color Use 140

Use White Space 140

Use a Simple Color Pallet 141

Use Colors That Are Consistent with the Data 141

Use Enough Contrast 141

Use Non-data Pixels When Necessary 142

Focusing Attention on Text 142

Fonts 142

Legends 143

Axes 144

Data Labels 145

Showing Insights with Charts 146

Comparisons 146

Compositions 147

Relationships 149

Chapter 5 Non-chart Visualizations 151

Understanding Custom Number Formats 151

The Four Sections of a Format 152

Special Characters 153

Digit Placeholders 153

Commas and Periods 154

Text 154

Underscore 155

Asterisk 156

Escaping Special Characters 156

The Accounting Number Format 156

Date and Time Formats 158

Conditional Custom Number Formats 159

Using Icons 160

Color Scales 160

Data Bars 165

Icon Sets 167

Creating Sparklines 170

Types of Sparklines 170

Creating a Sparkline 171

Sparkline Groups 172

Customize a Sparkline 172

Changing the Source Data 173

Changing the Color and Thickness 174

Adjusting the Axis 175

Chapter 6 Using Shapes to Create Infographics 179

Working with Shapes 179

Inserting Shapes 180

Customizing Shapes 182

Framing Data with Shapes 185

Creating a Banner 186

Creating a Binder Tab 188

Working with Multiple Shapes 191

Creating Simple Charts with Shapes 193

Creating Custom Infographics 195

Adding Other Illustrations 196

Part III Tell a Story with Visualization 203

Chapter 7 Visualizing Performance Comparisons 205

Single Measurements 206

Column Charts 207

Case Study: Sales by Quarter 210

Bullet Charts 212

Case Study; Expenses vs. Budget 212

Clustered Column Charts 216

Case Study: Production Defects 217

Funnel Charts 218

Case Study: Sales Conversion 219

XY Charts 221

Case Study: Temperature vs. Sales 222

Bubble Charts 225

Case Study: Home Mortgages 226

Dot Plot Charts 228

Case Study: Production Output 229

Chapter 8 Visualizing Parts of a Whole 239

Pie Charts 239

Doughnut Charts 241

Case Study: Sales by Region 242

Waffle Charts 244

Case Study: Employee Participation by Benefit 245

Sunburst Charts 249

Case Study: Manufacturing Process Time Study 250

Histograms 252

Case Study: Restaurant Ticket Totals 254

Treemap Charts 256

Case Study: Insurance Policy Averages 257

Waterfall Charts 259

Case Study: Net Income 261

Chapter 9 Visualizing Changes Over Tame 265

Line Charts 266

Case Study: Sales by Product Category 268

Column Charts with Variances 273

Case Study: Houses Sold by Month 274

Combination Charts 280

Case Study: Freight Revenue vs. Miles 281

Line Charts with Differences 284

Case Study: Current vs. Prior Quarter Revenue 285

Side-by-Side Box Plots 288

Case Study: Salaries by Department 290

Animated Charts 292

PivotCharts 293

Staging Area Formulas 295

Chart Animation Macros 299

Chart Automation 302

Manipulating Chart Objects 302

Creating Panel Charts 307

Index 317