We have written about dynamic charts several times before. But I think the technique I am going to show you today beats them all. It is so simple, so easy to set up and so beautiful that I am cursing myself for nothing thinking of it earlier.
First take a look at the dynamic charts in excel demo:
Just follow these 5 steps to create the dynamic chart in excel:
1. Prepare your charts: Make as many charts as you want. Lets say 3.
2. Set up the area where dynamic charts will be loaded: Just take 3 cells in a row and adjust the row height and column width such that the charts can be fit inside snugly. Also, type the chart names (1 for each cell) in the cell. Let us say, the charts you have are for Costs, Sales and Profits, just type these names in the cells.
3. Move and fit charts inside these cells: This should be simple.
4. Finally apply data filter to the cell on top of the 3 cells. Select a filter option and you will see only that chart.
5. Show off your dynamic chart and let people know you kick excel’s butt.
You can see these steps in the dynamic chart tutorial below:
What do you think about this technique?
Which technique you like better? This one or the Dynamic Charts using INDEX() function, Use Data Filters as Chart Filters tips? One issue I can think of with this technique is that, there is no way the filter will tell which chart is selected (as the chart covers the cell text). But this can be overcome with chart titles.
This post is part of our Spreadcheats series, a 30 day online excel training program for office goers and spreadsheet users. Join today.
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.