Correlation in Excel

Correlation helps you determine how separate datasets fix together and relate to one another. This is a common statistical analysis used to determine trends, analyze ideas, and more. What’s more: there’s no need to do this math by hand. Spreadsheets do the hard work for you. Let’s learn how to measure correlation in Microsoft Excel.

What is Correlation?

Before diving into working with correlation formulas and tools, it’s helpful to learn some key terms. When you find correlation in Excel, you’re measuring the correlation coefficient. The correlation coefficient is a number, always between -1.00 and 1.00, that tells you the strength of correlation.

Imagine two datasets, called A and B. Let’s consider some scenarios:

• When A increases, B always increases: this is positive correlation, and your coefficient will be +1. That tells you this is a perfect positive.
• When A decreases, B always decreases: this is positive correlation.
• When A increases, B always decreases: this is negative correlation, and your coefficient will be at or near -1.
• When A increases, B may increase, decrease, or remain unchanged: there is no correlation, and your coefficient will be approximately 0.

As you can see from those examples, correlation coefficients help you determine how seemingly unrelated datasets may (or may not) fit together. Correlation has a variety of real-world uses, including in academics, business, science, weather forecasting, finance, sales, and more.

Now, let’s dive in and explore Excel’s two best ways to measure correlation.

How to Measure Correlation in Excel (With Functions)

Consider two columns of data, Set A and Set B. You want to quickly determine what, if any, correlation exists between the two sets of data.

For a simple analysis, a good option is to use Excel’s built-in CORREL(Correlation) function. It’s quick and easy, and will return the correlation coefficient of any dataset.

To get started, click into any empty cell in your worksheet. The CORREL formula works based on cell references, and doesn’t rely on being physically adjacent to your data. Thus, it doesn’t matter what cell you choose to place your formula in.

Begin by typing an = sign. This tells Excel that you’re inputting a formula in your cell of choice.

Continue by typing CORREL(. This places the CORREL function inside your formula. Excel then asks you for two inputs: Array1, and Array2. These represent the two sets of data for which you want to measure correlation.

Click and drag your cursor to select the first range, which is the first column or dataset. Here, this range is A1:A5. So far, your CORREL formula reads:

=CORREL(A1:A5

Continue by typing a comma. Then, click and drag to select the second range. Finally, close the parentheses by typing ). Your finished formula is:

=CORREL(A1:A5,B1:B5)

Hit Enter on your keyboard, and Excel returns your correlation coefficient: 1. That tells you that you have a strong positive correlation in your data.

How to Measure Correlation in Excel (With Data Analysis)

Sometimes, you may find yourself in need of a more robust correlation coefficient analysis as you work in Excel. Your best option here is to use the Data Analysis feature in Excel. Data Analysis, by default, isn’t built into Excel. Instead, it comes as part of the Analysis ToolPak add-in.

If you don’t have the Data Analysis feature, it’s easy to add. Go to the Tools dropdown menu at the top of your screen, then choose Excel Add-ins.

You can either check the box if the Analysis ToolPak is visible, or browse to it on your computer if it’s not listed. When you’re finished, click OK. Then, exit and restart Excel.

Now that you’ve installed the Data Analysis tool, you’re ready to go. You’ll find it on the Data tab on Excel’s ribbon. Click on the Data Analysis button, and you’ll see a list of tools appear. Scroll through the list until you find the one you want: Correlation. They’re listed alphabetically.

Click on Correlation, then choose OK. Excel will automatically launch the Correlation menu. You have a few decisions to make and options to choose from here.

First, you need to select your Input Range. This is your entire, combined dataset. Click into the white box. Then, click and drag your cursor to select your data. In our example, the appropriate range is $A$1:$B$5. (Excel automatically locks the references by adding the dollar signs).

Then, check the box for Labels in First Row. Finally, you need to choose an output location. To place the correlation coefficient table onto your existing sheet, click into the white Output Range box, then click into an empty cell. You can also choose to add the table to a New Worksheet. And, for completely separate analysis, you have the option for New Workbook.

When you’re finished, click OK. Excel will insert a correlation coefficient table onto your worksheet. Once again, as you can see, the values have a strong positive correlation.

Once again, Excel has delivered multiple great ways of performing complex analysis tasks. Whether you use the Data Analysis feature, or the CORREL function, you can easily measure correlation within and between datasets.

Measuring correlation is a key component to data analysis, for very practical reasons. It helps you measure trends and identify relationships. You can use correlation to find cause-and-effect within your data.

In essence, it helps you make better, smarter, and more accurate decisions, whatever the nature of your work. Thanks to Microsoft Excel, it couldn’t be easier to find correlation.