Excel regression analysis estimates how one or more input variables relate to an outcome. In Microsoft Excel for Microsoft 365 on Windows, enable the Analysis ToolPak, then select Data > Data Analysis > Regression, choose the outcome as the Input Y Range and the predictor column or columns as the Input X Range, and review the output before drawing conclusions.
What regression analysis does
Regression fits an equation to observed data. In a simple linear regression, one predictor is used to estimate one outcome. The model has the form y = intercept + slope × x, where the slope estimates the average change in y associated with a one-unit increase in x. For example, a retailer might examine whether weekly advertising spending is associated with weekly sales. Sales are the outcome, and advertising spending is the predictor.
With multiple linear regression, the model includes two or more predictors, such as advertising spending, price, and store visits. This can help estimate the relationship between each predictor and the outcome while accounting for the other included predictors. Regression describes patterns in the data; by itself, it does not prove that a predictor caused a change in the outcome.
Excel’s Regression command fits a linear model using ordinary least squares, which chooses coefficients to minimize the sum of squared differences between observed outcomes and model estimates. It is useful for exploration, reporting, and relatively small analyses. The quality of the result still depends on suitable data, a sensible model, and careful interpretation.
Enable the Analysis ToolPak
If Data > Data Analysis is missing, the Analysis ToolPak may not be enabled. In Excel for Microsoft 365 on Windows, use File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins and select Go. Check Analysis ToolPak, then select OK. Return to the Data tab and look for Data Analysis, usually in the Analysis group.
On Excel for Mac, open Tools > Excel Add-ins, select Analysis ToolPak, and choose OK. Menu placement can vary slightly by release. Excel for the web does not provide the desktop Analysis ToolPak dialog; open the workbook in the desktop app to use this particular Regression command. Functions such as SLOPE and LINEST may be available in supported Excel versions without opening that dialog.
Prepare the data and run Regression
Arrange the data so each row is one observation and each column is one variable. Put the outcome in one column and predictors in adjacent columns, with an optional header row. Keep the observations aligned: a row’s outcome must correspond to the predictors in that same row. Avoid merged cells, blank rows within the data, text in numeric fields, and totals mixed in with individual observations.
- Select Data > Data Analysis, choose Regression, and select OK.
- Enter the outcome column in Input Y Range and the predictor column or columns in Input X Range. Include the same data rows in both ranges.
- If the selected ranges include variable names in the first row, check Labels. Otherwise, leave it unchecked.
- Choose an output location: Output Range on the current sheet, New Worksheet Ply, or New Workbook.
- Optionally select Residuals, Residual Plots, Line Fit Plots, or Normal Probability Plots. Leave Constant is Zero unchecked unless the model has a defensible reason to force its intercept to zero.
- Select OK. Excel creates a report containing model summary statistics, an ANOVA table, and coefficient estimates.
The Y range must contain one dependent-variable column. For multiple regression, the X range should contain the predictor columns side by side, with the same number of data rows as Y. If Labels is checked, the first cells should be text labels rather than observations.
A small worked example
Suppose A1:A6 contains the header x and values 1, 2, 3, 4, 5, while B1:B6 contains the header y and values 5, 7, 8, 11, 14. In Regression, set Input X Range to A1:A6, Input Y Range to B1:B6, and check Labels. The fitted line is approximately y = 2.4 + 2.2x. In this sample, the estimated outcome rises by 2.2 units for each one-unit increase in x; that is an association in these observations, not proof of a causal effect.
To verify the coefficient estimates with worksheet functions, with x values in A2:A6 and y values in B2:B6, enter =SLOPE(B2:B6,A2:A6) to return 2.2. With the same layout, =INTERCEPT(B2:B6,A2:A6) returns 2.4. These functions calculate the simple linear regression slope and intercept, but they do not produce the full ToolPak report with its ANOVA table and diagnostic options.
To calculate the model’s estimate at x = 6, with x values in A2:A6 and y values in B2:B6, enter =FORECAST.LINEAR(6,B2:B6,A2:A6). The result is 15.6. This is an extrapolation beyond the observed x values of 1 through 5, so it should not be treated as a reliable forecast without evidence that the relationship continues beyond the measured range.
How to read the output
The Regression Statistics section summarizes how the fitted model performs on the observations used to fit it. Multiple R is the nonnegative correlation between observed and fitted values in this output. R Square is the share of variation in the outcome accounted for by the model in the sample. For instance, an R Square of 0.70 means the fitted model accounts for 70% of the sample variation under the model’s calculation; it does not mean predictions are 70% accurate or that predictors explain the outcome causally.
Adjusted R Square modifies R Square to account for the number of predictors and sample size. It is often more useful than R Square when comparing models with different numbers of predictors, though it is not a substitute for validation on new data. Standard Error summarizes the typical size of residuals in outcome units under the report’s calculation. The Observations value should match the number of usable data rows.
The ANOVA section reports the model’s degrees of freedom, sums of squares, mean squares, F statistic, and Significance F. Significance F is the p-value for a test of whether the model as a whole provides evidence of a linear relationship with the outcome, under the test’s assumptions. In a model with multiple predictors, it does not establish that every individual predictor is useful.
The coefficients table gives an estimated coefficient for each predictor and the intercept. A coefficient’s sign indicates the direction of its estimated relationship with the outcome, holding other included predictors constant in a multiple regression. The P-value column tests a coefficient against zero under the model assumptions. A small p-value is not a measure of practical importance, and it does not prove causation. Check the coefficient’s size and units, its uncertainty, the data quality, and whether the model is appropriate.
Assumptions and diagnostic checks
A regression report can look precise even when its assumptions are poorly matched to the data. Linear regression commonly relies on an adequately linear relationship, independent observations, residuals with roughly constant variance, and—when using conventional significance tests—residual behavior compatible with the inference method. The predictors should also not be so strongly redundant that their separate effects become unstable. Real applications require judgment about which assumptions matter and how to assess them.
Residuals are observed outcomes minus fitted outcomes. A residual plot can reveal curvature, changing spread, or unusual points that a single summary statistic hides. A curved pattern may indicate that a straight-line model misses a relationship; a fan-shaped spread may suggest nonconstant variance. A Normal Probability Plot can help inspect residual distribution, but it is not a definitive pass/fail test. Review outliers in context: a data-entry error should be corrected, while a valid unusual observation should not be removed merely because it weakens the result.
Independence depends on how observations were collected. Repeated measurements from the same person, stores observed over time, or time-ordered data may have related errors. A standard ToolPak regression does not automatically account for clustered observations or time-series dependence. Consider the study design and use a method suited to it rather than relying only on the regression table.
Multiple regression and useful choices
To fit multiple regression, place each predictor in its own adjacent column and select them together as the Input X Range. Suppose weekly sales are in C2:C101, advertising spend in A2:A101, and price in B2:B101. Use C1:C101 for Y and A1:B101 for X, with Labels checked if row 1 contains headers. Excel estimates a coefficient for each predictor. The advertising coefficient describes its estimated relationship with sales while holding price constant, and vice versa.
Adding predictors can raise R Square even when the added variables contribute little useful predictive information. Adjusted R Square, domain knowledge, residual checks, and performance on held-out observations all help assess whether a more complex model is justified. Avoid including variables simply because they are available; a predictor measured after the outcome, for example, may make a model unsuitable for forecasting that outcome in advance.
Excel’s Regression dialog is convenient for a standard linear model and a readable summary. For repeatable calculations, worksheet functions can be easier to update. With x values in A2:A6 and y values in B2:B6, =LINEST(B2:B6,A2:A6,TRUE,TRUE) requests linear regression statistics. LINEST returns an array of results; how it spills or must be entered depends on the Excel version. In Microsoft 365, dynamic arrays can display returned values across cells. The ToolPak is often clearer for beginners, while LINEST is useful when results need to be incorporated into formulas. Neither removes the need to check model suitability.
Common problems to avoid
- Reversed input ranges: Put the outcome in Y and explanatory variables in X. Reversing them answers a different question.
- Different row counts: Make sure Y and X cover the same observations. A mismatch can cause an error or misaligned analysis.
- Text or missing values: Confirm that numeric columns contain actual numbers and that blanks or imported symbols are handled consistently. Do not silently substitute arbitrary values for missing data.
- Accidental zero intercept: Selecting Constant is Zero forces the fitted line through the origin. Use this only when the model and subject matter justify it; it can substantially change coefficients and fit statistics.
- Overinterpreting R Square or p-values: Neither proves causation, guarantees useful predictions, nor replaces checks of residuals and study design.
- Extrapolating: A line fitted over one range may not continue beyond it. Predictions outside the observed predictor values need particular caution.
Frequently Asked Questions
Can Excel do regression analysis?
Yes. Excel for Microsoft 365 on Windows includes the Regression command through the Analysis ToolPak add-in. Enable the add-in if Data Analysis is not shown. Excel also provides functions such as SLOPE, INTERCEPT, FORECAST.LINEAR, and LINEST for related calculations.
How do I interpret R-squared in Excel?
R Square reports the proportion of variation in the sample outcome accounted for by the fitted model. It is not the percentage of predictions that are correct, and a high value alone does not establish causation or reliable future performance.
What is the difference between R Square and adjusted R Square?
R Square generally stays the same or increases when predictors are added. Adjusted R Square accounts for model size and sample size, so it may decrease when an added predictor does not justify its complexity. Neither statistic alone determines which model is best.
Why is Data Analysis missing in Excel?
The Analysis ToolPak is probably not enabled. On Windows, go to File > Options > Add-ins, choose Excel Add-ins beside Manage, select Go, and check Analysis ToolPak. On Mac, use Tools > Excel Add-ins.
Does regression in Excel prove that one variable causes another?
No. Regression estimates statistical relationships from the supplied data. Causal conclusions require an appropriate research design and consideration of confounding, measurement, and how observations were selected; a coefficient or significant p-value is not proof of cause and effect.
Related
- Excel Slicers: Advanced Features and Smart Tips
- Excel Slicer Examples: PivotTables, Tables, and Tips
- Excel Slicer Formatting: Styles, Size, Layout, and Use
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Google Sheets COUNTIF Contains: Match Text AnywhereTo count cells in Google Sheets that contain a particular text anywhere, use =COUNTIF(A2:A100,"*apple*"). The asterisks are wildcards:…
- Create a Questionnaire Template in Word Step by StepTo create a questionnaire template in Microsoft Word, lay out your questions and response areas in a document,…
- Remove Duplicates in Excel: Keyboard ShortcutIn Excel for Microsoft 365 on Windows, select a cell in your data and press Alt, then A,…
- Excel Data Validation List AutoFill: What Works?Excel can autocomplete some data-validation dropdown lists in current Microsoft 365 versions, but the feature is not available…
- Identify Duplicate Values in Excel: A Step-by-Step GuideTo identify duplicate values in Excel, select the cells you want to check, then choose Home > Conditional…
- Book Inventory Spreadsheet: Build a Useful Excel TrackerA book inventory spreadsheet is a searchable list of the books you own, sell, lend, or manage, with…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.