Wednesday, November 26, 2014

MicroGraphs for All!

As many of you may know, “Sparklines” first appeared in Excel 2010.  Sparklines make it quick and easy to create readily-intelligible micrographs within the cells of your Excel spreadsheets that highlight important details of your data.  They have the same utility as traditional graphs and charts, but they have the additional benefit of being incorporated right next to the data which you wish to illustrate.

Well, that’s pretty cool, but what if your company is still using Excel 2007 or even Excel 2003, (a recent survey showed a significant number of companies still using these older versions), and you want to use some Cool Little Micrographs on your spreadsheets?  What can you do?

A very effective way to make these engaging little graphs is to incorporate an unpretentious formula and a touch of conditional formatting.  Let’s look at how this can be done…

Let’s say you have the Producers of your company’s products in Column A as illustrated below, and in Column B you have the Units Sold each producer has sold. Here is the simple formula you should put in Cell C2 (and then copy it to C8):

= REPT( “l” , B2/1,000) 

For each Approximate Count of One Thousand, the formula puts an old-fashioned Hash Mark, (using a bold, simple font works well), in Column C. With a bit of Conditional Formatting, the result is a clean, professional report that integrates a MicroGraph! Using these elementary graphs makes your data Visually Comprehensible and enhances the user’s understanding of what is being said in your Excel reports.

If you are using a pre-2010 version of Excel (or even if you are using a more contemporary version), try it out sometime and see what you think. It’s good stuff!

Happy Thanksgiving All!

No comments: