analysis, 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.

This entry was posted on Monday, July 31st, 2006 at 2:30 pm and is filed under analysis, excel. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

---