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.


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.

Easily select max/min values in each row/column or specified range in Excel

Kutools for Excel’s Select Cells With Max & Min Value can help you select the greatest/smallest values or greatest/smallest formula values from selected range in Excel easily. Full Feature Free Trial 30-day!
ad select max min 2

Kutools for Excel - Includes more than 300 handy tools for Excel. Full feature free trial 30-day, no credit card required! Get It Now


The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

  • Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
  • Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
  • Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
  • Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
  • Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
  • Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
  • Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
  • Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
  • More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
kte tab 201905

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Jenne · 1 years ago
    Code not complete. Get Error "Object variable not set or With Block variable not set". What do I do ti fix?
    • To post as a guest, your comment is unpublished.
      José · 9 months ago
      Just select the Chart first, then Run...
  • To post as a guest, your comment is unpublished.
    Bnakey · 1 years ago
    I 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.
    Bnakey · 1 years ago
    I 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.
    Raquel · 2 years ago
    Olá,
    Como posso fazer isto para múltiplos gráficos?


    Obrigado,
    Raquel