Dynamically update the order of an Excel chart

You may not want to permanently maintain the order of a chart, instead wanting it to dynamically update the order based on the results.

 

Using pivot tables, you can quite easily sort the results ascending or descending and add a filter to only bring back the top ‘x’ results. Here is an example of how to do this bringing back just the current top 10 results. But if you’re not pivoting the data you can also do this with formulas.

 

In this example we have sales figures from each country but we only want to display the current top 5 countries on a horizontal bar chart. The chart is based on these figures from a calculations sheet, the countries are not sorted by sales.

 

 

The easiest method is with your data stored in an Excel table as per the above image. You can simply drop-down Sales, sort them Largest to Smallest and then fix the chart to use B2:C7 to ensure it is always looking at the top 5 whenever the numbers change.

 

However if you’re using an older version of Excel, or for some other reason you can format your data in a table, there is still a method of doing this using formulas. You’ll need the LARGE, INDEX and MATCH functions.

 

The example below shows the LARGE function in action in cell D3 (see formula used in D2). The function simply returns the x largest number in the selected array, in this case because 1 has been entered it’s the 1st largest number which is the 78 sales from Canada. =LARGE(C3:C12,2) would’ve returned 76 which is the 2nd largest value (Italy).

 

 

 

For reference, if you wanted to do the opposite the SMALL function works in much the same starting from the smallest values. Continuing with our example, we now have 5 LARGE formulas providing us with the 5 largest values.

 

 

Finally you need a formula to tell you which country each value relates to. So you need Canada alongside the 78, Italy alongside the 76, etc. This is where the INDEX and MATCH functions come into play.

 

I’ll split the two functions for explanation purposes and then combine them. Firstly use MATCH to find which position in the country array relates to the 78. The below formula tells us that the match to D3 (the value of 78) is in the 7th position in the C3 to C12 array.

=MATCH(D3,$C$3:$C$12,0)

 

We then use INDEX to bring back the country in the 7th position. This function would return Canada, but the value of 7 has been hard-coded:

=INDEX($B$3:$C$12,7,1)

 

Bringing it all together, the hard-coded 7 should just be the result of the MATCH function instead:

=INDEX($B$3:$C$12,MATCH(D3,$C$3:$C$12,0),1)

 

Now we have our top 5:

 

 

Now simply set your horizontal bar chart to always take the values from D3:E7 and you have a dynamic automatically-updating top 5 countries sales chart:

 

Leave a Reply

Your email address will not be published. Required fields are marked *