# How to use the Excel TREND function to forecast months of future revenue

> The Excel TREND function uses linear regression and dynamic arrays to project future sales from existing revenue figures without complex statistical software.

- Canonical URL: https://coreiten.com/en/article/excel-trend-function-how-to-forecast-future-revenue
- Language: en
- Section: Excel
- Author: Sami
- Published: 2026-10-24T18:01:52+03:00
- Modified: 2026-10-24T18:01:52+03:00
- Publisher: CoreITen (https://coreiten.com)
- Keywords: Excel TREND function, Excel, Microsoft 365, Excel 2021, FORECAST.LINEAR, Forecast Sheet

## Summary

You can easily forecast future revenue in modern versions of Excel using the built-in TREND function with linear regression.

- Writer Ada from MakeUseOf points out that anyone with continuous historical revenue figures already has everything needed to use the function.
- For 24 months of sales data in cells B2:B25 with a timeline in A2:A25, entering =TREND(B2:B25,A2:A25,A26:A37) projects the next twelve months.
- Modern dynamic arrays in Excel for Microsoft 365 and Excel 2021 or later automatically spill forecast figures down when you press Enter.
- A common mistake is swapping argument order by putting the timeline before revenue, which produces unrealistic results.
- The function works best when historical revenue follows a consistent upward or downward path rather than fluctuating monthly or seasonally.

**Why it matters:** It provides a quick, practical baseline for revenue planning without requiring complex statistical software.

---

Businesses looking to predict future earnings over upcoming months do not necessarily need complex statistical software or manual regression formulas. With modern dynamic arrays in Excel for Microsoft 365 and Excel 2021 or later, users can enter the Excel TREND function into a single cell to project months of future revenue.

According to MakeUseOf writer Ada, anyone who already has revenue figures covering a sufficient, continuous period has all the data required to use the function.

### Forecasting revenue with Excel’s TREND function

The TREND function relies on linear regression to determine how an outcome or metric relates to the factors influencing it. In plain terms, it fits a straight line to existing data using the equation `y = mx + b`.

In that formula, **y** is the dependent variable (such as revenue, prices, scores, or expenses), and **x** is the independent variable (such as time, advertising spend, study hours, or distance traveled). Meanwhile, m represents the slope, and **b** represents the intercept.

After establishing that relationship, Excel can estimate new dependent variable values - like future revenue - from new independent variable values, such as upcoming months. For instance, if a worksheet holds 24 months of sales data with the timeline in `A2:A25` and corresponding revenue in `B2:B25`, a user can forecast the next twelve months listed in `A26:A37` by entering this formula in cell B26:

```excel
=TREND(B2:B25,A2:A25,A26:A37)
```

In Excel for Microsoft 365 or Excel 2021 or later, pressing Enter makes the formula automatically spill twelve forecast figures into the cells below, as long as those cells are empty. On older Excel versions without dynamic arrays, users may need to select all twelve output cells first, enter the formula, and confirm it with **Ctrl+Shift+Enter**.

The function’s arguments follow a specific structure:

| Argument | Description |
| --- | --- |
| Known_y's | The required first argument containing existing revenue figures. |
| Known_x's | The optional second argument holding corresponding timeline values, such as months. If omitted, Excel assumes sequential periods using the array {1, 2, 3, …}. |
| New_x's | The optional third argument containing independent variable values for which Excel should predict new revenue (such as A26:A37). If omitted, Excel calculates the trend using the known_x's values. |
| Const | The final argument accepting TRUE or FALSE to decide whether Excel calculates the intercept normally or forces it to zero. Leaving it out lets Excel calculate the intercept from existing data. |

### Common TREND function mistakes to avoid

While getting TREND to work is straightforward, Ada notes that several easy-to-miss mistakes can throw results off:

- **Swapping the argument order:** TREND requires **known_y**’s (historical revenue) first and **known_x**’s (the timeline) second. Reversing them makes Excel treat the timeline as the dependent variable and revenue as the independent variable, producing unrealistic results. Users familiar with FORECAST.LINEAR can easily make this mistake because that function uses a different argument order.
- **Incomplete or inconsistent data:** Because TREND does not automatically skip missing observations, historical revenue ranges must be checked and cleaned for blanks, errors, and missing periods before forecasting. Leaving messy data uncleaned could return common Excel errors or base the forecast on inaccurate sales history.
- **Mismatched timeline sequences:** The timeline needs to reflect the actual sequence of months. Treating sales from January, February, March, and May as four consecutive months ignores the missing April data and distorts the forecast (unless the business makes no sales in April). Using actual Excel dates or consistent period numbers ensures the function accounts for proper intervals.
- **Non-contiguous cell ranges:** Passing individual cells like `B2`, `B4`, and `B12` instead of a continuous range like `B2:B12` can cause issues when intended as a single argument. Historical revenue, timelines, and future periods should use continuous ranges.
- **Assuming numbers are automatically reliable:** Users should plot historical sales and projected figures on a chart and compare them against a linear trendline. If the chart's displayed equation yields slightly different values, check the decimal precision - Excel computes TREND with its underlying precision, whereas chart trendline equations may round the displayed slope and intercept.

### When to use TREND and when to look elsewhere

The TREND function works best when historical revenue follows a reasonably consistent upward or downward path. If sales spike during festive periods, dip in slow seasons, or fluctuate monthly, a straight line may miss those patterns and overlook key changes in a typical sales cycle.

When revenue growth accelerates or slows over time, users can explore polynomial regression to model curved relationships by supplying multiple predictor columns for known_x's, such as time and its powers `{(x), (x^2), and (x^3)}`. For seasonal fluctuations that a basic linear trend will not capture, Excel's Forecast Sheet - one of the program's lesser-known AI features - or other time-series forecasting methods may be more suitable.

Unusual revenue months also require attention. A one-off bulk order or an exceptional downturn can pull the trendline away from typical performance, particularly when historical data is limited. Users should investigate such outliers before letting them influence a forecast, as they may reflect rare circumstances rather than a repeatable pattern.

### Building a practical baseline for revenue planning

Excel’s TREND function offers a time-saving, practical starting point for revenue planning. While it cannot factor in a new competitor, an upcoming price change, or shifting customer demand, it provides a baseline prediction to build upon.

By entering arguments in the proper order, keeping data ranges consistent, confirming that sales follow a linear pattern, and validating outputs against chart trendlines, users can generate a useful revenue forecast from existing sales data.

## Sources

- [makeuseof.com](https://www.makeuseof.com/excel-trend-function-to-forecast-revenue/)

## Related topics

- [Microsoft 365](https://coreiten.com/en/topic/microsoft-365)