The Dashboard Spy

An Excel Dashboard: Dashboard Spy reader Andreas Lipphardt of microcharts.net has been working on some nice excel dashboards that monitor website traffic and use sparklines to provide  analysis. He kindly documented some of his excel dashboarding processes and has provided you Dashboard Spy readers with the main content of this post.  Thank you Andreas. Let’s start by having a look at the enterprise dashboard itself:

As you can see, the usual web traffic metrics are present. We get them categorized into the following groups: Visitor Metrics (unique visitors, visits, page views, pages per visit, new visits, bounce rate and average time on site), Traffic Metrics (direct traffic, referring sites, search engines) and Goal Metrics (I assume these to be user defined). While the metrics may be usual, the presentation is not. Note the very efficient use of space via the sparklines. Those thumbnail-style graphs give a good feel for trends in a very small space.

Here is the background of the project as explained to me:

Here is our latest Excel based executive dashboard. It is a WebSite Analytics Dashboard. The upper chart in the dashboard shows the Visitors in June, the lower part of the dashboard shows the Visitor / Traffic / Goal metrics enriched with sparklines and a ranking table.  The whole dashboard was built using Excel and MicroCharts.

People who ask us which tool we like to use to build our dashboards are often surprised that the answer is Excel. Microsoft Excel is one of the most flexible and cost effective dashboard tools in the market. It is able to build information-dense and effective dashboards.

As Stephen Few outlined in his article Pervasive Hurdles to Effective Dashboard Design, most dashboard vendors fail to build tools that can produce dashboards that demonstrate effective visual design. Mr. Few wrote: “Surprisingly, it is actually possible to achieve the layout flexibility that I’m advocating with Microsoft Excel. […] Charley Kyd’s book Dashboard Reporting with Excel is a good source for learning the dashboard design tricks that can turn Excel into a viable dashboard platform. Add to this the sparklines and bullet graphs that are provided by Excel add-in products such as MicroCharts from BonaVista Systems, and Excel can be used to create dashboard designs that would be impossible with most dashboard products.”

Here is my list of ingredients necessary for building an effective enterprise dashboard with Excel:

1. Learn the Basics. Read Stephen Few’s  Book Information Dashboard Design to learn the basics about dashboard design and follow his blog about visual design.
2. Extend Excel with Dashboard Widgets: Sparklines and bullet graphs are space efficient and information rich extension for Excel based dashboards. The MicroCharts Add-In extends Excel with sparklines, bullet graphs and many other in-cell charts, very easy to setup and format as they are simply new excel formulas .
3. Fix the Excel color palette: Excel users often produce unsatisfactory charts and reports using the Excel default color palette. Set up you own palette, search the web for templates with a better color palette or use a commercial Excel Color Manager like the Color Manager of MicroCharts.
4. Use Data-Connectivity Tools:  Use a data connectivity tool to pull the data from your database and OLAP cubes to Excel. Historically Excel’s built-in connectivity has been poor and there are a number of 3rd party tools such as  XLCubed, IntelligentApps, MIS Plain etc that do a much better job, providing you with a rich set of data integration capabilities.
5. Use Excel to Web Solutions: Finally you need an Excel-To-Web Solution to publish your dashboard to the Web / Intranet so that it can be shared throughout your company. For example with a few mouse-clicks you can publish from within Excel a full dynamic Web dashboard with XLCubed Web Edition.
6. Look on the Web for good example dashboards and Blogs: A good dashboard is one that tells the viewer about the data that it is presenting. Unnecessary graphics, garish colors, 3D charts, spinning text are visual distractions not visualization aids.  Good Websites for Good Dashboards and data visualization re are:
   Stephen Few’s articles and dashboards blog
   Juice Analytics: Data visualization blog that focus on practical data visualization tips and techniques
   The Data Warehouse Institute
7. Also read the Dashboard Spy at dashboardspy.com and study the dashboard screenshots at enterprise-dashboard.com!

Here are some screenshots that show how to build sparklines using the MicroCharts Excel Add In:

Tags: Excel Dashboard, Sparkline Dashboards, MicroCharts Dashboard