Table of Contents
Bivariate analysis in Excel is the process of analyzing two different variables, such as sales and customer satisfaction, to determine how they are related. This can be done in Excel by creating a scatter plot of the data points and then applying a linear regression model to identify the correlation between the two variables. This linear regression model will calculate the best-fit line based on the data points and provide a coefficient of determination (R-squared) value to indicate the strength of the correlation between the two variables. This analysis can be used to identify potential relationships and trends in the data that can be used to make data-driven decisions.
The term bivariate analysis refers to the analysis of two variables. You can remember this because the prefix “bi” means “two.”
The purpose of bivariate analysis is to understand the relationship between two variables
There are three common ways to perform bivariate analysis:
1. Scatterplots
2. Correlation Coefficients
3. Simple Linear Regression
The following example shows how to perform each of these types of bivariate analysis in Excel using the following dataset that contains information about two variables: (1) Hours spent studying and (2) Exam score received by 20 different students:
1. Scatterplots
To create a scatterplot of hours vs. score, we can highlight cells A2:B21, then click the Insert tab along the top ribbon, then click Insert Scatter Chart within the Charts group:
We can also modify the y-axis limits to gain a better view of the data points.
To do so, double click the y-axis. In the Format Axis panel that appears on the right side of the screen, click Axis Options and then change the Minimum and Maximum bounds to 60 and 100, respectively.
The y-axis will automatically update:
The x-axis shows the hours studied and the y-axis shows the exam score received.
From the plot we can see that there is a positive relationship between the two variables. As hours studied increases, exam score tends to increase as well.
2. Correlation Coefficients
A Pearson Correlation Coefficient is a way to quantify the linear relationship between two variables.
We can use the following formula in Excel to calculate the correlation coefficient between hours studied and exam score:
=CORREL(A2:A21, B2:B21)
The correlation coefficient turns out to be 0.891.
This value is close to 1, which indicates a strong positive correlation between hours studied and exam score received.
3. Simple Linear Regression
Simple linear regression is a statistical method we can use to quantify the relationship between two variables.
To fit a simple linear regression model in Excel, click the Data tab along the top ribbon, then click the Data Analysis option in the Analyze group. In the new panel that appears, click Regression and then click OK.
Note: If you don’t see the Data Analysis option, you need to first load the .
In the panel that appears, enter the following information and then click OK:
Once you click OK, the results of the regression model will appear:
The fitted regression equation turns out to be:
Exam Score = 69.0734 + 3.8471*(hours studied)
This tells us that each additional hour studied is associated with an average increase of 3.8471 in exam score.
We can also use the regression equation to estimate the score that a student will receive based on their total hours studied.
For example, a student who studies for 3 hours is estimated to receive a score of 81.6147:
- Exam Score = 69.0734 + 3.8471*(hours studied)
- Exam Score = 69.0734 + 3.8471*(3)
- Exam Score = 81.6147
The following tutorials provide additional information about bivariate analysis: