Example of a Hopelessly Complicated Dashboard

Dashboards are at their best when they are outrageously simple. Their “at-a-glance” nature takes advantage of universal human perception into order to bring instant understanding of data trends.

This example is outrageously complicated instead of simple. Sorry, but I would hate to have to attempt to understand this particular excel dashboard. See if you can make heads or tails out of it.

2009 Excel Dashboard Contest

Excel Dashboard designers get another chance to show off their skills in the BonaVista Systems 2009 Excel Dashboard Competition.

Dashboard Spy readers will recall that last year’s winners included an International Bank Dashboard, an Outpatient Surgery Center Dashboard, and a Pharmaceutical Sales Dashboard. See Best Excel Dashboards of 2008.

In addition to bragging rights to having the best Excel Dashboard of the year, the best dashboard will receive a $100 Amazon gift certificate, the second place finisher a copy of Stephen Few’s new dashboard book: Now You See It: Simple Visualization Techniques for Quantitative Analysis , and the third place contest winner a copy of Edward Tufte’s The Visual Display of Quantitative Information, 2nd edition.

Here’s what you’ll be judged on:

The dashboards are judged on the clarity and effectiveness of their design, particularly

  • Clean and clear organization
  • Effective table and chart design
  • A single-screen display, properly designed for the web, screen or print outs

We’ll also consider technical aspects of the dashboard, did it use effective techniques for

  • The Dashboard layout
  • Data management, data logic and calculation : YTD figures, variances, etc
  • Dashboard delivery: Sharing the dashboard via PDF, the web or as an Excel Workbook

Further details at the BonaVista Excel contest page.

Lightbox Images on Excel Dashboards

Today’s Dashboard Design Tip from the Dashboard Spy: Showing Images in Excel with a Web 2.0 Flair!

Excel dashboards can use the Lightbox image technique so popular with blogs and websites these days. Have you seen the effect where you click on an image or a link to a form and the new content pops up in front and the underlying page dims to a dark gray or black? That’s called lightboxing.

Thanks to the always innovative dashboard experts at Juice Analytics who wrote the post, Lightboxing Images in Excel, we get this Excel Dashboard xls file (right click and save xls file) and accompanying image media.jpg (right click and save to root of C drive). Download those files to see the following effect:

Here is the Excel dashboard:

Excel Dashboard with Lightbox Image

When you click on the “Show” button, the speadsheet dims and the following image appears on top of the excel dashboard. To dismiss the image, you would click on it.

Excel Lightbox

The lightbox javascript is called Lightbox2 and is maintained by Lokesh Dhakar.

Be sure to visit the Juice Analytics post to read more about this lightboxing technique in Excel.

PS. Have you seen the latest Dashboard Videos on dashboards.tv?

Tags: Excel Dashboard, Images in Dashboards, Business intelligence dashboard design

Real Time Excel Dashboard Tracks Gas Prices

Dashboard Topic: Excel Gas Dashboard

Web queries have been part of the Microsoft Excel object model for quite a while now, but are not often seen on Excel dashboards. Talented Xcelsius and Excel developer Kalyan Verma (author of the fabulous myxcelsius.com blog), has posted an example of using Excel Web Queries to power an Excel dashboard connected to the American Automobile Association’s database of pump prices of 100,000 gas stations.

The post is entitled Real Time Fuel Gauge Dashboard using Excel Web Query and walks the reader through not only a lesson in the use of Excel Web Queries, but also some principles learned from reading Stephen Few’s Information Dashboard Design: The Effective Visual Communication of Data. Can you guess which specific principles?

Take a look at this screenshot. Click on the image to enlarge this excel dashboard. Be sure to visit the post at the link above to read more from Kalyan as well as see more screenshots of the Excel dashboard. Most importantly, you can download the .xls file for the Excel Dashboard and play around with the Excel Web Query dashboard for yourself.

Excel Gas Price Dashboard

Excel Dashboard using Web Queries to Show Gas Prices

Excel Dashboard using Web Queries to Show Gas Prices

If you are new to Excel’s Web Query feature, consult this introduction from Microsoft’s Office Online resource:

Get and Analyze Data From the Web in Excel

Speaking of Microsoft Online, try this query and look who is the first result! Click on the word “Dashboard” below:

Dashboard Search on office.microsoft.com

Update: As noted in the comments below, we have covered similar gas price dashboards previously. Take a look at Ryan Goodman’s Xcelsius Gas Price Data Dashboard. It even has a Dashboard Spy Video!

Tags: Excel Dashboard for Gas Price Monitoring, Web query excel dashboard, myxcelsius.com, excel gas dashboard

A Beautiful Dashboard Journey

Business dashboard projects are often journeys rather than simple implementations. It’s a journey through a world of choices between the right metrics, the technologies, the data collection challenges and data visualization techniques. Sometimes it feels to us dashboard designers that BI is an exotic land in which our challenge is to find (or create) the right path for our users.

Enjoy the journey of exploration of your business intelligence choices, because, as I hope you agree, it’s the most rewarding part. As Don Williams Jr (American novelist and poet) said, “The road of life twists and turns and no two directions are ever the same. Yet our lessons come from the journey, not the destination.”

You don’t have to take the journey alone. There are plenty of us out in the world of business intelligence visualization that can help guide you. Maybe our advice can smooth your way. The gems that we share perhaps can save you fruitless trips down the wrong paths and steer you away from problem areas. This has been the goal of The Dashboard Spy’s efforts to document business dashboards as I come across them out in the wild. I hope my efforts have helped.

Sometimes we meet fellow travellers on our journeys of discovery that act so graciously and share information of such value that we should stop everyone else and tell them at once about what we’ve found. The purpose of today’s post is to do exactly that.

Tim Wilson, aka Gilligan, has put together a series of three wonderful posts that detail his year-long journey of envisioning, researching, designing and building a corporate dashboard for his company to use in performance management. Everyone involved in business intelligence dashboards must read this tale of a beautiful dashboard journey in which Tim so carefully details his discoveries and shares the struggles and rewards of his dashboard design efforts.

  1. Dashboard Design: An Iterative Tale Part 1
  2. Dashboard Design: An Iterative Tale Part 2
  3. Dashboard Design: An Iterative Tale Part 3

Of course, I said earlier that the journey and not the destination should be the focus, but let me jump right to the destination and show you the screenshot of where Tim ended up. It’s an Excel Dashboard created with Excel 2003 without any addons.

Excel 2003 Dashboard

Here are a couple of observations from Tim:

Some of the keys that make this work:

  • Heavy focus on Few’s Tufte-derived “data-pixel ratio” –- asking the question for everything on the dashboard: “If it’s not white space, does it have a real purpose for being on the dashboard?” And, only including elements where the answer is, “Yes.”
  • Recognition that all metrics aren’t equal –- I seriously beefed up the most critical, end-of-the-day metrics (almost too much – there’s a plan for the one bar chart to be scaled down in the future once a couple other metrics are available)
  • The exact number of what we did six months ago isn’t important -– I added sparklines (with targets when available) so that the only specific number shown is the month-to-date value for the metric; the sparkline shows how the metric has been trending relative to target
  • Pro-rating the targets -– it made for formulas that were a bit hairier, but each target line now assumes a linear growth over the course of the month; the target on Day 5 of a 30-day month is 1/6 of the total target for the month
  • Simplification of alerts -– instead of red/yellow/green…we went to red/not red; this really makes the trouble spots jump out.

Be sure to spend some time with each of Tim’s 3 posts. They contain lots of lessons learned.

Tim – thanks for the great effort. This is a wonderful tale of a beautiful dashboard journey. I’m adding you to top of The Dashboard Spy’s Big List of Dashboard Experts.

Tags: Excel Dashboard, Sparklines, Excel Dashboard Design, Data Visualization, Corporate Dashboards

Excel Tool Tips

A Dashboard Spy reader wrote me to ask about adding comments to his Excel worksheets so that his users would be prompted to take certain input actions. He’s building a series of excel dashboards complete with data input screens.

I directed him to Michael Alexander’s latest Excel Tip: Adding Tool Tips to Cells, a quick but excellent video tutorial on how to achieve a tool tip effect such as you see below:

Excel Dashboard Tool Tips

Continue reading

3rd Place Excel Dashboard is for Pharmaceutical Sales

Executive dashboards are hot IT projects at Bristol-Myers Squibb this year. The sales operations management area at the company has already developed 10 business intelligence dashboards (and 100 operational reports) so far in 2008.

This particular sales force dashboard, developed in Microsoft Excel by Hitesh Patel and Mike Askew of Data Intelligence, was entered into the Microcharts Best Excel Dashboard of the Year contest and won the third-place prize.

Here is the dashboard screenshot. Click on the image to enlarge it. 

 Bristol Myers Squibb Dashboard

Let’s take a look at the background and implementation details of this excel dashboard. Join us after the “read more” link:

Continue reading

2008 Best Excel Dashboard

Excel Dashboard Update:

New tutorial walks you through the design, architecture and construction of this Microsoft Excel dashboard:

excel dashboard tutorial

Click here to see the Excel Dashboard Tutorial details.

Excel dashboards submitted as candidates for the best 2008 Excel dashboard design contest run by BonaVista Systems have been evaluated.

Wade Stokes is the winner of the best excel dashboard with his entry of this International Bank Dashboard. Click on the dashboard screenshot to enlarge the image and you’ll see a dashboard that make efficient use of screen real estate through incorporation of the popular sparkline graphic:

Winner of Best 2008 Excel Dashboard Competition

Click on the “read more” link to read some commentary by the dashboard designer:

Continue reading

Customizing Ribbon Buttons for Excel 2007 Dashboards

Excel Dashboard Update:

Here are some recent pointers to resources that are very helpful to excel dashboard builders and users.

This is an excel sales dashboard template for you to download and reuse:

Sales Dashboard in Excel

This is a video on excel reporting and how a dashboard view can enhance excel reports:

This is the link for future info on these excel management reports:

http://budurl.com/exceldashboards

Building a dashboard with Excel 2007? Don’t forget to make use of the Excel 2007 ribbon. You can add your own custom tabs and buttons to the Excel ribbon using XML or RibbonX code. Thanks to Mike Alexander, author of Excel 2007 Dashboards & Reports For Dummies, there is a freeware Custom Button Builder for Excel 2007.

As this Excel 2007 Custom Ribbon Buttons video tutorial shows, the utility asks you to fill out a table of values and it provides a sample worksheet with your custom tabs/buttons (complete with icons and code that can call your macros).

Let’s have a look at some screenshots I captured from the video:

You can select from the billions of icons on your machine:

Excel 2007 Ribbon Icons - Datapig utility

Click to see more of this Excel 2007 Ribbon Customization utility:

Continue reading

A Look at Building Dashboards in Excel

Update: If you need a quick path to Microsoft Excel dashboards, consider these Excel Dashboard Templates from ExcelUser. They are plug and play, pure Excel worksheets. Just point them to your data source.

It’s not often that you get to look over the shoulder of a talented information visualization expert while he builds an Excel spreadsheet. Jorge Camoes’ post entitled How to Create a Dashboard in Excel allows us to do exactly that.

As Mr. Camoes wrote to me:

Just wanted to tell you about a dashboard I posted in my blog, the “Demographic Dashboard”. It is a fully functional Excel 2003 dashboard that uses population data from the US Census Bureau to show growth and age structures for each country around the world.

With this dashboard I wanted to show that an average Excel user can design a dashboard that fits the needs of his small/non-profit organization for data monitoring. I also tried to follow some visualization best practices (no gauges, no speedometers…).

Hope you can find it interesting.

Take a quick look at this Excel dashboard and you’ll agree that “interesting” is an understatement:

excel dashboard demo

Continue reading