How to Do Linear Regression Analysis in Excel

Buried in your spreadsheets is a powerful way to predict the future, a statistical method called linear regression. It sounds complex, but it's really just a way to find a straight-line relationship between a cause and an effect, like how ad spend affects website traffic or how a discount impacts sales. This article will show you exactly how to perform a linear regression analysis in Excel, breaking down the process into simple, actionable steps even if you’re not a math whiz.

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 Linear Regression, Anyway?

In the simplest terms, linear regression is about drawing the best possible straight line through a scatter of data points. Think about it with a real-world example: you run an ice cream shop and you want to know how the daily temperature affects your sales.

• Your independent variable is the thing you can't control but that you think causes an effect - in this case, the temperature.
• Your dependent variable is what you want to predict - ice cream sales.

You’d plot each day's temperature and sales on a chart. As the temperature rises, sales likely go up, too. Linear regression finds the single "line of best fit" that cuts through that data, giving you a formula to estimate sales at any given temperature. This is incredibly useful for forecasting and understanding what truly drives your business metrics.

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 1: Get Your Data Ready in Excel

Before you can run an analysis, your data needs to be clean and organized. This is the most important step, as the old saying goes: "garbage in, garbage out."

For a simple linear regression, all you need are two columns side-by-side in an Excel sheet. One column for your independent variable (the cause) and one for your dependent variable (the effect).

Let's use an example of a small e-commerce business. We want to see if our monthly spending on social media ads has a clear relationship with the number of online orders we receive. Here’s what our data looks like:

Independent Variable (X-axis): Ad Spend ($) Dependent Variable (Y-axis): Online Orders

• Column A: Month
• Column B: Ad Spend ($)
• Column C: Online Orders

Your sheet should look something like this:

With our data neatly organized, we’re ready to analyze the relationship between how much we spend and how many orders we get.

Step 2 (The Easy Way): Using a Scatter Plot

The fastest way to perform a linear regression in Excel is visually, by creating a scatter plot chart. This method is fantastic for a quick gut check and a simple predictive formula without getting bogged down in statistical jargon.

1. Create a Scatter Plot

First, highlight your two numeric data columns - "Ad Spend ($)" and "Online Orders." Make sure you only select the numbers, not the column headers.

• Go to the Insert tab on the Ribbon.
• In the Charts section, click on the icon that looks like a series of dots. This is the Insert Scatter (X, Y) or Bubble Chart option.
• Select the first chart type, a simple Scatter plot.

Excel will instantly generate a chart. Each dot represents a month, showing the intersection of ad spend (on the bottom X-axis) and online orders (on the left Y-axis).

2. Add a Trendline

Next, we need Excel to draw that "line of best fit."

• Right-click on any one of the blue dots in your chart.
• From the dropdown menu, select Add Trendline...

A straight line will appear on your chart cutting through the data points. This is your linear regression line! A "Format Trendline" pane will also pop up on the right side of your screen.

3. Display the Equation and R-squared Value

This is where we get the magic formula. In the "Format Trendline" pane, scroll down to the bottom and check two boxes:

• Display Equation on chart
• Display R-squared value on chart

Two small text boxes will appear on your chart. You can click and drag them to a better position if needed.

• The Equation: You’ll see something like y = 0.0935x + 76.579. This is your prediction formula! ‘y’ is what you want to predict (Online Orders) and ‘x’ is your input (Ad Spend).
• The R-squared (R²) value: You’ll see a value like R² = 0.9634. This number tells you how well the line fits the data. Think of it as a confidence score from 0 to 1 (or 0% to 100%). A value of 0.9634 means that about 96% of the variation in online orders can be explained by our ad spend. That's a very strong correlation! An R² of 0.2, on the other hand, would mean there's almost no 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

Step 2 (The Powerful Way): Using the Data Analysis ToolPak

The scatter plot method is great for a quick look, but if you want deeper insights, you need to use Excel’s powerhouse feature: the Analysis ToolPak.

First, Enable the Analysis ToolPak

By default, this tool isn't active. Here’s how to turn it on (you only have to do this once):

1. Click on File in the top-left corner, then choose Options at the bottom.
2. In the Excel Options window, click on Add-ins on the left-hand menu.
3. Near the bottom, make sure "Excel Add-ins" is selected in the "Manage" dropdown, and click the Go... button.
4. Another box will pop up. Check the box next to Analysis ToolPak and click OK.

Now, when you go to the Data tab in Excel, you'll see a new "Data Analysis" button on the far right.

Running the Regression Analysis

With the ToolPak enabled, let's run the full analysis:

• Navigate to the Data tab.
• Click the Data Analysis button.
• In the pop-up window, scroll down and select Regression, then click OK.
• Now, we fill in the regression settings:

In an instant, Excel will create a new sheet filled with statistical output. It looks overwhelming, but you only need to focus on a few key numbers.

How to Read the Regression Analysis Output (Without a PhD)

Let's demystify that crowded output table. Here are the most important pieces of information for making business decisions.

You’ll see a section called SUMMARY OUTPUT. Here’s what matters inside it:

Regression Statistics

• R Square: This is the same R² value we found on our chart: 0.9634. It confirms the strength of the relationship. It tells you that 96.3% of the movement in online orders is explained by the movement in ad spend.

ANOVA Table

• Significance F: Look at this one number. You want it to be very, very small (traditionally, less than 0.05). In our example, it's something microscopic like 6.35E-08, which is 0.0000000635. This confirms that your model is statistically significant - meaning the results aren’t just a random fluke. If this number were large (like 0.5), it would mean your independent variable is likely not a reliable predictor for your dependent variable.

Coefficients Table

This is where you get the pieces of your prediction formula and determine what's truly driving the results.

• Intercept (Coefficient): This is your baseline value, the "b" in the classic line formula y = mx + b. Here, it’s 76.579. This means that if you spent $0 on ads, your model predicts you would still get about 77 online orders.
• Ad Spend ($) (Coefficient): This is the heart of your analysis, the "m" in y = mx + b. Here, it’s 0.0935. This coefficient tells you the exact relationship between your variables.
• P-value: Next to the coefficient, you’ll see its P-value. Similar to Significance F, this tests the individual variable's reliability. Since the P-value for "Ad Spend ($)" is also tiny (6.35E-08), it confirms that ad spend is a statistically significant predictor of online orders.

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

Putting It All Together: Making Predictions

Now, let's use our findings. The coefficients table gives us the exact same predictive formula as the chart method:

Predicted Online Orders = (Ad Spend Coefficient * Your Ad Spend) + Intercept

y = (0.0935 * x) + 76.579

Imagine your boss asks, "What if we increase our ad budget to $5,000 next month? What kind of results can we expect?" You can now answer with data:

Predicted Orders = (0.0935 * 5000) + 76.579 = 467.5 + 76.579 = 544.079

You can confidently forecast approximately 544 online orders if the ad budget is increased to $5,000, assuming past trends continue.

Remember, this is a model, not a crystal ball. It’s an educated estimate based on historical data. It performs best when predicting within a reasonable range of your existing data, not for wildly different scenarios (e.g., spending $1 million when you've only ever spent $4,000).

Final Thoughts

Running linear regression in Excel transforms a simple spreadsheet from a static record of what happened into a dynamic tool for predicting what might happen next. Whether you use a quick visual scatter plot or the detailed Data Analysis ToolPak, you're now equipped to uncover the key relationships that drive your business and make smarter, data-driven decisions.

While running regression in Excel is powerful, it can still be a manual process, starting with the challenge of getting all your data in one place. Before you can even start this analysis, you often have to export CSV files from tools like Google Analytics, Shopify, and your ad platforms, then clean and stitch them together. We built Graphed to do that heavy lifting for you. We connect your tools automatically, creating a live, unified view of your data for instant analysis and reporting in real-time — all using plain English instead of complicated BI dashboards, moving you from question to insight in seconds.