How to Correlate Data in Excel

Trying to find the relationship between two sets of numbers is a core part of data analysis. You might want to know if your ad spend goes up, does your website traffic also go up? Or if you increase a product's price, do sales go down? Answering this question is called correlation analysis, and you can uncover these connections right inside Excel. This article will show you three different ways to calculate and visualize correlation in your spreadsheet.

What is Correlation, Anyway?

Before we jump into Excel, let's quickly review what correlation actually means. In simple terms, correlation is a statistical measure that shows how two variables move in relation to one another. This relationship is measured by a nifty number called the correlation coefficient, which always falls between -1 and +1.

• A coefficient of +1 means a perfect positive correlation. When one variable increases, the other variable increases by a consistent amount. Think hot weather and ice cream sales.
• A coefficient of -1 means a perfect negative correlation. When one variable increases, the other one consistently decreases. Imagine the hours you study and the number of mistakes you make on a test.
• A coefficient of 0 means there is no correlation. The two variables have no linear relationship at all. The price of coffee in Brazil and your team's quarterly sales, for example, are likely uncorrelated.

It's important to remember that correlation does not equal causation. Just because two things are correlated doesn't mean one is causing the other. We'll touch on this again later.

Prepping Your Data for Analysis

Before you can run any calculations, your data needs to be structured properly. For correlation analysis, this is super simple. All you need are two or more columns of numerical data, with each column representing a different variable. Your spreadsheet should look something like this:

Make sure of a few things:

• Your data is purely numerical within the range you're analyzing. Text values will cause errors.
• The data sets for each variable have the same number of data points (i.e., the columns are the same length).
• There are no blank cells in the middle of your data, as this can break the formulas.

With clean, organized data, you're ready to find the correlation.

Method 1: The Quickest and Easiest Way with the CORREL Function

Excel's built-in CORREL function is the most direct way to get the correlation coefficient between two variables. It's fast, simple, and you just need one empty cell.

Step-by-Step Instructions

Let's say you have "Ad Spend" in column A (from A2 to A21) and "Website Traffic" in column B (from B2 to B21).

1. Click on any empty cell where you want your result to appear.
2. Type the formula: =CORREL(A2:A21, B2:B21)
3. Press Enter.

That's it! Excel will immediately display the correlation coefficient between Ad Spend and Website Traffic in the cell you selected. For example, a result of 0.89 would tell you there's a strong positive correlation between how much you spend on ads and the traffic your website receives.

Method 2: Visualizing Correlation with a Scatter Plot

Numbers are great, but sometimes a picture tells a stronger story. A scatter plot is the perfect way to visualize the relationship between two variables. It helps you see trends, patterns, and any potential outliers that a single coefficient might not reveal.

How to Create a Scatter Plot Chart

1. Select the two columns of data you want to compare, including the headers.
2. Go to the Insert tab on the Excel ribbon.
3. In the "Charts" section, click on the icon named "Insert Scatter (X, Y) or Bubble Chart."
4. Choose the first option, the basic Scatter plot. Excel will instantly generate the chart.

Interpreting the Scatter Plot

You can tell a lot just by looking at the direction of the dots:

• If the dots generally move from the bottom-left to the top-right, you have a positive correlation.
• If the dots generally move from the top-left to the bottom-right, you have a negative correlation.
• If the dots are scattered randomly all over the chart with no clear pattern, there's likely no correlation.

Pro Tip: Add a Trendline

To make the relationship even clearer, you can add a trendline to your scatter plot.

1. Click on your chart to select it.
2. Click the "+" icon on the top right of the chart to open "Chart Elements."
3. Check the box next to Trendline. This will add a line that visualizes the general trend of your data points.

This simple visual can confirm what the CORREL function told you and helps present your findings clearly to an audience.

Method 3: Running a Full Correlation Matrix with the Analysis ToolPak

What if you want to analyze the correlation between three, four, or even more variables at once? Calculating them one by one with the CORREL function is inefficient. This is where the Data Analysis ToolPak comes in handy - it can generate a complete correlation matrix with a few clicks.

Step 1: Enable the Data Analysis ToolPak

The ToolPak is a free Excel add-in, but it's not always enabled by default. Here’s how to turn it on:

1. Go to File > Options.
2. In the Excel Options dialogue box, click on Add-ins on the left.
3. Near the bottom, make sure "Excel Add-ins" is selected in the "Manage" dropdown, and then click Go…
4. Check the box next to Analysis ToolPak and click OK.

You’ll now have a "Data Analysis" button on your Data tab in the ribbon.

Step 2: Create the Correlation Matrix

Imagine you now have three columns of data: "Ad Spend" (column A), "Website Traffic" (column B), and "Sales" (column C).

1. Click on the Data tab.
2. Click on the Data Analysis button on the far right.
3. In the pop-up window, select Correlation and click OK.
4. For the "Input Range," select your entire data set in one go, including the headers (e.g., $A$1:$C$21).
5. Make sure Grouped By: is set to Columns.
6. If you included the headers in your selection, check the box for Labels in first row.
7. For Output Options, choose where you want your matrix to appear. "New Worksheet Ply" is usually a safe choice to avoid overwriting existing data.
8. Click OK.

Excel will generate a simple but powerful table showing the correlation coefficient between every possible pair of variables. Reading the table is easy: just find the intersection of the two variables you want to compare. For instance, to see the correlation between Ad Spend and Sales, you would go to the row for "Ad Spend" and the column for "Sales".

Common Mistakes When Analyzing Correlation

Calculating correlation is easy, but interpreting it correctly requires care. Here are a few common pitfalls to avoid:

Mistake 1: Ignoring causality

We mentioned it before, but it's worth repeating: correlation does not mean causation. For example, data might show a strong correlation between ice cream sales and shark attacks. That doesn't mean ice cream causes people to get attacked by sharks. The hidden, or "lurking," variable is the season - more people eat ice cream and swim in the ocean during the summer. Always look for logical reasons for a correlation before jumping to conclusions.

Mistake 2: Forgetting the outliers

One extreme data value can significantly skew a correlation coefficient. A very high or low data point that doesn't fit the rest of the pattern can make a weak correlation look strong, or vice versa. This is another reason why a scatter plot is so useful - it helps you visually spot these outliers and decide whether they need to be investigated or removed.

Mistake 3: Only Considering Linear Relationships

The correlation coefficient we've discussed only measures linear relationships (ones that look roughly like a straight line). However, variables can be strongly related in a non-linear way (like a "U" shape or a curve). A scatter plot may show a clear pattern, but the correlation coefficient could be close to 0 because the pattern isn't a straight line. Always visualize your data to make sure you're not missing a non-linear relationship.

Final Thoughts

Excel gives you some powerful and accessible tools to understand the relationships hidden in your data. Whether you're using the simple CORREL function for a quick check, a scatter plot for a clear visual, or the Analysis ToolPak for a multi-variable matrix, you can move beyond raw numbers and find meaningful connections in your business metrics.

Of course, the process of exporting data, cleaning it up in a spreadsheet, and running manual calculations can still take up a good chunk of your day. At Graphed, we've focused on automating that entire process. By securely connecting your live data sources like Google Analytics, Shopify, and social media ad accounts, you can simply ask questions in plain English like, "show me the correlation between Facebook Ads spend and new customer revenue this quarter." We instantly build the analysis for you in a real-time dashboard, turning hours of spreadsheet work into answers in seconds.