TM AutoChart: Connect chart axis parameters to cell values
TM AutoChart is a shareware Excel add-in that links the minimum, maximum, major, and minor values of a chart axis to worksheet cells.
http://www.tushar-mehta.com/excel/software/autochart/index.html
Over the past few months I have been trying out different Ribbon layouts for chart related add-ins. For now, I have settled on a split button where the large button provides the primary functionality and the drop down menu includes additional capabilities.
New
Old
I haven’t looked at your add-in, Tushar, but here’s a very simply way of doing it, using a UDF:
http://www.dailydoseofexcel.com/archives/2007/01/12/modifying-shapes-with-udfs/
See the ChangeChartAxisScale function. It still works in Excel 2010. A distinct advantage is that it doesn’t wipe out the undo stack.
Hi John,
Thanks for the tip. The add-in was originally written in the days of Excel 2000. I’ll have to budget some time to explore use of a UDF to manipulate chart elements. The biggest weakness I can anticipate is that if the process stops working there’s not much one can do about it. After all, the documentation for a UDF states that it can only return a result and not change the Excel environment…unless, of course, that got dropped from the documentation also…{grin}
I have been using a invisible series on the chart to set min and max values. Especially useful when multiple charts need to have the same scale. I think I got the technique from Jon Peltier. Doesn’t set the major and minro values, though.
Alex -
If you got this trick from me, it’s because I got it first from someone else, I think from Charley Kyd.
@ AlexJ, I am a little confused why would you add an invisible series you add to set the min and max values. Isn’t this what Excel does using the Autoscale feature? I am sure if you and Jon see the need for this, then there’s something in here for me to learn, which eludes me at this time.
Chrisham -
As AlexJ says, this is “[e]specially useful when multiple charts need to have the same scale.” Each chart has the same dummy series, which contains the min and max X and Y values. Excel’s autoscale then works the same in all charts, so the scales are identical.
Ah gotcha! Thanks Jon, great trick to know!