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
11 Responses to “Who is the most consistent seller? [BYOD]”
The Date column in the sample file is Text not Dates
[…] http://chandoo.org/wp/2015/02/18/calculating-consistency-in-excel/?utm_source=feedburner&utm_med… […]
Great Chandoo. Keep it up, Looking forward more from BYOD..
Thanks
With Excel 2013 the pivot table could be connected to the data model which provides a distinct count.
This will do for invoice count
=COUNTIF(F:F,H12)
Instead of
=COUNTIFS(sales[SELLER],$H12)
Excellent document. How did you make the last graphic? Witch app. Thanks for answer.
Can someone tell me what =countif(sales[date],sales[date]) is counting? The value is 19. Its found in the =SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))
Hi Chris,
=countif(sales [date],sales[date]) function is counting the unique dates in the table.
Vândalo
Excellent document!
Can you explain more about the calculation on Weighted consistency? More specific the small number is 0,00001 ?
How come the number should be smaller if there is more sellers?
Hi,
Not understood this formula: {=SUMPRODUCT(IF(sales[SELLER]=H12,1/COUNTIFS(sales[SELLER],H12,sales[date],sales[date]),0))}
Please explain.
Thanks.