Excel Dashboards and Reports Book

Introduction 1

What You Need to Know 2

What You Need to Have 3

Conventions in This Book 3

Keyboard conventions 3

Mouse conventions 4

What the icons mean 5

How This Book Is Organized 5

Part I Moving from Spreadsheets to Dashboards 5

Part II Excel Chart Basics 6

Part III Going Beyond Tables and Charts 6

Part IV Creating Advanced Dashboard Components 6

Part V Automating Your Dashboards and Reports 6

Part VI Working with the Outside World 6

About the Companion Web Site 7

About the Power Utility Pak Offer 7

Reach Out 7

Part I Moving from Spreadsheets to Dashboards

Chapter 1 Introducing Dashboards 11

What are Dashboards and Reports? 11

Defining reports 12

Defining dashboards 12

Establish the User Requirements 13

Define the message(S) 14

Establish the audience 14

Define the performance measures 14

List the required data sources 15

Define the dimensions and filters 16

Determine the need for drill down details 17

Establish the update schedule 17

A Quick Look at Dashboard Design Principles 17

Rule number 1: Keep it Simple 18

Use layout and placement to draw focus 22

Format numbers effectively 23

Use titles and labels effectively 25

Key Questions to Ask Before Distributing Your Dashboard 25

Does my dashboard present the right information? 25

Does everything on my dashboard have a purpose? 25

Does my dashboard prominently display the key message? 26

Can I maintain this dashboard? 26

Does my dashboard clearly display its scope and shelf life? 26

Is my dashboard well documented? 27

Is my dashboard user-friendly? 27

Is my dashboard accurtate? 28

Chapter 2 Developing Your Data Model 29

Building a Data Model 29

Separating the data, analysis, and presentation layers 30

Data Model Best Practices 34

Avoid storing excess data 34

Use tabs to document and organize your data model 35

Test your data model before building presentation components 37

Excel Functions for Your Data Model 37

The VLOOKUP function 37

The HLOOKUP function 40

The SUMPRODUCT function 42

The CHOOSE function 45

Working with Excel Tables 47

Converting a range to an Excel table 48

Converting an Excel table back to a range 51

Part II Excel Chart Basics

Chapter 3 Introducing Excel Charts 55

What Is a Chart? 55

How Excel Handles Charts 56

Embedded charts 57

Chart sheets 58

Parts of a Chart 59

Basic Steps for Creating a Chart 62

Creating the chart 62

Switching the row and column orientation 64

Changing the chart type 65

Applying a chart layout 66

Applying a chart style 66

Adding and deleting chart elements 66

Formatting chart elements 67

Working with Charts 69

Moving and resizing a chart 69

Converting an embedded chart to a chart sheet 70

Copying a chart 70

Deleting a chart 70

Adding chart elements 71

Moving and deleting chart elements 71

Formatting chart elements 71

Copying a chart's formatting 72

Renaming a chart 72

Printing charts 72

Chapter 4 Understanding Chart Types 75

Conveying a Message with a Chart 75

Choosing a Chart Type 76

Excel's Chart Types 78

Column charts 79

Bar charts 83

Line charts 85

Pie charts 88

Scatter charts 90

Area charts 92

Doughnut charts 94

Radar charts 96

Surface charts 98

Bubble charts 99

Stock charts 101

Creating Combination Charts 104

Creating and Using Chart Templates 106

Chapter 5 Working with Chart Series 109

Specifying the Data for Your Chart 109

Adding a New Series to a Chart 112

Adding a new series by copying a range 112

Adding a new series by extending the range highlight 113

Adding a new series using the Select Data Source dialog box 114

Adding a new series by typing a new SERIES formula 115

Deleting a Chart Series 115

Modifying the Data Range for a Chart Series 115

Using range highlighting to change series data 116

Using the Select Data Source dialog box to change series data 117

Editing the SERIES formula to change series data 118

Understanding Series Names 120

Changing a series name 121

Deleting a series name 123

Adjusting the Series Plot Order 123

Charting a Noncontiguous Range 126

Using Series on Different Sheets 127

Handling Missing Data 128

Controlling a Data Series by Hiding Data 130

Unlinking a Chart Series from Its Data Range 132

Converting a chart to a picture 132

Converting a range reference to arrays 134

Working with Multiple Axes 135

Creating a secondary value axis 135

Creating a chart with four axes 136

Chapter 6 Formatting and Customizing Charts 139

Chart Formatting Overview 139

Selecting chart elements 140

Common chart elements 142

UI choices for formatting 143

Adjusting Fills and Borders: General Procedures 147

About the Fill tab 147

Formatting borders 148

Formatting Chart Background Elements 150

Working with the chart area 150

Working with the plot area 151

Formatting Chart Series 152

Basic series formatting 153

Using pictures and graphics for series formatting 153

Additional series options 154

Working with Chart Titles 156

Adding titles to a chart 156

Changing title text 157

Formatting title text 157

Linking title text to a cell 158

Working with a Chart's Legend 160

Adding or removing a legend 160

Moving or resizing a legend 160

Formatting a legend 160

Changing the legend text 161

Deleting a legend entry 161

Identifying series without using a legend 161

Working with Chart Axes 162

Value axis versus category axis 162

Value axis scales 164

Using time-scale axes 171

Creating a multiline category axis 173

Removing axes 174

Axis number formats 174

Working with Gridlines 175

Adding or removing gridlines 175

Working with Data Labels 176

Adding or removing data labels 176

Editing data labels 176

Problems and limitations with data labels 179

Working with a Chart Data Table 181

Adding and removing a data table 182

Problems and limitations with data tables 182

Part III Going Beyond Tables and Charts

Chapter 7 Using Pivot Tables 187

Introducing the Pivot Table 187

Anatomy of a pivot table 188

Creating the basic pivot table 190

Customizing Your Pivot Table 198

Renaming the fields 198

Formatting numbers 199

Changing summary calculations 200

Suppressing subtotals 201

Removing all subtotals at one time 202

Removing the subtotals for only one field 203

Removing grand totals 203

Hiding and showing data items 204

Hiding or showing items without data 206

Sorting your pivot table 208

Examples of Filtering Your Data 209

Producing top and bottom views 209

Creating views by month, quarter, and year 213

Creating a percent distribution view 215

Creating a YTD totals view 217

Creating a month-over-month variance view 218

Chapter 8 Using Pivot Charts 221

Getting Started with Pivot Charts 221

Creating a pivot chart 221

A pivot chart example 222

Working with Pivot Charts 225

Hiding field buttons 225

Moving a pivot chart 225

Working with slicers 226

More Pivot Chart Examples 227

Question 1 229

Question 2 231

Question 3 233

Question 4 234

Question 5 236

Question 6 237

Creating a Frequency Distribution Chart 238

Specifying which rows to plot 240

Chapter 9 Using Excel Sparklines 245

Introducing Sparklines 246

Creating Sparklines 247

Customizing Sparklines 250

Sizing and merging sparkline cells 250

Handling hidden or missing data 251

Changing the sparkline type 251

Changing sparkline colors and line width 252

Using color to emphasize key data points 252

Adjusting sparkline axis scaling 252

Faking a reference line 254

Specifying a date axis 256

Auto-updating sparkline ranges 257

Displaying a sparkline for a dynamic range 257

Chapter 10 Chartless Visualization Techniques 259

Dynamic Labels 259

Linking Formulas to Text Boxes 262

Excel's Camera Tool 263

Finding the Camera tool 263

Using the Camera tool 264

Enhancing a dashboard with the Camera tool 266

Formula-Driven Labels 268

In-cell charting 268

Using fancy fonts 271

Using symbols 273

Part IV Creating Advanced Dashboard Components

Chapter 11 Components that Show Trending 279

Trending Dos and Don'ts 279

Using chart types appropriate for trending 280

Starting the vertical scale at zero 282

Leveraging Excel's logarithmic scale 284

Applying creative label management 285

Comparative Trending 289

Creating side-by-side time comparisons 289

Creating stacked time comparisons 291

Trending with a secondary axis 292

Emphasizing Periods of Time 295

Formatting specific periods 295

Using dividers to mark significant events 297

Representing forecasts in your trending components 297

Other Trending Techniques 299

Avoiding overload with directional trending 299

Smoothing data 300

Chapter 12 Components that Group Data 305

Listing Top and Bottom Values 305

Organizing source data 306

Using pivot tables for interactive views 306

Using Histograms to Track Relationships and Frequency 310

Adding formulas to group data 310

Adding a cumulative percent 314

Using a pivot table 316

Emphasizing Top Values in Charts 318

Chapter 13 Components that Show Performance against a Target 323

Showing Performance with Variances 324

Showing Performance against Organizational Trends 325

Using a Thermometer-Style Chart 326

Using a Bullet Graph 327

Creating a bullet graph 328

Adding data to your bullet graph 331

Final thoughts on formatting bullet graphs 332

Showing Performance against a Target Range 335

Part V Automating Your Dashboards and Reports

Chapter 14 Macro-Charged Reporting 339

Why Use a Macro? 339

Introducing the Macro Recorder 340

The Record Macro dialog box 341

Recording macros with absolute references 342

Recording macros with relative references 345

Assigning a macro to a button 347

Enabling Macros in Excel 2010 348

Viewing the new Excel security message 348

Setting up trusted locations 349

Excel Macro Examples 351

Building navigation buttons 351

Dynamically rearranging pivot table data 352

Offering one-touch reporting options 353

Chapter 15 Adding Interactive Controls to Your Dashboard 355

Getting Started with Form Controls 356

Finding Form controls 356

Adding a control to a worksheet 358

Using the Button Control 359

Using the Check Box Control 360

Check box example: Toggling a chart series on and off 361

Using the Option Button Control 364

Option button example: Showing many views through one chart 365

Using the Combo Box Control 367

Combo box example: Controlling multiple pivot tables with one combo box 369

Using the List Box Control 373

List box example: Controlling multiple charts with one selector 374

Part VI Working with the Outside World

Chapter 16 Importing Microsoft Access Data into Excel 379

The Drag-and-Drop Method 380

The Microsoft Access Export Wizard 381

The Get External Data Icon 382

Microsoft Query 386

Start MS Query 386

Set up your data source connection 387

Build your custom data pull 389

Chapter 17 Sharing Your Work with the Outside World 395

Securing Your Dashboards and Reports 395

Securing access to the entire workbook 395

Limiting access to specific worksheet ranges 398

Protecting the workbook structure 402

Linking Your Excel Dashboards to PowerPoint 403

Creating the link between Excel and PowerPoint 403

Manually updating links to capture updates 405

Automatically updating links 407

Distributing Your Dashbords Via a PDF 409

Index 411