How to Use TREND in Excel

Key Insights

• TREND calculates linear regression forecasts using the least squares method, making it ideal for predicting future values when historical data shows a linear pattern
• Unlike FORECAST.LINEAR which returns a single value, TREND can calculate multiple future data points simultaneously using array formulas, saving time on complex projections
• The const parameter (TRUE/FALSE) determines whether the regression line passes through zero—setting it to FALSE forces the y-intercept to zero, which drastically changes your forecast accuracy

Introduction to TREND Function

TREND is Excel’s workhorse function for linear regression forecasting. It analyzes your historical data, identifies the linear relationship between variables, and projects future values based on that pattern. Think of it as drawing the best-fit straight line through your data points and extending it forward.

You’ll reach for TREND when you need to forecast sales based on historical trends, predict inventory requirements, estimate project timelines, or analyze any scenario where one variable changes predictably with another. The key word here is “linear”—if your data follows a curved pattern, you’ll want GROWTH or polynomial regression instead.

TREND shines when you need multiple forecasts at once. While FORECAST.LINEAR gives you one prediction at a time, TREND can calculate an entire array of future values in a single formula. This makes it invaluable for building dashboards, financial models, and automated reporting systems.

TREND Function Syntax and Parameters

The TREND function follows this structure:

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

known_y’s (required): Your historical dependent variable data—the values you want to predict. This could be sales figures, costs, or any metric you’re tracking over time.

known_x’s (optional): Your historical independent variable data—typically time periods, units sold, or another variable that drives your y values. If you omit this, Excel assumes the sequence 1, 2, 3, etc.

new_x’s (optional): The independent variable values for which you want predictions. If omitted, Excel assumes you want predictions for the same x values as your historical data (useful for filling gaps).

const (optional): TRUE or FALSE. TRUE (default) calculates the y-intercept normally. FALSE forces the regression line through zero. This is a critical parameter that most users ignore—but it can dramatically impact your results.

Here’s a basic example with sample data:

=TREND({100;120;135;155;170}, {1;2;3;4;5}, {6;7;8})

This analyzes five historical data points and forecasts values for periods 6, 7, and 8.

Basic TREND Implementation

Let’s build a practical forecast for monthly sales data. Assume you have six months of historical sales in cells B2:B7:

To forecast July sales in cell B8, use:

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

Wait—that won’t work because month names aren’t numeric. You need numeric x values. Create a helper column with numbers 1-6, or use row numbers:

=TREND(B2:B7, ROW(B2:B7)-1, 7)

For a cleaner approach that forecasts three months (July, August, September) simultaneously, select cells B8:B10 and enter this as an array formula:

=TREND(B2:B7, ROW(B2:B7)-1, ROW(B8:B10)-1)

In Excel 365, just press Enter. In older versions, press Ctrl+Shift+Enter to create the array formula. You’ll see curly braces {=TREND(…)} indicating it’s an array formula.

This single formula populates all three cells with forecasts: approximately 19,900 for July, 20,700 for August, and 21,500 for September (actual values depend on the regression calculation).

Advanced TREND Scenarios

Multi-Period Quarterly Forecasting

For quarterly revenue projections, TREND handles larger datasets elegantly. If you have quarterly revenue in B2:B9 (8 quarters) and want to forecast the next 4 quarters:

=TREND(B2:B9, ROW(B2:B9), ROW(B10:B13))

The const Parameter in Action

The const parameter fundamentally changes your forecast. Consider this scenario: you’re analyzing production costs where you know fixed costs exist (non-zero intercept) versus variable costs that should theoretically be zero when production is zero.

With const=TRUE (default):

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

With const=FALSE (forced zero intercept):

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

Run both formulas on the same dataset and you’ll often see significantly different results. The FALSE parameter is appropriate for scenarios like:

• Unit costs that should be zero when volume is zero
• Scientific measurements where zero input should yield zero output
• Any relationship that logically must pass through the origin

For most business forecasting (sales, revenue, expenses), use TRUE or omit the parameter entirely.

Non-Contiguous Ranges

TREND doesn’t directly support non-contiguous ranges, but you can work around this limitation:

=TREND((B2:B4, B6:B8), (A2:A4, A6:A8), A9)

However, this often returns errors. Instead, use a helper column to consolidate your data, or use array constants:

=TREND({100;120;135;170;185;200}, {1;2;3;5;6;7}, 8)

Combining TREND with Other Functions

Dynamic Range with OFFSET

Create forecasts that automatically adjust as you add data:

=TREND(OFFSET(B2,0,0,COUNTA(B:B)-1,1), OFFSET(A2,0,0,COUNTA(B:B)-1,1), A10)

This formula counts how many data points exist and adjusts the TREND calculation accordingly.

Conditional Forecasting with IF

Forecast only when sufficient historical data exists:

=IF(COUNTA(B2:B7)>=4, TREND(B2:B7, ROW(B2:B7)-1, 8), "Insufficient data")

Dashboard Integration with INDEX and MATCH

Build a flexible forecasting dashboard:

=TREND(
    OFFSET(DataRange, 0, MATCH(SelectedMetric, Headers, 0)-1, DataRows, 1),
    OFFSET(DateRange, 0, 0, DataRows, 1),
    ForecastPeriod
)

This lets users select which metric to forecast from a dropdown, and the formula automatically pulls the correct data range.

Using TREND Data in Charts

You can plot TREND results directly. Calculate your forecast values in a column using TREND, then create a line chart with both historical and forecasted data. Format the forecast series with a different line style (dashed) to distinguish predictions from actuals.

Common Errors and Troubleshooting

#REF! Error

This occurs when your known_y’s and known_x’s arrays have different sizes:

=TREND(B2:B7, A2:A6, A8)  // Error: 6 y values but 5 x values

Fix: Ensure both ranges have identical row counts.

#VALUE! Error

Non-numeric data in your ranges causes this error. Check for:

• Text values in numeric columns
• Blank cells (use 0 or remove them)
• Date formatting issues

Unexpected Results

If forecasts seem wildly off:

1. Check for outliers: One extreme value skews the entire regression line
2. Verify data order: TREND assumes your data is in sequence
3. Assess linearity: Plot your data—if it’s curved, TREND isn’t appropriate
4. Review the const parameter: Wrong setting here produces misleading forecasts

Best Practices

• Always plot your historical data before forecasting
• Use at least 4-5 data points for reliable trends
• Keep known_x’s as simple integers (1, 2, 3…) unless you have specific reasons for other values
• Document whether you’re using const=TRUE or FALSE
• Validate forecasts against business logic

TREND vs. Alternatives

TREND vs. FORECAST.LINEAR

These functions use identical linear regression calculations, but differ in output:

// Single forecast value
=FORECAST.LINEAR(7, B2:B7, ROW(B2:B7)-1)

// Same result with TREND
=TREND(B2:B7, ROW(B2:B7)-1, 7)

Use FORECAST.LINEAR for single predictions. Use TREND when you need multiple forecasts or want to work with arrays.

TREND vs. GROWTH

TREND fits a straight line (linear). GROWTH fits an exponential curve. Compare them with the same data:

// Linear forecast
=TREND({100;150;200;250}, {1;2;3;4}, 5)  // Returns ~300

// Exponential forecast  
=GROWTH({100;150;200;250}, {1;2;3;4}, 5)  // Returns ~312

For data that grows at an increasing rate (compound growth, viral adoption, exponential decay), use GROWTH. For steady, consistent change, use TREND.

When to Use Regression Analysis Tools

TREND is fast and formula-based, but Excel’s Data Analysis Toolpak provides deeper insights:

• When you need R-squared values to assess fit quality
• When you want confidence intervals
• When you’re analyzing multiple independent variables (multiple regression)
• When you need residual analysis

For quick forecasts in spreadsheets, TREND wins. For statistical rigor and detailed analysis, use the Analysis Toolpak or specialized statistical software.

TREND remains one of Excel’s most practical functions for everyday forecasting. Master it, understand its limitations, and you’ll build more accurate models and make better data-driven decisions.