Here is a ridiculously easy trick to do nice incell pie charts in excel, what more, they will make you look like a charting wizard.
- Download free pie chart font from here (direct download link). The font has 21 pie chart codes starting with empty circle (character ‘a’) and ending with full circle (character ‘u’)
- Install the font (just extract the zip file, copy the font file to your fonts folder, click here to learn how to install a font in windows)
- Open excel, go to your data table and insert a column where you want incell pie charts
- Enter a formula like
=CHAR(CODE("a") + ROUND(data cell*21,1)), just replace the data cell with actual cell code. - Finally change the font to “pie charts for maps” from font dialog.
- There is no step 6, so go ahead and show off this cool table to your colleagues, make their jaws drop in awe.
You can add some conditional formatting to the pie charts like I did to make it nice.
Feel free to download the sample excel I have prepared to know how to do in-cell pie charts
32 Responses to “Create in-cell pie charts in Excel”
The conditional formatting part comes as the icing on the cake... Great tip, thanks.
Bhars
The in cell pie charts concept is not clear to me......the process is not self explanatory..i tried to understand the excel sheet u prepared but failed....i should not learn these concepts while talking to my girlfriend over phone...
Akash
@Bhars ... you are welcome...
@Akash ... Welcome to PHD. Can you tell me if you can understand this while you are not talking with your girlfriend.. :D, just kidding.. if you have any doubts feel free to ask... I may know how to answer them 🙂
Great post ! Thanks for that.
Regards,
John
@John... you are welcome 🙂
[...] since writing the create in-cell pie charts in excel, I have been itching to find a simple enough method to do incell bar graphs. An in-cell bar would [...]
Chandoo, thanks for a wonderful pie charts in a cell trick. If I have a pie chart font which has all the pie from 1% to 100% then do I need to change the formula?
Rgds
Champak
@Champak ... thanks for the comments & welcome to PHD...
Oh yeah, just make sure the formula returns a character code that you can map to your pie chart font.. btw, can you let me know if you come across such font... I may post another tutorial using that font.. 😀
[...] to pie charts, beautify your charts with these 73 designer quality templates, put together in-cell pie charts, bar charts and much more. Tags: charts, cool, excel, howto, ideas, learn, microsoft, technology, [...]
[...] time you had to create a pie chart, consider building a partition chart as these charts can reveal trend information along with how [...]
[...] easy and often over do it. I have blogged few techniques involving pie chart visualizations like in-cell pie charts, speedometer charts, donut clocks and the response from readers has been [...]
Chandoo,
i follow the instructions above, but i getting seven different pies in each cell. what am i doing wrong?
Thanks,
[...] chart / pie font in our computer we can create incell graphs in excel with ease. Click here to see example pie chart, line [...]
[...] Incell Pie charts Tutorial [...]
[...] Bar | Sparklines | Pie charts | Bullet Graphs | w/ Conditional [...]
FYI the formula works better when you multiply by 20 instead of 21.
I am having trouble installing the pie chart font on a Mac G4. It won't pass the verification step. Do you have another link or different copy?
@Ethoros: Thanks for the tweak...
@Bernie: I am not aware of any other fonts like this. Also, may be there is a limitation on TTFs in Mac (no idea what I am talking about.. :D) Can you google for some more fonts and let us know if you find an interesting option that works for Macs as well?
How can you make the font to show up the same way on other people's computers who have not installed the font? In other words, when I share the spreadsheet with other people, their computers don't recognize the font and don't display it correctly. Is there a way to hard code the font?
@Acinil: Unfortunately, that is a limitation with using custom fonts. You need to have the font installed on your reader's computer before they can enjoy your file.
@acinil: I use custom fonts regularly within Word, the only way to share a document containing these is to embed them. The downside is that drastically increases the filesize, but it's still an option worth investigating within Excel if you really need it.
Question, I love this idea however when trying to do the install, I am getting the following error message: Cannot install PIE4MAP.TTF The file....... does not appear to be a valid font.
Any idea why this is occurring? Also when I download your sample, the icons where the formulas are do not resemble a pie chart, they are like wingdings.
Thanks for the help!
Great tip!
Link is step #2 is not working: "Install the font (just extract the zip file, copy the font file to your fonts folder, click here to learn how to install a font in windows)"
Here's a link that might work: http://windows.microsoft.com/en-us/windows-vista/install-or-uninstall-fonts
Thanks chandoo.org for all the excel awesomeness!!!
Thank you so much Chandoo for very useful tutorials.
The char() formula doesn't seem to read the decimal numbers. So why round the data to 1 decimal place instead of 0?
Thanks very much - these pie charts are great. I'm just having one issue - If my data reaches 100%, the pie chart empties and looks like it's representing 0% rather than 100%. All other percentages work fine. 97% looks like 100%. Wondering why this could be/what I can do...
Hi chandoo
Usefull stuff. Today I used this for my work.
Thank you .
Hi Chandoo,
I can't install this font on my office laptop until the support team see the licence file saying Free to install thought it will only be used for self-learning and team report if possible. Is there a workaround as we don't follow BYOD policy ans use only one laptop.
Thank You.
Its bit difficult. Can anyone guide please?
Thanks for the tricks. But how to do conditional formatting