How do I drive the min and max values of an axis from an Excel Range? This is one of the most commonly asked questions about Excel and with each new release it always amazes me that this feature hasn’t been added to the base product.
It’s a very common scenario to come across, you are building a line chart and it’s all looking ok until Excel suddenly decides to set the min value to 0, all of the detail is lost and you have gone from a nice detailed set of lines to a mishmash of colours a few pixels high.
There are some pretty sophisticated techniques Excel is using when working out what min & max to use, but sometimes we just want to set them to a particular value (normally anything other than 0!).
Here’s a pretty simple set of numbers and the resulting chart we get from Excel (just with all the defaults).
This all looks fine, but let’s change “C” Monday’s value to 86, now look what happens:
Excel has applied its rules and decided that 0 is a good place to start the chart from, but in this case I lose a lot of the detail and end up with all the lines grouped together.
We could, of course, change the Axis min value to something a bit more sensible, so we’ll use the Format Axis option to set a minimum value of 84:
That looks better!
The base numbers had been entered manually, so being able to type a fixed value into the minimum axis is fine, but what if the numbers were coming from a cube or Sql database? Wouldn’t it be really helpful to be able to drive the minimum value from a range; I can change just about every other thing about the chart but after so many years and so many different version I still can’t do this.
Luckily for me (and our customers!) we already have an Excel addin so we can simply add the functionality to do this using one of the new formulae in 6.5:
XL3SetProperty( ObjectType, ObjectName, Property, Arg1, [Arg2],…, [Arg27] )
The formula to drive the chart axis from a range is simply:
=XL3SetProperty("Chart","Chart 1","YMin",$C$1)Other options are:
| Property | Description | Value |
|---|---|---|
| “YMin” or “YMax” | Sets the limits of the Y Axis. | Numeric |
| “Y2Min” or “Y2Max” | Sets the limits of the Y2 Axis. | Numeric |
| “XMin” or “XMax” | Sets the limits of the X Axis. | Numeric |
| “X2Min” or “X2Max” | Sets the limits of the X2 Axis. | Numeric |
Now finally we can build reports (and publish them to the Web), confident that regardless of the data or criteria selected we aren’t going to end up with a line chart starting at 0 and bunching all the lines together.
This formula can also be used to modify various aspects of our own grids, slicers & small multiples based on the values of excel cells. The kind of things that we and our customers wanted to achieve were things like:
- Move dimensions between axes
- Change the member selection types
- Modify various grid properties based on different formulae
Lets look how the formula works to do some of these things:
=XL3SetProperty("Grid","My Grid","HierarchiesOnColumns","[Products]","[Regions]", $a$1)Would move the Product, Region and whichever hierarchy is in $a$1 to the columns (I could use a slicer or drop down to update $a$1 to let the user switch between various hierarchies)
=XL3SetProperty("Grid","My Grid","RemoveEmptyRows",$b$1)Would toggle whether to display rows without data based on the value of $b$1
If there are any aspects of Excel that you think would be useful to drive from a range, please let us know!