Create Excel Dashboards
For Data Visualization and Business Intelligence

- Jon Wittwer
Vertex42, LLC

"Excel dashboards" is a fairly new term, but the idea of creating concise and professional reports is definitely not new. Excel can be a great tool for summarizing data in charts and tables, and creating interactive financial models. However, creating good Excel dashboards is something that few people are able to do without some training.

The worth of data depends upon the ability to access it, analyze it, interpret it, and communicate it. When you can make vital information accessible to the people who need it to make decisions, in a way that is easily and correctly communicated, you become a valuable asset.

A new e-book, "Dashboard Reporting With Excel," by Charley Kid of ExcelUser.com has made it much easier to create dashboards in Excel. The images below come from ExcelUser.com, the home of the "Excel Dashboard Kit". This kit includes the 150 page e-book, the Excel workbooks used as examples in the book, and a bunch of other stuff.

Have you noticed that you can pick out a default Excel chart from a mile away!? I am a firm believer that you shouldn't use fancy graphics to make bad data look impressive, but when it comes to presentations and other high-level reports, nobody likes ugly graphics!

The examples at ExcelUser.com show that it is possible to make nice concise summary reports using Excel. For more information, check out the "Excel Dashboard Kit"

Dashboard Report Tips

Clearly the easiest way to create a good Excel dashboard is to start from a template already created, like those shown above. But if you want to start from scratch, here are a few tips I've learned along the way.

• Get Rid of the Grid - After laying out your spreadsheet the way you want it, turn off the grid or cell outlines to make your spreadsheet look cleaner.
• Chop Down the Charts - Do you really need a half sheet of paper to show a single line or bar graph? Make your charts smaller. Just be careful to not make the text too small to read. The tricks that Charley Kyd describes in his Dashboard Kit to optimize chart size are spot on. You could also try using sparklines if that makes sense for your data.
• Go Easy on the Eyes - Unless you are graphic designer or have an innate sense for color schemes, it may be best to stick with monochromatic colors in your dashboard layout (especially cell background colors). This is easy in Excel 2007 with the new palette. But, if you are trying to create your own monochromatic color scheme, pick your base color, and then make the other colors have different lightness or luminance (the L value in the HSL mode).