Smart Dashboard Ranking Tables

Chandoo and Robert over at the PHD blog have a nice a 4 post series of posts about Creating KPI Dashboards in Microsoft Excel.

I really recommend you to read Robert’s articles. Having scrolling in your sorted table is just a really smart addition to your Excel dashboard.

Yesterdays post was about Adding One Click Sort:

Continue reading Smart Dashboard Ranking Tables

Heatmap Tables with Excel

The following article shows how to create Heatmaps in Excel 2003, for Excel 2007 & 2010 please see the updated article “Heatmap Tables with Excel – Revisited

This Heatmap Table shows you the revenues and the discounts of a company over the course of one year per product group. The size of a bubble shows the revenue made in a particular month and the bubble color shows the discount rate given. The discount rate has been encoded as a range of green colors, ranging from a light green, for low discounts to a dark green for high discounts. The years and product totals are shown at the right and bottom as an integrated part of the table.

image

Tufte often talks about the integration of number, images, and word, and I think he’s quite right. A way archive this in Excel is to integrate charts into tables, so called graphical tables, a very effective means to archive More Information Per Pixel. I already wrote about graphical tables here, here, and here.

 

Continue reading Heatmap Tables with Excel

Interactive English League Bumps Chart

This bumps chart shows you the English Premier League 2007 – 2008. It allows you to highlight and compare two teams by clicking a team in in the table or a line in the bumps charts.

image

 

Interactively highlighting a data series in a large data set is very powerful. All lines in the data set are set to light Gray to show you the big picture and the patterns and general tends in the data set. Once you have identified an interesting data series you click it and we highlight it with a bright, saturated color. This has the effect of bringing it into the foreground and allows the user to see the details in the context of the other data series.

As a bumps chart has the lines equally spaced on the value axis you can put an Excel table next to the chart that serves as a legend on one side, and as a detailed ranking table on the other side.

 

Continue reading Interactive English League Bumps Chart

Hyperlink Legends to Highlight a Series

In a recent post Jon presented a way to dynamically hover over the chart legend to highlight a data series. Jon’s method is very smart, as it really shows the capabilities of the event rich Excel chart programing model.  The downside of this approach is that it requires you do code some VBA and only works with chart sheets. The chart events also work in theory for embedded charts, but you have to activate the chart, in order to make Excel handle your events, which is one click you want to avoid.

So I picked up Jon’s idea and tried to combine it with ParamLink. In  The Missing Link I introduced the (free) ParamLink Add-In. It implements a new hyperlink formula ParamLink(). When the hyperlink is followed the formula can set cell values or define names.

image

 

Continue reading Hyperlink Legends to Highlight a Series

Excel Dashboard Competition: Bank Dashboard

This blog post is the first in a series of blog post that features the winners of the 2008 Excel dashboard competition.

“A dashboard is a visual display of the most important information needed to achieve one or more objectives; consolidated and arranged on a single screen so the information can be monitored at a glance.”

Stephen Few, Information Dashboard Design (2006)

The dashboards were 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

Furthermore we honored the technical aspects of the dashboard, did it use effective (Excel) techniques for

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

Today we will review the winning entry, Wades Stokes Bank Dashboard:

image


Continue reading Excel Dashboard Competition: Bank Dashboard

The Dashboard Squint Test

Before we go and review the 2008 Excel Dashboard Competition Winners I have to make you familiar with  the Dashboard Squint Test.

Software usability experts and web designer use a quite effective way to assess the organization of a web page or a user interface, the so called Squint Test. You squint your eyes and make an assessment on the overall layout, of elements that stand out, the visual balance and other characteristics of an effective user interface.

SquintTestScreen0001

 
This test can be easily extended and applied to dashboards. Squint your eyes and assess the overall layout.

SquintTestScreen0002


Continue reading The Dashboard Squint Test