Here is a simple to trick to beat the 56 color limitation in excel when you are designing a chart: use picture files (jpg, gif, png etc.) to fill the chart area.
You will no longer have to worry about limiting your project report / website / annual report etc. colors to the 56 that excel has.
1 + 3 steps to get more colors in excel charts:
- First create a 1*1 pixel sized image of the color you want to use in excel. You can use any software like MS Paintbrush to do this, all you need is the hex code or rgb of the color you want. Visit colour lovers if you are looking for cool color ideas.
- Right click on any of your chart data points and select format data point
- In the dialog select “fill effects”
- Finally, go to “Picture” tab in fill effects and specify your picture file path.
OK your changes. Thats all, now your charts have any color your want.
Also: Download 73 beautiful excel chart templates | Art of excel charting | Become a conditional formatting pro
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.