A while back I developed a solution to a Chandoo.org Forum question, where the user wanted a 4 level doughnut chart where each doughnut was made up of 12 segments and each segment was to be colored based on a value within a range.
You can read the original post here: http://forum.chandoo.org/threads/hourly-goals-chart.30621/
This post will examine the techniques I used for the solution.
Data
Download the sample file: Download Hourly Goals Chart File
The first thing to note is that there are 4 column of data, one for each measure of Safety, Quality, Delivery and Cost.
Secondly is that each measurement has 12 values representing the times from 4:30 am to 3:30 pm.
We need to setup a Doughnut Chart with 4 layers of 12 segments each
The easiest way to do this is to replicate the data area, but fill it with the same value in all cells,
I choose 1, but as long as all values are the same value, it can be any value
Add a Doughnut Chart
Select the Range A16:E28
Goto the Insert, Chart and select the Pie/Doughnut menu
We have a bit of work to do yet to get the charts format correct
First select the chart then select the Chart’s Legend and press Delete
Next with the chart still selected, Right Click on any Doughnut and select Format Data Series
Set the Doughnut Hole Size to 25%
Do not change the angle of the first slice
Right click on the Outer Doughnut and select Add Data Labels, Data Labels
Right Click on any Data Label and Select Format Data Labels
Tick Value From Cells, Select a range A17:A28
Untick Value
Untick Leader Lines
Now manually click and drag each data label outwards to its final location
Finally set the Border Color for the doughnuts
Right Click on each Doughnut in turn
Set the Doughnut’s Border Line to a Grey Color and a 2 Pt line size
We can now add a text box for the Doughnut Labels
With the chart selected, goto the Insert, Text Box menu
Drag a Text Box inside the chart
Right click on the Text Box and edit Text and type in the value Cost
Now repeat this for the other 3 Doughnuts
Connect the Doughnut Segments to the Data Area
We now have a basic Doughnut chart with all the facilities we require.
Unfortunately, Excel doesn’t have a built-in Conditional Formatting option for charts.
So we will need to develop a system using some simple VBA.
Understand the Doughnut Chart
To write a piece of code we will need to loop through each segment of each doughnut and reference it back to the source data area
Then use some code to set the fill color
then repeat for each segment
To do this we need to understand which doughnut is which column of data and which segment in the doughnut is which time period
First select the inner Doughnut, Note that when you select it, Excel highlights the Safety Series as well as showing the Series Number in the Formula Bar
Repeat with the outer Series and you will see that Doughnut 4 is connected to the Cost Data and is series 4.
To determine which segment is which goto cell E17 and change the value from 1 to 2
So we understand that the series go from Value 1 to 4, Inner to Outer Doughnuts and that the segments go from value 1 to 12 clockwise, starting to the right of 12 O’Clock.
Finally select the Chart and make note of it’s name.
The Charts Name is shown in the Name Dialog above cell A1
Now for some VBA
Lets start by first manually recording a macro in VBA and we will then edit and add to the macro to get our final result
Start the macro Recorder by Pressing the Macro Button in the lower left corner of the Excel Window
Note the Macro Name, which is most likely Macro1 and press Ok
Now everything that you do is being recorded by the Visual Basic Editor (VBE)
Select the Outer Doughnut, then select Segment one, then Right Click on Segment one, Format Data Point
Select the Fill & Line menu
Set the Fill to a Solid Fill and Select a Color Red
You can now stop macro recording by pressing the Macro button again
Lets look at our code
To change to VBA press the Alt+F11 button
You should have a screen similar to this:
Take note of the above.
We can see that we have a Macro1 subroutine, located in Module 1 of our Excel file.
If you can’t see a Properties or Immediate window, don’t worry.
Looking at the VBA Code we can see
- That the chart is called Chart 1
- We selected Doughnut 4, the outer doughnut
- We selected the first segment in Doughnut 4
- We set the Fill Color of Segment 1 to Red = RGB(255, 0, 0)
So this little bit of code will form the basis of our macro
What we need to do next is to place that within 2 loops, one loop for the Doughnut and one loop for the Segment
So lets do that:
You can see above that we have initialised two variables Doughnut and Segment as Integers
We have setup two loops, one for the Doughnut which will loop from 1 to 4 and a second loop for the Segment, which will loop from 1 to 12.
We can now use these variables within the code to reference each Doughnut / Segment as relevent
The next thing is to add lines to lookup the value of the measure in the original data table.
We can use our variables to assist us with this:
I have added a new variable declaration myVal and declared it an Integer as it is only storing the values from, 0 to 3.
Then we retrieve the value from the data area by using a Range(“”).Offset(Row,Column) combination.
We know that the segment loops from 1 to 12 and this is the Row Offset in each Doughnut.
The Doughnut loops from 1 to 4 and this is the Column Offset from the cell A1
Next we need to allow for each fill color remembering that the data area has a legend
We could loop from a value of 0 to 3 and check the new variable myVal against each value and set the color.
But VBA has a Select Case function which is ideally suited to this task
A also took the opportunity to streamline the Chart selection process in the previous step
That allowed the use of the With Object construct, allowing the Select case to use the myVal to apply different colors to the fill property of each segment
At this stage we can run the code, by simply pressing F5 in VBA
We can change the code to allow it to update automatically when Data range changes
To do this we need to shift the code to a Sheet1 Code Module associated with Worksheet Sheet 1
Note above that the code is now located in a Private Sub Worksheet_Change event. This means that the code runs whenever worksheet1 chnages.
The next line If Intersect(ActiveCell, Range(“B2:E13”)) Is Nothing Or Target.Count > 1 Then Exit Sub
Checks whether the cell that changed was not part of our Data Area or that multiple cells were selected.
If either are are true the macro ends
Then finally I removed the MyVal calculation and made it part of the Select Case function.
because we aren’t using myVal I removed the Dim myVal statement
We can now also remove Module 1, right click on it and Remove Module.
Save the file and return to Excel with Alt+F11
You can now change any cells in the data area and the macro updates the chart accordingly
Can we tidy up the layout of the worksheet?
Although we now have a fully functional model, we are stuck with an ugly worksheet layout because our template of 1’s is being used to support the framework of the 4 Doughnuts in the chart.
What if there was another way to achieve that?
Well there is.
Firstly, we could simply shift the range A18:ER30 well away from the Chart and data area or even move it to another worksheet.
This will work, but risks a person adding data, rows or columns and messing up the layout
But there is a better way
I am going to add 4 Named Formula to the worksheet, one for each Doughnut
Goto the Formula, Name Manager Tab and add 4 Names as listed below:
_Safety    =1+(ROW(OFFSET(Sheet1!$A$1,,,12,1))-1)*0
_Cost       =_Safety
_Delivery =_Safety
_Quality =_Safety
The 4 Names now contain an array of 12 x 1 each with a value 1.
We can use that to link the Doughnuts to instead of the Physical Range
Right click on the chart and Select Data
Select each Doughnut in term and Edit
Change the Series Name to Row 1 and insert the Names into the Series values dialog.
Note that the formula must include the Worksheet name =Sheet1!_Safety etc
Repeat this for the 4 Series
You can now select the framework range: A18:E30 and press Delete
The chart remains intact and is now supported by the Named Formula
Change some values in the Data range at the top and the Chart updates as it should.
You can download the final version of the file here: Download Completed File
Final Thoughts
The technique applied to the doughnut chart above can fairly easily be modified to any chart type or in fact any other shapes.
Let me know what you think in the comments below:
ps: This has been one of my hardest posts to write, simply because Microsoft has misspelt Doughnut. In my native Australian English it is Donut.
56 Responses to “Creating in-cell bar charts / histograms in excel”
Ay jhakkas!!!
Man, you're on a roll. A true-blue Excel innovator. What you're writing makes me think - why didn't anyone else think of this before?
Now that I've showered all the praises on you, it won't hurt to have a few comments on my blaag 😉
PS. I meant the innovator part.
@Amit ... thanks, I was also curious why this one was not explored, but again, I havent really searched a lot to ensure that I am posting the same ideas again. My intent is to make few people to benefit from this, if that happens I would be happy...
btw, posted a comment on your blaag... hope you are happy now 😀
Don't worry about repeating the ideas in the online world. As long as you are not copying it off anyone else and it is helpful for the readers, it's fine.
PS. the comment does not count.
The idea actually is not a new one :).
Check out MicroCharts
http://www.bonavistasystems.com/
to see how far you can get with font based in-cell charting
[...] can never get tired of in-cell charts, whenever I get sometime, I try to experiment something on them. Here is an idea to design true [...]
[...] Since we can insert any character in to a cell using formula, by installing a custom bar chart / pie font in our computer we can create incell graphs in excel with ease. Click here to see example pie chart, line chart. [...]
Where is the file? I can't seem to locate it. I want to donwload it. Thanks Chandoo!
Found it.
Great job, Chandoo. Love the site - and the fact that you provide downloads to help us (me) learn your secrets faster. I downloaded the font but can't figure out how to add it to my font library... Any hints? Thanks! Keep up the fantastic work.
@Mahqooi: Thank you and welcome to PHD 🙂
This is how you can install a font in a windows machine:
unzip the font files (if needed)
select and copy the font file to clip board by pressing ctrl+c
go to control panel > fonts
paste the file by pressing ctrl +v
repeat this procedure for other font files if any
if you are using mac, just right click on the font file and select install option.
let me know if you have some issues with this.
Hi Chandoo,
is there any mirrors for the bargraph font?
it seems that fontstruct.com is down for maintenance.
thanks!
@Cybsych: I am not sure if they have any mirrors. I will look in to my backup to see if a copy of the font can be located and ping you back. Thanks.
hi Chandoo, fontstruct is back online 😉
BTW, I am wondering about this in-cell chart.
How do I apply an automated conditional formatting to only a bar/point?
For example, the first image in this post, whereby RED = highest, BLUE=lowest.
Chandoo,
I guess this bars only work with positive numbers? so if you a list of costs per month, but one month you have negative cost meaning income due to let's say vendor credits. This incell bar could despict the month with a negative digit. or could it?
hi Chandoo, guess that you missed out my query 😀
is there a way to highlight the MAX and MIN bar based on the actual data (not the normalized)?
@Pedro, for that you need to have another set of characters (may be A-J for 0-9 and K-S for -1 to -9 and then use them to show the bars. It is a bit tricky, but achievable.
@Cybpsych: The highlighting was done manually (As you can see, there is probably no easy way to highlight / change colors of a portion of cell using Conditional formatting etc.). I am sorry, but you need to use someother sparkline technique to achieve this (or, write your own macro)
http://chandoo.org/wp/2008/09/05/microcharting-excel-howto/
thanks chandoo!
I love this simple and quick way of visualization results. I would like to learn more about normalizing values (i.e. the use of linear normalization). Can someone kindly point me in a good direction for this beginner? Much thanks to everyone (especially Chandoo) for the wealth of information provided. Long live the internet age!
@Jason: you can use simple excel formulas to normalize a set of values. If the list of values is in say a1: a10 and you want them to be normalized from 1 to 100, you can do that with a formula like: =A1/max($A$1:$A$10) * 100. Also, you can use the RANK formula to calculate the percentile of any value in the list.
[...] Bar | Sparklines | Pie charts | Bullet Graphs | w/ Conditional [...]
Nifty way to normalize the data....I'll have to take that into account when working with my charts.
One thing I'd like to add, you can eliminate the need for custom fonts with the bar charts by using a REPT function and using a small "g" set to the Webdings font. It's more likely anybody opening the file will have access to that font than the custom one you've provided. (More portability is a good thing 🙂 )
Portability is great.
I don't quite see how the REPT formula and the webding fonts can combine to solve the portability issue.
Mind you, i see that +REPT("g",1) will give you a bar, but we would need several bars of unequal lenght.
Can you elaborate?
Thank you
@Matt: I almost forgot about this comment. Thanks to Pedro for the bump.
As he points, portability is a good idea, but we will not be able to get bars of variable height using webdings font.
We can ofcourse use that along with text rotation and char(10) to create a pseudo incell bars. Here is a tutorial: http://chandoo.org/wp/2008/07/15/incell-bar-charts-revisited/
@Chandoo: Yep, that's exactly what I meant, use your text rotation and char(10) trick with REPT("G",) (then set the font to Webdings) to get your string of bars with variable height.
@Pedro: REPT("g",1) will give you one "g" (or in Webdings a bar of 1 height).
REPT("g",B2) will repeat for the value in B2... 🙂 Use that with Chandoo's take on linear normalizing, and yer all set.
Wingdings with an "n" character would be even more portable, but just doesn't look quite as cool...but pretty much everybody has that font, so it'd be portable.
You may have to adjust the font size in order to get all the bars to show correctly, perhaps some sizing of the row heights as well...
You can fake an incell line chart by using:
REPT(" ",B2-1)&REPT("n",B2)
where B2 is the value in the cell you want as a data point.
Wow, the formatting was horrid, let's elaborate a bit more...
REPT("",-1)&REPT("n",) - would give you a line graph, where could be a reference to each cell you'd like as a data point.
REPT just repeats a text string a number of times, it can be either a hard number (like Pedro's example), or a reference to a value in another cell (more handy). I believe Webdings is a common font in the MS Office suites I'm familiar with (2000 thru 2003), but I'm not sure of 2007's suite.
@Matt A: I am sorry for the formatting mishap. I am afraid of using too many plug ins, but I guess a simple HTML based comment box seems like a good idea now that lot more commenters are typing formulas and vba code in the comment box.
Coming to the formula.. thanks for sharing it. And yes, you are right, webdings is common to Office 2007 too. But even better solution would be to use good old pipe | symbol. When the font is Arial, the pipe character spacing looks optimum and subtle enough to look like an incell histogram / column chart.
After some searching through the character maps in Arial I noticed that there's a box symbol --> ? (created by holding ALT then typing 5595 on the numpad) that would work perfectly as another character to use for column charts. It looks just like the Webdings "g" character.
Is there a way to change the colour of the bars based upon the data. eg. 1-5 = red, 6-7 = amber, 8-10 = Green
@Ben... you can change the color of all bars in a cell using conditional formatting. But selectively changing color of bars inside cell is not possible unless you do it manually or through VBA.
[...] Creating in-cell bar charts / histograms in excel @ Pointy Haired Dilbert Filed under: Stuff [...]
Is this work only for the numbers or will it work for % data also. I tried to do the same for % data, but i didnt get. Pls let me know the formula for % data.
[...] trick is to use Incell Charts. [...]
Hello Chandoo,
I really like this, but I have Office for Mac 2011 and for the life of me I cannot figure out how to see the bargraph as an available font.
I have followed all the instructions for adding a font, but it does not appear. Do you have any suggestions?
Thanks
prb
Thanks. This one was cool and helpful. Can we experiment the same with "in cell" line graph as well? 🙂
Chandoo,
How do you "manually" change the color of the last bar in the series?
Lawrence
@Lawrence
Select the chart
Select the series
Select the last point/column of the series
Ctrl 1 or right click Format Point
Select a color
Hui,
Thanks!
I should have been more descriptive. What I meant to ask was about the in-cell bar graph created with the REPT function described above. How do I get the last REPT (the last bar) to be a different color than the rest?
Lawrence
@Lawrence
You cannot change colors in a cell using formula
You can use either VBA code or do it manually
Select the cell
Copy and paste it as values
Edit the cell F2
using the arrows move to the character you want to color
Shift and select the cell by arrow keying over it
with the characyer selected
Ctrl 1 (Format Cells)
Change the Font Color to suit
It won't be a color change per se...but you can set an IF statement in your REPT formulas for different characters to show as the bars. The characters "c" and "g" in Webdings are both boxes, one is a solid block, the other an outline.
For example, say I wanted to highlight the highest bar in my REPT formulas...my formula to translate the numeric cells A2:A15 to characters would be:
IF(A7=MAX($A$2:$A15),REPT("c",B7),REPT("g",B7))
so if the cell I'm checking (here it happened to be A7), is the highest number...its bar would display differently further along down in the concatenations...
@Hui...THANKS!
@ Matt A... Very cool idea. What formatting do you recommend for the cell? The Webdings "c" hollow box is very faded and hard to read even if bolded and bigger font size is used. If I could just punch it up a bit it would be perfect with 5 "c" columns followed by a single solid "g" column...as in showing the trend in the trailing 6 months of data.
Lawrence
@ Lawrence
Good question...lately I've been using ? (which you get from holding ALT then typing 5595 on the numeric keypad) for most of my bars. Unfortunately the character map doesn't lead me to a differently "shaded" box of the same size. Reason I use this nowadays...it's part of arial font...just a special char map character I can rapidly input w/o any formatting nonsense.
I'll check to see if I can replicate another box of same size that may have different shading using the same method...no luck as of yet.
I've just built the in cell bargraph and was trying to create a pop up window which would display the Monthly Sales for Last 12 months when they click on any of the bargraph cells
[...] Reference:Â http://chandoo.org/wp/2008/05/13/creating-in-cell-bar-charts-histograms-in-excel/ Like this:LikeBe the first to like this. [...]
[...] To quickly insert an in cell micro-chart, use REPT() function… Get Full Tip [...]
Hi, there is a problem with the Bargraph font. On my win7 machine it works perfectly but when I try to install it on my boss's mac it returns an error called " 'Name' Table Structure"
I tried to install on two different macs and the same error resulted. As a result the font does not show up as an option in any program.
Â
Just an FYI. I don't use macs but I know some people do.
Whats up! I just wish to give a huge thumbs up for the good info you might have right here on this post. I can be coming back to your weblog for extra soon.
[...] like .docx, .htaccess etc.) 43. To quickly insert an in cell micro-chart, use REPT() function… Get Full Tip 44. COUNT() only counts number of cells with numbers in them, if you want to count number of cells [...]
Thanks Chandoo for the font!! It works great once installed on my machine, but is there any way (besides printing and scanning the doc) that I can get the graphs to show up on other peoples' machines without going through the font install process? My file has to be sent out to clients that don't have that font installed.
Sarah, Excel doesn't allow embedding of fonts (aside from a workaround using a macro). The font will need to be sent to all who want to view the file. I went through the same question with my boss. I ultimately just installed the font on her computer.
If the data is only to be viewed, and not modified, moved, etc. you can save the file as a pdf. The font can be viewed that way.
Hello every one there is a problem I need auto update summary formula from other sheets data pick please give me sample file and also auto up grate summary sheet format.................
@Joesali
I'd suggest asking this type of question at the Chandoo.org Forums
I'd suggest uploading a sample file also
Hi chandu,
Apart from excel, i need the formula to find bar graph height dynamically when using with log scale, for example for linear graph i would take the maximum value to height of the panel as
(value divided by maxvalue) * height.
Now , i am using a logarithimic graph can you tell me the right formula which fits perfectly.
Thanks in advance
Nice info... Thanks... very hepfull... 🙂
The font does not seem to be available at fontshop. Is there somewhere else to download the bargraph font?
@Amber
Try doing a Google search for Bargraph Font
it returns several possibilities
Is there a way to do this without using bar graph font? We have a financial report to be published to stakeholders and they will not have this font installed, so probably will not be able to view the bar chart as well.