How to Perform Bivariate Analysis in Excel (With Examples)


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 Excel Analysis ToolPak.

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

Additional Resources

The following tutorials provide additional information about bivariate analysis:

An Introduction to Bivariate Analysis
5 Examples of Bivariate Data in Real Life
An Introduction to Simple Linear Regression

Leave a Reply

Your email address will not be published.