# How to Make a Correlation Scatter Plot in Excel (2 Quick Methods)

Get FREE Advanced Excel Exercises with Solutions!

When we want to establish a mathematical relationship between two variables, we use Correla

### Scatter Plot in Excel (with Easy Steps)

tion. Here, we’ll show you 2 methods of how to make and format a Correlation Scatter plot in Excel. For our demonstration, we’ve taken a dataset with 3 columns: “Month”, “Advertising”, and “Sales”. We’ll plot the impact of Advertising on Sales in this tutorial.

## 2 Methods to Make a Correlation Scatter Plot in Excel

### 1. Use of Insert Charts Feature to Make a Correlation Scatter Plot in Excel

Firstly, we need to remember that our independent variables should be on our left side. Here, our independent variable is “Advertising”, hence, it is on the left of the dependable variableSales”. We’ll select our data range, then we’ll insert Scatter Plot from the Insert tab.

Steps:

• Firstly, select the cell range C4:D10.
• Secondly, from the Insert tab >>> Insert Scatter (X,Y) or Bubble Chart >>> select Scatter.

This will display the basic Correlation Scatter plot.

Now, we’ll format the Scatter plot. At first, we’ll change the Chart Title

• Double Click on the text “Sales” inside the Chart then, type “Correlation Scatter Plot”.

Secondly, we’ll add Axis Title to both X and Y-Axis.

• Firstly, select the plot.
• Secondly, from Chart Elements >>> put a tick mark on Axis Titles.

• Finally, Double Click on the Axis Title and change it.
• Y-Axis title: “Sales”.

Thirdly, we’ll add a Trendline in our Scatter plot.

• Firstly, select the Scatter plot.
• Secondly, from the Chart Elements >>> Trendline >>> select “More Options…”.

The Format Trendline option will appear.

• Thirdly, select Linear from Trendline Options.
• Finally, put a tick mark on these –
• Display Equation on chart”.
• Display R-Squared value on chart”.

This will show the Correlation Equation and Trendline on the Scatter plot.

We can simply move the Equations for better visibility.

Then, we’ll change the starting value of both Y and X-Axis.

• Firstly, Double Click on the Y-Axis values.

This will bring up the Format Axis option.

• Finally, set the Minimum Bounds to 230 from 0.

We’ve changed the initial value for the Y-Axis.

Similarly, we can do the same for the X-Axis. Now our Graph looks much better. However, we’ll do a little more formatting on this.

After that, we’ll turn off the Gridlines and show Data Labels.

• Firstly, select the Correlation Scatter plot.
• Secondly, from the Chart Elements >>> untick Gridlines to hide it.
• Finally, put a tick mark on Data Labels to show this.

Our Scatter plot looks tiny. We can enlarge it.

• Firstly, select the plot and move the cursor to the edge.
• Then, drag it to resize the Scatter plot.

Moreover, we can hold SHIFT to keep the aspect ratio intact.

Thus, we’ve made a Correlation Scatter plot in Excel and formatted it. This is what the final step should look like.

### 2. Incorporating VBA to Make a Correlation Scatter Plot in Excel

In the last method, we’re going to use Excel VBA to make a Correlation Scatter plot. In our code, we just need to change the range of X-Axis and Y-Axis. Without further ado, let’s jump into the procedures.

Steps:

• Firstly, from the Developer tab >>> select Visual Basic.

This will bring up the Visual Basic window.

• Secondly, from Insert >>> select Module.

Here in this Module window, we’ll write our code.

• Thirdly, type the following code.
``````Sub CorrelationScatterPlotVBA()
Dim xRange As Range
Dim yRange As Range
Set xRange = Range("vPlot!C5:C10")
Set yRange = Range("vPlot!D5:D10")
Dim sChart As Chart
sChart.HasLegend = False
With sChart
.ChartType = xlXYScatter
.SeriesCollection.NewSeries
.SeriesCollection(1).Name = "=""Scatter Correlation Plot Using VBA"""
.SeriesCollection(1).XValues = xRange
.SeriesCollection(1).Values = yRange
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales"
End With
ActiveChart.ChartArea.Select
ActiveChart.FullSeriesCollection(1).Trendlines(1).Select
Selection.DisplayEquation = True
Selection.DisplayRSquared = True
End Sub``````

VBA Code Breakdown

• Firstly, we’re calling our Sub Procedure CorrelationScatterPlotVBA.
• Secondly, declaring our variable types.
• Thirdly, setting the X-Axis and Y-Axis. We’re working with the “vPlotSheet. You need to change this line for your data.
• Then, we’re hiding the Legend with HasLegend property.
• After that, we’re setting our plot type as Scatter.
• Then, we’re adding Trendline. Axis Titles to the plot.
• Finally, using the DisplayEquation property we’re showing the Equation in our Chart.

• Then, Save and close the VBA window.

Now, we’ll run our code.

• Firstly, from the Developer tab >>> select Macros.

This will bring up the Macro window.

• Finally, select our Sub Procedure and press Run.

We’ll notice a Correlation Scatter plot in a new Sheet. In conclusion, we’ve shown you yet another method of achieving our goal.

## Practice Section

We’ve included practice datasets for each method in the Excel file.

## Conclusion

We’ve shown you 2 methods of how to make a Correlation Scatter plot in Excel. If you face any problems, feel free to comment below. Thanks for reading, keep excelling!

## Related Articles

Rafiul Haq

Greetings! I am Rafiul, an experienced professional with an engineering degree and an MBA in finance. Data analysis is my passion, and I find Excel to be an invaluable tool in this domain. My goal is to simplify complex concepts by providing comprehensive and user-friendly guides on Excel and finance topics, aiming to enhance people's productivity and understanding at ExcelDemy.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF