To forecast a value with a one-predictor linear regression, arrange historical predictor values in one column and the outcomes in another, then use FORECAST.LINEAR. With period numbers in A2:A7, observed values in B2:B7, and the next period number 7 in A8, enter =FORECAST.LINEAR(A8,$B$2:$B$7,$A$2:$A$7) in B8; for the example data below, Excel returns about 204.33. Regression estimates the relationship between the two columns and extends that relationship to a new predictor value. It is useful when a reasonably consistent linear trend is plausible, but it does not automatically account for seasonality or sudden changes.
What You’ll Need
- A desktop version of Excel for Microsoft 365 on Windows for the steps below. The FORECAST.LINEAR function is also available in current Excel for Mac and Excel for the web, though the desktop Regression ToolPak is not available in Excel for the web.
- At least two columns of paired historical data: one predictor, such as time period or advertising spend, and one outcome, such as sales or demand. More observations usually make it easier to judge whether a relationship is reliable.
- A future predictor value to forecast for, such as the next period number. The forecast is only as useful as the data and assumptions behind it.
- Optional: the Analysis ToolPak if you want a regression report with statistics or want to model multiple predictors. You can enable it in the desktop app during the steps.
Step-by-Step
-
Choose the outcome and predictor. Decide what you want to forecast and what variable may help explain it. For a simple trend forecast, time is the predictor and sales, units, or another measure is the outcome. Put each observation on one row, with the predictor in the left column and the outcome in the next column. For example, enter period numbers 1 through 6 in A2:A7 and sales of 120, 135, 149, 160, 178, and 190 in B2:B7. Keep the paired rows together; a missing outcome in one row must not cause the predictor and outcome ranges to become misaligned.
-
Check and prepare the data. Make sure both columns contain numeric values, not numbers stored as text, and that each row represents the same observation in both columns. Sort the rows by time if they are time-series data. For evenly spaced periods, an index such as 1, 2, 3 is often clearer than irregular date gaps. If using actual dates, ensure Excel recognizes them as dates and provide a future date in the same units. Remove headings from ranges unless you plan to select the Labels option in the ToolPak. Investigate missing values and unusual observations rather than silently filling or deleting them.
-
Look at the relationship before forecasting. Select the predictor and outcome data, then choose Insert > Scatter (X, Y) and create a scatter chart. The points should show a pattern that a straight line could reasonably represent. A curved pattern, separate clusters, or a single extreme point can make a straight-line forecast misleading. A chart is a useful first check, not a statistical guarantee. If you are forecasting over time, also look for recurring seasonal peaks or a sudden level change; a simple linear regression will not learn those patterns by itself.
-
Calculate the forecast with FORECAST.LINEAR. In the example layout, enter 7 in A8 as the next period. With period numbers in A2:A7, observed sales in B2:B7, and the future period in A8, enter
=FORECAST.LINEAR(A8,$B$2:$B$7,$A$2:$A$7)in B8. The result is approximately 204.33. The function fits a least-squares straight line to the known pairs and estimates the outcome at the new predictor value. The dollar signs keep the historical ranges fixed if you copy the formula elsewhere; the A8 reference remains relative so it can change when copied. Format the result to a sensible number of decimal places, but keep the underlying precision for calculations. -
Optionally review a regression report. To use the desktop Analysis ToolPak on Windows, go to File > Options > Add-ins. At the bottom, set Manage to Excel Add-ins, select Go, check Analysis ToolPak, and select OK. On Mac, use Tools > Excel Add-ins and select Analysis ToolPak; the exact location can vary slightly by Excel release. Then choose Data > Data Analysis > Regression. Set the Input Y Range to the outcome column and the Input X Range to the predictor column. Select Labels only if the selected ranges include headings, choose an output location, and select OK. The report includes measures such as R Square, coefficients, and residual output. In Excel for the web, use the worksheet function or open the workbook in desktop Excel for this ToolPak report.
-
Use the report to check fit, not to certify the forecast. For a simple regression, the coefficient for X describes the estimated change in the outcome for a one-unit change in the predictor; the intercept is the estimated outcome when X is zero. For the example, the fitted line has a slope of 14 and an intercept of about 106.33, so period 7 gives about 204.33. Check the chart and residuals, which are the differences between observed and fitted values. Residuals should not show a strong curve, a pattern over time, or a few unusually large errors. A high R Square alone does not show that the model will predict future periods well.
-
Save, label, and monitor the forecast. Give the predicted cell a clear label such as Forecast for period 7, and keep the observed data separate from predictions so they are not mistaken for actual results. Record the date you made the forecast and the assumptions you used. When the next actual result becomes available, compare it with the forecast and review the model. If its errors keep growing, the underlying relationship may have changed or a different forecasting method may be more appropriate.
Common Mistakes
A frequent error is reversing the known_y and known_x ranges. The outcome belongs in known_y and the predictor belongs in known_x. Another is selecting ranges with different numbers of rows, or shifting one range by a row so the pairs no longer match. Check the selected addresses before calculating. Also avoid using a text label, blank, or an unintended total inside a data range.
Do not assume that every time series follows a straight line. Linear regression can continue a steady upward or downward relationship, but it may give poor results for seasonal sales, changing growth rates, or a one-time event. Extrapolating far beyond the historical predictor values is particularly risky: the model has no evidence that the old relationship will continue that far. Finally, do not treat R Square as a promise of accuracy. A model can fit historical data well and still miss future outcomes.
Tips
Keep a clean source-data table and put forecasts in a separate area or worksheet. Use consistent units and period definitions, and add descriptive headings so you can verify which range is which. When more observations arrive, extend the historical ranges and recalculate rather than manually adjusting a past forecast. If you expect to copy the function down, use absolute references for the historical ranges and relative references for the future predictor.
Use a holdout check when you have enough history: fit the model using earlier observations, then forecast one or more later observations you kept out of the fitting data. Compare those forecasts with the actual values to estimate how it might perform. For a time-based forecast, keep the holdout period later than the training period; randomly mixing past and future rows can give an unrealistically reassuring test. Document important events, promotions, price changes, or policy shifts that could explain unusual results.
If you have several meaningful explanatory variables, the Regression option in the ToolPak can fit a multiple regression. Put the outcome in the Y range and the predictor columns side by side in the X range, with each row still representing one observation. The report gives a coefficient for each predictor and an intercept. To forecast, use the reported coefficients with the corresponding future predictor values, taking care to keep the same column order and units. Adding more predictors is not automatically better; avoid variables that are unavailable at forecast time or that merely duplicate information in other columns.
Troubleshooting
Excel shows an error or refuses the formula: confirm that the function is spelled FORECAST.LINEAR, the ranges have matching lengths, and the predictor values are numeric. If Excel uses a different regional list separator, it may require semicolons instead of commas; the formula shown here uses the standard comma syntax. Check that A8 contains a number or a recognized date, not text.
The forecast is unexpectedly high or low: inspect the selected ranges and confirm that X and Y are not reversed. Check for a misplaced row, an extreme observation, or inconsistent units such as dollars mixed with thousands of dollars. Compare the chart with the fitted direction and verify the future predictor value. If the forecast is far outside the historical outcome range, determine whether that is a plausible extrapolation or a sign that the model is unsuitable.
Data Analysis is missing: enable Analysis ToolPak using the desktop steps above, then close and reopen the Data tab if needed. If you are using Excel for the web, open the workbook in the desktop app to access the ToolPak. If the ToolPak runs but the report looks wrong, recheck the Y and X ranges, Labels selection, and output location.
The model fits poorly: inspect the scatter chart and residuals for curvature, seasonality, outliers, or a structural break. Verify that the observations are comparable and that no important explanatory variable has been omitted. Consider shorter forecast horizons or a method designed for seasonality rather than forcing a straight line onto a changing pattern.
Frequently Asked Questions
What is the difference between FORECAST.LINEAR and the Regression ToolPak?
FORECAST.LINEAR quickly returns a predicted value from one known predictor and one outcome range. The Regression ToolPak produces a report with coefficients and diagnostic statistics, and it can use multiple predictor columns. Use the function for a simple forecast and the report when you need to examine the model in more detail.
How many data points do I need?
There is no universal minimum that makes a forecast dependable. A line can be calculated from two distinct predictor values, but two points provide no meaningful way to assess how well the line fits. More observations across the conditions you expect to forecast generally make model checks more informative. A short or unrepresentative history still creates substantial uncertainty.
Can I forecast several future periods at once?
Yes. Enter each future predictor value in its own row, then copy the forecast formula down. In the example, A8 could contain 7 and A9 contain 8; copy the formula from B8 into B9. The known-data ranges stay fixed because of their dollar signs, while the future-period reference moves. Remember that forecasts become less certain the farther they extend beyond the observed data.
Can linear regression account for seasonality?
Not by itself when time is the only predictor. A straight line does not represent recurring weekly, monthly, or quarterly peaks. You can model additional factors with a more advanced regression setup, but those factors need to be represented correctly and have values available for the future. For recurring seasonal data, consider an Excel forecasting method designed for seasonality and compare its out-of-sample performance.
Should I use dates or period numbers as the predictor?
Either can work if Excel reads the dates as numeric date values and the historical and future entries use the same scale. Period numbers are often easier to interpret for evenly spaced observations, such as months numbered 1 through 24. For irregularly spaced observations, actual dates may better represent the distance between points, but they do not solve seasonality or changing trends.
Related
- F Statistic in Excel: How to Find It
- Find Duplicates in Google Sheets: A Step-by-Step Guide
- Excel Edit History: How to View Changes and Versions
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Excel Named Range Won’t Delete? Causes and Fixes to TryNo—Excel can delete most named ranges, which it calls defined names. In Excel for Microsoft 365 on Windows,…
- Google Sheets Dropdown: How to Add a Blank OptionGoogle Sheets does not offer a dependable, built-in blank choice that users can select from a dropdown menu.…
- How to Freeze X and Y Axes in Excel at the Same TimeTo keep a worksheet’s top row and leftmost column visible while you scroll, select cell B2, then choose…
- Excel Add Slicer to Table: Steps, Limits, and TipsTo add a slicer to an Excel table in Microsoft 365 for Windows, select a cell in the…
- Share an Outlook Calendar So Others Can EditTo let someone edit an Outlook calendar, open Calendar, choose Share Calendar, enter their email address, set the…
- Schedule a Zoom Meeting in Outlook Email: Full GuideTo schedule a Zoom meeting and send it through Outlook, create a calendar event, add the Zoom for…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.