The Any-Version Anti-Chart

Excel 2010 (and the next Excel generation rumored to be released late this year) has brought new ways of providing Visual Displays of data other than traditional charts. This is a true advance, since creating a dynamic traditional chart can be a bit of a hassle at times.

But what if you are using, (or your audience is using), earlier versions of Excel such as 2003 or 2007? Is there a Cool Way to provide a Visual Display of the data without using a conventional chart?

Yes, There Is! Using the little-know and seldom used REPT function, you can produce an Anti-Chart that can provide you with a display that is immediately understood, dynamic, and easy to create. The REPT function repeats a text string the number of times you specify. For instance, the following formulas returns Nine Asterisks: =REPT(“*”, 9)

The cool thing about this innovative, yet simple technique is that it works in Any Excel Version. You can therefore create one of these visually interesting displays and send it to other users without concern as to their version of Excel.

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

You will note that we are dividing the number in B2 by 1,000 is to keep the scale of the resulting Anti-Chart to a reasonable width. Simply enter the formula into cell C2 and drag it down to complete your visual illustration of your data. Bamm! Anti-Chart!

Cheers!