Waterfall Chart - Min & Max Scale Based On Cells

GPerky

New Member
Joined
Oct 10, 2020
Messages
7
Office Version
  1. 365
Platform
  1. Windows
Hi Everyone,

Firstly, I would like to set the scale on my Waterfall chart so it maximises the length of the bars - the Auto option on the Waterfall doesn't seem to do this very well.

Secondly, all my data changes based on a drop down for month, so I have a calculation to determine the min & max values.

I have then setup the VBA below on the sheet with the waterfall graph to trigger the change whenever cell B1 (the month) changes, however the scale doesn't seem to change??

Any help much appreciated.

Thanks

Graham

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$b$1" Then

ActiveSheet.ChartObjects("Chart 1").Activate
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("b19")
.MaximumScale = Range("b20")
End With

End If

End Sub
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
Go look at my example workbook for the waterfall chart. It has a formula in the title and gets updated when I change the cell it references. If your chart is not updating then I suspect you don't have automatic calculations on.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,965
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
The trick @Jeffrey Mahoney describes does not work in a waterfall chart, or any of the other new charts added in the past couple of years. These charts use a new charting engine that has some great capabilities but which also is not up to speed on some of the regular features of Excel charts.
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
I disagree with you Jon. I opened my waterfall chart workbook and it does have a formula in the chart title that links to a cell.
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,965
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I disagree with you Jon. I opened my waterfall chart workbook and it does have a formula in the chart title that links to a cell.
Before my earlier reply, and again just now, I built an Excel Waterfall chart (not a stacked column chart formatted to look like a waterfall), and I could not link the chart title to a cell. I've done this thousands of times over the years, so I know the protocol.

Did you do this with a native Excel waterfall, or with a handmade one?
 

Jon Peltier

MrExcel MVP
Joined
May 14, 2003
Messages
4,965
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
@Jeffrey Mahoney , I just went back and saw your link to your waterfall article. That is not a "native" Excel waterfall, it's a stacked column chart. Being a "regular" Excel chart, your waterfall can have a chart title that links to a worksheet cell. @GPerky 's chart is a native waterfall, so it is deficient is several ways, including linking of text elements to cells.
 

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
I was just ready to reply. Yup, it's a column chart. I don't think native waterfall charts were available when I needed it.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,932
Messages
5,639,067
Members
417,067
Latest member
rohitbabshet

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top