Something cool for the Excel day-trippers
August 18th, 2006 by JTJ

OK, OK.  Maybe we've crossed over some line social acceptability, but this is neat addition to the analytic journalist's toolbox.  My friend Mike Collins tips us off to:

Lightweight data exploration in Excel data exploration in Excel digg:Lightweight data exploration in Excel reddit:Lightweight data exploration in Excel Y!:Lightweight data exploration in Excel


Lifehacker, delicious folks! This post generated a ton of great community ideas. Check out our followup post to see some more ideas and to download a spreadsheet with demos. Thanks.

We often are given a chunk of data in Excel that we need to explore.
Of course, the first tool you should pull out of your toolbox in cases
like this is the trusty PivotTable (it slices, it dices!). But at times
we have to dig a little deeper into the toolbox and pull out the
in-cell bar chart. Here’s what it looks like.

In cell bar charts in Excel

This picture shows some Major League Baseball data. I’m graphing the
number of walks each player has taken. The bar graphs are built using
the Excel REPT function which lets you repeat text a certain number of
times. REPT looks like this:


For instance, REPT(”X”,10) gives you “XXXXXXXXXX”. REPT can also
repeat a phrase; REPT(”Oh my goodness! “,3) gives “Oh my goodness! Oh
my goodness! Oh my goodness! ” (my daughter’s an Annie fan).

For in-cell bar charts, the trick is to repeat a single bar “|”.
When formatted in 8 point Arial font, single bars look like bar graphs.
Here’s the formula behind the bars:

The formula behind the bar

What are some practical uses of in-cell bar graphs? For starters,
they offer a good way to profile a dataset that has hundreds or
thousands of rows. Here’s a picture of in-cell bars compared to a
standard excel bar graph for a dataset with about 500 rows. It can be a
lot easier to scan the results when they’re in-cell.

Exploring tall data with in-cell bar graphsExploring the same data with an Excel bar graph

Another usage is lightweight dashboards. The report below compares a
number of metrics for players using both in-cell bar graphs as well as
conditional formatting. The conditional formatting highlights the top
25% of each metric in green and the bottom 25% in red but that is a
story for another day.

The formula behind the bar

Leave a Reply

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

»  Substance:WordPress   »  Style:Ahren Ahimsa