Excel TREND function

The Excel TREND function is used to calculate a linear trend line through a given set of dependent
y-values and, optionally, a set of independent x-values and return values along the trend line.

Additionally, the TREND function can extend the trendline into the future to project dependent y-
values for a set of new x-values.

The syntax of the Excel TREND function is as follows:

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

Where:

Known_y's (required) - a set of the dependent y-values that you already know.

Known_x's (optional) - one or more sets of the independent x-values.

 If only one x variable is used, known_y's and known_x's can be ranges of any shape but
equal dimension.
 If several x variables are used, known_y's must be a vector (one column or one row).
 If omitted, known_x's is assumed to be the array of serial numbers {1,2,3,...}.

New_x's (optional) - one or more sets of new x-values for which you want to calculate the trend.

 It must have the same number of columns or rows as known_x's.

 If omitted, it is assumed to be equal to known_x's.

Const (optional) - a logical value specifying how the constant a in the equation y = bx + a should
be calculated.

 If TRUE or omitted, the constant a is calculated normally.

 If FALSE, the constant a is forced to 0, and the b-values are adjusted to fit the equation y =
bx.

How TREND function calculates linear trendline

The Excel TREND Function finds the line that best fits your data by using the least squares
method. The equation for the line is as follows.

For one range of x values:

y = bx + a

For multiple ranges of x values:

y = b1x1 + b2x2 + … + bnxn + a

Where:

 y - the dependent variable you are trying to calculate.

 x - the independent variable you are using to calculate y.
 a - the intercept (indicates where the line intersects the y-axis and is equal to the value of y
when x is 0).
 b - the slope (indicates the steepness of the line).

This classic equation for the line of best fit is also used by the LINEST function and linear
regression analysis.

TREND function as an array formula

To return multiple new y-values, the TREND function should be entered as an array formula. For
this, select all the cells where you want the results to appear, type the formula and press Ctrl +
Shift + Enter to complete it. As you do this, the formula will get enclosed in {curly braces}, which
is a visual indication of an array formula. Since the new values are returned as an array, you won't
be able to edit or delete them individually.

What is an array in Excel?

Before we start on array functions and formulas, let's figure out what the term "array" means.
Essentially, an array is a collection of items. The items can be text or numbers and they can reside
in a single row or column, or in multiple rows and columns.

For example, if you put your weekly grocery list into an Excel array format, it would look like:

{"Milk", "Eggs", "Butter", "Corn flakes"}

Then, if you select cells A1 through D1, enter the above array preceded by an equal sign (=) in the
formula bar and press CTRL + SHIFT + ENTER, you will get the following result:

What you have just done is create a one-dimensional horizontal array. Nothing dreadful so far,
right?

Simple example of Excel array formula

Suppose you have some items in column B, their prices in column C, and you want to calculate the
grand total of all sales.
Of course, nothing prevents you from calculating subtotals in each row first with something as
simple as =B2*C2 and then sum those values:

A usual Excel formula

However, an array formula can spare you those extra key strokes since it gets Excel to store
intermediate results in memory rather than in an additional column. So, all it takes is a single array
formula and 2 quick steps:

Select an empty cell and enter the following formula in it:

=SUM(B2:B6*C2:C6)

Press the keyboard shortcut CTRL + SHIFT + ENTER to complete the array formula.

Once you do this, Microsoft Excel surrounds the formula with {curly braces}, which is a visual
indication of an array formula.

What the formula does is multiply the values in each individual row of the specified array (cells B2
through C6), add the sub-totals together, and output the grand total:

An array formula

This simple example shows how powerful an array formula can be. When working with hundreds
and thousands of rows of data, just think how much time you can save by entering one array
formula in a single cell.

What is an array formula in Excel?

The difference between an array formula and a regular formula is that an array formula processes
several values instead of just one. In other words, an array formula in Excel evaluates all
individual values in an array and performs multiple calculations on one or several items according
to the conditions expressed in the formula.

Not only can an array formula deal with several values simultaneously, it can also return several
values at a time. So, the results returned by an array formula is also an array.

Array formulas are available in all versions of Excel 2019, Excel 2016, Excel 2013, Excel 2010,
Excel 2007 and lower.

And now, it seems to be the right time for you to create your first array formula.
Excel TREND formula examples
At first sight, the syntax of the TREND function might seem excessively complicated, but the
following examples will make things a lot easier.

TREND formula for time series trend analysis in Excel

Supposing you are analyzing some data for a sequential period of time and you want to spot a
trend or pattern.

In this example, we have the month numbers (independent x-values) in A2:A13 and sales numbers
(dependent y-values) in B2:B13. Based on this data, we want to determine the overall trend in the
time series ignoring hills and valleys.

To have it done, select the range C2:C13, type the below formula and press Ctrl + Shift + Enter to
complete it:

=TREND(B2:B13,A2:A13)

To draw the trendline, select the sales and trend values (B1:C13) and make a line chart (Insert tab
> Charts group > Line or Area Chart).

As the result, you have both the numeric values for the line of best fit returned by the formula and
a visual representation of those values in a graph:

Excel line chart (graph)

A line graph (aka line chart) is a visual that displays a series of data points connected by a straight
line. It is commonly used to visually represent quantitative data over a certain time period.
Typically, independent values such as time intervals are plotted on the horizontal x-axis while
dependent values such as prices, sales and the like go to the vertical y-axis. Negative values, if any,
are plotted below the x-axis.

The line's falls and rises across the graph reveal trends in your dataset: an upward slope shows an
increase in values and a downward slope indicates a decrease.

When to use a line graph

Line charts work well in the following situations:

1. Good visualization of trends and changes. Of all the variety of Excel charts, a line
graph is best suited for showing how different things change over time.
2. Easy to create and read. If you are looking for a simple and intuitively clear way to
visualize large and complex data, a line graph is the right choice.
3. Show relationships between multiple data sets. A multiple line graph can help you
reveal relationships between two or more variables.

When not to use a line graph

There are a few cases in which a line graph is not suitable:

1. Not suited for large data sets. Line graphs are best to be used for small data sets under
50 values. More values would make your chart more difficult to read.
2. Best for continuous data. If you have discrete data in separate columns, use a bar
graph
3. Not suited for percentages and proportions. To display data as a percentage of the
whole, you'd better use a pie chart or a stacked column.
4. Not recommended for schedules. While line charts are great to show trends over a
certain period, a visual view of projects scheduled over time is better done by a Gantt
chart.
How to make a line graph in Excel

To create a line graph in Excel 2016, 2013, 2010 and earlier versions, please follow these steps:

1. Set up your data

A line graph requires two axes, so your table should contain at least two columns: the
time intervals in the leftmost column and the dependent values in the right column(s).

In this example, we are going to do a single line graph, so our sample data set has the
following two columns:

2. Select the data to be included in the chart

In most situations, it is sufficient to select just one cell for Excel to pick the whole table
automatically. If you'd like to plot only part of your data, select that part and be sure to
include the column headers in the selection.

3. Insert a line graph

With the source data selected, go to the Insert tab > Charts group, click the Insert Line
or Area Chart icon and choose one of the available graph types.

As you hover the mouse pointer over a chart template, Excel will show you a
description of that chart as well as its preview. To inset the chosen chart type in your
worksheet, simply click its template.
 In the screenshot below, we are inserting the 2-D Line graph:

Projecting a future trend

To predict a trend for the future, you just need to include a set of new x-values in your TREND
formula.
For this, we extend our time series with a few more month numbers and do trend projection by
using this formula:

=TREND(B2:B13,A2:A13,A14:A17)

Where:

 B2:B13 is known_y's
 A2:A13 is known_x's
 A14:A17 is new_x's

Enter the above formula in cells C14:C17 and remember to press Ctrl + Shift + Enter to complete
it appropriately. After that, create a new line chart for the extended data set (B1:C17).

The below screenshot shows the calculated new y-values and extended trendline:

Excel Trend formula for multiple sets of x-values

In situation when you have two or more sets of independent x values, enter them in separate
columns, and supply that entire range to the known_x's argument of the TREND fucntion.

For example, with the known_x1 values in B2:B13, known_x2 values in C2:C13, and known_y
values in D2:D13, you use the following formula to calculate trend:

=TREND(D2:D13,B2:C13)
Additionally, you can enter the new_x1 and new_x2 values in B14:B17 and C14:C17, respectively,
and get the projected y-values with this formula:

=TREND(D2:D13,B2:C13,B14:C17)

If entered correctly (with the Ctrl + Shift + Enter shortcut), the formulas output the following
results:

Other ways to do trend analysis in Excel

The TREND function is the most popular but not the only trend projection method in Excel. Below
I will briefly describe a few other techniques.
Excel FORECAST vs TREND
"Trend" and "forecast" are very close concepts, but still there is a difference:

 Trend is something that represents the current or past days. For example, by analyzing the
recent sales numbers, you can determine the cash flow trend and understand how your
business has performed and is currently performing.
 Forecast is something that relates to the future. For example, by analyzing the historical
data, you can project future changes and predict where current business practices will take
you.

In terms of Excel, this distinction is not so obvious because the TREND function can not only
calculate current trends, but also return future y-values, i.e. do trend forecasting.

The difference between TREND and FORECAST in Excel is as follows:

 The FORECAST function can only predict future values based on the existing values. The
TREND function can calculate both current and future trends.
 The FORECAST function is used as a regular formula and returns a single new y-value for
a single new-x value. The TREND function is used as an array formula and computes
multiple y-values for multiple x-values.

When used for time series forecasting, both functions produce the
same linear trend/forecast because their calculations are based on the same equation.

Please take a look at the screenshot below and compare the results returned by the following
formulas:

=TREND(B2:B13,A2:A13,A14:A17)

=FORECAST(A14,$B$2:$B$13,$A$2:$A$13)
Draw a trendline to visualize the trend

A trendline is commonly used to observe the general trend in your current data as well as project
future data movements.

To add a trend to an existing chart, right-click the data series, and then click Add
Trendline… This will create the default linear trendline for the current data and open the Format
Trendline pane where you can choose another trendline type.

To forecast a trend, specify the number of periods under Forecast on the Format Trendline pane:

 To project the trend into the future, type the number of periods in the Forward box.
 To extrapolate a trend into the past, type the desired number in the Backward box.

To show the trendline equation, check the Display Equation on chart box. For better accuracy,
you can show more digits in the trendline equation.
As shown in the image below, the results of the trendline equation are perfectly in line with the
numbers returned by the FORECAST and TREND formulas:

For more information, please see How to add a trendline in Excel.

Smooth trend with moving average

Another simple technique that can help you show a trend is called moving average (aka rolling
average or running average). This method smoothes out short-term fluctuations in a sample time
series and highlights longer-term patterns or trends.

You can calculate moving average manually with your own formulas or have Excel make a
trendline for you automatically.

To display a moving average trendline on a chart, here's what you need to do:

1. Right-click the data series and click Add Trendline.

2. On the Format Trendline pane, select Moving Average and specify the desired number
of periods.