In-cell graphs in Excel

By Filip Salo; published on August 16, 2006.

You can do a lot of things with Excel. Sometimes it's pretty; sometimes it's not. Sometimes the users come up with better solutions than the developers. Let's have a look.

The trail

Microsoft Excel 2007 (nee Excel 12): Conditional Formatting - overview of what we did, and what's a "data bar"?
shows off Excel's new "data bars" - a sort of in-cell bar graphs. A good idea, but poorly executed. The bars extend _behind the text_, and the gradient fills makes it even harder to get a decent overview of the values they represent.
[Juice Analytics: Lightweight data exploration in Excel](http://www.juiceanalytics.com/weblog/?p=236) (via [Lifehacker](http://lifehacker.com/software/top/excel-tip--instant-incell-graphs-193425.php))

Excel _users_ have found a rather ingenious way to make _better_ data bars than the Excel team did, by using the `REPT` function to repeat the bar character ("|"), resulting in a rather visually pleasing in-cell graph variant.

Some creative commenting ensues, thus leading to...

[Juice Analytics: More on in-cell graphing](http://www.juiceanalytics.com/weblog/?p=239)
with examples of various enhancements, including a demo spreadsheet for download (much to my delight, OpenOffice Calc handles it brilliantly, too).

A few tweaks

After a bit of toying around with examples, I ended up tweaking it a bit:

  1. Instead of dividing values by a constant (to get the numbers of bar characters to use), I divide by MAX($G$2:$G$32) (G2:G32 being the relevant cell range), and multiply by the maximum number of bars I want.

  2. Low values may result in no bars at all, even though the value isn't strictly zero. I use CEILING to round the above value to the nearest larger integer, making all positive values get at least one bar.

  3. Using a lowercase "o" for dot graphs is cute, but using a bullet ("•") is better. Insert it as a special character (U+2022) or, on windows, by holding down alt while typing 0149 on your numeric keyboard. They can also quite favorably be used instead of the bar character for bar-like charts.

Various links

The obvious connection here is Sparklines, Edward Tufte's "Intense, Simple, Word-sized graphics". Infographics make my fingers tingle.

  • Bissantz sparkline tools uses a special font to generate sparklins graphics just about anywhere.

  • A Google search for sparklines reveals all you need to know about them, including sparkline generation libraries for various programming languages.

  • This is apparently what Excel 2007 looks like. Just slap a Hello Kitty sticker on there somewhere, and you're done. (Oh, look, there's another "skin" available too! Because, y'know, you can never have enough of inter-user inconsistencies in the interface.)

  • Sander Viegers, Excel User Experience Designer talks about part of the process behind some charting improvements in Excel 2007. I like the paper sketches best.