This is the most useful thing I've found all day. Thanks.

How long have you been sitting on this little nugget?
A much better presentation than the gradient cell-fills in Excel 2007!

Smooth trick, guys! Maybe the readers are interested in following.

You can extend this by conditional formatting, as you put, but another way is making bars growing to left or to right, depending when they are + or - values. Easiest way is to use two neighbouring columns, the left one for the negative values, the other one for the positive values. Use =IF(value0, REPT("|",value),"") for the positive column. "value" is the reference to the cell containing the repeat factor in the same way you described it. Make sure to right-align the column containing the "negative" bars, and to left-align the "positive bars. To increase the dramatic effect, format the negative column as red, the positive column as green.

Correction!

Reading over my post , I noticed that a line is missing. Maybe it was coincidentally deleted (it's approaching midnight here; I am not so sharp). Also ">" (the "larger than sign" disappeared so I write it in words to correct it.

Before "Use =IF" it should read ... For the negative values, use =IF(value "smaller than" zero, REPT(”|”,ABS(value)),""). Use =IF(value "larger than" zero, etc) ...

I can repost it if this looks interesting, but looks too messy.

Chris -

Nice demonstration of "less is more" and "keep it simple stupid". This is one of those elegantly simple approaches that everyone always forgets.

Wow, very cool. Simple and useful. Thanks!

Seriously, I don't even use Excel that much, and I am very impressed by this. What a superb tip, and very clearly and simply explained. Thank you very much for taking the time to do this, I really appreciate it :)

Very usefull trick!

You can also use ASCII codes 177-178 or 219-223 instead of the pipe character. ASCII code 219 will give you a solid bar. Simply hold down the alt key and type 219, then release the alt key. Do the same for any other ASCII code. This works in most applications including Excel.

Do a google search for "ascii table" and you will probably find some nice tables of all the ascii codes.

So simple! I knew about making little bars with REPT "X", especially for putting into plain text emails, but it never occurred to me to substitute little narrow characters for higher data density.

With a bit more effort, some spaces, and two or more symbols, you could have dot plots comparing the values of two cells.

With the appropriate width spaces for "task start", and bar-symbols for "task duration", you have a simple Gantt chart! In Arial Unicode MS, the four-per-em space (U+2005) seems to match the bar symbol for width.

I used this today. Thanks. Have you found a typeface/size that works a little better than Ariel 8, i.e. where the pipes are so close that it is more seamless? (but I am not complaining. It is fabulous, can't wait to do my 10 am presentation.) Robbin

Wow. I have never seen this anywhere.

I\'ve been playing with this since yesterday, and just thought I\'d throw out there that you can achieve some nice ancillary effects using rept and concatenate, the simplest being adding a value label to the beginning or end of the bar.

The fonts Stencil and Niagara Solid give solid bars.

Oh my goodness, this is wonderful. And I thank you for showing us the ways of excel.

Oops, sorry. Niagara Solid LOOKS solid at small sizes but isn't at large sizes. Stencil is definitely solid. Bold is bigger, too.

I was never satisfied with REPT because of too much whitespace between characters so I had to slap my forehead when I saw how great your example looked. It's the font, dummy! It also got me thinking about the problem of representing negative values. Two columns is one way. Here is a single column solution:

=IF(a1>0,REPT(" ",M14)&"o"&REPT("|",a1),REPT("|",-a1)&"o"&REPT(" ",-a1))

The trick is keep everything vertically justified. Use center justification and find a font that is monospaced but narrow or where the SPACE character is the same width as the indicator character. I used Agency FB, the first font in my list!

o|||||||||
|||o

Oops. this is the formula if the value in question in in cell "a1"

=IF(a1>0,REPT(” “,a14)&”o”&REPT(”|”,a1),REPT(”|”,-a1)&”o”&REPT(” “,-a1))

Art, maybe I'm being dense, but can you explain what value is being called by either the M14 or A14 cell reference?

That's great. Thanks!

Britannic Bold does it solid too, but whomever suggested Stencil and Niagara is smart because it makes a shorter bar within the cell. (Or course, if your numbers are small, Britannic Bold is better.)

Another wonderful application here is Gannt chart work. So you specify the task in one cell, the number of days (weeks, months, whatever) in the second cell and then the third cell has the picture. And then there is no reason you can't color the pipes a different color for different tasks or phases...

Gantt Charts can be better made with a stacked bar chart where you hide the bars you just use for positioning the bars. E.g. Jon Peltier has some examples. [http://peltiertech.com/Excel/Charts/GanttChart.html]. It can be dynamic, and linked, with a few tricks. But for some "quick and dirty" Gantts in not too complex projects this REPT Charts can be good enough, indeed.

What a super tip! I have to scan lots of test data at the College where I work - this will save lots of eyestrain.
I'd love to see the conditional formatting solution you have devised for the percentile ranking.
Many thanks for the tip

Many Thanks.

This is a real gem of a tip. Quite often when I am in a lull between projects I will research advanced tips and tricks for MS Excel and MS Word. This one is quite a find. Simple and effective. I appreciate you taking the time to share this and all of the commentary and revisions by the group. Big kudos...
...Matt

Yes, Chris, this is what I had in mind. Now we are in exploration mode, it jumps to mind to make more complicated tricks to analyze and visualize data.

- You can calculate the average score of a data series and use these + / - REPT Chart concept to show the deviations (so instead + / - around zero, you use the same around the average of a data series).

- A second example is to make a so-called Tornado chart. This kind of chart is used for comparing two populations, e.g. responses from women vs response from men, or Europeans versus Americans, etc. It can be done with the Excel bar chart (with some tricks), but with this REPT Chart it goes quicker and for some quick analysis it works wonderful.

- Other symbols may be indicated in particular cases, e.g /// and \\\. Or a solid square, or .. whatever one's creative mind comes up with.

- Colour can be second attribute - e.g., to indicate the topic or product.

The next level is making simple sparklines ?

To Clint's question:"Art, maybe I’m being dense, but can you explain what value is being called by either the M14 or A14 cell reference?"

You're not dense. I'm being a sloppy cut and paster. The only cell reference in the formula should be "A1," the cell containing the value we're graphing. Sorry.

-- art

Chris:
Sorry for not being too clear re #1. Here is the idea.
Suppose you have a list of values, say for simplicity sake the length of all women in your home town. You calculate the average (AVERAGE(A:A), command in Excel), and for each woman you let Excel calculate the difference to this average (Length MINUS Average(list)). These difference values (either pluses or minuses) you plot with this REPT concept (left aligned for the pos values; right aligned for the neg values, in adjacent columns). You now visualize the difference to the average. You can sort of course. Basically it works same as neg/pos related to ZERO - here it is against the average. You can take other pivot values (the mean for instance).
A silly example of course (who wants to know the women's length? the colour of their eyes is far more important!), but I hope it's more clear now.

Henk -

Re Gantt charts, you can also do quick and dirty charts using conditional formatting of cells, which I show (along with simpler stacked bars) here:

http://pubs.logicalexpressions.com/Pub0009/LPMArticle.asp?ID=343

Jon Peltier, Microsoft Excel MVP
http://PeltierTech.com

For those of you in a Lotus Notes Or Domino environment the formula is basically the same.
@Repeat("l";NumberField) for a Number Field
and
@Repeat("l";@TextToNumber(TextField)) if a text Field

Format the col as you like.

This is BRILLIANT! I would spend time thanking you but I must now go show off to my colleagues....

Great Tip, loved this feature

Now I will start using Excel again :) Very cool tip. Thank you.

sweeeeeeeeeeet!

Very nice. Charting-lite. I completely agree with the "less is more" and "KISS" sentiments above.

Well done.

Great stuff! Really simplifies adding a bar chart. Showed your "trick" to my colleagues - they loved. Simplicity wins out every time.

Hi,
I'm new to this. I've been using SPSS for these kinds of things. But I like the excel formula. How do you get that vertical "bar" --is it the capital "I" in Arial?
Thanks JD

This is not just an Excel thing, you can do exactly the same thing in Open Office, which unlike Excel, is free :-)

outstanding tip!!

BTW: lower case 'g' in webdings font makes an "emphatic" solid bar - it's relative to squaring differences for exaggerating the variance. It's only really useful for data with minimal variance. If there is an outlier it's runs off the "page" relative to the other values.

Freaking sweet way to do data.

I'm beta testing office 2007 excel (like Clint metioned above), and they have come up with something like this actually, but have many different options for it. Go to Conditional Formatting, Data Bars, Color Scales or Icon Sets, and that gives you a wide-array of choices.

Thanks for the tip though - I like it a lot and will use it!

The Webdings and Wingdings fonts are great for this. You can create solid bars with Webdings "g" for example. Or you can be more creative and have dots or shapes or whatever. Also, the arrow graphics are good for replicating the Excel 2007 arrows.

Very very nice excel trick. Thanks so much. I have lots of uses for this!

This is cool. Thanx for sharing.

HOly shnikeez i am gonna rock my presentaytion tommorow thanks!!

ps- can i use this w/o bein suede?

WOW - this is an awesome tip - very useful for creating mini dashboards - as you mentioned. It's funny, but I had just spent 2 hours struggling with pivot tables to get it to show some data the way I wanted - finally I got frustrated and went to digg.com to take a break and found this!! It solved my problem - I'm using it in conjunction with vlookup to create a dashboard that shows how metrics are changing by fiscal week!!
Thanks

I'm sure this is not news...but if you want smaller bars...such as when using the webdings trick....click on the font size drop down and manually type in a 6 or 4, etc...and press enter for a smaller font than whats in the drop down selection.

and oh yea...great trick! :-)

This is great.. I love it.

A few thoughts.
1. to get more accurate spacing on use the background color of the cell to hide some of the missing data. This would allow you to make the the gantt charts easier and the Positive/Negative view could be done in one colum.

Here is the best description I can come up with. Not sure if this will take inlike HTML but here it goes....
||||
||||||||

The first line will show 4 red lines. The second line should show 4 black lines shifted over exactly 4 lines invisible white lines.

2. Creating an addin for this would make it nice. so that is it a function that can either graph a line, or do some comparision between several selected lines.

3. You could also rotate the cell to make it a vertical chart

Very interesting. I'd give it <a href="http://keith.hostmatrix.org/testbed" rel="nofollow">a try later</a>.

This reminds me of the In-Cell line-graph I put together some time back:

http://www.dailydoseofexcel.com/archives/2006/02/05/in-cell-charting/

I CANNOT WAIT to use this trick the first chance I get and impress the sox off of somebody. Seriously, I can already think of a few sheets I maintain where this would be really handy.

I would have liked to see just one formula demonstrating the conditional formatting technique. Can anyone who knows about conditional formatting maybe post an example here?

This is a great tip for integers. Any idea how to tweak it for fractions/decimals? Something like "4.5" is giving me four ticks. I lose the extra "1/2 tick."

Giving away my age but this solution is very similar to how we used to print graphs back in the days of line printers. Excel does, however, offer a lot more options for formatting than a line of x's or dots.

Extremely useful exploration.

Wow..thanks for sharing. That's definitely a helpful one!

If you sort the rows on the column that drives the bars, you'll get a "Pareto chart," which has the tallest bars at either the top or the bottom.

This makes it easier to see not only the most frequently occurring items, it also makes it easier to see how quickly the frequencies fall off from the peak.

And if you're trying to compare bars in different columns, it helps to have one column that's ordered. For instance, if the frequencies in two columns are reversed, it'll jump out rather than remain buried in the details.

I improved the graph by including negative values. Assuming that the Data is in column B, the negative numbrer formulas in column C are:
=IF(B60,REPT("|",B10),"")
Then all you have to do is to justify to the right column C !

Best regards,
Ray Payette

Missing code:
The positive numbers in column D have this formula:
=IF(B10&gt;0,REPT("|",B10),"")

Best regards,
Ray Payette
www.excelhawk.com

The negative formula that was incorrectly read is:
=IF(B6

There is a problem reading the formula.
The positive formula is the same as the negative formula except that the greater than symbol is replaced with the less than symbol.

Best regards,
Ray Payette
www.excelhawk.com

This is great. I have always wanted to do this sort of thing for probabalistic decision analysis applications. Thanks.

This is really great! Don't know if anyone would find this useful, but you can also make the bars vertical by formatting the calls so the orientation is 90 degress. I can see using that for final results, although you lose the ability to sort or filter.

Holy wow. I've been searching for this solution for literally months, under excel sparklines, inline charts, etc... and ended up inventing a kludgy method, by basically redundantly creating a grid of values of the same resolution of my desired display, then using conditional formatting on an adjacent grid; i.e. if my data in A1 is 37, i set up 10 cells in a row with formulas of is A1 less than 10, is A1 less than 20, etc..... I only mention this because of its rediculosity.
Thank you.
Do you know of a way to include font size or background color in your 'lightweight data exploration'? any suggestions appreciated.!

great tip ... so simple ... but therefore very powerfull. Should make scanning through long rows of data much easier. Thanks!

xaei, you could use vba to build case statements - this is a generic workaround to the limits on conditional formatting in the current versions of Excel - I bet the Juice guys would be more than happy to discuss writing script-based case statements for this kind of scenario.

right guys? Wink, wink. Nudge, nudge. Know what I mean?

This is great -- it solves a lot of problems with the axis labels on rotated bar charts.

Has anyone figured out how to put a numeric data label on the right hand side?

I was able to do it by copying two columns with the bars and labels from Excel to Word, then converting the table to text with a space separator. But I can't figure out how to do it with just Excel.

Great,
however when I tried it first it did not work. system does not recognize the REPT-function
After I've visiting your site the same code does worked !?
Now again it does not recognize the REPT-function.

I am using the MS OWC 10 pivottable.
Any clue what to do to keep it stable and why this happens? dll's??

This information is really handy! Thanks for posting the article.

I have also worked out a Pareto chart...
=IF(F4

Gary, if you check out the follow up post <a href="http://www.juiceanalytics.com/weblog/?p=239" rel="nofollow">here</a> and download the example file you\'ll see an example of how to append data to the bar chart (essentially using concatenate to build out the bar chart with a data label)

nice tip, it was the old way to have bar graph in Visicalc
http://www.bricklin.com/history/refcard5.htm

rufus: just a hunch. Did you put =before the REPT function? Secondly, you need to put the symbol to be repeated between double quotes. e.g. =REPT(a,5) returns #NAME? but REPT("a",5) produces /aaaaa/.
HTH.

One thing i noticed is if you use "|" combined with sub/superscript you get excellent looking tight bars.

So sure enough i couldn't duplicate what I just said in another workbook...until i realized you need to drop the zoom to something below 100%...anything sub 100 will give you solid bars when you use sub/superscript. So drop it to 99 if you want all the sizes to remain constant.

How do you caluclate the difference between times when formulating employee time records? For instance: An employee works an 8 hour day in regular hours, and then 3 hours of overtime, which gives him 11 hours total for the day. When I insert 11 hours, I want a formula that will show me 8 hours are regular and 3 are overtime. Can it be done?

Thanks for the formula response; I must be doing something wrong. It comes back with an error of an improper formula. I will keep playing around with it, though. I was able to get it to calculate total hours between two times. Maybe I can look for a formula to subtract 8 hours from the original formula that gave me the total hours of 11?....

Here is a slight change to the formula in Mr. Steinmetz' submission which requires a cell defined as MaxAllowed and the cells can then be "left justified" which is the usual default:

=IF(R6&gt;0,REPT(" ",MaxAllowed)&amp;"o"&amp;REPT("|",R6),REPT(" ",MaxAllowed+R6)&amp;REPT("|",-R6)&amp;"o")

Arrrrg, there should only be one space in the first REPT.
Sorry, I must've hit the tab or something!

I first saw this technique in an article by John Walkenbach in the July 1998 issue of PC World magazine. In the article, he attributes the method to a Keith Hermann of Raleigh, North Carolina.

Nice trick.... we say in french Malin :-D

This works in Google Spreadsheets! An easy way to hack up a graph. (GSS doesn't support graphing yet)

What is a spreadsheet again?

Great tip! Can't even begin to list the uses for this!

You can add something like . . .

=REPT("|",A2/MAX($A$1:$A$9999)*CELL("WIDTH",B2)*3)

. . . so that the chart fills the entire width of column for the maximum value.

(Needs F9 after changes to the column width, it seems)

Great little tip!

Is there any way to format the "I" colour?

I want a single 'total' bar chart for a ranking, but shown such that you can see the composition from the 3 cells it is a combination of.

i.e. cell '1' in red, cell '2' in blue, cell '3' in black, but all shown as a single total in your one cell graph.

If you press down your "ALT" key and type in 219 on your numeric keypad you get the symbol █ whih makes the bar graph looks much more like a bar graph. Albeit a bit larger but I think a bit cooler

[...] As an example, let say you&#8217;re trying to decide whether to go skiing this weekend. You&#8217;d begin by listing the rewards - such as fun, seeing friends, etc. - along with a weight on a scale from 0-5 on the importance of each reward. Next you&#8217;d list the risks - you can&#8217;t ski, breaking something, death, etc. - and the weight of each risk. The worksheet uses a simple calculation to determine the outcome, and displays a message along with a simple bar graph of the results. [...]

[...] Add visual eye candy to your Excel spreadsheets (workbooks, whatever) with an elegant bar chart technique from Juice Analytics. In addition to the basic bar chart and dashboard described in the initial post, they follow up with more styles, positive/negative and Gantt chart variations, and a downloadable spreadsheet with all of the examples. [...]

[...] Weitere Informationen siehe unter Lightweight data exploration in Excel und More on Excel in-cell graphing, außerdem gibt es eine Excel-Datei zum Download. [...]

The concept of text sized graphics was made popular by Edward Tufte. He coined these "sparklines". A company called Bissantz created several add-ons for excel (and MS Office in general) which create very beautiful text size graphics. http://www.bissantz.de/sparklines/ There are several versions ranging from "pro" to "free". Biggest downside I see to this approach is that it loads/requires some special fonts. Meaning, if you forward your spreadsheet to a colleague who doesn't have the sparklines add-on, it won't display as intended.

Also, I loved the comment suggesting the use of a calculation to gauge the width of the cell to make the width of the graph scale appropriately. An alternate suggestion would be to determine max width desired manually (I find a rept of 50 using font 10-Stencil to be satisfactory). Then in the formula, we simply find the max value of the range being graphed and divide each graphed value by the max value and then multiply by 50.
=REPT("|",A2/MAX($A$2:$A$20)*50)
Note: Above formula is for a graph in cell B2, where value being graphed is A2 and range of values being graphed is A2 through A20. The formula is then copied from B2 and pasted to cells B3 through B20. Change the 50 at the end of the formula to adjust max width.

With regards to Henk's tip,

You can concatenate the "charted" figure to the bar chart with the &amp; operator

assuming that data is in cell A21
negative side (E21): =IF(A210,REPT("|",A21)&amp;" "&amp;A21,"")

[...] Lightweight Data Visualisation in Excel Not all data visualisations need to be complex. This technique for Excel creates easily comparable data using only conditional formatting and a simple command to repeat characters. [...]

[...] 2. Gráficos de barras en la propias celdas [...]

[...] The Juice Analytics spreadsheet for effective graphical display in Excel [...]

[...] 2. Gráficos de barras en la propias celdas [...]

Visual Representation of Data

GREAT ...! Bar Graphs with characters such as the | character or -, *,^,, ... can make for very meaningful presentation of data. I recall doing this for the first time with +/- characters in Lotus or AppleWorks even before the charting capability was made available in those programs. I have implemented positive and negative representation by putting positive data to the right and negative data to the left. Use of Conditional Formatting of the data to show positive and negative data, or data larger or smaller than a certain amount, such as a quoted price, set sales quota, etc., in different colors, can be very meaningfuld visually.

I found the discussion in this weblog very interesting and very meaningful. Enough chatting, now back to the drawing board.

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
http://www.energyefficientbuild.com

This is a spectacular tip! thanks!

I'm having a hard time getting the bars to work with negative values. Can somebody please email me the correct formula. To help things along, my negative value is in cell H41. Thanks..

email: daniglesmythe@gmail.com

Hi Dan:

I just saw your post -- sorry about such a long delay in writing back.

If my numeric value (positive or negative) would be in cell H41, then I can use the following formula in cell I41

=REPT($I$1",IF(COLUMNS($A:A)=1,MAX(0,-$H41),MAX(0,$H41)))

to get my character graph for negative values.

Then I drag the formula in cell I41 to cell J41 to get the character graph for positive values.

In the above formulas cell $I$1 houses the character that I want to use in making my in-cell graphs in cells I41 and J41 for numeric values in cell H41. The character in cell $I$1 can be '|' (the pipe character) or for that matter any character with your desired font, color, and alignment to suit your fancy.

Let me know how it goes. If it will help I can post a sample spreadsheet on http://spreadsheets.google.com

Yogi Anand, D.Eng, P.E.
Energy Efficient Building Network LLC
http://www.energyefficientbuild.com

HEy, that really neat!
I love this trick!!

I'm so excited! This helped me figure out how to do a tornado graph for a simple list of data: insert a column for ABS values, sort the data to be graphed by the ABS column and then do the REPT process for the neg and pos numbers as described above. Thanks so much!

Wooo hooo .... just love this one. I'm off to play with all the variations - thanks! :0)

what a simple and brilliant adaptation of a built-in command!

this is a great feature, kick butt! i can't believe i didn't know about this one.

Thanks a lot for this topîc and all the comments

I made a summary (in french) there :
http://www.xlerateur.com/?p=137
and the related excel file :
http://www.polykromy.com/nl/nl74/reporting.xls

I am using Excel since three years. And this is one of the most amazing things I came to know. Kudos to the writer.

Kanwaljit

Two improvements over the technique described here: better resolution and solid-looking bars that show better at different font sizes.

If your values are integers in a range 0-9 or so, you can use the REPT formula as presented there, and perhaps you like the dashed type bars, so the formula as shown would work perfectly for you. If not, keep reading.

For larger values, when you reduce them to a 0-9 range by dividing by a factor, you can use the decimal part to show a half character, having effectively 20 steps on your bars, instead of just 10 you would get with the simple “|” character. The trick is to use unicode character 0×2588 (dec 9608) that will show a full block, and character 0×258c (dec 9612)that will show a half vertical block.

To get those unicodes you can hold and press the Alt key (Windows PCs), then type 09608 or 09612on the numeric keypad, then release the Alt key. For most, this should work with the default input methos. Unfortunately this method is not universal and depends on selected settings and input methods. You can just go to a Excel cell, choose the menu “Insert->Symbol” and type 2588 or 258c on the box at the bottom reading “Character code”

Finally the formula to use is

=REPT("█",TRUNC(C49/scale))&IF(MOD(C49/scale,1)>0,"▌","")

For more details, check my post at http://jcandkimmita.info/jc/2007/07/business/tools-methodologies/yet-another-in-cell-excel-bar-chart-technique/

Anyone know how I can get Lotus Domino to produce lightweight bars only on the Totals rows of a view? I have a count column that is simply the number 1, hidden for detail rows, and displayed for all subtotals. I would like bars to go with this, but only for the subtotals, not for the detail rows.

Luc Betbeder commented at #34:
<i>For those of you in a Lotus Notes Or Domino environment the formula is basically the same.
@Repeat("l";NumberField) for a Number Field</i>

Wow, this is a great teip. THX!

...or should I have said "tip"...anyway, good job!

I don't use Office products for anything (with the occasional exception of Word), but this was so awesome, I had to stop for quickie: Nice!

fyi: I can't see the right side of this textarea box, hope you get legible words.

I work a lot with Excel, mainly in order to manage data - often with very complex sets of formulas... and this tip is really a good addition to my toolbox of tricks.

Cool find, nice tip.

This is an awesome tip, but for some reason, I can't get my bars to "sort" into decending order for a pareto chart. For some strange reason, when I select the rows, and try and sort them, the values change, and some bars do not display.

here is an example of the format for my rows:

=IF(J33=0,"",REPT("|",J33/10)&" "&J33)

when sorting, some of the actual cell values (ie J33) change to some other value...say J31, which totally screws things up.

How do I set up excel to automatically line graph the preceeding 12 months of data. I have a spreadsheet which needs to graph 50 charts (rows)of data every month

Excellent tip. Thanks for sharing...

Hi Guys

Thanks for such a useful Tip. What I would like to know is if there is a way I can make the width of these bars the same as the cell, without changing the font size. Keep up the fab work!!!

Thanks for the nice tip.