Change chart from value by year to value in ascending order

[Ironman]

Hi

I'm having difficulty in changing the layout of the simple chart below from chronological order to ascending mileage order.

The chart data range is ='Daily Tracking'!$B$369:$CC$369,'Daily Tracking'!$B$375:$CC$375

Row 369 is the year and Row 375 is the mileage.

What I'm trying to do is change the order of the chart to ascending mileage and then the year (so 2013 will be the first entry, 2017 the second, 2011 the third and so on), but I don't know how to do this without messing with the above ranges, which I don't want to do. If this can be done then the data bar for the current year will change position in the chart as the year progresses.

Help greatly appreciated.

Many thanks!

 

[Jon Peltier]

You need to sort the chart's source data. If you don't want to mess with the source data, then use the SORT() function to produce another range with the appropriate sorting, and use that as the chart source data.

 

[Ironman]

Many thanks Jon. Coincidentally, within the last hour I've copied the range and have worked from that (I just used Data > Sort). Although it looks just the way I want it to, when I input a value resulting in the next year's end total being exceeded, I need to manually re-sort the range for it to update on the chart. Is there any way this can be automated - or does the SORT function enable this?

 

[Jon Peltier]

I don't know what this means: "when I input a value resulting in the next year's end total being exceeded".

Does this break the sort order? Sorting the sheet is totally independent of the chart, and the chart does not sort the data. If the data is changed and is no longer sorted, you will need to sort it again.

 

[Ironman]

Ah, OK - yes, I input a figure that changed the sort order i.e. it moved up a position. What is the advantage of the SORT function vs. simply highlighting the range and going to the ribbon and selecting Data > Sort?

 

[Jon Peltier]

SORT is as dynamic as any function (if you've selected your data to sort correctly), meaning you don't have to go up and click on the Sort button.