Michael Alexander is a senior consultant at Slalom Consulting with more than 15 years’ experience in data management and reporting. He is the author of more than a dozen books on business analysis using Microsoft Excel, and has been named Microsoft Excel MVP for his contributions to the Excel community.

Introduction 1

About This Book 2

Foolish Assumptions 4

Icons Used in This Book 4

Beyond the Book 4

Where to Go from Here 5

Part 1: Getting Started with Excel Dashboards and Reports 7

Chapter 1: Getting in the Dashboard State of Mind 9

Defining Dashboards and Reports 10

Defining reports 10

Defining dashboards 11

Preparing for Greatness 12

Establish the audience for, and purpose of, the dashboard 12

Delineate the measures for the dashboard 13

Catalog the required data sources 14

Define the dimensions and filters for the dashboard 15

Determine the need for drill-down features 16

Establish the refresh schedule 16

A Quick Look at Dashboard Design Principles 16

Rule number 1: Keep it simple 17

Use layout and placement to draw focus 19

Format numbers effectively 20

Use titles and labels effectively 20

Chapter 2: Building a Super Model 23

Data Modeling Best Practices 24

Separating data, analysis, and presentation 24

Starting with appropriately structured data 27

Avoiding turning your data model into a database 30

Using tabs to document and organize your data model 31

Testing your data model before building reporting components on top of it 33

Excel Functions That Really Deliver 34

The VLOOKUP function 34

The HLOOKUP function 38

The SUMPRODUCT function 40

The CHOOSE function 43

Using Smart Tables That Expand with Data 45

Converting a range to an Excel table 46

Converting an Excel table back to a range 49

Introducing Dynamic Arrays 49

Getting the basics of dynamic arrays 49

Understanding spill ranges 51

Referencing spill ranges 53

Exploring Dynamic Array Functions 54

The SORT function 55

The SORTBY function 56

The UNIQUE function 57

The FILTER function 58

The XLOOKUP function 61

Chapter 3: The Pivotal Pivot Table 65

An Introduction to the Pivot Table 65

The Four Areas of a Pivot Table 66

Values area 66

Row area 67

Column area 67

Filter area 68

Creating Your First Pivot Table 69

Changing and rearranging your pivot table 72

Adding a report filter 73

Keeping your pivot table fresh 74

Customizing Pivot Table Reports 76

Changing the pivot table layout 76

Customizing field names 78

Applying numeric formats to data fields 79

Changing summary calculations 80

Suppressing subtotals 81

Showing and hiding data items 84

Hiding or showing items without data 86

Sorting your pivot table 88

Creating Useful Pivot-Driven Views 89

Producing top and bottom views 89

Creating views by month, quarter, and year 93

Creating a percent distribution view 95

Creating a month-over-month variance view 97

Chapter 4: Using External Data for Your Dashboards and Reports 101

Leveraging Power Query to Extract and Transform Data 102

Reviewing Power Query basics 102

Understanding query steps 109

Importing Data from Files 111

Getting data from Excel workbooks 111

Getting data from CSV and text files 113

Importing Data from Database Systems 114

Importing data from Microsoft Access 114

Managing data source settings 116

Part 2: Building Basic Dashboard Components 119

Chapter 5: Dressing Up Your Data Tables 121

Table Design Principles 122

Use colors sparingly 122

De-emphasize borders 123

Use effective number formatting 126

Subdue your labels and headers 127

Getting Fancy with Custom Number Formatting 129

Number formatting basics 129

Formatting numbers in thousands and millions 132

Hiding and suppressing zeroes 134

Applying custom format colors 135

Formatting dates and times 136

Chapter 6: Sparking Inspiration with Sparklines 139

Introducing Sparklines 139

Understanding Sparklines 141

Creating sparklines 142

Understanding sparkline groups 144

Customizing Sparklines 145

Sizing and merging sparkline cells 145

Handling hidden or missing data 146

Changing the sparkline type 147

Changing sparkline colors and line width 147

Using color to emphasize key data points 147

Adjusting sparkline axis scaling 148

Faking a reference line 149

Specifying a date axis 151

Autoupdating sparkline ranges 152

Chapter 7: Formatting Your Way to Visualizations 153

Enhancing Reports with Conditional Formatting 154

Applying basic conditional formatting 154

Adding your own formatting rules manually 162

Showing only one icon 166

Showing Data Bars and icons outside of cells 169

Representing trends with Icon Sets 171

Using Symbols to Enhance Reporting 173

Wielding the Magical Camera Tool 176

Finding the Camera tool 176

Using the Camera tool 177

Enhancing a dashboard with the Camera tool 179

Enhancing Excel Reports with Shapes 180

Creating visually appealing containers with shapes 180

Layering shapes to save space 182

Constructing your own infographic widgets with shapes 182

Part 3: Adding Charts to Your Dashboards 185

Chapter 8: Charts That Show Trending 187

Trending Dos and Don’ts 188

Using chart types appropriate for trending 188

Starting the vertical scale at zero 190

Leveraging Excel’s logarithmic scale 192

Applying creative label management 193

Comparative Trending 196

Creating side-by-side time comparisons 196

Creating stacked time comparisons 198

Trending with a secondary axis 199

Emphasizing Periods of Time 202

Formatting specific periods 202

Using dividers to mark significant events 203

Representing forecasts in your trending components 204

Other Trending Techniques 206

Avoiding overload with directional trending 206

Smoothing data 207

Chapter 9: Grouping and Bucketing Data 211

Creating Top and Bottom Displays 211

Incorporating top and bottom displays into dashboards 212

Using pivot tables to get top and bottom views 213

Top Values in Charts 216

Using Histograms to Track Relationships and Frequency 220

Using Excel’s Histogram statistical chart 220

Creating a formula-driven histogram 223

Adding a cumulative percent 226

Using a pivot table to create a histogram 228

Chapter 10: Displaying Performance against a Target 231

Showing Performance with Variances 231

Showing Performance against Organizational Trends 233

Using a Thermometer-Style Chart 234

Using a Bullet Graph 235

Creating a bullet graph 236

Adding data to your bullet graph 239

Final thoughts on formatting bullet graphs 241

Showing Performance against a Target Range 243

Part 4: Advanced Reporting Techniques 247

Chapter 11: Giving Users an Interactive Interface 249

Introducing Macros 249

Why use a macro? 250

Recording your first macro 251

Running your macros 254

Enabling and trusting macros 257

Understanding macro-enabled file extensions 258

Enabling macro content 258

Setting up trusted locations 258

Examining some macro examples 259

Building navigation buttons 260

Dynamically rearranging pivot table data 261

Offering one-touch reporting options 262

Getting Started with Form Controls 263

Finding Form controls 263

Adding a control to a worksheet 264

Using the Button Control 266

Using the Check Box Control 266

Toggling a Chart Series On and Off 268

Using the Option Button Control 270

Showing Many Views through One Chart 272

Using the Combo Box Control 274

Changing Chart Data with a Drop-Down Selector 275

Using the List Box Control 277

Controlling Multiple Charts with One Selector 279

Chapter 12: Adding Interactivity with Pivot Slicers 283

Understanding Slicers 283

Creating a Standard Slicer 286

Getting Fancy with Slicer Customizations 288

Size and placement 288

Data item columns 288

Other slicer settings 289

Creating your own slicer style 289

Controlling Multiple Pivot Tables with One Slicer 293

Creating a Timeline Slicer 294

Using Slicers as Form Controls 296

Using Slicers on Excel Table Objects 300

Chapter 13: Sharing Your Workbook with the Outside World 303

Protecting Your Dashboards and Reports 303

Securing access to the entire workbook 304

Limiting access to specific worksheet ranges 307

Protecting the workbook structure 310

Linking Your Excel Dashboards to PowerPoint 311

Creating a link between Excel and PowerPoint 312

Manually updating links to capture updates 313

Turning off automatic updating of links 314

Distributing Your Dashboards via a PDF 316

Distributing Your Dashboards to OneDrive 318

Limitations When Publishing to the Web 321

Part 5: The Part of Tens 323

Chapter 14: Ten Chart Design Principles 325

Avoid Fancy Formatting 325

Skip the Unnecessary Chart Junk 327

Format Large Numbers Where Possible 329

Use Data Tables Instead of Data Labels 330

Make Effective Use of Chart Titles 332

Sort Your Data before Charting 333

Limit the Use of Pie Charts 333

Don’t Be Afraid to Parse Data into Separate Charts 334

Maintain Appropriate Aspect Ratios 336

Don’t Be Afraid to Use Something Other Than a Chart 337

Chapter 15: Ten Questions to Ask Before Distributing Your Dashboard 339

Does My Dashboard Present the Right Information? 339

Does Everything on My Dashboard Have a Purpose? 340

Does My Dashboard Prominently Display the Key Message? 340

Can I Maintain This Dashboard? 341

Does My Dashboard Clearly Display Its Scope and Shelf Life? 341

Is My Dashboard Well Documented? 341

Is My Dashboard Overwhelmed with Formatting and Graphics? 342

Does My Dashboard Overuse Charts When Tables Will Do? 343

Is My Dashboard User-Friendly? 343

Is My Dashboard Accurate? 344

Index 345

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.