Automatic values of Y axis

**keith2511** · 06-20-2023, 04:46 AM

I have a stacked pivot chart that also uses a slicer that has three buttons.

Depending on how the slicer is determining which data to use for the chart, the automatic Y axis values are as shown in the attached.

Why is the Minimum value not set to zero, when the first button on the slicer is selected and how can I fix it so that the value is zero?

The underlying data for the pivot table is in the range blank to 102.

Due to the sensitive nature of the data, I cannot provide a copy of the workbook, sorry.

Thanks

**josephteh** · 06-20-2023, 04:50 AM

Type "0" (without the double quotes) in the Minimum Bounds box and follow by the tab key.

**keith2511** · 06-23-2023, 11:45 AM

I can't do that because when the slicer is used to filter different areas of the data, a negative value is (rightly) included but is not shown correctly in the chart if the minimum value is set to zero.

**MrShorty** · 06-23-2023, 12:12 PM

This article is the best I am aware of that explains how Excel's "auto" algorithm chooses axis limits: https://peltiertech.com/how-excel-ca...t-axis-limits/

However, I'm not sure that really is much help here. However the "auto" algorithm works, MSFT gives us only two built in choices -- accept the auto axis limits or manually enter the axis limits we want. There are no built in options for an "auto" algorithm that is not the "auto" algorithm provided by Excel. In your case, you mostly like the "auto" algorithm, but not always. I expect that the scenarios where you don't like the auto algorithm occur frequently enough that you are unhappy needing to manually edit the axis limits in those situations.

At this point, the only path forward that I'm aware of is to use a macro script of some kind. For your application, the script might simply need to check if the default "auto" algorithm chooses a value greater than 0 and forces the value to 0 in those cases. Otherwise, it would leave the "auto" selection checked. VBA has long been the preferred macro language (though that seems to be changing), so everything I know about these scripts would use VBA as the macro script language. These two tutorials might be more than necessary, but should allow you to see some of what is involved in a macro script that modifies chart axis limits:
https://peltiertech.com/chart-udf-control-axis-scale/
https://peltiertech.com/link-excel-c...lues-in-cells/
I would add that most of the discussion I see pertains to regular charts. In some ways, pivot charts are their own animal, so you may need to explore how these ideas work with pivot charts.

Before delving deeper, cost-benefit analysis. Is it worth the effort to continue? As tedious as it is, sometimes I prefer to just manually change the axis limits because I judge it is not worth the effort to automate it.