Table of Contents
Creating a correlation graph in Excel allows you to visually represent the relationship between two variables. To do so, first organize your data in a table with the two variables side by side. Then, select the data and go to the Insert tab, where you can choose the type of graph you want to create. Select “Scatter” and then the “Scatter with only Markers” option. This will create a basic scatter plot graph. To add a trendline and correlation coefficient, right click on the data points and select “Add Trendline.” Finally, click on the trendline and go to the Format tab to customize the appearance and display the correlation coefficient. This will create a clear and informative correlation graph in Excel.
Create a Correlation Graph in Excel (With Example)
Often you may want to create a graph in Excel that allows you to visualize the between two variables.
This tutorial provides a step-by-step example of how to create this type of correlation graph in Excel.
Step 1: Create the Data
First, let’s create a dataset with two variables in Excel:
Step 2: Create a Scatterplot
Next, highlight the cell range A2:B21.
On the top ribbon, click the Insert tab, then click Insert Scatter (X, Y) in the Charts group and click the first option to create a scatterplot:
The following scatterplot will appear:
Step 3: Add Correlation Coefficient
To calculate the correlation coefficient between the two variables, type the following formula into cell A23:
=CORREL(A2:A21, B2:B21)
The following screenshot shows how to use this formula in practice:
The correlation coefficient between these two variables is 0.9835.
Note that the correlation between two variables can range between -1 and 1 where:
- -1 indicates a perfect negative linear correlation
- 0 indicates no linear correlation
- 1 indicates a perfect positive linear correlation
In our example, a correlation of 0.9835 represents a strong positive correlation between the two variables.
This matches the pattern that we see in the scatterplot: As the value for x increases, the value for y also increases in a highly predictable manner.
Related:
The following tutorials explain how to perform other common tasks in Excel:
Cite this article
stats writer (2024). How do I create a correlation graph in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-i-create-a-correlation-graph-in-excel/
stats writer. "How do I create a correlation graph in Excel?." PSYCHOLOGICAL SCALES, 25 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-i-create-a-correlation-graph-in-excel/.
stats writer. "How do I create a correlation graph in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-i-create-a-correlation-graph-in-excel/.
stats writer (2024) 'How do I create a correlation graph in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-i-create-a-correlation-graph-in-excel/.
[1] stats writer, "How do I create a correlation graph in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do I create a correlation graph in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.