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!