Excel chart: Ordering by values (automatically)

Question

I've got the following problem:

I need to order my values in bar chart from the highest to lowest:

I know that i can use pivot table and pivot chart, but it could be a bit complicated in the future.

Answer 1

I would recommend to achieve this by using helper columns to order the data as you need.

1. Column C: Create a rank of the values using RANK function, for example: =RANK(B2, $B$2:$B$16)
2. Column D: Create an order column with numbers running from 1 to 15
3. Column E: Lookup each of the values in the order column using a combination of MATCH and INDEX, for example: =INDEX($B$2:$B$16, MATCH(D2, $C$2:$C$16, 0))
4. Column F: Lookup each of the dates using a similar approach as 3. wrap with a TEXT function to avoid excel ordering the dates in the graph, for example: =TEXT(INDEX($A$2:$A$16, MATCH(E2, $C$2:$C$16, 0))|"dd mmmm yyyy")
5. Update your graph to reference columns E and F
6. If all value cells aren't populated (as per your example) then you can wrap the formulas with an IFERROR to tidy it up

Example output below:

UPDATE:

To avoid issue when you have same value appearing you can add a countif to your rank function RANK(B2, $B$2:$B$16) + COUNTIF($B$2:B2, B2)-1