Last year I tracked my region's performance at DonorsChoose.org from week-to-week using sparklines, generated with the data that Lauren my SQL ninja would compile for us. This (fiscal / school) year, the data has multiplied (tracking nine regions instead of two) and my team has grown from 2 to 5. I needed a better dashboard. Here it is:
Over on the right are sparklines for week-to-week trends. To the left of those are Bullet Graphs which basically show a thermometer against my YTD goals. Both are made with Micro Charts which gets the thumbs up from me, especially if you're doing this sort of thing.
One of the articles on their site showed these nifty red dots as Key Performance Indicators, and I wondered how to do them. It turns out you don't need any plug-ins at all, you can just do some fancy conditional formatting, as explained here. The dot is actually a dingbat from Webdings.
But I wanted to take this idea a bit farther. Could I have more than three states to show the severity of the problem? And couldn't I also vary the size of the dot to indicate the magnitude of the problem, in addition to the shade of red? If, say, my Maine figures were only at 56% of my goal, but since my Maine goal was so low to begin with it only amounted to a deficit of a few hundred dollars, I really shouldn't worry too much about it:
Well the problem is two-fold. First, Excel (at least in Office 2003) you are limited to three formats. Second, Excel won't let conditional formatting change the size of a font. Drat! Getting around the first isn't hard:
There are actually four conditional formats that can be specified. The fourth one is the format that is used by Excel if none of the three conditions specified in the Conditional Formatting dialog box is true. (In other words, the way you format the cell to begin with is the fourth format.)
In my case, I formatted the "dots" column white. If none of the three "alert" conditions apply, then the dot's invisible. If one of them applies, the conditional formatting rules turn the dot light pink, pink, or REDOMGREDOMGHELP!!!
Changing the size of the dot merely requires a bit of slight of hand. (Mouse?) Instead of using the Webdings "n" (large dot), I substitute the Webdings "=" (small dot) if the problem isn't large using a simple IF formula where the dot should appear:
=IF(J5>10000,"n","=").
Format the column with font Webdings and ta-da! Tufte Dots.
| Design | Dashboards | Tufte Alert |
