Correlation Chart in Excel
Last Updated :
11 Sep, 2023
Correlation basically means a mutual connection between two or more sets of data. In statistics, bivariate data or two random variables are used to find the correlation between them. Correlation coefficient is generally the measurement of correlation between the bivariate data which basically denotes how much two random variables are correlated with each other.
If the correlation coefficient is 0, the bivariate data are not correlated with each other.
If the correlation coefficient is -1 or +1, the bivariate data are strongly correlated with each other.
r=-1 denotes strong negative relationship and r=1 denotes strong positive relationship.
In general, if the correlation coefficient is close to -1 or +1 then we can say that the bivariate data are strongly correlated to each other.
The correlation coefficient is calculated using Pearson’s Correlation Coefficient which is given by :

where,
r: Correlation coefficient
[Tex]x_i
[/Tex]: Values of the variable x.
[Tex]y_i
[/Tex]: Values of the variable y.
n: Number of samples taken in the data set.
Numerator: Covariance of x and y.
Denominator: Product of Standard Deviation of x and Standard Deviation of y.
In this article, we are going to discuss how to make correlation charts in Excel using suitable examples.
Example 1: Consider the following data set :

Finding Correlation Coefficient in Excel
In Excel to find the correlation coefficient use the formula :
=CORREL(array1,array2)
array1 : array of variable x
array2: array of variable y
To insert array1 and array2 just select the cell range for both.
1. Let’s find the correlation coefficient for the variables X and Y1.

array1 : Set of values of X. The cell range is from A2 to A6.
array2 : Set of values of Y1. The cell range is from B2 to B6.
Similarly, you can find the correlation coefficients for (X, Y2) and (X, Y3) using the Excel formula.
Finally, the correlation coefficients are as follows :

From the above table we can infer that :
X and Y1 has negative correlation coefficient.
X and Y2 has positive correlation coefficient.
X and Y3 are not correlated as the correlation coefficient is almost zero.
Correlation Chart in Excel
A scatter plot is mostly used for data analysis of bivariate data. The chart consists of two variables X and Y where one of them is independent and the second variable is dependent on the previous one. The chart is a pictorial representation of how these two data are correlated with each other.
Three cases are possible on the basis of the value of the correlation coefficient, R as shown below :

Types of Correlation Chart
Example 2: Consider the following data set :

The correlation coefficients for the above data set are :

The steps to plot a correlation chart are :
- Select the bivariate data X and Y in the Excel sheet.
- Go to the Insert tab at the top of the Excel window.
- Select Insert Scatter or Bubble chart. A pop-down menu will appear.
- Now select the Scatter chart.

- Now, we need to add a linear trendline in the scatter plot to show the correlation between the bivariate data. In order to do so, select the chart and from the top right corner click on the “+” button and then check the box of Trendline.

- The trendline is now added and our correlation chart is now ready.

Negative relationship chart
- Now you can format the Trendline by selecting and clicking on the “Format Trendline” option. A dialog box will open where you can change the type and color of the trendline and also show the [Tex]R^2
[/Tex] value in the chart.


You can further format the above chart by making it more interactive by changing the “Chart Styles”, adding suitable “Axis Titles”, “Chart Title”, “Data Labels”, changing the “Chart Type” etc. It can be done using the “+” button in the top right corner of the Excel chart.
Finally, after all the modifications the charts look like this:

Correlation Chart 1
Since the correlation coefficient is R=-0.79, we have obtained a negatively correlated chart. The linear trendline will grow downwards.

Correlation Chart 2
Since the correlation coefficient is R=0.89, we have obtained a positively correlated chart. The linear trendline will grow upwards.

Correlation Chart 3
Since the correlation coefficient is R=0.01, which is approximately 0, so we have obtained a zero-correlated chart. The linear trendline will be a straight line parallel to X-axis and it implies the bivariate data X and Y3 are not correlated to each other.
What does a positive correlation look like on a correlation chart?
In a correlation chart, a positive correlation is visually represented by points that tend to form an upward-slopping trendline. As one variable increases, the other variable also tends to increase.
How to create a correlation chart in Excel?
To create a correlation chart in Excel follow the below steps:
Step 1: Select the data for both variables.
Step 2: Go to the “Insert” tab and choose “Scatter” from the Chart group.
Step 3: Select the Scatter plot type that suits your data.
Step 4: If desired, add a trendline to the chart by selecting the chart and going to ” Chart Elements”. Check the “Trendline” Option.
Similar Reads
Combination Charts in Excel
Sometimes while dealing with hierarchical data we need to combine two or more various chart types into a single chart for better visualization and analysis. These are known as "Combination charts" in Excel. In this article, we are going to see how to make combination charts from a set of two differe
5 min read
Creating Charts using openpyxl
Charts are a powerful way to visualize data, making it easier to analyze and present information. While working with Excel files in Python, the library openpyxl comes into the picture and can be used as a tool for automating tasks like reading data, writing data, formatting cells, and of course crea
7 min read
Correlation and Regression
Correlation and regression are essential statistical tools used to analyze the relationship between variables. Correlation measures the strength and direction of a linear relationship between two variables, indicating how one variable changes in response to another. Regression, on the other hand, go
8 min read
How to Calculate Partial Correlation in Excel?
Partial correlation helps find the correlation between the two variables by removing the effect of the third variable. There can be situations when the relations between variables can be many. This could reduce the accuracy of correlation or could also give wrong results. Partial correlation removes
5 min read
How to Create Custom Charts in Excel?
The instructional exercise makes sense of the Excel outlines fundamentals and gives the point-by-point direction on the most proficient method to make a chart in Excel. Diagrams assist you with imagining your information in a manner that makes a most extreme effect on your crowd. Figure out how to m
2 min read
How to Create a Bar Chart in Excel?
To learn how to create a Column and Bar chart in Excel, let's use a simple example of marks secured by some students in Science and Maths that we want to show in a chart format. Note that a column chart is one that presents our data in vertical columns. A bar graph is extremely similar in terms of t
4 min read
How to Find Correlation Coefficient in Excel
Finding the correlation coefficient in Excel is a fundamental skill for anyone working with data analysis, statistics, or business insights. It helps you understand the relationship between two sets of data, indicating whether they are positively or negatively correlated. In this article, you will l
9 min read
How to Create a Line Chart in Excel
Line graphs are a highly valuable tool in Excel, helping users analyze trends and patterns effectively over time. They are widely used for time-series analysis in Excel, allowing a clear visual representation of changes in data. In this guide, you'll learn how to create a line graph in Excel, includ
10 min read
How to Make a Comparison Chart in Excel?
A comparison chart is a general kind of chart or diagram which shows the comparison of two or more objects or groups of objects. This comparison diagram shows qualitative and/or quantitative information data. Generally, it is recommended to use a bar or column chart for representing the comparison d
3 min read
How to Calculate Correlation in Excel: Step by Guide
Understanding the relationship between two variables is essential in data analysis, and correlation is a powerful statistical tool to measure that relationship. Excel, as a versatile data analysis tool, allows you to calculate correlation easily. In this article, you will learn the different methods
9 min read