Dynamic Excel Graph Problem (Axis bounds)

dinesababu1

New Member
Joined
Oct 28, 2016
Messages
12
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.

Example of issue.JPG
 

Attachments

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

Some videos you may like

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
Joined
Mar 22, 2020
Messages
707
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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.
1608891057450.png
1608891538265.png
 

dinesababu1

New Member
Joined
Oct 28, 2016
Messages
12
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
Joined
Mar 22, 2020
Messages
707
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I can't help with VBA. These links may ...
 

Watch MrExcel Video

Forum statistics

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