Here is our latest Excel based dashboard. It’s 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 area sparklines and a ranking table.
The whole dashboard was built with Excel and MicroCharts. People often ask us which
tool we use to build our dashboards and are surprised that the answer is Excel. Excel is one of the most flexible (and cost effective)
dashboard tools in the market and is useful for building information-dense,
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.
Stephen 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 to build an effective 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 third 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. Recommended Websites for good Dashboards and data visualization are:
Here is a screen that shows how to build an area sparkline
with the Excel Add-In MicroCharts:
MicroCharts has a color manager that allows you to
re-configure the 56 Excel colors with an effective color pallet: Graduations
in color, rather than separate colors, make it much easier for the dashboard consumer
to comprehend the data.
