How to Use the TREND and GROWTH Functions in Microsoft Excel for Linear and Exponential Forecasting

Projecting future sales numbers, estimating customer acquisition rates, and modelling operational costs are routine requirements in business planning. In Microsoft Excel, many analysts rely on adding visual trendlines to chart objects or manually chaining SLOPE and INTERCEPT equations. While effective, these methods can be unwieldy when you need immediate, formula-driven dynamic arrays that output forecasts across multi-period horizons. The TREND and GROWTH functions provide dedicated statistical array engines: TREND models linear progression using least-squares regression, while GROWTH fits exponential curves to model compounding growth.

Linear vs Exponential Forecasting: TREND vs GROWTH

Choosing between TREND and GROWTH depends on the underlying mathematical behaviour of your historical data:

• The TREND Function (Linear Regression): Assumes a steady, constant absolute change over time (e.g., adding roughly £5,000 in monthly revenue regardless of baseline volume). Fits the straight-line equation y = mx + b.
• The GROWTH Function (Exponential Regression): Assumes a constant percentage or compounding rate of change over time (e.g., growing web traffic by 8% per month). Fits the exponential curve equation y = b * m^x.

Syntax and Parameters for TREND and GROWTH

Both functions share identical, highly flexible argument structures:

=TREND(known_y's, [known_x's], [new_x's], [const])
=GROWTH(known_y's, [known_x's], [new_x's], [const])

• known_y's: The range of dependent historical observations (e.g., historical sales, units manufactured, or subscriber numbers).
• known_x's: (Optional) The range of independent observations (e.g., timeline period numbers 1 to 12). If omitted, Excel assumes an array {1, 2, 3...} matching the size of known_y's.
• new_x's: (Optional) The future periods for which you want to calculate predicted values (e.g., periods 13 to 18). If omitted, the function calculates fitted trendline values across the original known_x's.
• const: (Optional) A logical value. If TRUE or omitted, the y-intercept constant b is calculated normally. If FALSE, b is forced to 0 (for TREND) or 1 (for GROWTH).

Step-by-Step Example: Forecasting with TREND

Imagine tracking historical product deliveries over the first six months of the year, with a plan to forecast the subsequent three months:

• Column A (Month Number): 1, 2, 3, 4, 5, 6 in cells A2:A7
• Column B (Deliveries): 120, 145, 160, 190, 215, 240 in cells B2:B7
• Column A (Future Months): 7, 8, 9 in cells A8:A10

In modern Excel (Microsoft 365 and Excel 2021+), enter the following formula in cell B8:

=TREND(B2:B7, A2:A7, A8:A10)

Because TREND is a dynamic array function, it automatically spills the calculated linear estimates down into cells B8:B10, returning values that reflect steady, least-squares additions across the upcoming quarters.

Step-by-Step Example: Forecasting Compounding Growth with GROWTH

Now consider a rapidly expanding SaaS startup tracking monthly active users exhibiting compounding geometric growth:

• Column A (Month Number): 1, 2, 3, 4, 5 in cells A2:A6
• Column B (Active Users): 1000, 1250, 1580, 2010, 2550 in cells B2:B6
• Column A (Future Months): 6, 7, 8 in cells A7:A9

To project user numbers along an exponential curve, enter this formula in cell B7:

=GROWTH(B2:B6, A2:A6, A7:A9)

Excel evaluates the historical compounding rate and spills predicted user numbers into cells B7:B9, accelerating future values to mirror exponential trajectory rather than flattening into a linear projection.

Comparing Existing Fitted Data vs Out-of-Sample Forecasting

You can also use TREND and GROWTH without the new_x's parameter to calculate a smoothed baseline trend directly beside your historical figures:

=TREND(B2:B7, A2:A7)

Entering this in an adjacent column outputs the fitted linear regression values for periods 1 to 6. Comparing actual figures against this fitted baseline allows you to evaluate period-by-period variance, identifying seasonal peaks and underperforming troughs.

Troubleshooting Common Errors (#REF!, #VALUE!, #NUM!)

When applying statistical array functions, ensure your data satisfies these formatting rules:

• #SPILL! Error: Occurs when cells directly below the formula entry point contain existing text, numbers, or formatting that block the dynamic array from expanding. Clear the obstruction to resolve the error.
• #VALUE! Error: Occurs if known_y's, known_x's, or new_x's contain text strings that Excel cannot parse as numbers, or if range dimensions mismatch.
• #NUM! Error in GROWTH: Occurs if known_y's contains zero or negative numbers. Because exponential models require mathematical logarithms, all values in known_y's must be strictly positive (> 0).