Both Classic & New Excel, Windows & Mac

Create Excel Dashboard
Reports Quickly, Easily,
And Almost for Free

Compared to standard reports, dashboard reports can provide more information in less reading time and effort. Excel gives us a powerful and nearly free way to create those dashboard reports.

by Charley Kyd, MBA
Microsoft Excel MVP

Do most of your Excel reports look somewhat like these gray examples?

If so, your audience must work hard to convert those lists of data into useful insight. But your Excel reports don't have to be that way. They could be easy to read and understand.

This figure shows dashboard reports created entirely with Excel. These "magazine-quality" reports act like the dashboard of your car. They can present a massive amount of useful information quickly and easily.

Most Business Intelligence (BI) software costs many thousands of dollars, and typically displays much less information per dashboard than these reports do.

But because I already own Excel, I created these great reports for free. I used only Excel workbooks, with no macros, and no 3rd-party tools.

To be clear, because you already own Excel you pay nothing for the software needed to create these dashboard reports.

(Of course, if you own 3rd-party tools to enhance Excel's reporting capabilities -- like spark-chart software -- the techniques described in this book can help you to use those tools to create even better-looking reports in Excel.)

Excel users in more than half the countries in the world are using Excel to create reports like these for their clients, co-workers, managers, or investors.

You can do the same.

Of course, if there's something you would like to change about these reports, you can do so easily. This isn't complex software, after all. It's only Excel.

Two Dashboard Kits to Choose From

I offer two ways to create dashboard reports.

If you need a dashboard report in a hurry, you can use the Plug-N-Play Dashboard Kit to generate your first report in a few hours. You merely choose among ten report worksheets, enter your data, choose one of 15 color schemes, recalculate your workbook, and then print.

You also can learn how to modify the PNP reports, or create your own Excel dashboard reports from scratch. My Excel Dashboard E-Book Kit provides an e-book and sample files that teach you what you need.

The Plug-N-Play Dashboard Kit

You quickly can print your own Excel reports like the ones shown here. I've done most of the work for you.

My Plug-N-Play Excel Dashboard Kit #1 is the quickest, easiest, and least-expensive way I know for average Excel users to generate magazine-quality Excel dashboard reports.

Plug-N-Play (PNP) versions are available for both Classic Excel (97 - 2003) and New Excel (2007).

(Separate PNP versions are necessary because the two versions of Excel are not compatible when it comes to colors. It took me hours of work to convert one version to the other.)

Each chart in each report shows 13 months of actual and targeted performance. If you don't have targets for some of your measures, just enter zero values for your target data.

After your data is ready you should be able to create your first dashboard reports in a couple of hours. And you should be able to update your reports each month in about half an hour.

Later, if you care to do so, you can reduce your update time to nearly nothing by using the techniques I describe in the Excel Dashboard E-Book Kit.

To get started with the PNP reports, you need to take only three general steps:

1. Choose your report design. The kit includes a report workbook with ten different report designs, each in its own worksheet. Five of the designs are shown at the right.

Some designs have tables, some don't. Some have many charts, some have fewer charts. Most are in portrait mode, but two are in landscape mode.

2. Enter your titles and data. Each chart and table in the workbook is linked to a worksheet that acts as a data-entry form. First, enter your titles for each figure, which quickly makes your report look somewhat familiar. Then just enter (or copy and paste) your data into the worksheets.

If you own the Excel Dashboard E-Book Kit, you can enter the formulas I discuss in chapters 6 - 8, formulas that link the dashboard workbook to your Excel database, or to other databases.

3. Choose your color schemes. The kit includes fifteen color schemes, five of which are shown here. (In New Excel, the proper term is color "themes.") Use a simple Excel command to apply any color scheme to your report workbook. Experiment until you find the one you like best. If you don't like a particular color in one of the color schemes, just change it.

Then print your first report.

Monthly, you can follow a simple process to update your report quickly. Maintaining your report workbook is easy. There are no macros to break. Nothing is hidden or protected. It's just ordinary Excel.

High Quality Reporting

These reports look like they came from the pages of a business magazine. Unless you tell your audience, they probably won't guess that you created the reports in Excel.

I created these reports with one primary goal in mind: As Excel users, our purpose is to communicate business information clearly; our purpose is not to impress our audience with pretty pictures.

Therefore, these reports avoid chart junk. This is Edward Tufte's term for the distracting eye candy that many commercial dashboard companies add to their expensive dashboard reports.

Inexpensive Reporting

Because of the value this kit offers, I originally planned to price it at about $100. But I decided to lower the price significantly. I want as many people as possible to be able to generate their own Excel dashboards, and $100 is beyond the reach of many budgets.

Even so, $100 would have been a bargain:

• Commercial dashboard software typically costs tens of thousands of dollars and takes many days to set up.
   
• Custom Excel dashboard reports could cost thousands of dollars for just a few reports. But this kit includes ten report designs and fifteen color themes, for a total of 150 reports.
   
• Because none of the expensive solutions would allow you to generate reports in a few hours, and modify them easily, this kit offers significant benefits that the others lack.

However, instead of $100, I've set the price at $34.95. This is about $3.50 per report page, or less than $.25 per report if you factor in the 15 color schemes available for each report page.

You can save even more if you buy the PNP kit bundled with the Excel Dashboard E-Book Kit.

The Excel Dashboard E-Book Kit

This figure illustrates a few of the many types of dashboard reports that you could create for your organization.

My ebook, Dashboard Reporting With Excel, teaches you how to create Excel dashboards from scratch. It doesn't discuss these reports specifically, but it explains how to set up the charts, tables, and formatting that I use to create reports like this.

The kit consists of the 150-page e-book and a package of 21 files that the book discusses. Here are its major topics:

How to Create Mini-Charts. Excel users often print charts about the size of post cards. But business magazines often print charts about the size of postage stamps. By using small charts in our reports, we give our audience more insight more quickly than any other method of reporting. Chapter 2 of the e-book explain how to create those magazine-quality "mini-charts." And the workbooks that accompany the e-book illustrate each step along the way.

Other Useful Techniques for Dashboards. Good dashboards often require additional charting techniques. In Chapter 3, I explain how to add an additional data series to a chart, how to include multiple chart types, how to overlay two charts to create special effects, how to use auto-shapes in charts, and so on. This chapter also explains why you shouldn't use gauges in dashboard reports.

How to Create Figures that Use Both Charts and Worksheets. In dashboards, cell formatting, column widths, row heights, special formulas, and other spreadsheet elements all combine with charts to create the figures we see. Chapter 4 explains how to use these elements in your dashboard reports.

Excel's Camera Tool. This long-time Excel feature probably is the most-useful feature that most Excel users have never heard of. It gives you the ability to position live images of tables anywhere in your dashboard report. You can rotate the images and format them. And because you even can set up a formula that determines which of several live images to display, the Camera tool allows you to include "traffic light" figures in your reports. Chapter 5 discusses the Camera tool in detail, and the sample files include working examples.

Linking Your Reports to Databases. You can set up your dashboard reports to return data from an Excel database, a PivotTable, or an Excel-friendly OLAP database. Chapter 6 discusses techniques for using Excel database and PivotTables, and it touches on techniques for using OLAPs. And Chapter 7 discusses Excel databases in detail.

How to Create an Excel Dashboard From Scratch. Chapter 8 begins with an empty spreadsheet and ends with a completed dashboard report, showing you each step along the way. The files that come with the kit include the completed dashboard, which is linked to an Excel database.

Dashboard Design Ideas. Business magazines are an excellent source for dashboard design ideas. To help you get the most from those magazines, Chapter 9 provides a wide variety of magazine figures, and shows Excel versions of those figures. The files include the actual spreadsheets that duplicate the magazines' charts and tables.

Bonus Dashboard Files

The three files shown here are not discussed in the e-book. But they also are included with the E-Book Kit.

The top file is based on a design that Business Week used in the early 1990s to describe economic performance for the week. I changed it to display Performance for the Month.

The middle file is based on a design that Forbes magazine used in the 1990s, also to display economic performance. I turned it into an Executive Overview.

This dashboard is unusual because it uses only one chart. However, notice that it contains seven tables, each of which summarizes key information that might interest an executive.

Again, let me reassure users that it doesn't matter what your numbers and text represent. You could use pages like this to report web traffic, car traffic, or astronomical observations. Excel doesn't care. That is, these reports aren't limited to financial and marketing information.

The bottom file also is unusual. It displays 110 charts on one printed page.

This is the only workbook in either kit that contains a macro. First you format the top-left chart any way you want. Then you run the well-commented macro to create the remaining charts. Each chart gets its data from another data set in the workbook.

How to Avoid the Coming Price Increase

I recently hired a very expensive web marketing firm to advise me. Their first recommendation was to increase all my prices by five to ten times.

The consultants pointed out that my customers get better insight about their organizations in much less time. Considering what many managers are paid, and what the value of good decisions are worth, this benefit is worth thousands of dollars they said. Also, my customers will be able to use Excel software they already own instead of buying and learning commercial BI software. This saves tens of thousands of dollars.

I've been charging way too little for these benefits, they told me.

Honestly, I'm resisting the idea, because I don't want to price the dashboards beyond the budgets of small companies and other under-funded organizations around the world. But I won't be able to keep the price this low for long. Today, I'm still charging only $49.95 for the Excel Dashboard E-Book Kit, even less if you buy it bundled with the PNP files.

But I can't guarantee how long these low prices will last. So you'll save money if you order now.

Frequently Asked Questions about the Kits

  Can I get a receipt so my company will reimburse me for the book?

No problem. You'll receive one type of receipt within minutes of purchase. If it doesn't meet your needs, just email me. I'll send you another version with more complete information. And if that doesn't work let me know. One way or the other, I'll get you what you need.

  My company paid for your product. How can I get a copy for myself?

If your company purchased our products, we give you permission to make one copy of them for your personal use. The way the worldwide economy is these days, Excel users need all the Excel training the can get.

  What kind of guarantee do you offer?

I offer an unconditional one-year money-back guarantee.

  How soon will the kits arrive?

Within minutes after you place your order you'll receive an email message with download links. Just click the links to download your files immediately. Nothing will be shipped to you.

  If my hard drive crashes, or I change jobs, can I get another copy?

No problem. This happens all the time. Just send me an email message and I'll send you another download link.

  Can I buy your book without giving you my credit card information?

Certainly! You can use PayPal, send us a check, or even a wire transfer. Just email me for further instructions. However, I will say that we've sold thousands of books by credit card and PayPal, and haven't had one security problem.

  What's your email policy?

I'll never sell your email address to others. And I won't spam you myself. Personally, I receive more than a thousand spams a day, and I don't intend to add to this worldwide problem.

Choose the Dashboard Package That's Right for You

If you use Mac Excel 2008, choose the Excel 2007 version. Otherwise choose the Excel 97-2003 version.

If you don't already have Adobe Acrobat PDF Reader, you can download a FREE copy here. You'll probably use it a lot. To open the Zipped version, you'll need to install a program like WinZip. You can download a free copy here.

Here's some additional information about ordering:

• Secure transaction protects you.
• Privacy protection against identity theft.
• About the author.
• How does an "e-book" differ from a printed book?
• Satisfaction guarantee and refund policy.

If you have any problems downloading or un-zipping the files, contact me immediately. I'll make sure to get you what you need.

Choose the Dashboard Package That's Right for You

Here are even more comments about these dashboard products:

If you use Mac Excel 2008, choose the Excel 2007 version. Otherwise choose the Excel 97-2003 version.