stastics correlation coefficient excel

How to Calculate Correlation in Excel with the Data Analysis ToolPac

Correlation is a statistical measure that shows how two variable might move together.   For Example as the price goes down, the sales volume might go up. Or, as the weather gets warmer, sales volume might decrease.  These types of correlation might be obvious in some organisations, but looking at the correlation between different data sets can revile interesting and actionable insights for a business.

Mapping these sort of trends has become common practice for businesses looking for key insights from their data. But for many of us, working with data in this way is new. Analyzing data in Excel for many has meant producing some sort of historical report. Maybe a chart or two and a little guesstimate forecasting.

As the data trend explodes and terms like correlation coefficient get thrown about more often in business I thought it was time to explore this area in Excel.  After all, Excel users work with data every day. If this is where things are moving then we can’t get left behind.

In this week’s video we will look at how we can use Excel to calculate the correlation between sets of variables.  This is not a lesson on correlation but how to carry out correlation using Excel.

If you want to learn more about correlation then I would suggest you read a little more on Wikipedia

Excels Data Analysis Toolpak add in

Correlation in Excels is calculated using the Data Analysis toolpak add in.  To turn on the add in, got to file, then options then go to add in’s.  In Excel Add in’s, select Analysis tool pack.

correlation in excel

Once you have turned on the Analysis Toolpak in Excel, you will find a new option in your data ribbon.

data analysis toolpac in excel

How to Calculate Correlation Coefficient using Excel

Select Data Analysis from the data ribbon. A dialogue box will open with a suite of Data analysis statistical calculations.  As we are looking at the correlation between variables we will select Correlation.

stastics correlation coefficient excel

Selecting Correlation will open a new dialogue box.

how to calculate correlation in excel

First you need to select your Input Range.  This is the range that contains all the values for the set of variables in which you are testing for correlation.  If your data is going down the rows, so you have columns of data, the select Grouped by Columns.  Should the data be presented  across the rows then select Grouped by Rows.

If your data has headers, then also select Labels in First Row.  You then need to select where you want your output to be placed.

In the background Excel works its magic and returns a table with a correlation coefficient value between variables. The values returned are between -1 and 1. A strong positive correlation will show a value close to 1.  Whereas a strong negative correlation will show a value close to -1.

If you are working with multiple variables, the table results might not be so easy to read quickly. To over come this can add some conditional formatting to our correlation table.  That way strong positive and negative values can be easily seen.

correlation in excel

Take A FREE course with us Today!

FREE beginner excel training

The Ultimate Excel Formulas Course

* includes XLOOKUP and will soon include Dynamic Arrays

Become a Power Pivot Hero

Power Pivot online training course


Power Query Excel 365

Learn DAX for Power Pivot and Power BI

DAX for powerpivot course

Best Value Excel and Excel Power Tool Learning.  Access All Areas, Unlimited Learning Subscription


In return for this Tip -How to Calculate Correlation in Excel with the Data Analysis ToolPac – I ask that you share this post or the video with your friends and colleagues

Sign up for our newsletter and get Excel tips, trick and learning activities straight to your inbox.  

 Do you want to start collecting rewards quickly for learning Excel?  Then you should try:

10+ Excel Learn and Earn Activities YOU can do Today

%d bloggers like this: