Trend Analysis in Excel: Use the TREND Function (With Examples)

If you can describe a trend in one sentence — sales are rising about 8% a quarter — you can have Excel calculate it instead of eyeballing the chart. The TREND function fits a straight line to your existing numbers and returns the fitted values, and with one extra argument it also returns the values you have not recorded yet.

Trend Analysis in Excel: Use the TREND Function (With Examples)

Add a Trend Analysis Column with the TREND Function

TREND uses simple linear regression, so it answers "if the current direction continues, what should each period be?"

The syntax is =TREND(known_y's, [known_x's], [new_x's], [const]) — only the first argument is required.

Put your measured values in one column (for example sales in B2:B13) and the matching periods in the column to its left (A2:A13).
In C2 enter =TREND(B2:B13,A2:A13). Modern versions spill the fitted values down the column; in older builds select the range first and confirm with Ctrl + Shift + Enter.
Select the new column and add a second data series to the chart so you can see the recorded values and the trend side by side.
TREND function creates a trend analysis column

Use Word, Excel, and PPT for FREE

Project Future Values and Compare TREND with FORECAST

Add the periods you want to predict as a third argument and TREND returns the projected values.

Type the next four periods into A14:A17, then in C14 enter =TREND(B2:B13,A2:A13,A14:A17).
If you have a second explanatory column, pass both ranges as known_x's — =TREND(D2:D13,B2:C13) — and TREND fits the line against both variables at once.
Choose between the two functions by shape: FORECAST.LINEAR returns a single value for a single new x, while TREND returns a whole range and handles multiple x variables.
Remember the output is a straight line. If your data swings with the season, add a moving average or a seasonal forecast instead of trusting the linear fit.
Project future trend values from your data

Add a Trendline and Moving Average to the Chart

A chart trendline reaches the same conclusion visually and is easier to show to someone else.

Click a data series, right-click it, and choose Add Trendline.
In the options panel keep Linear for a steady rise or fall, then tick Display Equation on chart and Display R-squared value.
Read R² as the share of variation the line explains; a value well below 0.5 means a straight line is a poor description of the trend.
To smooth noise instead, switch the trendline type to Moving Average and set a period of two to four points.
Trendline options for trend analysis in a chart

Use Word, Excel, and PPT for FREE

The Best Free Alternative: WPS Office

TREND, FORECAST.LINEAR, and chart trendlines behave the same in WPS Spreadsheet as in Microsoft Excel, so the analysis you build keeps working when the file changes hands.

What you need	WPS Spreadsheet	Microsoft Excel
TREND and FORECAST functions	Identical results	Identical results
Trendline with equation and R²	Included	Included
Opening .xlsx workbooks	Full compatibility	Full compatibility
Cost	Free for everyday use	Subscription

No licence wall: trendlines and R² are free, so the forecast is not locked behind a plan.
Familiar shortcuts: Ctrl + Shift + Enter for array TREND works the same way.
One window: build the table, the trend column, and the chart without switching apps.
Free templates: start from a ready-made sales or budget dashboard instead of a blank sheet.

WPS Spreadsheet for trend analysis

FAQs

What does the TREND function actually calculate?

It fits the least-squares straight line through your known data and returns the y values that line predicts — for the existing x values, and for any new ones you supply.

Why does TREND return several numbers?

Because it returns one value per period. Modern versions spill them automatically; in older versions you select the target range and press Ctrl + Shift + Enter.

Is TREND the same as a chart trendline?

They use the same linear regression. A trendline draws the result on the chart, while TREND puts the numbers into cells so you can reuse them in other formulas.

When should I use FORECAST instead of TREND?

Use FORECAST.LINEAR when you need a single predicted value for a single new x. Use TREND when you want a range of predictions or more than one explanatory variable.

Can TREND handle seasonal data?

Not well. TREND only draws a straight line, so for repeating seasonal peaks use a seasonal forecast function or compare a moving average with the raw series.

Free Office Download

Use Word, Excel, and PPT for FREE, No Ads.
Edit PDF files with the powerful PDF toolkit.
Microsoft-like interface. Easy to learn. 100% Compatibility.

100% secure

Algirdas Jasaitis

Summary

Trend analysis in Excel starts with the TREND function: pass your known values and periods, and it returns the fitted trend for each period. Add future periods as a third argument to project them, and use FORECAST.LINEAR when you need a single value instead of a range. For a visual check, add a linear trendline with its equation and R-squared, or a moving average to smooth noise.

Free Excel Editor

Table of Content

Trend Analysis in Excel: Use the TREND Function (With Examples)

Add a Trend Analysis Column with the TREND Function

Project Future Values and Compare TREND with FORECAST

Add a Trendline and Moving Average to the Chart

The Best Free Alternative: WPS Office

FAQs