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:

http://www.juiceanalytics.com/weblog/?p=236

## 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.

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:

=REPT(text,number_of_times)

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:

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.

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.