The power of dashboard reporting with Excel

July 2005

By Charley Kyd, founder of ExcelUser.com

Applies to
Microsoft Office Excel 2003

About the author

Charley Kyd is a consultant and the founder of Excel User. He is the author of several books about using Excel for financial analysis, including the e-book Dashboard Reporting With Excel.

In this article

Five great Excel features

Sample Excel dashboards


Microsoft Office Excel is one of the best applications on the market for dashboard reporting, a technique that many Excel users may not be familiar with or know how to implement. Essentially, a dashboard report is a way to visually present critical data in summary form so that you can make quick and effective decisions, in much the same way that a car dashboard works. I've included some Excel dashboard samples at the end of this article. Not only do these samples illustrate the power Excel offers to create good-looking dashboards, these samples also show different types of dashboard reports.

Some Excel users may not know what a powerful tool Excel can be for dashboard reporting. It's not often that you find Excel dashboard reports like those shown in this article.

Five great Excel features

Excel has at least five features that make it an outstanding application for dashboard reporting:

  • Magazine-quality    

Excel can produce magazine-quality reports. It's true that few Excel users take advantage of this feature. But they could.

Business magazines are an excellent source of ideas for good-looking reports. Their many figures of business data can provide ideas for color schemes, fonts, and designs for charts and tables. Better yet, if you can find these figures online, you can capture the colors and use them in Excel.

To capture those colors, you could use Microsoft Office FrontPage which has an eye-dropper tool that can capture the RGB colors from any object that the tool hovers over.

  • Flexibility    

Many managers have a simple philosophy of management reporting: "I want what I want when I want it." One reason for the popularity of Excel is that it gives users the ability to adapt quickly to managers' changing requirements.

This flexibility applies to dashboards as well. Using Excel, you can replace one chart with another, add a new column of data, change a color, add a second page, or use a bigger font.

  • Analytical power    

Excel reports often present complex ratios, statistical calculations, and other sophisticated results that could be displayed very effectively in a dashboard.

  • Error finding    

Some third-party dashboard programs have no built-in method to identify errors in their data. That is, without error correction, the best that you can do is present pretty versions of bad data.

Excel enables you to test for errors in your data. You can compare your data to other data, search for missing values, search for values that are unreasonably high or low, and so on.

If your Excel formulas find an error, you can use many commands and tools to display this error in your dashboard. For example, suppose your analysis returns TRUE in some cell if an error is found. You could name this cell ErrorTest. Then you could find an empty cell in your dashboard and enter a simple formula such as the following one:

=IF(ErrorTest=TRUE, "Error!!!!","")

  • Excel users are everywhere    

No matter how good any third-party dashboard program might be, you'll probably have a difficult time finding people who know how to use it. On the other hand, virtually everyone in business knows something about Excel.

Top of Page Top of Page

Sample Excel dashboards


Dashboard 1

The following dashboard sample displays 20 charts and 2 tables on one printed page. When used with internal data, charts such as these typically contain the most-recent 13 months of performance. They often include budgets for comparable periods or performance for the prior year.

Dashboard with charts and tables


Dashboard 2

The following dashboard compares key measures of one company's financial performance with that of publicly traded competitors. In this case, the columns in the table don't match the columns of the spreadsheet because this report uses the Excel Camera tool to return a table from another sheet in the workbook.

General approach used to create dashboard


Dashboard 3

The following dashboard sample uses seven rows of data to present similar information about seven different companies. Each column of charts in this figure uses the same Y-axis values. This dashboard enables readers to visually compare each company's performance for each of the four measures charted.

Dashboard with seven rows of data


Dashboard 4

This dashboard sample compares 13 months of sales performance for 110 different products. Managers can use this report to find the poorly performing products. Typical reports containing this data would use many pages and take much longer to read and understand.

Dashboard with 13 months of sales performance


Dashboard 5

The following is an elaborate dashboard based on a business magazine style, which are a great source of design ideas for Excel dashboards. This Excel dashboard is a mockup that I created about 12 years ago, using a format that appeared in Business Week at the time.

Dashboard with business magazine mockup


Top of Page Top of Page


 
 
Applies to:
Excel 2003