BTW, just a couple more edge cases that folks might find useful.
1. Vertical Bar Charts (text-align 90 degrees)
This is a bit cludgy since you either have to adjust the row height or merge cells to create space for the vertical chart but it does work

2. Simple funnel visualization (each step of the funnel on a different row).
This is a great simple way to replicate a funnel visualization in Excel especially when the alternative of doing it in a standard Excel chart can be ... challanging. Also, it provides a simple and effective way to compare segmented funnels side-by-side.

Folks, I posted a reply, but got the reply directory wrong - they are in the original "lightweight .."

Hello guys, have been observing your excel explorations with great interest and I have got a small query...How does one create an anchored dot graph for the bars which grow in the negative direction ?

What are the advantages of in-cell bar graphs vs. simply dropping a horizontal bar graph next to the data? I'll posit a couple. I typically put narrow excel charts next to tabular data and it looks good but but requires finesse in positioning the graphs next to the data to make them line up. So far, this is the only way I've found to get this kind graphic next to pivot tables. Unfortunately it is completely intolerant of changes to the table.

Here's a challenge question. Is it possible to make an in-cell bar graph formula that's a calculated field in a pivot table? Excel applies its pivot summary calculation after applying the field calculation, making the resulting cell #VALUE.

Art

This has been a great discussion so far! Thanks for the original post.

I agree with the GETPIVOTDATA comment, Chris. I handle those types of calculations in the same manner.

For those wanting to play with Clint's idea of vertical charts, instead of using the text align approach, which is indeed "cludgy", try using a camera object rotated 270 degrees. Same end result, but much less work.

I can post details of the camera object if you're not familiar with it.

Art, try to make a bar chart of a list of 100 data lines or more ....
The brilliance of the in-cell graph is that the human brain is unparalleled in recognizing patterns. While spotting numbers are difficult in lists, the bar lengths immediately catch the eye.
It's not to say these graphs are everyhting. They are just another weapon in our armory for data visualization. Thanks to the Juice Analytics Team this concept is unlocked.

Kruncher:
The camera tool is great. It is hidden in Excel, but OUT in 2007!
But Excel has so many tricks most of us don't know. Like zooming with Ctrl pressed + mousewheel (also works in XP, even in this blog view!). Or as line break in a cell - took me ages to find out. Or right-clicking the "video buttons" in the left under corner, enabling to navigate a multi-sheet workbook easily, or... Sorry, I am drifting away from the discussion. If MSFT would only be more user-driven....

Chris, there's also a "Camera" toolbar icon hidden in the "Customize" dialogue that lets you take control of camera snapshots without the "Shift-Edit" procedure. The camera tool seems funny and useless, because why would anyone want to embed a spreadsheet range object in a spreadsheet? But it's useful for putting spreadsheet ranges into graphs, and also for shrinking embedded images of graphs smaller than the embedded graphs alone can be shrunk (i.e. to sparkline size). I understand designers of Excel-based dashboards like it for that reason.

Personally, I find the camera tool is too buggy to do the latter well with my Excel 97; it makes my machine crash too often. So I just blow up everything else to 20 point text, and then shrink the view to 50%. The graphs are now not too big to function as sparklines alongside the text.

Ok, the paste-picture-link is definitely cool.
Chris, CTRL-ALT-[ARROW] (left or up) does nothing for me. I'm running XP SP2 with Excel 2003 SP2 :(

Ever struggle designing a complicated form on a worksheet? You're merging cells, setting row and column widths, that kind of thing. You're trying to put something somewhere specific, but if you change widths or heights, something else won't appear the way you want it to. Arrgh!

That's the time to pull out the camera tool. Put what you want to display on a different sheet, format it exactly as you want. Easy, now that you won't have to fight with anything else. Now that bit looks good: good formatting, widths, heights, etc.

Now take a picture of it and put the picture on the main form page. Size and position the object precisely as you need. Simple and elegant.

Interesting ideas... One more potential use of this technique is to draw Boxplot charts.

Assuming you have 5 numbers for each distribution: Min, 1st Quart, Mean, 3rd Quart and Max. It is very simple to draw Min spaces,then "[", then REPT("-",Min-1st quart), etc. and you can draw a very decent box plot.

I just did a proof of concept implementation and it seems to work pretty well. It will look something like this (though you really need to use a fixed width font like Courier to maintain character alignment)

[────────██████X████████────]
[─────██████████X████───────]

Naturally that should have been REPT(”-”,1st quart-Min)...

Henk -

The Camera tool is still present in Excel 2007. While customizing the ugly little QAT, you need to pull up the Commands not in the Ribbon list, then find Camera.

Derek -

Excel has all kinds of issues when you deviate from 100% view. Copy your chart at 50% view, and paste it elsewhere in the sheet. See what I mean? It gets pasted at half of its apparent size. I've done projects in which I've aligned a bar chart such that each bar corresponds with a row in a table. A kludgey approach, and if the zoom is not at 100%, it will not line up precisely. There are certain zooms where it almost fits, and other magic zooms, like 139% and 127% on my laptop, where the misalignment is more than a cell height. At 100%, it's always dead nuts.

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

It's really not too complicated to write a VBA procedure to process a table of numbers. In the picture here:

http://peltiertech.com/Excel/Commentary/InCellBarChart.html

I show a small sample of a large worksheet. I've left out the proprietary data. Each cell in the column displays a number of values. The width of the outer rectangle reflects the high-medium-low importance of a record, the width and position of the filled rectangle within it indicate something about the range of values of some property of the record. The colors of the outline and filled rectangle indicate other values, and the blue vertical line indicates something else. It looks complex, but the client is able to scan hundreds of rows of these graphics and pick out the records with the criteria he is interested in, much more quickly than if he had to stare at tables of numbers, even if they were conditionally formatted. The code only took a few hours to bang into shape, and it runs in under a second.

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

I've tried uploaded the sample spreadsheet to EditGrid and it works very well, too:

http://www.editgrid.com/tnc/david/In_cell_graphing

Thanks for sharing this great tip!

Wow, i love the Gantt possibilities here. This will ensure MS Excels dominance within the enterprise.

Just for the record, all this works just the same in Open Office Calc. No need for Excel here.

these are sparklines ala dr. tufte and he has 18 pages in his new book on it
http://www.edwardtufte.com/bboard/q-and-a-fetch-msg?msg_id=0001OR&topic_id=1&topic=

Hmm.. I thought the gradiant fill bars looked much better. oh well, just my opinion. the REPT stuff certainly is cool though.

I didn't understand how you got the different colors for the different lengths of rept bars?

I see that my sample image of in-cell bar charting was not accessible, so I wrapped a web page around it:

http://peltiertech.com/Excel/Commentary/InCellBarChart.html

Anyone have a suggestions how to handle fractional numbers (non-integers) with this method?

Love this technique! Have used it in Perl scripts before, but never considered it in a spreadsheet.

If you want to limit the maximum size of the bar dynamically, you can use:

(OpenOffice Calc)
=REPT("|";INT((A1/MAX($A$1:$A$1000)*200)))

(Microsoft Excel)
=REPT("|",INT((A1/MAX(A:A)*200)))

This will ensure that the longest bar is 200 characters, and the others are all sized relative to this.

In the OpenOffice example, the range of source numbers is listed explicitly, whereas in the Excel example I have just used the entire column A.

A further extension of same idea.

Using same REPT calc, and combining it with the CHAR() formula, plus using some of the specialty fonts like Webdings, or Wingdings you can get a solid bar, instead of line with some space around it. (my examples use 10 as the Quantity, but you can common denominator it like Gordon suggests above).

Here's an example that worked pretty good.
=REPT(CHAR(103),10)
When used with a Webdings Font (lower point size to 6 or so) it produces a solid bar.

Another one that offered some possibilities for variance analysis was:
=REPT(CHAR(104),10) ; WingDings3 Up-Arrow
=REPT(CHAR(105),10) ; WingDings3 Down-Arrow
Combine these with an if statement and you're golden.
Added bonus: Since the output will produce a text result, you could even sort it descending and you'd have a pareto style, top-down list of the big variances.

Very useful idea!

Any OpenOffice users here? I've looked at these two posts and their comments and can replicate everything that has been done except for one thing, though how it was acheived wasn't actually covered - conditionally formatting the cell in which the graph lives.

My favorite trick now is to include the value at the far extension of the graph using the & operator. The problem is that the numerical data is not in the graph cell, so any conditional formatting in the graph cell tried to use "|||||||||||||||||| 23" as it's data. In excel can you conditionally format one cell based on a condition that is applied to another cell, with relative positioning?

good

This is very neat. I never thought of doing graphs in excel this way. Now I use it often when trying to show information quickly and simply. The other cool thing is like said before it works on all spread sheets not just excel.

Drawing a Gantt chart with this method is totally awesome. But is there a way to make the graph run "real time", using this method?

Use the U+2588 character in Time New Roman character set.

Run CMD charmap choose Times New Roman scroll 3/4 way down, choose U+2588 Full Block Character, Copy, Paste this into the cell, make sure it is formatted as Times New Roman, and the in cell bar looks more solid. It works with conditional formatting as well.

I really like the idea of in-cell graphing; I had a play a while ago using symbols to create some different styles.

http://www.markaylward.co.uk/blog/tech/in-cell-graphing-with-excel-and-the-rept-function

Jon Peltier took a few hours to hack up that VBA code - and the results are hideous in comparison to these simple in cell REPT charts!

Love the site and the great ideas!

Beautiful!!!
It took a couple of minutes to add this (the red and green version separate columns for positive and negative conditionals) for a spreadsheet bill of materials to indicate surplus or shortages for parts in eight subassemblies for a new product we are developing. Probably less time than to write this thank you to everyone who came up with this. I was looking for a conditional font control solution to format my calculated number for each part. The numeric value goes in a cell straddled by a green bar graph to the right if a surplus or a red bar graph is a shortage.

I am totally blown away. Made my day, maybe even my week.

This post is incredible, I have learned a lot already! Thank you all. Now I have a question about the Camera tool. Does anyone know if the process of "copy", "shift+edit", "paste picture link" can be replicated using VBA? I want to add to a custom menu the option to display some predifined range of cells with a simple menu click.

Any thougths?

I work in Traffic Impact Analysis and use a lot of graphs. Normally we go to powerpoint to do the turning movement arrows (you know, left turn, right turn, through and right turn, etc). I cam here wanting to know a way to insert an autoshape (the ones we use in the reports) into a cell so the process can be automatically made. However, reading your comments I found out a way to do it with chars and the font Wingdings3 with the exception of a shared through and right (or left) movement. Can you help me out with this?, either putting the autoshape arrows in the cells (if possible at all so that I can use it a text) or finding the appropriate font for the shared thru-right movement. Hope you can help me out

I learned a lot reading about the REPT function. I tried to apply it to factory yield data. Yield reports are normally quite horrible: either too many graphs or too much data in a sheet. Never a real good balance. Using the REPT function for yield introduces a scaling issue:
Yield losses in a process can be very similar:
- e.g. 82.3%, 84.1%, 80.5% for one process step
- 93.1%, 60.4%, 23.8% for the next
Either you end up with poor resolution or you end up with very long columns in your REPT-based histogram. Scaling is needed.

Has anybody seen small and elegant solutions for this?

Paul

I like the idea of in-cell data bars a lot because of tis simplicity and effectiveness. I have done some extra work to them, and created scalable, customizable in-cell bars.

Options include:

Overall scaling; by setting the lower and upper limit of measured data, setting and showing target value, settings for direction of improvement, setting and showing unit of measurement, settings for the length of Data Bars in number of symbols, show value of lower and/or upper limit, show target value and symbol in bars, show measured value, set symbol for lower and upper limit, color formatting; none, 2, or 3 bandwidths, bandwidth in % of middle band if using 3 bandwidths for color formatting, setting the symbol for Data Bars, setting the symbol for the open part of a Data Bar.

I am currently building my website, a downloadable template will be available there soon. Please let me know your opinion and questions about my version of the In-cell Data Bars.

Jeroen Jonker

[...] Altijd leuk om te zien hoe vindingrijk mensen zijn om iets voor elkaar te krijgen. Met de REPT functie in Excel (of HERHALING in de Nederlandse versie) kan je makkelijk een grafische weergave (bijvoorbeeld een bar chart / kolom grafiek) maken van een waarde IN een cel. Kan vooral erg nuttig zijn als je veel records hebt. Zie de volgende posts op Juice Analytics: More on Excel in-cell graphing Lightweight data exploration in Excel [...]

Hi everyboby, and happy fiestas ...

An excel file w/ user defined functions to create in-cell graphs is free to download at this adress : http://excelidees.blogspot.com/

The site is in french, but you will find the formulas self-explanitory.

Direct link to file : http://www.box.net/public/1g84l64vod

If any question or commen, drop me a line on the blog.

Bonne Anné 2007 !

I've been playing with Box Plots in Excel 2007 and I just posted the step by step technique to draw them.

Check it out at http://blog.immeria.net/2007/01/box-plot-and-whisker-plots-in-excel.html

S.Hamel
http://immeria.net

[...] [...]

[...] Esto tiene muchas aplicaciones, dependiendo de tus conocimientos y tu capacidad para anidar funciones en Excel. En los comentarios del artículo original y en este otro post del mismo autor proponen verdaderas virguerías. Puedes descargarte una hoja de cálculo con todas ellas. [...]

As far as visual tricks go, this is hands down the best thing I have learned in Excel in a long time. I also enjoyed all of the feedback and added tricks that readers submitted. The only thing that could have been improved is me finding this blog entry much sooner. Nice job guys!

Another way to implement this technique in the vertical orientation is to create the chart in the standard horizontal orientation, then highlight the range that contains the chart and select Edit > Copy. Then HOLDING THE SHIFT KEY select Edit > Paste Pitcure Link. At this point you have a Picture object on the spreadhseet that can be rotate 90 degrees to display the chart in vertical orientation!

Actually the Paste Picture Link functionality is absolutely awsome wlthough little known out there. I owe my discovery of this technique to John Walkenbach of J-Walk & Associates, Inc. fame (http://j-walk.com). Thanks!

[...] Esto tiene muchas aplicaciones, dependiendo de tus conocimientos y tu capacidad para anidar funciones en Excel. En los comentarios del artículo original y en este otro post del mismo autor proponen verdaderas virguerías. Puedes descargarte una hoja de cálculo con todas ellas. Filed under : TrucosBy Juan Carlos On 17 March 2007 At 9:45 pm Comments :   [...]

Great post very useful.

Graphs make our life much easier. Be it economical analysis,social or political...

Andres Sotil.
I only saw your question recently.

The webdings / wingdings fonts have some neat arrows that may meet your needs with a simple Choose function.
e.g. Choose(A1,Char(x),Char(y),Char(z),Char(a))
where X, Y & Z, and A are the values for the Arrows, and A1 holds the value corresponding to the type of arrow you want displayed.

One drawback to switching to a Webding or Wingding fonts is that the fonts don't have numbers. So you can't combine the Rept() type bar-chart with the nice solid bar format and add a "data label" number at the end of it.

I shouldn't say can't, its more accurate to say not readily / easily. There are some ball-shaped numbers in the Character 105-126 range of the Wingding2 font. However, you would have to manually calculate each digit with a lengthy mod() calc and concatenate the whole thing.

e.g. the value "123" in cell A1 would require a calculation for each digit.
For the digit "3"
=CHAR(105+MOD(A1,10))
For the digit "2"
=CHAR(105+ROUNDDOWN(MOD(A1/10,10),0))
For the digit "1"
=CHAR(105+ROUNDDOWN(MOD(A1/100,10),0))

As you can see, it gets complicated fairly quickly, because you'd still need to concatenate it all together with the REPT bar part. I don't practice "mega-formulas", so that would mean 3 or 4 cells per display item.

Judge based on your needs, because its probably simpler to reformat your sheet and go with a chart.

Thanks for a great blog, very helpful

Hi Guys,
I'm using excel spreadsheets to make "auto-drawings" of parts, where I nest *.wmf generic drawing images with camera objects of cells containing the relevant dimensions.

My question is:- looking at the range reference for the camera object, I would like to make the range reference conditional - so that the camera object refers to one group of cells when applicable, but another when the value changes. I know how to use the if,and,vlookup functions etc, but I can't seem to input a formula to the camera object reference.

Does anyone have an idea how i might be able to do this?

Thanks for a brilliant post by the way.

Cheers
Gareth

Hi again - I think I found a solution to my question above from someone at this site - for anyone else who is interested

http://www.exceluser.com/solutions/traffic.htm

Thanks again for your brilliant work here!
Cheers
Gareth

excellent idea, thanks!

playing with different fonts i noticed that i get best results with BRITANNIC BOLD, use character "|" and the graph line will be solid

Thanks for all of the tips! This is great. I have a feeling I'll be making use of this very often.

I don't know if this has already been covered in the long list of posts here but:

If you combine the REPT and the Camera Tool functionality you can flip around the in-cell graphs that REPT gives to give a column feel.

Chris: "On the other hand, I've just learned that Windows XP has a "great" new feature that makes vertical charting easy. Hit Ctrl-Alt-left arrow. Problem solved! ;-)"

Chris: "Clint, on my laptops (same config as you), the Ctrl-Alt arrow stuff rotates the entire screen. You're really missing out because its a totally essential feature. ;-)"

Chris, looks like you're using Intel on-board graphics or something similar.

1) http://tabletdev.com/andy/archive/2005/05/04/469.aspx

2) http://support.dell.com/support/topics/global.aspx/support/dsn/en/document?c=us&l=en&s=gen&dn=1089038

Crappy blogging software - cuts off long URLs! >:(

2) http://tinyurl.com/yuj4kc

For large values, it may be helpful to include tick marks. This can be done by repeating a string "|||||||||I" for every ten, followed by a Mod of the rest.

=REPT("|||||||||I",ROUNDDOWN(A1/10,0))&REPT("|",MOD(A1,10))

|||||||||I|||||||||I|||||||||I||||||

(Looks much better in Arial!)

Beautiful posts. I did try a small variation to the InstaGantt in-cell chart by formatting the cells in Courier. Excel transforms the three dots in a beautiful vertical bar.

=REPT(" ",(D2-Start_Day))&REPT("…",(1*(E2-D2+1)))

This is an excel-lent site.

Does anyone know how to use a graphic to manipulate data example: Moving Graphics along a grid example A1:Z1 changing the value in A20?
graphiex2001@yahoo.com

Thanks so much for this. And I do love that it works in Google spreadsheets, except, it seems, the Gantt chart example. At least when I tried it, the spaces don't seem to show up (all the "bars" were flushed to the left). Does anyone have a solution? Or know what I'm doing wrong?

Thanks again!

I have a customer satisfaction survey that gives three choices: Good, Indifferent, Bad. From this, I produce a metric that shows broadly how well we're performing (very broadly). Currently, we're using a percentage of "Goods" to total of all three to determine how well we're performing. I'm of the mindset that we should use weighting such as 75% Good + 25% Indifferent + 0% Bad to better reflect those who are responding Indifferent. For the prior month we showed 80% satisfaction rating off 12 Good, 3 Indifferent and 0 Bad. It looked terrible (we have high standards), but it was due to both the low number handled and the 3 Indifferents. Any recommendations from this group on how to handle including the Indifferents appropriately in the measure would be appreciated.

Thanks in advance for any advice you can provide.

Very creative work here. I use

REPT(CHAR(103),$R$2) and set the font to webdings and format the cell for vertical alignment. You can also group a few cell above the target cell, if necessary, to preserve your row height integrity.

Great tips!

Instead of bar chart, is it possible to come with a circle (green fill for +ve and red fill for -ve)? Diameter of the circle is function of another cell. Its like bubble - growing and shrinking based on the values coming in. Small text inside the circle from another cell would also be great!

would be sweet if somebody could think of stacked bars.

You can do a similar thing using VBA to draw rectangles for you with their width based on a cell value. Clearly a lot more work... but the granularity is far superior (seeing as you can have the bars down to pixel resolution without having gaps (as you get with '|||') and also when using decimals).

Hi,
I am not much familiar with excel and i got an operation to do,like for example: if the column is of char type and i must insert 2 in the starting position of each value in that column...like if it is 345 then it should become 2345...and so .Please suggest a solution to this task.

Hi,
This was a great learning.
But in any ways is it possible to show the graph in two colours. Say I have start Dt, Intermediate Dt and End Dt. I will calculate the days between these Dates and would make the In-Cell Graph (eg 15days for start dt to intermediate dt and 20 days for intermediate dt and end dt). I'll now make the graph by expression =rept("!",15)&rept("@",20).
In this formula is it posiible that i give different colour to "!" and "@". Please help me out.
Thanks in advance

Hi,
Could anyone let me know how I can get the below format in Excel 2003

I'd like to insert an object (autoshape) in a simple excel formula. specifically, I want to use an if-then statement to display a green up-arrow (created using autoshapes) when a condition is true and a red down-arrow (also created using autoshapes) when the condition is false. I am able to reference a specific cell and not an object which is giving the problem. Please help

Smitha, no way as far as I know of getting the Autoshapes, but as for arrows, you know there are plenty of arrows in Wingdings and similar symbol fonts? That's what I always use for my up and down arrows in dashboard-type applications. You can use Conditional Formatting to make them red or green.

Thanks Derek. My client was informed by someone that they got the autoshapes displayed using IF-cond and now I have to implement the same. But from what I was analyzing since yesterday, I didnt get a break through. Thanks for your comments/information. In case I get any info, I would share the same here.

A better character for the REPT function is ALT+219.

This produces █, which looks much better than a a thin line, when repeated.

This is a fantastic post. The methods are really clever, and a wide variety of permutations are considered. I'm interested in making in cell bar graphs that go vertically. As someone else has posted, you can change the allignment or the orientation of the font to accomplish this. But I'd like the width of the bar to equal the width of the cell so that there is no gap between the bars as one looks horizontally. And, I'd like there to be no gap between the bars going vertically too. I think this means that I need a character that is as wide as a cell or as tall as my cell's width, and tall enough so that two typed together and then oriented up/down do not produce a gap. I looked for a such a character, but couldn't find one in Webdings or other symbol fonts (Webdings "g" is great, as suggested by another, but it is not "tall enough"). I tried to use a dash (_) and make the font really big, but then the cell had to be really big in order to see it, and there was still a gap when they were "stacked" vertically. It made me want to make my own character, but I have no idea how to do that. Does anyone have any thoughts on how to do this? I can also make a regular "bar graph" (using the chart wizard, etc,) but it is hard to allign it so that the bars are exactly the width of a cell, and then by the end of the row, the bars are not quite underneath the cells that characterize them. Thanks in advance.

You can convert the bars into columns using Excel's often forgotten Camera tool - in Excel 2003 you'll find it in in Customize under Tools.

Build the bars as in the example spreadsheet then simply highlight them, click the camera and paste the resultant picture. The picture will update as the data changes but can be rotated or scaled as necessary.

The Haettenschweiler font, which came with my Excel 2000 installation, makes a nice, tall solid bar at 10 pts and 100% zoom.

Regardless of font, it seems they don't scale well if you zoom the Excel view or try to paste into another application. I've had to use a screen capture for that. Still, it's a great tool.