by Svetlana Cheusheva, updated on
The tutorial shows how to calculate trend in Excel by using the TREND function, how to create trends on a graph, and more.
These days when technologies, markets and customer needs are changing so rapidly, it is critical that you move with trends, and not against them. Trend analysis can help you identify underlying patterns in the past and current data movements and project future behavior.
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:
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.
New_x's (optional) - one or more sets of new x-values for which you want to calculate the trend.
Const (optional) - a logical value specifying how the constant a in the equation y = bx + a should be calculated.
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:
This classic equation for the line of best fit is also used by the LINEST function and linear regression analysis.
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.
At first sight, the syntax of the TREND function might seem excessively complicated, but the following examples will make things a lot easier.
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:
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:
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:
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:
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.
"Trend" and "forecast" are very close concepts, but still there is a difference:
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:
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)
For more information, please see Using the FORECAST function in Excel.
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 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.
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:
That's how you use the TREND function to calculate trends in Excel. To have a closer look at the formulas discussed in this tutorial, you are welcome to download our sample Excel TREND workbook. I thank you for reading and hope to see you on our blog next week!
Table of contents