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
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
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.
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
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
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
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.