To build a forecast in Excel, organize past results by date, select the timeline and values, then use Data > Forecast Sheet to create a forecast chart and table. For a simple straight-line projection, enter historical periods and results in columns and use FORECAST.LINEAR. A forecast estimates what may happen next; it is not a guarantee, so check the assumptions before using it to plan.
What You’ll Need
- Excel for Microsoft 365 on Windows for the Forecast Sheet instructions below. Excel for Mac and Excel for the web may not show the same Forecast Sheet command; the formula method described later works in current versions of those apps too.
- Historical observations, such as monthly sales, with one date or period and one numeric result for each row. Use enough history to show the pattern you want to forecast.
- A clear forecast horizon, such as the next six months, and a consistent measurement unit. Do not mix daily and monthly totals or currencies without converting them first.
Step-by-Step
- Define what you are forecasting. Decide on one measurable quantity, such as units sold, customer visits, or monthly expenses. Specify the time interval and how far ahead you need to forecast. A monthly forecast for the next year is easier to interpret than an undefined request to predict future performance.
- Prepare a clean history table. Put the dates in column A and the corresponding values in column B. Use one header row, such as “Month” and “Sales,” followed by one observation per row in chronological order. Make sure the dates are real Excel dates, not text that merely looks like a date. Select a date cell and check whether Excel displays a date format when you change its number format.
- Check the timeline for gaps and duplicates. For a monthly series, include one row for every month, even if the value was zero or no activity occurred. Do not enter zero when the value is actually unknown; that would tell Excel that a real zero occurred. Investigate duplicate dates and decide how to combine them before forecasting. If your observations are irregular, aggregate them into a consistent interval where possible.
- Review the historical values. Scan for typos, blank cells, unusual spikes, and changes in how the data was collected. Compare suspicious values with the original records rather than deleting them automatically. If a one-time event caused a genuine spike, keep it when it is relevant to future planning, but note that it may influence the forecast.
- Select the data. On Excel for Microsoft 365 for Windows, select both columns, including the headers. For example, if the dates are in A1:A37 and results are in B1:B37, select A1:B37. Including the headers helps Excel identify which column contains the timeline and which contains the values.
- Open the Forecast Sheet tool. Choose Data > Forecast Sheet. In the dialog, choose the line chart for a time-based series, then set the Forecast End to the last date you want included. Open Options to review settings such as the forecast start, confidence interval, seasonality, and treatment of missing points. The available settings can vary slightly by Excel build.
- Choose seasonality thoughtfully. If the data repeats in a known cycle, set a seasonality length that matches it: for example, 12 for a yearly pattern in monthly data or 4 for a yearly pattern in quarterly data. If you do not know the cycle, Auto lets Excel estimate it. Seasonal estimates are more useful when the history includes at least two full cycles; three or more cycles usually give you more evidence to assess the pattern. If there is no recurring cycle, do not force one into the model.
- Create and inspect the forecast. Select Create to add a forecast worksheet containing a chart and a table. The chart normally distinguishes historical data from forecasted values and shows a confidence interval. Read that interval as a range of plausible outcomes under the model, not as a promise that the result will fall inside it. Check that the forecast begins where you expect and extends to the intended end date.
- Build a simple formula forecast when you need a linear trend. This method fits a straight line to numbered periods, so it is suitable for a steady trend, not a strongly seasonal series. Suppose A2:A5 contains period numbers 1, 2, 3, and 4, while B2:B5 contains values 100, 110, 120, and 130. Put period 5 in A6. With periods in A2:A5, values in B2:B5, and the target period in A6, enter this in B6:
=FORECAST.LINEAR(A6,$B$2:$B$5,$A$2:$A$5). It returns 140: the values rise by 10 each period, so the straight-line projection adds 10 to 130. Use this only when a linear pattern is a reasonable assumption. - Validate before sharing or acting on it. Hide the most recent few historical observations temporarily, forecast them using only the earlier data, and compare the predictions with what actually happened. This is a simple holdout check. If the estimates are consistently too high or too low, revisit the data, seasonal settings, or method. Restore the full history afterward and label the forecast, time period, units, and assumptions clearly.
Common Mistakes
- Using a messy timeline: Text dates, missing periods, or duplicate dates can make the model misread the sequence. Standardize the timeline before creating the forecast.
- Assuming a forecast is a target: A prediction describes a pattern in historical data; a business goal is a decision. Keep forecast values separate from targets so readers do not confuse the two.
- Choosing a long horizon without enough history: The farther the forecast extends beyond the observations, the more uncertainty grows. Avoid presenting distant estimates with the same confidence as near-term ones.
- Ignoring seasonality or inventing it: A December sales peak may recur each year, while a one-off promotion may not. Check whether the pattern repeats before setting a seasonal cycle.
- Deleting outliers without investigation: Removing a genuine event can make the model less representative. Correct data-entry errors, but document real anomalies and consider whether they are likely to recur.
- Copying a linear trend into seasonal data: FORECAST.LINEAR extends a straight-line relationship. It does not automatically reproduce a monthly or quarterly cycle.
Tips
- Keep a copy of the original data on a separate worksheet, and create the forecast from a clean, clearly named table.
- Use consistent units and labels, such as “dollars per month” or “orders per week.” A chart without units is easy to misread.
- Compare forecasts with a basic benchmark, such as using the last period’s value or the same period last year. A more sophisticated model is not automatically more accurate.
- Record the date you created the forecast and the assumptions used, including seasonality and any excluded observations. This makes later reviews more meaningful.
- Refresh the forecast as new actual data arrives, then track forecast error. Do not overwrite historical actuals with predictions; keep the two clearly distinguishable.
- Use a chart title and axis labels that explain the measure and period. If the confidence interval is visually important, retain it in the forecast chart rather than presenting only the central estimate.
Troubleshooting
- Forecast Sheet is unavailable: Confirm that you are using desktop Excel on Windows and that the selected data contains a recognizable date timeline and numeric values. Excel for the web or some Mac versions may not provide the same command. In those cases, use the formula method for a linear trend or open the workbook in a supported desktop version.
- Excel rejects the selected data: Check that dates are actual date values, the values column contains numbers, and the columns have headers. Remove merged cells and blank rows within the selected range, then select the complete table again.
- The result looks flat or unrealistic: Check whether the source values are stored as numbers, whether the selected range includes the right rows, and whether there is enough history. A stable or flat history can reasonably produce a flat estimate; do not alter data simply to force a preferred result.
- The forecast misses seasonal peaks: Confirm that the data has a regular interval and that the history covers multiple complete cycles. Review the seasonality setting, then compare the forecast with prior years. If the pattern changed recently, older cycles may no longer be representative.
- The formula returns an error: For the example formula, confirm that A6 is numeric, the known period and value ranges are the same length, and the values are numeric. If your Excel uses semicolons as list separators because of regional settings, replace the commas with semicolons in the formula.
- The dates on the chart are out of order: Sort the source table by date from oldest to newest, confirm that the dates are not stored as text, and recreate the forecast from the corrected range.
Frequently Asked Questions
Can Excel forecast from monthly data?
Yes. Arrange one real date and one numeric value per month, with no missing months in the timeline. The Forecast Sheet tool can estimate a recurring monthly-data pattern, including a yearly cycle when the history supports it.
How much historical data do I need?
There is no single amount that suits every series. A simple linear trend can be calculated from a few observations, but a few points provide little evidence that the trend will continue. For seasonal monthly data, aim for at least two full annual cycles and preferably more so you can assess repeatability.
What is the difference between FORECAST.LINEAR and Forecast Sheet?
FORECAST.LINEAR projects a straight-line relationship between known values and periods. Forecast Sheet creates a time-series forecast and can account for seasonality, then adds a chart and forecast table. Choose based on the pattern in your data, not just convenience.
Can I make a forecast in Excel for Mac or the web?
Menu availability differs by platform and version. The Forecast Sheet steps here are for Excel for Microsoft 365 on Windows. Current Mac and web versions can use worksheet formulas such as FORECAST.LINEAR; check the Data tab in your version for forecast tools before following the Windows menu path.
What does the shaded area around the forecast mean?
It represents a confidence interval: a range that communicates uncertainty around the estimate under the model’s assumptions. A wider band means more uncertainty. It is not a guarantee, and it does not account for every possible change in future conditions.
Related
- Google Sheets Budget Template: Find and Use One
- How to Freeze Rows and Columns in Excel
- Freeze in Excel: Keep Rows and Columns Visible on Screen
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Microsoft Excel Power Query: What It Does and How to Use ItMicrosoft Excel Power Query is a built-in tool for importing data and transforming it into a clean, repeatable…
- How to Merge Cells in Google SheetsTo merge cells in Google Sheets in a web browser, select the adjacent cells, then choose Format >…
- Split Cell Color in Excel: What Works and What Doesn’tExcel cannot natively give one cell two separate solid fill colors, such as red on the left and…
- How to Delete a Page in the Word AppTo delete a page in the Word app, select the page’s contents and press Delete; Word does not…
- AutoCAD Excel Data Links: Insert and Update TablesTo insert Excel data in AutoCAD as a linked table, run TABLE, choose From a data link, create…
- How to Copy a Column in Excel Without Losing Any DataTo copy a column in Excel for Microsoft 365 on Windows, click its lettered header, press Ctrl+C, select…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.