Autoscale chart axes based on user inputs

glad_ir

Board Regular
Joined
Nov 22, 2020
Messages
143
Office Version
  1. 2010
Platform
  1. Windows
Hello,

Can anybody help me with this one please?

Is it possible to auto scale chart axes based on cell values?

I have 4 cells of interest - A1 & A2 describe the x axis range of interest and B1 & B2 the y axis range of interest. Ideally I would like my chart to scale the x axis to between 90% of A1 value to 110% of A2 value and the y axis similarly from 90% of B1 value to 110% B2 value.

So for example if cell A1 = 200, A2 = 1000, B1 = 0.5, B2 = 1.5 I'd like my chart x-axis to run from 180-1100 and my y-axis from 0.45-1.65.

Is this possible? Any help is much appreciated!

Thanks!
Iain
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Hi Iain,

You're really talking about 2 distinct methods here. To scale the Y axis, you use code as per below. You said "auto scale" so presumably you want it to run automatically if a change happens in cells B1 or B2. I can help with that but try the standard macro below first to see if it does what you want:

VBA Code:
Sub ScaleMe()
    Sheets(1).ChartObjects(1).Activate
    With ActiveChart.Axes(xlValue, xlPrimary)
        .MaximumScale = Sheets(1).Range("B2").Value2 * 1.1
        .MinimumScale = Sheets(1).Range("B1").Value2 * 0.9
    End With
End Sub

As far as scaling the X axis, this is really achieved via worksheet formulas. There's probably a more elegant way than that below, but but seems to work.

Book2
ABCDEFGHIJKL
11000.5
210001.5
3
4
5
6
7
8
9
10
11
12
13
14
15
16Chart Data Used
17period1002005007501000   
18value0.250.50.7511.25   
19
20Total Chart Data
21period1002005007501000120015002000
22value0.250.50.7511.251.51.752
23
Sheet1
Cell Formulas
RangeFormula
D17D17=A1
E17:K17E17=IFERROR(IF(OFFSET(INDEX($D$21:$K$21,MATCH(D17,$D$21:$K$21,0)),0,1,1,1)<=$A$2,OFFSET(INDEX($D$21:$K$21,MATCH(D17,$D$21:$K$21,0)),0,1,1,1),""),"")
D18:K18D18=IFERROR(HLOOKUP(D17,D21:K22,2,FALSE),"")
E22:K22E22=D22+0.25


1681039753998.png
 
Upvote 0
Solution

Forum statistics

Threads
1,213,501
Messages
6,114,010
Members
448,543
Latest member
MartinLarkin

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
Back
Top