How to Do a Correlation Analysis in Excel

Trying to find the relationship between two sets of data can feel like a chore, but Excel makes it surprisingly simple. With a correlation analysis, you can see if your marketing spend is actually moving the needle on sales, or if more website traffic leads to more sign-ups. This article will walk you through a few straightforward methods to calculate and visualize correlation directly in your spreadsheet.

What Exactly Is a Correlation Analysis?

In simple terms, a correlation analysis helps you understand the relationship between two variables. It tells you if they move together, in opposite directions, or if there's no relationship at all. It's a fundamental part of data analysis that helps you spot trends and make better predictions.

There are three main types of correlation:

• Positive Correlation: When one variable increases, the other variable also increases. For example, as your ad spend for a campaign goes up, you might see your sales revenue go up too.
• Negative Correlation: When one variable increases, the other variable decreases. Think of it like this: as the number of customer complaints goes up, customer satisfaction scores tend to go down.
• Zero or No Correlation: There is no discernible relationship between the two variables. For example, the daily price of coffee in Brazil probably has no correlation with the number of leads your company generated last month.

The strength of this relationship is measured by a value called the correlation coefficient, often represented by the letter "r". This number will always fall between -1.0 and +1.0.

• An r of +1.0 indicates a perfect positive correlation.
• An r of -1.0 indicates a perfect negative correlation.
• An r of 0 indicates no correlation at all.

Most real-world data won't give you a perfect +1.0 or -1.0, but the closer the value is to either of those numbers, the stronger the relationship. We'll cover how to interpret this number in more detail later on.

A Quick but Important Warning: Correlation Is Not Causation

This is a critical point to remember. Just because two variables are correlated doesn't mean one is causing the other to change. For example, a city might find a strong positive correlation between ice cream sales and the number of swimmers at the local pool. Does eating ice cream cause people to swim? No. But a third variable - hot weather - causes both to increase.

When you find a strong correlation, think of it as a signal to investigate further, not as a final conclusion.

Step 1: Get Your Data Ready in Excel

The first step to any analysis is organizing your data properly. For a correlation analysis in Excel, the setup is easy. You just need to make sure your data lives in two or more separate columns.

Let's say a marketing manager wants to know the relationship between their weekly Facebook Ad Spend and the number of Website Leads they generated. Their spreadsheet should look something like this:

Each variable gets its own column, and each row represents a corresponding time period or observation (in this case, weekly). Don't leave any blank cells in your data range, as this can cause errors.

Method 1: Using the CORREL Function for a Quick Answer

If you only need to compare two variables, the quickest way to find the correlation coefficient is with Excel’s CORREL function. It’s perfect for getting a fast, specific number without any extra tools.

How to Use CORREL

The syntax for the function is very simple:

=CORREL(array1, array2)

Where:

• array1 is the first range of data (e.g., your "Facebook Ad Spend" column).
• array2 is the second range of data (e.g., your "Website Leads" column).

Step-by-Step Example:

1. Click on an empty cell where you want the result to appear.
2. Type the formula =CORREL( into the cell.
3. Select the range of cells for your first variable. Using our example, you'd highlight the cells containing your Ad Spend data (e.g., A2:A11).
4. Type a comma , to separate the arguments.
5. Select the range of cells for your second variable, the Website Leads data (e.g., B2:B11).
6. Close the parenthesis ) and press Enter.

Your finished formula should look like this:

=CORREL(A2:A11, B2:B11)

Excel will instantly calculate and display the correlation coefficient. In our example data, this might be a number like 0.96. This indicates a very strong positive correlation between ad spend and leads.

Method 2: Using the Data Analysis ToolPak for Multiple Variables

What if you want to analyze the relationship between three, four, or even more variables at once? That's where the Data Analysis ToolPak comes in. It's a free Excel add-in that unlocks more advanced statistical functions, including the ability to generate a correlation matrix.

First, Enable the ToolPak

By default, the ToolPak is not active. You'll need to enable it once, and then it will be available every time you use Excel.

1. Go to File > Options.
2. In the Excel Options box, click on Add-ins from the left-hand menu.
3. At the bottom, where it says "Manage:", make sure Excel Add-ins is selected, and click Go...
4. In the new dialog box, check the box next to Analysis ToolPak and click OK.

You should now have a "Data Analysis" button in the "Data" tab of your Excel ribbon.

Creating a Correlation Matrix

A correlation matrix is a table that shows the correlation coefficients for all possible pairs of variables in your dataset. Let's add two more metrics - Website Clicks and Sales Revenue - to our example data.

1. Organize your data with each variable in its own column (e.g., Ad Spend, Clicks, Leads, Sales). Make sure to include titles in the first row.
2. Go to the Data tab and click on the Data Analysis button.
3. From the list, select Correlation and click OK.
4. A dialog box will appear. For the Input Range, select your entire data set, including the column headers. In our example, this would be columns A through D.
5. Make sure Grouped By: Columns is selected.
6. Check the box for Labels in first row since you included a header row in your selection.
7. Choose where you want the output to appear (a new worksheet is usually the cleanest option) and click OK.

Excel will generate a nicely formatted correlation matrix. This table is easy to read. A value of 1.00 means a variable is perfectly correlated with itself. To find the correlation between "Facebook Ad Spend" and "Sales Revenue," you’d find the intersection of that row and column, which shows a very strong positive correlation of 0.92.

Step 3: Visualize the Correlation with a Scatter Plot

Numbers provide the facts, but a chart can tell the story. A scatter plot is the perfect way to visualize the relationship between two variables. Seeing your data points plotted on a graph makes it much easier to spot trends.

How to Create a Scatter Plot in Excel

1. Select the two columns of data you want to compare (just the numbers, not the headers).
2. Go to the Insert tab.
3. In the Charts group, click the icon that looks like a set of dots, the Insert Scatter (X, Y) chart menu.
4. Choose the first option, which is just the basic scatter plot.

Make Your Chart Even Better with a Trendline

To make the direction of the relationship even clearer, you can add a trendline.

1. Click on your newly created chart to select it.
2. Click the "+" icon (Chart Elements) that appears on the right of the chart.
3. Check the box next to Trendline. This will add a straight line trying to "fit" your data.

If the trendline slopes up from left to right, you have a positive correlation. If it slopes down, you have a negative correlation. If the line is mostly flat, there is likely very little correlation.

How to Interpret Your Results

Once you have your correlation coefficient (r), what does it actually mean? It’s all about direction and strength.

Direction:

• Positive (+) value: As one variable increases, the other tends to increase.
• Negative (-) value: As one variable increases, the other tends to decrease.

Strength:

The closer the absolute value is to 1, the stronger the linear relationship. Here’s a general rule of thumb:

• 0.8 to 1.0 (-0.8 to -1.0): Very strong correlation.
• 0.6 to 0.8 (-0.6 to -0.8): Strong correlation.
• 0.4 to 0.6 (-0.4 to -0.6): Moderate correlation.
• 0.2 to 0.4 (-0.2 to -0.4): Weak correlation.
• 0.0 to 0.2 (-0.0 to -0.2): Very weak or no correlation.

A result of +0.96 suggests a very strong positive relationship, while a result of -0.21 suggests a very weak negative one.

Final Thoughts

Running a correlation analysis in Excel is a powerful yet accessible skill for anyone looking to find insights in their data. Whether you use the simple CORREL function for a quick check or the Data Analysis ToolPak for a complete matrix, you can uncover key relationships that drive your business forward.

As you get comfortable with this, the real bottleneck often becomes gathering and organizing the data in the first place, especially if it lives in different apps like Google Analytics, Shopify, and Facebook Ads. At Graphed, we built a tool that connects to all of your data sources automatically. Instead of downloading CSVs and running manual analyses, you can just ask questions in plain English - like "What's the correlation between an ad campaign's spend and its revenue?" - and get instant answers and dashboards, all backed by live data.