Charts and Anti-Charts

I believe we can all agree that Charts are a quintessential way of telling your story in Excel in a way that will be readily understood and appreciated. We have talked about modifying and enhancing the built-in options for charts, improving on the standard fare that we are all-too-familiar with.

Newer versions of Excel offer new, additional ways of making mini-charts that help to visually expand on often-dry, lifeless data on a line-by-ling basis.  Sparklines are a good example of the newer charting tools that are available.

Not all individuals or offices (corporations are often a bit slow in this regard) have the most update Excel versions to work with.  So what if you are using, (or your audience is using), previous versions of Excel such as 2003 or 2007? Is there a Dynamic Way to provide a Visual Display of the data without using a conventional chart?  Well, Yes, of course, (why else would I be asking the rhetorical question…).  By using the 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 Five Asterisks: =REPT(“*”, 5)

The advantage of using this about this innovative is that it works in Any Excel Version, old or new.  You can therefore create one of these visually interesting displays and send it to other users without concern as to their version of Excel.

For example, 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 calling it an Anti-Chart, since we are not using a built-in chart option) in Column C. We can then insert the following function in our Chart Column C: =REPT(“*”, B2/1,000):

Noting that we are dividing the number in B2 by 1,000, this 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. Presto - Anti-Chart! Give it a try!