Dynamic x axis break based on values

[NickvdB]

Hi,

I have a waterfall graph in which I can select a team. Some teams have large budget and some have smaller budgets. Is there a possibility (preferably not vba) to have the x axis automatically break at a certain point? For instance 80% of budget amount? So y axis would start at 80K for a team with a budget

Thanks

 

[felixstraube]

Can you show us you data and your graph?

 

[NickvdB]

Can you show us you data and your graph?

No will not show any added value and causing more privacy related issues. Just keep it general: one time I want Y axis to be starting at 80K and another time at 280K how to do that?

 

[felixstraube]

I understand that there may be privacy issues. But you should explain a little more detailed what you need exactly, from the OP its hard for me to tell.

 

[NickvdB]

I understand that there may be privacy issues. But you should explain a little more detailed what you need exactly, from the OP its hard for me to tell.

ok understood. If I have two teams, see below. One with a budget of 1.45mio and another of 0.725mio. I would like the Y axis to start at 1.2mio for team 1 and for instance 0.6mio for team 2.

Team​	Category​	Performance Group​	Account category​	Order​	Comment​	Amount​
1​	Annualized 2024​	IT​		1​		1450000​
1​	Budget 2024​	IT​		2​		1400000​
1​	Merit​	IT​	Personnel costs​	3​		50000​
1​	Vac Fact 2024​	IT​	Personnel costs​	4​		50000​
1​	Replace​	IT​	Personnel costs​	5​		125000​
1​	Growth​	IT​	Personnel costs​	6​		125000​
1​	Transfer​	IT​	Personnel costs​	7​		125000​
1​	Inflation​		Automation costs​	8​		60000​
1​	New​	IT​	Automation costs​	9​		45000​
1​	Savings​	IT​	Automation costs​	10​		-80000​
1​	Others​	IT​		11​		50000​
1​	Budget 2025​	IT​		12​		1950000​
1​	Target 2025​	IT​		13​		1635000​
2​	Annualized 2024​	IT​		1​		725000​
2​	Budget 2024​	IT​		2​		700000​
2​	Merit​	IT​	Personnel costs​	3​		25000​
2​	Vac Fact 2024​	IT​	Personnel costs​	4​		25000​
2​	Replace​	IT​	Personnel costs​	5​		62500​
2​	Growth​	IT​	Personnel costs​	6​		62500​
2​	Transfer​	IT​	Personnel costs​	7​		62500​
2​	Inflation​		Automation costs​	8​		30000​
2​	New​	IT​	Automation costs​	9​		22500​
2​	Savings​	IT​	Automation costs​	10​		-40000​
2​	Others​	IT​		11​		25000​
2​	Budget 2025​	IT​		12​		975000​
2​	Target 2025​	IT​		13​		817500​

 

[felixstraube]

And what rows do you want to show in the graph for each team? How should the graph look like?

 

[NickvdB]

And what rows do you want to show in the graph for each team? How should the graph look like?

Thank you for the patience. The amounts should show up in the graph. For which the annualized, the budget 2024 and budget 2025 should be totals. And based on those amounts I would like to see a break in the axis. So for team 1 the break could be at 80% of the 1400000 and for team 2 the break could be at 80% of 700000.

and for team 2

 

[felixstraube]

I understand.
Excel wont let you dynamically change the min max values of an axis.
So the options I could think of are:
1. Use VBA to set the min value of the axis. Is VBA an option for you?
2. Use a bar chart (not waterfall chart) and use some hidden values to emulate a waterfall chart, and hiding the y axis and just show the data labels based on the real data. (but I'm not sure it this will work).
3. Use 2 different graphs. One for the totals, and one for the non-totals (Merit to Others) like so:

Let me know what option would suite your best and we could work on that.

 

[felixstraube]

Ok with some helper columns and a bar chart hidding some data i got this:

It is not exactly a Waterfall chart but close.
So it would be possible

 

[felixstraube]

Here comparing both charts (with the same size) and how they look :