# Dynamic Excel Graph Problem (Axis bounds)

#### dinesababu1

##### New Member
Hello Everyone,

There is a dashboard I update every month for which I can use some help better automating.

I only need the green column labeled "Prior Year" to appear on certain "forecast" months (hence for the example below the months November and December...and next month I will roll forward to just display in December)

Every month I have been clearing out the formula for this green column so it appears only in the forecast months.

I would like to automate the formula for this column so it is truly blank or nonexistent for all months except the forecast months.

I tried combining IF formulas with NA() formula or "" however this effects the axis my graph. (in the example below I use "IF(AG3="FRCST",AG12,NA())")

It seems that if there are any formulas at all the graph interprets it as data and starts the axis at 0 rather than auto adjusting the axis based on the data.

Is there anyway to solve this issue prefably without using VBA?

..... If there is truly no way to solve it without using VBA than can someone tell me how to use VBA to solve this issue. Thank you everyone.

#### Attachments

• Example of issue.JPG
131.6 KB · Views: 4

### Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

#### GraH

##### Well-known Member
Hello,

One could argue the second chart is better since the y-axis starts at 0. In the first chart you exaggerate the differences because you start at 34,000.
Visually NOV is about half of JAN (50%), while in reality the difference is only 5%.

Since you can't set the bounds via a cell value/formula, you need to set it manually. Or use VBA.

Or maybe an alternative chart can be used, that takes JAN as base 0 (% or Amount). Or the deviation of your budget line (so budget is 0). But I could not derive those values from your picture.
NOV is 5% less then JAN and difference is 2K, clearly depicted in the chart.

#### dinesababu1

##### New Member
Thank you for response but I would prefer not to change the graph.

Is there any formula I can use so the graph ignores the data and the axis adjusts? (similar to NA(), or maybe even a format change?)

If there isn't any formula that can do what I need, may I have a VBA code that can help?

#### GraH

##### Well-known Member
I can't help with VBA. These links may ...

Replies
7
Views
64
Replies
5
Views
496
Replies
3
Views
280
Replies
1
Views
242
Replies
0
Views
80

### Forum statistics

1,127,107
Messages
5,622,782
Members
415,927
Latest member
vedasinternational

### 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.

### Which adblocker are you using?

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

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