In-Cell Spreadsheet Graphs

September 23rd, 2007 by bill · 3 Comments

»How to create simple in-cell graphs in Excel, IBM Lotus Symphony, OpenOffice, and Google Spreadsheets.

 

Edward Tufte has promoted the use of inline, word-sized graphs called sparklines to communicate data more effectively, giving rise to several methods of creating inline graphs within documents, including spreadsheets.

This article describes one very simple way of incorporating dynamic bar graphs in your spreadsheets. It works in Excel, IBM Lotus Symphony, OpenOffice, and Google’s online spreadsheet application.

The Basic In-Cell Graph

Find the data cell you want to graph. This will probably be the first in a vertical series. It will be the reference used within the formula.

Say, for example, you want to graph the value in cell B2 (or B2-B6). Enter the following formula into cell C2 (or wherever you want the graph).

=REPT( "|", B2 )

For Symphony and OpenOffice, function parameters are separated by semi-colons instead of commas. The examples in this article use commas, so just change them to semi-colons and it should work okay. E.g. Instead of the above formula, use the following for Symphony and OpenOffice.

=REPT( "|"; B2 )

The next step is a matter of taste, but you will probably want to change the font of the graph to something like Arial 8 point. Since we are using the vertical bar as our graph element, a smaller font makes for a tighter-looking graph.

Then, depending on your numeric range of values and the general amount of space you want the graph to use, you will probably want to divide the cell value by some constant. This is entirely up to you and your needs. If your value range is between 0 and 20,000, you might want to divide by 500 or 1000; whereas if your values run from 0 to 100, dividing by 5 would make more sense.

=REPT( "|", B2 / 5 )

Now, copy the formula down from C3 through C6. (After each change to the formula, you’ll want to copy the change throughout your graph.)

In fact, you can use a formula to calculate the divider. Divide the maximum value of the graph by something like 40, then divide the cell value by that.

=REPT( "|", B7 / ( 10000 / 40 ))

Negative Values

Of course, you will probably want to be able to handle negative values as well. By simply spreading your graph across two cells and using the IF() and ABS() functions, negative values are no problem.

Let’s say you have a range of numbers in cells F2 to F6 and you want a graph in cells G2 and H2 down to G6 and H6. In G2, enter the following formula to handle negative numbers.

=IF( F2 < 0, REPT( "|", ABS( F2 / 5 )), "" )

And in H2, the following will take care of positive values.

=IF( F2 > 0, REPT( "|", ABS( F2 / 5 )), "" )

Next, format the text in G2 to be right-aligned and red.

Select G2 and H2, then copy them down to G3:H3 through G6:H6.

Just A Little Fancy

You can experiment with various fonts, sizes, colors, etc. to get the graphs you want. One little tweak that’s useful is using the CONCATENATE() function to mark the value at the graph’s endpoint.

As an example, the graph below uses a vertical bar for the value and hyphens for the graph bar itself. Note, that since the hyphen takes more horizontal space than a vertical bar, we use a larger divisor (seven, in this case) to keep the graph at about the same scale.

=CONCATENATE( REPT( "-", B2 / 7 ), "|" )

And the negative/positive graph uses a lower-case “o” for the value and the vertical bar for the bar.

Negative:

=IF( F2 < 0, CONCATENATE( "o", REPT( "|", ABS( F2 / 5 ))), "" )

Postive:

=IF( F2 > 0, CONCATENATE( REPT( "|", ABS( F2 / 5 )), "o" ), "" )

Note that the parameters to the CONCATENATE() function are reversed for negative and positive.

Also, as in the above graph, green sometimes looks nice for positive values.

What Else Can We Do With Graphs?

While much more complex graphing is available with your spreadsheet’s built-in graphing features, this technique provides simple graphs to enhance a boring block of numbers in a spreadsheet. It provides visual cues to the relative values of the numbers and helps to put things in context.

There is more that can be done with graphs like this. In a future article, we’ll look at similar lightweight graphing techniques. Do you have suggestions or any graphing tips you use? If so, please post them in the comments.

Tags: Software · Tips

 

3 responses so far ↓

  • 1 Grant // Sep 23, 2007 at 8:25 pm

    That’s pretty cool. It is one of those things that has been possible to do for a long time, but nobody was creative enough to think of until now.

    Nice find!

  • 2 bill // Sep 24, 2007 at 1:12 pm

    Thanks. Actually, some people were creative enough before now. I saw the basic idea a year or two ago and have been using (and tweaking) it since.

  • 3 Eric // Nov 10, 2010 at 6:46 am

    Hi what about a “line graph” inside the cell, y was readin thisa month of the “pc magacine” and they were talking about somo tips of the new vertion os office (2010) and i saw a line graph in the photo of the story, they did not talk about that in the paper, and i was wandering how to do it in 2007.

    thank’s for any help could give me………..

Leave a Comment


− 4 = three