One way to give more meaning to your numbers in Excel is to understand the trends lying behind them, and being able to do so is crucial in today’s ever-changing world. Using Excel’s TREND function will help you identify patterns in previous and current data, as well as project future movements.

What Is TREND Used For?

The TREND function has three primary uses in Excel:

TREND can be used to measure performance or forecast finances in the workplace, and you’re able to also use it on your personal spreadsheets to track your spending, predict how many goals your team will score, and a host of other uses.

How Does TREND Work?

Excel’s TREND function calculates what would be the line of best fit on a chart using the least-squares method.

The line of best fit is calculated using the equation

An Excel sheet containing a table with the month in column A, the revenue in column B, and an empty trend column in column C.

where

While it’s not essential to know this information, it’s handy for choosing what you want to include in your TREND formula syntax.

An Excel worksheet containing the TREND formula and resultant array in column C.

What Is the Syntax for TREND?

Now that you understand what a trendline is and how it’s calculated, let’s look at the Excel formula.

Calculating Your Existing Data’s Trend

It’s now time to put this into action. In the example below, we have the revenue for each of the past few months, and we want to work out the trend for this data.

To do this, we need to type the following formula into cell C2:

An Excel worksheet containing the TREND formula to forecast future values.

whereB2:B14are the known y-values, andA2:A14are the known x-values. We don’t need any new x-values at this point, as we aren’t creating any forecast revenue data. Also, we don’t need a const value, as setting the y-intercept at 0 would yield inaccurate data.

Press Enter after typing your formula, and see that the trend is displayed as an array.

Data selected in an Excel table, and the 2D clustered chart selected in the Insert tab on the ribbon.

Predicting Future Figures

Now that we have an idea of the trend of our data, we’re ready to use this information to forecast the following months. In other words, we want to complete the trend column in our table.

So, in cell C15, we will type

An Excel chart containing trend data as column, rather than a line.

whereB2:B14are the known y-values,A2:A14are the known x-values, andA15:A18are the new x-values for the predictive trend we’re calculating. We haven’t specified a const value, as we want the y-intercept to calculate normally.

Press Enter to see the outcome.

Using TREND With Charts

Now that we have all the data up to the current date, as well as the predictive trendline for the coming month, we’re ready to see how this looks on a chart.

Select all the data in your table, including the headings, and click “2D Clustered Column Chart” in the Insert tab on the ribbon.

An Excel chart selected, with the Change Chart Type in the Chart Design tab selected.

At first, you will see both the independent variable (in our case, revenue) and the trendline on the x-axis, but we want the trend data to show as a line.

To amend this, click anywhere on the chart, and select “Change Chart Type” in the Chart Design tab on the ribbon.

Excel’s Change Chart Type dialog box, with the Trend series identified as a Line chart type.

Then, selectthe “Combo” chart type, and ensure your trendline is set to “Line.”

Then, click “OK” to see your new chart with the trendline showing as you would expect, including as a projection for future months. We’ve also clicked “+” in the corner of our chart to remove the data legend, as it’s not necessary for this chart.

A revenue chart in Excel containing a line of best fit that extends over forthcoming months.

If you aren’t looking to see future projections in your data, you canadd a trendline to your chartwithout using the TREND function. Simply hover your cursor over the chart, click the “+” in the top-right corner, and check “Trendline.” Another way to see trends is touse Excel’s Sparklines, which you may find in the Insert tab on the ribbon.