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.

how to make a correlation scatter plot in excel


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

how to make a correlation scatter plot in excel

This will display the basic Correlation Scatter plot.

how to make a correlation scatter plot in excel

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.

how to make a correlation scatter plot in excel

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

how to make a correlation scatter plot in excel

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.

how to make a correlation scatter plot in excel

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.

how to make a correlation scatter plot in excel

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.

how to make a correlation scatter plot in excel

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.

how to make a correlation scatter plot in excel

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.

how to make a correlation scatter plot in excel

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


Similar Readings


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.

how to make a correlation scatter plot in excel

  • 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

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.

how to make a correlation scatter plot in excel

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.

how to make a correlation scatter plot in excel

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

Rafiul Haq

Rafiul Haq

Hello! This is Rafiul. I have an engineering degree and an MBA (finance) degree. I am passionate about all things related to data, and MS Excel is my favorite application. I want to make people's lives easier by writing easy-to-follow and in-depth Excel and finance related guides here at ExcelDemy.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo