Home > Excel Dashboards > 

How to Get Started With
Excel Dashboard Reporting

Are you ready to create dashboard reports in Excel? Here are
some tips and techniques to get you started.

by Charley Kyd
October, 2004

Excel works very well for dashboard reporting. But there are a wide variety of techniques you can use, and they all can't be explained in one short article.

Even so, here are some tips and techniques to get you headed in the right direction.

Use Small Charts

Compare the charts you see in typical management reports to the charts you see in business magazines. In management reports, charts are frequently the size of a post card; in magazines, charts are frequently the size of a postage stamp.

To see examples of many small Excel charts on a page, visit Displays of Business Data. The page provides links to several pages of dashboard reports that were created with Excel. Each one-page display contains 20 charts and two tables.

By making your charts small you typically make them more useful. Readers can view them at a glance, and easily can compare one chart with another. If we had used big charts, as many companies use for management reporting, we would have needed at least six pages to contain the same information presented on one dashboard page.

Use Excel's Non-Charting Objects
With Your Charts

The simple figures shown here use several objects from the Drawing toolbar. They use two Auto Shape objects (for the rounded corner backgrounds) and four Text Box objects. All objects are placed on a worksheet.

(Where's the forth text box? It contains only the arrow in the bottom left corner of each figure. The arrow needs to be in its own text box because it uses the Wingdings 3 font; the "Millions of Dollars" text uses Arial.)

One of Excel's most useful objects is its Camera tool. This little-used tool can provide many of the features offered by more expensive dashboard-reporting programs.

For example, the Camera tool can automatically change the decoration around a single chart, as shown in these three figures. In the months ahead we'll explain how this is done.

Use a Reporting Template

Your first one-page dashboard report probably will take several days to create. Don't waste that investment. Set the report up as a reporting template.

By template, I do not mean that you should save your workbook as an Excel template file...an XLT file. Instead, you should design your report workbook so you can update it easily from month to month, department to department, and so on.

The key step is to design your workbook so that it contains no data, only formulas and key values...values including the month to report, the department, the division, and so on.

The easiest way to do this is to use an Excel-friendly OLAP product. This would allow Excel formulas to return specific values from cubes of data on your computer or a server.

If your company doesn't own an Excel-friendly OLAP database for some reason, use INDEX, MATCH, LOOKUP, GETPIVOTDATA, and other spreadsheet functions to return data to your reporting template.

With your formulas set up properly, you'll find it very easy to shift your report from month to month or division to division. Just enter the month or division in the appropriate cells, and then recalculate.

Use Magazine Formatting

Graphic artists who work at business magazines know how to create charts and tables that are easy to read. Learn from these people. The easiest way to do so is to look for charts and tables in business magazines, figures that you can adapt to your own reporting needs.

At one level, it seems like a waste of time to worry about ways to format your management reports. It's difficult enough to make reports accurate; you shouldn't also need to make them pretty.

However, pretty is not the issue; clear communication is. With millions of readers each week, magazine designers have learned ways to communicate numeric data in clear and interesting ways. Your reports probably will benefit from their knowledge.