Waterfall Chart - Dynamic Axis Start

EasterGreen

New Member
Joined
Sep 7, 2009
Messages
48
I have a waterfall chart in a report that has different departments' data fed in to it and each has significantly different spends.
For larger departments the total "start" and "end" columns are quite tall and force the chart axis to be large numbers.
Any minor bars showing small movements between start and end are hard to interpret.
Is there any way to dynamically change the Y axis minimum from zero to x such that I can set a formula to define the start point?
Image 1 is the automatic axis, Image 2 is where I have manually altered the axis start point.
1593600278631.png

1593600286584.png
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,063
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You could use a function like this:

Code:
Function SetMin(MinValue As Double, ChartName As String)
    Application.Caller.Worksheet.ChartObjects(ChartName).Chart.Axes(xlValue).MinimumScale = MinValue
    SetMin = "Set"
End Function
and then call it from a cell on the same sheet as the chart (you'll need to alter it if it's a chart sheet) passing the min value and chart name.
 

EasterGreen

New Member
Joined
Sep 7, 2009
Messages
48
Thanks for your reply. I'm afraid I don't follow it.
My chart is "Chart1" and the minimum value could be in cell D14.
And not sure what call the cell means.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,063
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
I mean you'd add that code to a normal module in your workbook, then in a cell enter;

=SetMin(D14, "Chart 1")

for example. You could also use an event Like Worksheet_Change and just monitor the cell(s) that you use for the relevant chart values.
 

EasterGreen

New Member
Joined
Sep 7, 2009
Messages
48
I really appreciate the advice but I can't get this to work.
I have entered:
Function SetMin(MinValue As Double, ChartName As String)
Application.Caller.Worksheet.ChartObjects(Chart1).Chart.Axes(xlValue).MinimumScale = MinValue
SetMin = "Set"
End Function

and then in my worksheet typed =SetMin(D14,"Chart1") in cell D15
I get #Value! as the result in D15 and the chart doesn't change.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,038
Messages
5,472,116
Members
406,804
Latest member
xbinsx

This Week's Hot Topics

Top