How to change chart axis's min/max value with formula in Excel?
When we create a scatter chart in Excel, the chart will figure out the max/min axis value automatically. Sometimes, users may want to determine and change the max/min axis value by formulas, how to deal with it? This article will introduce a VBA macro to determine and change chart axis's min/max value with formula in Excel.
Excel Productivity Tools
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
Change chart axis's min/max value with formula in Excel
To change chart axis's min/max value with formulas in a scatter chart in Excel, you can do as follows:
Step 1: Select your source data, and add a scatter chart with clicking the Insert Scatter (X, Y) and Bubble Chart (or Scatter)> Scatter on the Insert tab.
Step 2: Find a blank range besides source data, says Range E1: G4, add titles as below screen shot shown:
Step 3: Figure out the minimum value and maximum value for X axis and Y axis with formulas:
(1) Maximum value in X axis: In Cell F2 enter the formula =ROUNDUP(MAX(A2:A18)+2,0), and press the Enter key;
(2) Minimum value in X axis: In Cell F3 enter the formula =ROUNDDOWN(MIN(A2:A18)-1,0), and press the Enter key.
(3) Tick value in X axis: In Cell F4 type 1 or other tick value you need;
(4) Maximum value in Y axis: In Cell G2 enter the formula =ROUNDUP(MAX(B2:B18)+20,0), and press the Enter key;
(5) Minimum value in Y axis: In Cell G3 enter the formula =ROUNDDOWN(MIN(B2:B18)-10,0), and press the Enter key;
(6) Tick value in Y axis: In Cell G4 type 10 or other tick value you need.
Note: In above formulas, A2:A18 is the Price column that you figure out x axis scale based on, B2:B18 is the Weight column that you figure out y axis scale based on. And you can adjust the maximum value or minimum value with changing the numbers in above formulas based on your needs.
Step 4: Select the scatter chart, and right click current worksheet name on the Sheet tab bar, and select the View Code from the right-clicking menu.
Step 5: In the opening Microsoft Visual Basic for Applications window, copy and paste the following code into the window:
VBA: Change Chart Axis's min/max values by formulas
Sub ScaleAxes() With Application.ActiveChart.Axes(xlCategory, xlPrimary) .MinimumScale = ActiveSheet.Range("F3").Value .MaximumScale = ActiveSheet.Range("F2").Value .MajorUnit = ActiveSheet.Range("F4").Value End With With Application.ActiveChart.Axes(xlValue, xlPrimary) .MinimumScale = ActiveSheet.Range("G3").Value .MaximumScale = ActiveSheet.Range("G2").Value .MajorUnit = ActiveSheet.Range("G4").Value End With End Sub
Note: F3 is minimum value in X axis, F2 is maximum value in X axis, F4 is the tick value in X axis, G3 is minimum value in Y axis, G2 is maximum value in Y axis, G4 is the tick value in Y axis, and you can change all of them based on your needs.
Step 6: Run this VBA with clicking the Run button or pressing the F5 key.
Then you will see the minimum value and maximum value of X axis and Y axis all are changed in the selected scatter chart at once.
Excel Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 months agoI should point out that I have multiple charts that I want to set (all the same) in the same sheet.
To post as a guest, your comment is unpublished.· 2 months agoI got an error running this code. Not sure why, as everything is the same. Error: "Object variable or With block variable not set"
To post as a guest, your comment is unpublished.· 8 months agoOlá,
Como posso fazer isto para múltiplos gráficos?