Leadership

Add a quick comparison chart to a worksheet

With just one function, you can create a quick chart that lets you compare information, visually.

Excel's a master at charts, but sometimes you don't need all its bells and whistles. Sometimes, you just want to visually compare values. For example, the sheet below lists units sold per person. Because the list is short, a quick glance is all you need to determine that Bill sold the most and Kate sold the least. If that's all you need, a quick sort will display the highest and lowest values in a longer list, but a sort won't give you a feel for the overall performance—you can't compare values.

To get a quick feel for how each value compares to the others in the group, without using Excel's chart feature, use REPT(). This function repeats a character, and you can use it to draw a quick picture, as follows:

  1. For this example, select cell C2.
  2. Enter the following formula, =REPT("I",B2).
  3. Copy the function in C2 to cells C3:C9.

By repeating the character I, the REPT() function creates a bar. Extending the function creates a simple chart.

The first argument, the character I in this case, is the character the function repeats. The second argument, determines how many times the function repeats the character I. The result is a simple chart of sorts, right in the sheet and it tells an interesting story—a story you can't get from the list :

  1. Bill is the highest seller and Kate is the lowest, but you already knew that.
  2. The sales for four of your employers are strong, but four are weak; half of your sales force isn't performing well.
  3. Even though Kate has the lowest sales, the sales for at least two others, Laurie and Alexis, are almost as weak as Kate's.
  4. Lilly's sales are almost as good as Bill's. You happen to know that she's new—with a little more experience and mentoring, she might go far.

This is one of my favorite How did you do that tricks. It's easy to implement and remember, and the results are effective.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

25 comments
catcompare.com
catcompare.com

Really helpful in presenting not overly-done top level information for top management. www.catcompare.com

TsarNikky
TsarNikky

I can't make it work. I enter the formula, then when I try to "copy" it the referenced cell turns blue. One can't "paste special" (Formula) into the following cells. I can't get rid of the blue cell. What am I doing wrong? Bob @ yohe44@sbcglobal.net

melissatutors
melissatutors

Brilliant! Best for comparing relatively small numbers

wehmeier
wehmeier

Thanks for the tip - one I hadn't used before, but will a LOT in my job.

lyambor
lyambor

Though it is a nice trick - I agree with the others the data bars with conditional formatting is a better and more visual way of displaying the bars THe REPT function might be helpful with folks with earlier versions of excel

yibran
yibran

que buen tip, bastante practico para mostrar resultados visuales, gracias.

my99rx
my99rx

Sure that is ok if you want a static drawing. Here's a better idea that extends the original: 1) Highlight both columns including the headers 2) Click on the Excel 'Insert' tab 3) Drop down the 'Pie' graph ribbon and select the second 3-D Pie graph. It auto centres it and it looks great!

martin
martin

I suppose Microsoft has identified this use of easy charts also, since Excel 2010 offers similar functions. But for the 99,5% of non-2010 users this is a perfect solution!

david.white
david.white

This is one of my favourite shortcut chart methods. If you add to this the use of Windows Character Map utility, you can actually use all kinds of symbols for your chart character, change the size and colour and even conditionally format the resulting Bar.

spiras
spiras

is to select the range of values, and then choose from the Home ribbon Styles => Conditional Formatting => Data Bars, and select the pattern of preference. This will place a bar in the values cells themselves. Well worth trying out!

martin
martin

Hi Bob, are you sure you entered the exact formula: =REPT("|";B9) There should not be anything after the formula In the article there's a dot after the formula, but that's because of the text it is entered in. Secondly: if you enter the formula, please close the cell before you start copying (close by Enter or Tab). Then you can copy and paste (also using past special). Good luck, Martin

jody.burton
jody.burton

Not sure what you meant to say, but static means unable to change. In Susan's example, if the sales data changes, so does the "graph". In that sense it is dynamic. People will differ on which type of chart conveys the information best. As much as I like pie, I still have to do some mental processing to compare one salesperson to another with the pie chart. For me, Susan's solution is simpler to analyze...longer is better. I've spent some time with the 2010 beta of Office and have tried the sparklines and other conditional formatting for in-cell graphing. When everyone in my company is on that version of Excel those will be good options. Until then, if I want to share a spreadsheet with co-workers I have to worry about compatibility, something Susan's solution provides.

djones
djones

Hi Managed to do teh symbol (used wingdings) but I couldnt get conditiojnal formatting to work. I set it as anyhting >15 is green but when it was applied even < 15 went green. BTW -- nice tip re the REPT

loan_stores
loan_stores

It's Very useful for maintained the data & easy way for saving the data.

Rick_from_BC
Rick_from_BC

(a) change the font of the repeating character to Britannic Bold - the bar appears solid. (b) reduce the number of lines to 'repeat' by specifying a constant divider - in the example: B2/2 or B2/4. The bars remain proportional, but the display is shorter. (c) change the "|" character color with conditional formatting: Green over 70 sales, orange between 30 and 69, red under 30. This works in Excel Mac 2004 and Excel X, as well as in PC versions of Excel.

danny
danny

Yes, the ability to add in Color Bars, Shadings,and Icon Sets via Conditional Formatting is a great new tool. Here is a link to a short video that I created to "visualize" this topic - truly, you have to see this to believe how easy it is to show the trends in your data. http://www.thecompanyrocks.com/make-excel-data-come-alive-visually/ Danny Rocks The Company Rocks www.thecompanyrocks.com

ssharkins
ssharkins

I'll have to check this out!

dlovep
dlovep

choose conditonal format, then select Formular, put in =LEN($C$2)>10

kkroon_ftb
kkroon_ftb

... I was replying to spiras's suggestion upthread about using Excel's data bars. Those are affected, unless you dig in and make some adjustments (which should have been the default).

Editor's Picks