Selecting specific data for a waterfall chart

Hi,

I made a waterfall chart using a very helpful youtube video. It shows full year 2021 actuals and 2022 forecast, with the variance in between for around 60 different markets.....you can guess the issue I'm having with this.

Most of the markets are not worth showing as the variance is tiny. I've gone through the variances manually and this is what I want to show:

2021
USA
Canada
UK
Colombia
Germany
South Africa
Netherlands
All other markets
2022

Please can someone advise me on how to go from showing all 60 markets to a select few i.e. the ones showing the highest positive variance and the ones showing the highest negative variances, with everything else added together as 'All other markets'? And how to automate this, so that I don't have to go through all the variances and manually choose which ones to show / add in the 'All other markets' etc etc - this will be provided within a tight deadline so automated would be ideal!

Do I need ranking formulas / conditional formatting? If so (or for any other method), please can you explain the steps.

Thank you!

P.S. I have attached a copy of the waterfall chart I have so far showing all 60 markets.

=ABS(A12) gives me #VALUE! as A12 is text. Did you mean another cell, not A12?

I understand your approach, to sort, decide how many to plot and then add a line for All other markets - the problem is I would need to create the waterfall chart everytime right?

Thank you! This is getting there - please see attached an updated version.

However, a couple things I noticed / changed:

• I used RANK instead of ABS because I'm not sure why I would want to see the -0.9 as a positive. When hiding the rows I don't want to see, the ABS formula would mean I have to hide 2 sets of numbers instead of 1 (the lower positive and lower negative variances.....if that makes sense)
• Using RANK, I could hide all the markets I don't want to see in one go.

And the bit I'm stuck on now:

• Automating the formulas highlighted in yellow, so that it knows to skip the hidden rows. E.g. AF68 should be 606.3 and AF69 should be 626.1 - please can you advise on this? the waterfall chart is almost there!

Thank you

Hi @JeteMc

Sorry for the late reply, didn't see this until now... And thanks for your reply!

I've done as suggested, but now the bar for 'others', in the waterfall chart itself is missing. You can see this in the attached version.

Do you know what I need to do to get the bar for 'others' please?

Thanks!