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

**Table of Contents**hide

### 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
```

**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 “**vPlot**”**Sheet**. 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.

**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)**