More on Excel in-cell graphing

We received an enthusiastic response to our post on in-cell bar graphs in Excel. The community quickly explored every edge case. I want to highlight some of the great ideas raised.

Henk was first out of the gate with a great suggestion that two columns could be used to show positive and negative values. What he's thinking of looks like this:

Using two columns to show positive/negative values

Clint compares this approach favorably to the upcoming Excel 12's gradient fills. Here's a comparison:

Excel 12's in cell bar graphs—the gradient fill is poor designExcel 12—the gradient fill is bad infographics

In cell bar graphs using rept--looking goodIn cell graphs--better

Benjamin Selmer, derek, and Chris Grant had some nice ideas to improve the look of the bars by choosing different fonts or characters. Fonts that may work include Niagara Solid and Stencil.

You can also use characters other than a bar to nice effect. Here's a dot graph created by repeating spaces terminated with the letter "o".

An in-cell dot graph

Here's an anchored dot graph created by repeating dashes terminated with the letter "o".

An in-cell anchored dot graph

You can also label the bar with a value by concatenating the value after the bars. Remember, you can use "&" to glue together text strings in Excel formulas.

A labeled bar graph

You can change the width of the bar by dividing the value your graphing by some numbers.

Changing the width of bar graphs

Some folks raised the interesting (some would say perverse ;-) ) idea of using this technique to create Gantt charts. Here's an example. I'm using the fact that the width of a space character is exactly 1.5x the width of a bar character in Arial to make this work.

A lightweight Gantt chart

Finally, here's an Excel spreadsheet illustrating all these techniques.

Excel in-cell graphing ideas.xls

It's humbling to see the explosion of interest and all the great and diverse ideas. Thank you to all contributors.

This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United States License. All source code is released under a BSD License unless otherwise specified.

86 comments | Show all comments only the last 5 are shown


July 29, 2008
mac millen said:

We can convert the bars into columns using Excel's often forgotten Camera tool - in Excel 2003 you'll find it in in customise under Tools.

Construct the bars as in the instance spreadsheet then simply highlight them, click the camera and paste the resultant picture. The picture will update as the data changes but can be rotated or scaled as necessary.
__________________________________________________
Mac
<a href="http://www.clickidentify.com">Wide Circles</a>


August 6, 2008
mel said:

Nice piece of work ! It takes genius to provide "simple effective solutions."
I've been using your approach for gantt charts to quickly asses project schedules. Where MS project is overkill.

Thank you !


September 27, 2008
minseok said:

surprising! nice inspiration. thank you!


October 3, 2008
alice said:

This is fantastic. Thanks for sharing! Agree with Wallace's comment: the product looks polished in Haettenschweiler font (10 pt)


October 28, 2008
shirisha said:

it is a fantastic idea. we can do short work instead of the heavy sheet working.thank tou

Your name

Email (optional, will not be shared)

Type the word "juice" (required to confuse the spammers)

Your comment


Add a comment