Always Color Last Bar of a Bar Chart

[rickyou]

Hi, I have an ever increasing bar chart (Day 1, Day 2, Day 3) in blue. At the last data, is an additional row called "Average", which I colored in orange.
Whenever I update the chart, I will add a row before the Average row... and I need to recolor the last bar.

May I know whether there is any way to dynamically set up the formatting so that the last bar is always in orange color?

Thank you.

chart question.xlsx

[jeffreybrown]

Why not just add the average as a second series and then change the series into a line?

Now the average can be seen across all of the data points for a quick analysis.

[rickyou]

Hi Jeff, thanks for the tips. I liked it... but the requirement I have is a standard format used company wide... is there a way to what is needed?

Thanks!

[Andy Pope]

Add another series, with Average data only, and set the overlap to 100%