Gift Guide

Excel Dashboards and Reports

( 2 )


Learn to use Excel dashboards and reports to better conceptualize data

Updated for all the-latest features and capabilities of Excel 2013, this go-to resource provides you with in-depth coverage of the individual functions and tools that can be used to create-compelling Excel reports. Veteran author Michael Alexander walks you through the most effective ways to present and report data. Featuring a comprehensive review of a wide array of technical and analytical concepts, this ...

See more details below
$24.98 price
(Save 37%)$39.99 List Price

Pick Up In Store

Reserve and pick up in 60 minutes at your local store

Other sellers (Paperback)
  • All (21) from $13.99   
  • New (13) from $17.51   
  • Used (8) from $13.99   


Learn to use Excel dashboards and reports to better conceptualize data

Updated for all the-latest features and capabilities of Excel 2013, this go-to resource provides you with in-depth coverage of the individual functions and tools that can be used to create-compelling Excel reports. Veteran author Michael Alexander walks you through the most effective ways to present and report data. Featuring a comprehensive review of a wide array of technical and analytical concepts, this essential guide helps you go from reporting data with simple tables full of dull numbers to presenting key information through the use of high-impact, meaningful reports and dashboards that will wow management both visually and substantively.

  • Details how to analyze large amounts of data and report the results in a way that is both visually attractive and effective
  • Describes how to use different perspectives to achieve better visibility into data, as well as how to slice data into various views on the fly
  • Shows how to automate redundant reporting and analysis processes
  • Walks you through creating impressive dashboards, eye-catching visualizations, and real-world What-If analyses

Excel Dashboards and Reports, Second Edition is part technical manual, part analytical guidebook, and exactly what you need to become your organization's dashboard-dynamo!

Read More Show Less

Product Details

  • ISBN-13: 9781118490426
  • Publisher: Wiley
  • Publication date: 5/28/2013
  • Series: Mr. Spreadsheet's Bookshelf Series , #17
  • Edition number: 2
  • Pages: 432
  • Sales rank: 264,452
  • Product dimensions: 7.40 (w) x 9.20 (h) x 0.90 (d)

Meet the Author

Michael Alexander is a Microsoft Excel MVP who has written several books on advanced business analysis with Microsoft Access and Excel. Visit Michael at for free Excel and Access training.

John Walkenbach, arguably the foremost authority on Excel, has written hundreds of articles and created the award-winning Power Utility Pak. His 50-plus books include Excel 2010 Formulas, Excel 2010 Power Programming with VBA, and the bestselling Excel Bible, all published by Wiley. Visit his popular Spreadsheet Page at

Read More Show Less

Table of Contents

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

Read More Show Less

Customer Reviews

Average Rating 3
( 2 )
Rating Distribution

5 Star


4 Star


3 Star


2 Star


1 Star


Your Rating:

Your Name: Create a Pen Name or

Barnes & Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation


  • - By submitting a review, you grant to Barnes & and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Terms of Use.
  • - Barnes & reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing all of 2 Customer Reviews
  • Posted April 13, 2012

    SAVE YOUR MONEY!! Despite the misleading title, the first 278 pa

    SAVE YOUR MONEY!! Despite the misleading title, the first 278 pages (68%) of the book deal with very basic things like working with an Excel chart and chart formatting. Really? By the time you get around to DASHBOARDS they offer no practical value beyond such sage advice as "Keep it simple." I suspect its just designed to drive traffic to the author's website because there's little of real value here. Certainly not worth $35 + shipping. You can learn more for free on the Internet.

    4 out of 4 people found this review helpful.

    Was this review helpful? Yes  No   Report this review
  • Anonymous

    Posted July 27, 2014

    I liked it so much I bought it.

    I actually found and started reading "Dashboards and Reports" through my local eLibrary and liked it so much that I bought it so I could add it to my personal technical eLibrary.

    This book really opened my eyes to features of Excel that I kind of knew about, but had not fully comprehended the possibilities of previously.

    At my office I'm one of the "Excel guys" who everybody seeks out when they need help with a spread sheet. Rarely do I find myself wanting help to get Excel to behave, but these guys Really know how to make Excel perform. I was book marking pages right from the first section to start using immediately.

    I'm not a trained visual designer, so the basics covered in Chapt 2 were extremely helpful and well explained. I've tried reading Toftke (sp?) before, but these guys broke the subject down clearly enough for my mechanical engineering mind to follow. (It's single accounting underlines in headers for me from now on.)

    Even when the authors were explaining the steps for some basic tasks, they still managed to insert nuggets of knowledge that helped me learn to do basic tasks faster.

    This book will provide you with enough good, solid, functional knowledge of pivot tables, macros and form controls that you could be one the "Excel people" at your offce, too. (Wait a minute... that might not be good for me... :-) ).

    I really liked the ideas for how to arrange dashboard
    components to maximize their communication impact. Basic stuff that other people probably know, but it was never covered in my non-arts directed education.

    I imagine I could have found all this information somewhere on the internet, but I don't imagine I could have found it in as compact and friendly a form as this (& I thought I was looking pretty dilligently.)

    Oh, and the example sheets are Pure Gold. Don't miss them.

    I recomend this highly for any level of Excel user looking to advance their data presentation techniques and to learn the problem solving approach of some really long time Excel users. They cut through tasks and explain the rationale really clearly. These guys really know their stuff and I Thank them So Much for taking the time to write this all down.

    I'm planning to show varoius sections of this book to so many people at work ASAP so I can spend less time reporting about work and concentrate on doing my engineering again.

    Was this review helpful? Yes  No   Report this review
Sort by: Showing all of 2 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)