This is a guest post by Sohail Anwar.
August 29, 1994. A day that changed my life forever. Football World Cup? Russia and China de-targeting nuclear weapons against each other? Anniversary of the Woodstock festival?
No, much bigger: Two Undertakers show up at WWE Summerslam for an epic battle. Needless to say: MIND() = BLOWN().
And thus begun one boy’s journey into understanding the phenomenon of Multiple Occurrences.
My journey continued, when just a few years later my grandfather handed me down a precious family heirloom: A few columns of meaningless data that I could take away and analyze in Excel. You may laugh but in the 90’s, every boy only wanted two things 1) Lists of pointless data and 2) To be as bad ass as this guy:
Ohhh yeah.
All good but how best to deal with multiple occurrences? Well, it broadly involves a cunning collusion of SMALL, LARGE, IF and our good friend the Array formula. To explain, let’s have a look at one of granddad’s prized pointless lists:
All kinds of repetition of names exist here, so how, for example, can we look up the pointless things about ‘Das Hoff’?
A typical VLOOKUP or INDEX/MATCH combo will give us the first entry (‘Talented’), but what about the rest? The following ARRAY formula will saves us:
SMALL(IF(Lookup Range = Lookup Value, Row(Lookup Range),Row ()-# of rows below start row of Lookup Range)
Entered with Ctrl + Shift + Enter because it’s an Array formula
In this case:
SMALL(IF($A$1:$A$20=$E$2,ROW($A$1:$A$20)),ROW()-2)
Bear in mind this will give us the position numbers of the multiple occurrences in our main list. That’s a good start. Now we drag this formula down so we end up with another list since our need to find multiple occurrences will necessitate creating another shorter subset of the main list, even if there are just two entries. How far do we drag it down? It doesn’t matter too much but enough to capture the likely number of multiple occurrences. we’ll come back to this point in a bit.
I just want to bring your attention to the last part of our SMALL formula, in this case ROW()-2. This creates a rank; think of it as 1st occurrence, 2nd occurrence…as you are dragging the formula down.
Why did I put Row()-2? Well I placed it in a cell which is in the 3rd row and as a rule the first instance of the formula you write, you want the Row()-x to equal 1 (assuming your lookup range starts from row 1). So if your looukup range is in A1:D20 and your first SMALL formula is in cell E5 then you will write ROW()-4 at the end .
Let’s see what happens when we put the formula in E3, search for ‘Michael Bluth’ and drag down to E7:
We can visually see there are just two entries in the main list and their position numbers have come through nicely (4 and 7). Beyond that we are met with the #NUM! error. So from here, we need to do two things
- Utilize the position number to give us value or related value from the list (i.e. do what the lookup is supposed to do!)
- Conceal the errors.
To accomplish (1) we can just put this whole thing into an INDEX formula, define an array size (same vertical dimensions as our main table), use our SMALL formula to provide the row number, then define whatever column number we want, in this case we want column 2:
INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=$E$2,ROW($A$1:$A$20)),ROW()-2),1)
Which yields:
Now, the final bit involves wrapping all this in our trusted friend IFERROR for some easy tidying up:
IFERROR(INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=$E$2,ROW($A$1:$A$20)),ROW()-2),1),"")
Ta da! Let’s have a quick recap of how we evolved the formula.
What else can we do?
Let’s extend this bad boy formula and make it really work for us. Here are some select ways I have extended the Multiple Occurrence formula to help extract from challenging text data.
Please download the workbook, since it contains the examples for your learning pleasure.
Note: Temporarily for this next section, I am going to ignore the IFERROR and the INDEX parts purely to make the formula slighter shorter and thus a bit easier to read. Instead, what we will get are the position numbers (which are good enough to demonstrate how the formulas work). Relax, in the final section, I’ll bring them back in!
Descending List
Okay, not very exciting, but if we wanted our list to be in a descending order, we simply switch the SMALL with LARGE!
LARGE(IF($A$1:$A$20=$E$2,ROW($A$1:$A$20)),ROW()-2)
Partial Text Search
What if just want to look for part of the text? Easy!
SMALL(IF(IFERROR(SEARCH($G$2,$A$1:$A$20)>0,FALSE),ROW($A$1:$A$20)),ROW()-2)
The urge to use a wildcard just won’t work due to the mechanism of an Array. Arrays require like for like comparisons and a partial text won’t correspond to a range. So we need to create TRUE and FALSE outputs, which is what wrapping the SEARCH(…)>0 in an IFERROR does.
Left side of Text
Let’s say we are looking for a first name in a cell with a full name, we can do:
SMALL(IF(LEFT($A$1:$A$20,LEN($I$2))=$I$2,ROW($A$1:$A$20)),ROW()-2)
Some of you are thinking, well this can be achieved with a partial text search and most of the time you are right. But I routinely deal with tens of thousands of rows of data with varying text and used to fall foul of not preparing for every permutation or combination. It’s subtle but it can be very useful.
Partial text in the right side
‘Now you’re just being silly Sohail! Who needs this?’ I’ll stand by what I said, when you work with lots of data and need to extract all kinds of things, this sort of formula soon finds a place! Unfortunately I can’t reproduce data that I’ve worked with to show you the reality of needing something like this. It’s not often but once in a while it comes and it’s quicker then VBAing!
SMALL(IF(IFERROR(SEARCH($K$2,RIGHT($A$1:$A$20,LEN($A$1:$A$20)-SEARCH(" ",$A$1:$A$20)))>0,FALSE),ROW($A$1:$A$20)),ROW()-2)
So we’re just searching for things past the first space, this sort of thing would need to be extended as more spaces crop up but you get the point.
Multiple Occurrences and Multiple Criteria!
What?! This is more confusing than making Time Traveling Flux Capacitors.
Okay, to make this work, let’s increase our data set, I’m going to throw in a region column for all the patriots in da house.
So now things are getting interesting. ‘Das Hoff’ is a great example; we can see from a visual inspection he covers two regions (discussing the dual German and US citizenship of the Hoff is out of the scope of this article, but just know how awesome he is!). How can we lookup the two different occurrences of ‘Das Hoff’?
Easy, but first if we harken back to the ultimate VLOOKUP trick I suggested the use of CHOOSE in an array to create ‘virtual’ helper columns, the good news is since we are in an Array format, its pretty straightforward do this without messing with VLOOKUP or CHOOSE. So we simply concatenate the Person and Region ranges and we concatenate the Person and Region lookup cells:
=SMALL(IF($A$1:$A$20&$B$1:$B$20=$E$2&$F$2,ROW($A$1:$A$20)),ROW()-2)
So now if we look up ‘Das Hoff’ in ‘Germany’ and ‘US’ we get:
Das ist gut, nein? Ja, Ãœber gut.
Let’s go a step further; what if we wanted to separately lookup the First and Last names? Easy, same concatenation but also concatenate a space in between, like so:
=SMALL(IF($A$1:$A$20=$K$2&" "&$L$2,ROW($A$1:$A$20)),ROW()-2)
So if we are searching for the first name ‘Thom’ and surname ‘Morello’ we get:
There you have it. Multiple Occurrences WITH Multiple Lookups, take that to the bank!
Autofiltering without an Autofilter!
So, now we have seen the power of what can be done with Multiple Occurrences, how else might we use this in our work? Well, in the Chandoo tradition of creating awesome dashboards let’s build a bit of interactivity in a dashboard. Now I’m not going to build a dashboard, the web’s finest materials on dashboards can already be found on Chandoo.org! No point me recreating. What if we want to create a makeshift Autofilter in the middle of a dashboard/report? We can use everything we’ve learned about Multiple Occurrences and with a bit of conditional formatting we can cook up something pretty decent.
How about we poach the multiple criteria technique from the previous section: First Name, Surname and also Region as drop downs (by using simple data validation lists) to control a table of formulas:
Let’s just look at the formula in each column of the table:
Column 1: Person
IFERROR(INDEX($A$1:$C$20, SMALL(IF($A$1:$A$20&$B$1:$B$20=$F$3&" "&$F$4&$F$5, ROW($A$1:$A$20)),ROW()-2),1),"")
Column 2: Region
IFERROR(INDEX($A$1:$C$20, SMALL(IF($A$1:$A$20&$B$1:$B$20=$F$3&" "&$F$4&$F$5, ROW($A$1:$A$20)),ROW()-2),2),"")
 Column 3: Pointless Thing
IFERROR(INDEX($A$1:$C$20, SMALL(IF($A$1:$A$20&$B$1:$B$20=$F$3&" "&$F$4&$F$5, ROW($A$1:$A$20)),ROW()-2),3),"")
The only difference between these is the Column number in the INDEX formulas. Now, I am fully aware of the absurdity of having your search criteria (Name and Region) appear in the results table but it’s cool, I’m just illustrating with minimal pointless made up data. Let’s try using this:
Selecting Thom, Yorke and UK gives us a nice chunky result. And how did we get it looking so slick with expanding/contracting borders and alternating colored rows?! Easy, let’s take a closer look at the conditional formatting:
Pay close attention to the order of the conditions, it won’t work properly otherwise. The formulas used are:
For the first condition, I have selected ‘No Color’ for fill:
For the second condition, the formula is:
=NOT(MOD(ROW(),2)) – Choose a white fill AND complete Border around the cell.
For the last condition, the formula is:
=AND(MOD(ROW(),2)=1,H3<>"")
The last thing is to turn the grid-lines off or at least paint the cells in and around the table white. Have a look in the workbook if it doesn’t make sense.
Download Example Workbook
Click here to download Multiple Occurrences workbook. It contains all the examples. Play with the formulas to learn more.
Conclusions
So there you go. I hope you have taken away a number of things about the value of extracting multiple occurrences from a list and a technique for enhancing interactive reporting. If there is one thing I really wanted to convey during this article, its how much I love the Hoff and we can never have enough occurrences of this Germanic demigod. If you enjoyed this article then please share it and let’s get a discussion going in the comments to see what other multiple occurrence madness we can come up with!
Added by Chandoo
Thank you so much Sohail for another wonderful, intelligent & useful article. I had loads of fun reading & learning from it.
If you enjoyed this, please say thanks to Sohail in the comments section.
Keen to learn Advanced Formulas?
Check out Formula Forensics & Array Formula pages.
About the author: Sohail Anwar is a Londoner who has spent over 10,000 hours applying Excel in his professional life and earns well over 6 figures as a result. Now he is on a mission to teach professionals how to massively increase their earnings by learning and applying Excel like never before. Find out more about Sohail on Earnwithexcel and connect with him on LinkedIn.
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.