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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.
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.· 6 months agoCode 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.· 1 years 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.· 1 years 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.· 1 years agoOlá,
Como posso fazer isto para múltiplos gráficos?