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

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.

Download Practice Workbook

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 variable “Sales”. 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.
  • X-Axis title: “Advertising”.
  • 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.

Read More: How to Find Correlation between Two Variables in Excel

Similar Readings

• How to Make a Correlation Matrix in Excel (2 Handy Approaches)
• Calculate Pearson Correlation Coefficient in Excel (4 Methods)
• Use Scatter Chart in Excel to Find Relationships between Two Data Series
• How to Calculate Autocorrelation in Excel (2 Ways)

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
Set sChart = Charts.Add
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
        .SeriesCollection(1).Trendlines.Add
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Advertising"
        .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

• 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.

Read More: How to Calculate Cross Correlation in Excel (2 Quick Ways)

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

• Calculate Spearman Correlation in Excel (3 Easy Methods)
• How to Calculate Correlation Coefficient in Excel (3 Methods)
• Interpret Correlation Table in Excel (A Complete Guideline)
• How to Calculate P Value for Spearman Correlation in Excel
• How to Make a Scatter Plot in Excel with Two Sets of Data (in Easy Steps)
• How to Combine Two Scatter Plots in Excel (Step by Step Analysis)