Thursday, July 1, 2010

Funky Histogram Anti-Chart

Charts are tremendous tools that visually represent the often dry data that is contained in a table or database. Sometimes it can be a hassle to create one using Excel standard chart-building tools, especially if you want something basic (but still dynamic).

Here is a way to create a Funky Histogram-Style Chart by employing the little-used REPT function. This function repeats a text string the number of times you specify. For instance, the following formulas returns seven dollar signs: =REPT(“$”, 7)

One nice aspect of this simple technique is that it works in Any Excel Version. (Something that can’t be said about the Data Bars feature in versions 2007 and 2003.)

Let’s say that you have a table with Months in Column A, Sales in Column B, and we want our Histogram Chart (let’s call it an Anti-Chart, since we are not using Excel’s typical charting capabilities). We can then insert the following function in our Chart Column D: =REPT(“$”, B2/1,000).


The reason we are dividing the number in B2 by one thousand is to keep the scale of the resulting Anti-Chart to a reasonable width. Simply enter the formula into cell D2 and drag it down to complete your visual illustration of your data. Bamm! Anti-Chart!

4 comments:

Anonymous said...

所有的資產,在不被諒解時,都成了負債............................................................

Anonymous said...

人有兩眼一舌,是為了觀察倍於說話的緣故。............................................................

Anonymous said...

where do you enter this formula

Bob DeLaMartre said...

Thanks for the comment, Steve! You enter the forumula in Column D adjacent to the table.

Best,

Bob