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