To create a sorted bar chart of this data, one option would be to highlight cells J7:L13, manually sort it and then create a bar chart.
In cases where the data is changing very often, it would make sense to set it up and let Excel sort the data automatically.
In this case, create a data preparation table that gives a sorted version of the information in the original table.
Note that we will be retaining the Other category at the last spot.
The chart will then be used as the reference to the chart, so when the sorting in the table changes, so does the chart.
The RANK() function is great for getting the information in ascending or descending order.
In this case, we will be using the descending order because ultimately, we want to show rank 1 first, followed by rank 2 and so on.
The syntax of the RANK() function is RANK(number,ref,[order])
- number – the cell value
- ref – the number or range it should compare to. In this case we exclude Other.
- [order] – 0 for descending, 1 for ascending order. By default, this is set to descending so you can also just leave this parameter blank.
Cell M7 = RANK(K7,$K$7:$K$13)
Pull this formula down to row 13, which contains the category just above Other.
This will now show the rank of the categories in descending order, with China having the highest rank.
Add another column with numbers 1 down to 8.
This will be used as the reference to display the country corresponding to the rank number in column O and the corresponding value in column P.
For rank 8, do a cell reference to the Other category.
Cell P14 = J14
Cell G14 = K14
For ranks 1 to 7, we will use the Index Match approach to display the country and value. (Here is the link to the Index Match walkthrough.)
The syntax of INDEX() is: INDEX(array,row_num,[column_num])
- array – The range that contains the value you want to display. In this case, it is J7:J13. The Other category is excluded since we already pinned it as the last row of the data preparation table.
- row_num – This tells how many rows the function needs to go down. Since our data is dynamic, we will use the MATCH() function.
- column_num – This defines how many columns it needs to move right. Since we only have one column, we can leave this blank.
The syntax of MATCH() is: MATCH(lookup_value,lookup_array,[match_type])
- lookup_value – The cell that contains the value you want to find. For the first row, it is O7.
- lookup_array – The range that contains the value you want to find. In this case, it is M7:M13. Make sure you have the exact same height as your INDEX() area.
- match_type – 1 for less than, 0 for exact match, and -1 for greater than.
Cell P7 = INDEX($J$7:$J$13,MATCH(O7,$M$7:$M$13,0))
Drag this formula down to the last row above the Other.
Do the same for the Value column, but instead of using J7:J13 as the index array, use K7:K13.
Cell Q7 = INDEX($K$7:$K$13,MATCH(O7,$M$7:$M$13,0))
Drag this formula down to the last row as well.
We will be using this data prep table to create the bar chart.
In case you want to exclude the Other category dynamically, use the formula below.
Further details will be discussed in a separate walkthrough.
Cell M7 = IF(J7=”other”,COUNTA($J$7:$J$14),SUMPRODUCT(($J$7:$J$14<>”other”)*(K7<$K$7:$K$14))+1)