How to Calculate Regression Analysis in Excel

Curious if your ad spend is actually driving sales? Regression analysis in Excel can give you a clear, data-backed answer. It's a statistical method used to find and measure the relationship between two or more variables. This guide will walk you through exactly how to perform regression analysis in Excel, even if you’re not a statistics expert.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What is Regression Analysis? A Simple Explanation

At its core, regression analysis helps you understand how a dependent variable (the thing you want to predict) changes when an independent variable (the thing you control or observe) changes.

Think of it like this: You have a scatter plot chart with a bunch of data points. Regression analysis draws the "line of best fit" through those dots, giving you a simple formula to estimate how the variables are related.

In marketing and sales, this is incredibly useful for questions like:

• How does my marketing budget affect my revenue? (Independent: Budget, Dependent: Revenue)
• Does increasing website traffic lead to more newsletter sign-ups? (Independent: Traffic, Dependent: Sign-ups)
• Is there a relationship between the number of sales demos and closed deals? (Independent: Demos, Dependent: Deals)

For this tutorial, we’ll stick to simple linear regression, which involves just one independent variable to predict a dependent variable. The concepts can also be applied to multiple regression, where you use several independent variables.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Getting Your Data Ready in Excel

Before you can do any analysis, you need your data organized properly. The quality of your analysis is directly tied to the quality of your data - "garbage in, garbage out" absolutely applies here.

For a simple linear regression, all you need are two columns in Excel:

1. Your Independent Variable (X) - This is the factor you believe is causing the change.
2. Your Dependent Variable (Y) - This is the outcome or result you are measuring.

Let's use a common marketing scenario. We want to see the relationship between our monthly ad spend and our monthly sales revenue. Our spreadsheet would look like this:

Make sure your data is cleaned up, with no text in the numerical columns and consistent formatting. With your data structured this way, you're ready to start the analysis.

Method 1: The Visual Method with Excel Charts

The fastest way to get a high-level view of the relationship between your variables is by creating a scatter plot and adding a trendline. This gives you a visual representation of your data and the basic regression formula.

Step 1: Create a Scatter Plot

1. Select the two columns of data you want to analyze (in our example, "Ad Spend" and "Sales").
2. Go to the Insert tab on the Excel ribbon.
3. In the Charts section, click on the icon for Scatter (X, Y) or Bubble Chart.
4. Choose the first option, the basic Scatter plot.

Excel will instantly generate a chart showing your ad spend on the X-axis and sales on the Y-axis. Each point on the chart represents a month's data.

Step 2: Add a Trendline and Display Formulas

Now, we'll draw that "line of best fit."

1. Click on any of the data points in your newly created chart to select them.
2. Right-click on a data point and select Add Trendline... from the dropdown menu.
3. The "Format Trendline" pane will appear on the right side of your screen. Ensure Linear is selected as the trendline option.
4. At the bottom of the Trendline options, check the boxes for Display Equation on chart and Display R-squared value on chart.

Your chart will now have a straight line running through your data points, along with a regression equation (like y = 10.5x + 3000) and an R-squared value.

This is regression analysis at its simplest. The equation tells you the mathematical relationship, and the R-squared value tells you how strong that relationship is. An R-squared of 0.85, for example, means that 85% of the variation in your sales can be explained by your ad spend, which is a very strong relationship.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Method 2: Using the Data Analysis ToolPak for a Deeper Dive

The chart method is great for a quick look, but for more detailed statistics (like statistical significance), you need to use Excel's Analysis ToolPak. It’s a built-in add-in that unlocks more advanced data analysis features.

Step 1: Enable the Analysis ToolPak

If you haven't used it before, you probably need to turn it on first. You only have to do this once.

• Go to File > Options.
• In the Excel Options window, click on Add-ins from the menu on the left.
• At the bottom of the window, next to "Manage," make sure Excel Add-ins is selected and click the Go... button.
• In the Add-ins pop-up, check the box next to Analysis ToolPak and click OK.

You should now see a "Data Analysis" button on the far right of your Data tab in the Excel ribbon.

Step 2: Run the Regression Analysis

Now it's time to run the full analysis.

1. Click the Data Analysis button on the Data tab.
2. In the pop-up window, scroll down and select Regression. Click OK.
3. A Regression dialog box will open. This is where you tell Excel what to analyze.
4. Click OK.

Excel will instantly generate a detailed regression analysis report in a new worksheet.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 3: Making Sense of the Regression Output

The output sheet can feel intimidating with all its numbers, but you only need to focus on a few key values to understand the results.

SUMMARY OUTPUT Table

Here’s what to look at:

• R Square: This is the same R-squared value we saw on the chart. It tells you the percentage of variation in your dependent variable that is explained by your independent variable. Higher is generally better (closer to 1). A value of 0.70 means 70% of the movement in sales is explained by ad spend.
• Adjusted R Square: This is a slightly more accurate version of R Square, especially important when you start using multiple independent variables.

ANOVA Table

This table is primarily used to assess the overall significance of your model. For a beginner, the most useful value here is the Significance F. Suppose it’s a very small number (e.g., 0.001). In that case, that is a good sign, indicating that your regression model is statistically significant and predicts the outcome better than chance.

Coefficients Table

This is often the most important part of the report. It gives you the components of your regression equation and tells you if your variables are statistically significant predictors.

• Intercept: This is your baseline. In our example, it's the predicted amount of sales you'd get even if you spent $0 on ads. This is the "b" in the classic y = mx + b formula.
• Coefficient (e.g., "Ad Spend"): This is the most crucial number. It tells you how much your dependent variable (Sales) is expected to increase for every one-unit increase in your independent variable (Ad Spend). If the coefficient for Ad Spend is 12.5, it means that for every additional $1 you spend on ads, you can expect sales to increase by $12.50. This is the "m" (the slope) in the formula.
• P-value: This tells you about the statistical significance of your independent variable. The common rule is: if the P-value is less than 0.05, you can be confident that the relationship between your variables is not just due to random chance. If the P-value for Ad Spend is very small (e.g., 0.0001), it confirms that Ad Spend is a significant predictor of Sales. If it's high (e.g., 0.60), the variable's effect isn't statistically significant.

Common Pitfalls and Best Practices

As you start using regression, keep a couple of things in mind:

• Correlation is Not Causation: Regression shows a relationship between variables, but it doesn't automatically prove that one causes the other. A strong relationship suggests causation, but other external factors might be at play.
• Use Enough Good Data: Your analysis is only as reliable as your data. Try to use plenty of data points (e.g., 20-30 periods are better than 5) and make sure the data is accurate.
• Check for Outliers: An outlier - an unusually high or low data point - can significantly skew your results. Take a look at your scatter plot to identify any points that seem way off from the general trend.

Final Thoughts

Understanding the relationship between your marketing efforts and business outcomes is critical, and Excel provides powerful, accessible tools to do just that. Whether you take a quick visual approach with charts or a deeper dive with the Analysis ToolPak, you can uncover valuable insights that were previously hidden in your spreadsheets.

While Excel is great for this, the manual work of exporting data from different platforms and preparing your files can be time-consuming. We wanted to skip the manual report building, which is why we connected our data sources into a single place. With Graphed, we can ask questions like "show me a dashboard comparing my Google Ads spend vs. revenue last quarter," and get a live, automated dashboard in seconds - no CSVs or pivot tables required.