What is the CORREL function in Excel?

Data can be overwhelming, but Excel's CORREL function helps you cut through the noise. Calculating the correlation coefficient is the secret weapon for uncovering hidden trends and making smarter decisions.

What is the CORREL function in Excel?

CORREL is a statistical function in Excel that measures the relationship between two data sets. It calculates the correlation coefficient - a value between -1 and 1 that indicates the degree of linear relationship between two variables. Here is the syntax for the CORREL function in Excel:

=CORREL(array1, array2)

Array1 and array2 are the two data ranges you want to analyze.

A correlation coefficient of 1 means a perfect positive correlation, while -1 means a perfect negative correlation. A value of 0 indicates no linear relationship. In this way, CORREL helps you quantify the strength and direction of these relationships in your data.

How to use the CORREL function in Excel

Now that you understand what the CORREL function does and how to prepare your data, let's go through an example to see how the CORREL function works.

Let's say you're a sales manager analyzing data from the previous year. You have three data sets: Monthly sales, TV ad spend, and radio ad spend, each with 200 data points.

What is the CORREL function in Excel? Picture 1

To calculate the correlation between sales revenue and TV ad spend, select an empty cell and enter:

=CORREL(A2:A201, B2:B201)

What is the CORREL function in Excel? Picture 2

This formula tells Excel to calculate the correlation between the sales revenue data in A2:A201 and the TV ad spending data in B2:B201 . Next, to calculate the correlation between sales revenue and radio ad spending, use the following formula:

=CORREL(A2:A201, C2:C201)

What is the CORREL function in Excel? Picture 3

This formula will give you the correlation coefficient between the sales revenue data in A2:A201 and the radio ad spend in C2:C201.

Suppose the first formula returns a correlation coefficient of 0.78, indicating a strong positive correlation between TV ad spending and sales. The second formula returns a correlation coefficient of 0.576, indicating a weaker positive correlation between radio ad spending and sales.

Combining the CORREL function with other Excel tools

The CORREL function is a powerful tool, but when combined with other Excel features, it becomes even more powerful. Two key tools that can enhance your correlation analysis are PivotTables and the XLMiner Analysis ToolPak.

PivotTables in Excel allow you to quickly summarize and analyze large data sets. Using PivotTables, you can easily calculate correlations between variables and detect trends across multiple dimensions.

What is the CORREL function in Excel? Picture 4

For example, suppose you have sales data broken down by region and product category; you can use a PivotTable to correlate sales and various factors such as price, advertising spend, or customer demographics for each combination of region and category.

XLMiner Analysis ToolPak is an add-in that provides advanced statistical functions, including correlation. While the CORREL function calculates the correlation between two variables, the Correlation tool in XLMiner Analysis ToolPak can calculate correlation for an entire data set in one go.

What is the CORREL function in Excel? Picture 5

The CORREL function is a powerful tool for analyzing relationships in data, helping you gain deeper insights. However, if you want to analyze your data over time, the TREND function in Excel is another great tool to explore.

Microsoft Excel MS excel

Micah Soto

Update 07 February 2025

You should read it

May be interested

How to use MID functions to get strings in Excel
mid function in excel is a function that takes the middle character string corresponding to the value that the user requires to perform.
How to use Excel's VALUE function
excel's value function will convert a string to a digital form, which can be combined with other functions such as left function, right function, mid function.
How to use the WRAPROWS function in Excel
having trouble with handling large data sets in excel? then learn how to use the wraprows function to split them into more manageable rows.
How to use the IFS function in Excel 2016
the ifs function in excel is a logical function introduced in excel 2016. this function is a function that replaces the nested if function and is much easier to use. the ifs function checks one or more conditions and returns a value that meets the first true condition.
How to use the NPER function in Excel to plan loans and savings
do you want to effectively manage and control your personal finances? then we invite you to learn how to use excel's nper function.
How to use the function to delete spaces in Excel
deleting white space with functions in excel makes it easier for users to handle content, instead of traditional editing.
How to fix the SUM function doesn't add up in Excel
in the process of summing with sum in excel, you will encounter some errors such as not jumping the number, not adding the sum. so how to handle this problem?
How to use the SUBTOTAL function in Excel
the subtotal function in excel is used in many different cases, helping you to sum subtotals in a list or database, unlike the sum function in excel such as counting cells, calculating average, finding the largest / smallest value. or sum the filtered list values in excel
Save time with these text formatting functions in Microsoft Excel
microsoft excel is a main application for anyone who has to work with numbers, from students to accountants. but its usefulness extends beyond a large database, it can do a lot of great things with text. the functions listed below will help you analyze, edit, convert, change text and save many hours of boring and repetitive tasks.
SUMPRODUCT function in Excel: Calculates the sum of corresponding values
the sumproduct function is an extremely useful function when you have to deal with a lot of data numbers in microsoft excel. here are the things you need to know about the sumproduct function in excel.