To build a dynamic forecast in Excel, put monthly actuals in an Excel Table, add editable growth and forecast-length assumptions, and use spill formulas to generate future dates and values. The forecast recalculates when you change an assumption or add a new month of actuals, so you do not need to rebuild the projection each time.
What You’ll Need
- Microsoft Excel for Microsoft 365 on Windows. The formulas below use dynamic arrays and XLOOKUP; these are also available in Excel for Microsoft 365 on Mac and the web. XLOOKUP is also available in Excel 2021.
- At least several months of historical data, with one row per month and a consistent measure such as revenue, units sold, or customer sign-ups.
- A reasonable assumption for annual growth and a chosen forecast length. Treat these as planning inputs, not guaranteed outcomes.
Step-by-Step
- Arrange and check the historical data. Create two columns with headers Month and Revenue. Put one month-end or month-start date on each row, with the corresponding actual value beside it. Use real Excel dates, not text that merely looks like a date. Keep the dates in ascending order, use the same date convention throughout, and remove duplicate months. If you are forecasting a different metric, use a clear header such as Units and apply the same method to that measure. A longer, representative history usually makes it easier to judge whether a growth assumption is plausible.
- Convert the history to an Excel Table. Select the two columns, including their headers, then choose Insert > Table and confirm that the table has headers. Click inside the table and, on the Table Design tab, change the Table Name to
Actuals. Table names cannot contain spaces. When you append another month directly beneath the table, Excel normally expands it and includes the new row in the table references. On Mac, use Insert > Table as well; the table-design controls may appear in a contextual tab when the table is selected. - Add a small assumptions area. In cells E1:F3, enter labels and values: put Annual growth in E2 and
12%in F2; put Forecast months in E3 and12in F3. Format F2 as a percentage and F3 as a whole number. The 12% and 12-month values are illustrative: replace them with assumptions suitable for your model. Keep assumptions in separate, clearly labeled cells rather than burying them inside formulas. This makes it easier to review or compare scenarios later. - Create the forecast output headings. In A1 and B1, enter Month and Forecast. Leave the rows below these headings empty so the formulas can spill into them. Do not turn this output range into an Excel Table: spilled-array formulas cannot expand inside a table. You can put the output on a separate worksheet if you want to keep assumptions, source data, and projections visually distinct.
- Generate the forecast dates. With the historical dates in
Actuals[Month]and the number of forecast months in F3, enter this in A2:
With the layout above, where the source dates are in the Month column of the Actuals table and F3 contains the forecast-month count, the formula creates one date per forecast month:=EDATE(MAX(Actuals[Month]),SEQUENCE($F$3))
Format the spilled dates as dates, for example with Home > Number and a suitable date format. If the latest actual is December 2025 and F3 is 12, the first forecast date is January 2026 and the last is December 2026. The formula uses the latest date in the table, so adding a later actual moves the forecast start forward. - Calculate the forecast values. With annual growth in F2, the forecast length in F3, and the source dates and revenue in the Actuals table, enter this in B2:
For that same layout, this formula takes the revenue from the latest actual and compounds the annual growth assumption across the forecast months:=XLOOKUP(MAX(Actuals[Month]),Actuals[Month],Actuals[Revenue])*(1+$F$2)^(SEQUENCE($F$3)/12)
Suppose the latest actual is $10,000 for December 2025, F2 is 12%, and F3 is 12. January 2026 is approximately $10,094.38, and December 2026 is $11,200.00. The annual result is $10,000 multiplied by 1.12; monthly values rise by the equivalent compounded monthly rate. The forecast does not include seasonality, changing prices, or one-off events. - Format and label the output. Format the forecast values as currency or as the appropriate unit, using Home > Number. Make the assumptions visually distinct from calculated results, and consider adding a note that identifies the annual growth assumption and the date through which actual data is available. Avoid typing into the cells beneath A2 or B2: those are part of the spill ranges. If Excel shows
#SPILL!, clear any content or merged cells blocking the output. - Check the forecast against the source. Verify that the final actual date is the month immediately before the first forecast date, that the last date matches the selected horizon, and that the latest actual value is the base for the projection. Change F2 temporarily to a smaller growth rate and confirm that the forecast decreases; restore the intended assumption afterward. With 0% annual growth, every projected month should equal the latest actual. This is a useful simple test of the setup.
- Add a chart if it helps your audience. Select the forecast dates and values and choose Insert > Line or Area Chart to chart the projected period. To show actuals and forecast together, prepare a display range with actual dates and values followed by the forecast dates and values, using separate series if you want the line styles to differ. Label the forecast clearly so readers do not mistake projected values for recorded results. Check the chart again after extending the horizon; a chart built from a fixed range may not include additional spilled rows automatically.
- Update it when actuals arrive. Add each new month to the bottom of the Actuals table and enter the actual value. The formulas use the latest date and value in that table, so they should shift the forecast window forward and recalculate. Confirm that the new row was included in the table and that the date is later than the previous latest month. Save a copy before making major structural changes, especially if other reports or charts refer to this workbook.
Common Mistakes
A frequent error is storing dates as text. They may look correct on screen, but MAX cannot reliably identify the latest month from text entries. Convert them to real dates, then apply date formatting. Also check that every month appears only once and that there are no blank or mistyped dates at the bottom of the source table.
Another mistake is using a growth rate without checking what it means. The example assumes a steady annual compound growth rate, distributed evenly across months. It does not mean that every month grows by 12%. Applying 12% each month would create an exceptionally different projection. Similarly, using a simple addition of 1% per month is not identical to compounding an annual 12% rate.
Do not overwrite spilled results with manually entered values, or insert merged cells in the output area. Either can stop a dynamic-array formula from expanding. Avoid mixing actual values and forecast values in one unlabeled column; readers should be able to tell which numbers are history and which are estimates. Finally, do not assume a smooth growth forecast is appropriate for a seasonal business or one with known contract changes, price increases, or capacity limits.
Tips
Keep source data, assumptions, and outputs visually separate. Add units to headings, use consistent date conventions, and record why an assumption was chosen. If several people edit the workbook, protect the formula cells while leaving the input cells available for updates. Test the sheet with low, base, and high growth assumptions by changing F2 and noting the results; save each scenario separately or document the values before changing them.
Compare forecasts with actual results as new months arrive. A forecast is a model, not a promise: if it repeatedly misses, revisit the assumptions and the method rather than merely extending the projection. For businesses with seasonal patterns, a flat compound-growth model may be misleading. Excel also has forecasting features such as Forecast Sheet, but a driver-based formula is often easier to adapt when you need transparent assumptions or a simple monthly projection.
Troubleshooting
The formula returns #NAME?. Check that the table is actually named Actuals and its column headings are exactly Month and Revenue. Confirm that your Excel version supports XLOOKUP and dynamic arrays. If your installation uses semicolons rather than commas as formula separators because of regional settings, Excel may adjust separators when you enter the formula; use the separators Excel expects.
The formula returns #SPILL!. Select the warning icon or the formula cell to inspect the spill range. Clear any occupied cells, merged cells, or other obstruction below the formula. Keep the forecast output outside an Excel Table. Also make sure the forecast-month input is a positive whole number rather than text or a decimal.
The forecast starts in the wrong month or shows zeros. Check that the latest source date is a real date and that the latest row contains a numeric actual. Make sure the table has expanded to include the new row. If the date convention is month-end, keep using month-end dates; do not mix month-end and mid-month entries. Check F2 for an accidental value such as 12 instead of 12%.
The results look too high or too low. Confirm the annual rate, base actual, and units. A rate entered as 12 rather than 12% means 1,200% in a percentage-style assumption. Check whether the formula is meant to model revenue, units, or another metric, and whether steady compounding makes sense for it. If the projection still appears unreasonable, compare several alternative assumptions and explain the limitations rather than presenting a single number as certain.
Frequently Asked Questions
Does this forecast update when I add a new actual month?
Yes, if the new row is included in the Actuals table and contains a later, valid date and a numeric value. The formulas use the latest date and corresponding value as the starting point.
Can I change the forecast length?
Yes. Change F3 to the number of months you want. The date and value formulas spill to match that count, provided the cells beneath their spill ranges are clear.
Can I use this for costs, units, or headcount?
Yes. Replace Revenue with the name of the measure you are projecting and format the output appropriately. The method assumes that the measure can reasonably be projected using a constant compound-growth rate.
Can the forecast account for seasonality?
Not in this simple growth model. It applies a smooth monthly increase. For seasonal data, use an approach that reflects recurring monthly patterns and compare its results with historical performance before relying on it.
Will the formulas work in older Excel versions?
The formulas shown rely on dynamic arrays and XLOOKUP, so use Microsoft 365 or a version that supports both features, such as Excel 2021. In Excel for the web and current Microsoft 365 for Mac, dynamic arrays are supported too, although ribbon placement can differ slightly.
Related
- Excel File Too Large to Send? Fixes and Workarounds
- Export to CSV vs Excel: Which Format Should You Choose?
- Extract Data from Google Maps to Excel: Practical Options
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Choose an Excel Data Range: Methods, Tips, and ShortcutsTo choose a data range in Excel for Microsoft 365 on Windows, click the first cell and drag…
- Excel Go To Referenced Cell: Shortcuts and NavigationIn Excel for Microsoft 365 on Windows, press Ctrl+[ to select the cell or cells directly referenced by…
- Conditional Formatting to Highlight Blank CellsYes. In Excel for Microsoft 365 on Windows, select the range and choose Home > Conditional Formatting >…
- Google Sheets for Finances: Budgeting and TrackingYes—Google Sheets can help you make a household budget, track spending, monitor bills, and compare actual costs with…
- Excel Shortcut to Center Text: Windows, Mac, and WebIn Excel for Microsoft 365 on Windows, select the cell or cells and press Alt, H, A, then…
- Excel Weekday Name: Show the Day from Any Date in ExcelTo show the weekday name for a date in Excel, put the date in A2 and enter =TEXT(A2,"dddd")…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.