Is it possible to have Excel use the min of the data instead of 0 for the Y-Axis minimum. I know that I can manually set the minimum value, but if I am using a filter on the pivot table, then probably the minimums will be different for each filter selection, so I would like for Excel to compute a y-axis minimum like it computes a maximum. I don't see any way to do this, but I thought that I would check. a VBA solution is fine.
I have created an example with random data. In the example, I have 23 companies over a 4 month period. In order to best see a pivot chart of the companies, I divided the companies into 4 groups based on total sales: Fantastic, high, medium & low. I put the group into the pivot chart filter. Now, if I pick the low group, it sort of looks OK, but it would be better if the Y-axis lower bound were 10k. But, by the time you choose the fantastic group (#4), the lines are scrunched because Excel wasted 0 to 70k. Arggg, I hate it when Excel is stupid.
BTW, this cannot be a macro-enabled workbook, so programmatically changing the Y-axis bounds when the Quartile changes is not an acceptable solution.
Pivot Chart Y-Axis
-
- StarLounger
- Posts: 93
- Joined: 04 Mar 2010, 16:32
Pivot Chart Y-Axis
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78686
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Pivot Chart Y-Axis
As far as I know, you're out of luck. Automatically changing the minimum or maximum of the y-axis of a chart can only be done using VBA code.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 93
- Joined: 04 Mar 2010, 16:32
Re: Pivot Chart Y-Axis
I just looked at VBA solutions. The best that I can do is to initially set the MinimumScale to a value based on the data minimum. At least, this minimum does not get reset to zero when switching the item filtered.
-
- 4StarLounger
- Posts: 596
- Joined: 14 Nov 2012, 16:06
Re: Pivot Chart Y-Axis
Maybe:
You do not have the required permissions to view the files attached to this post.
-
- StarLounger
- Posts: 93
- Joined: 04 Mar 2010, 16:32
Re: Pivot Chart Y-Axis
Thanks snb, that's what I meant when I said " set the MinimumScale to a value based on the data minimum." It's not what I wanted, but it's better than leaving it at zero.