One way to give more meaning to numbers in Excel is to understand the trends behind them, and in today's ever-changing world, it's crucial to be able to do that. Using Excel's TREND function will help you identify patterns in previous and current data, as well as predict future trends.
What is TREND used for?
The TREND function has three main uses in Excel:
- Calculate a linear trend from a set of existing data
- Predict future data based on existing data
- Create trend lines in charts to help you predict future data
TREND can be used to measure performance in the workplace or forecast finances, and you can also use it on a personal spreadsheet to track your spending, predict how much your team will achieve, and many other uses.
How TREND works:
Excel's TREND function uses the least squares method to calculate the most suitable lines on the chart.
Use the following equation to calculate the best-fit line:
y=mx+b Thereinto:
- y is the dependent variable
- m is the gradient (steepness) of the trend line
- x is the independent variable
- b is the y-intercept (where the trend line intersects the y-axis)
While knowing this information is not required, it comes in handy for choosing what to include in the syntax of the TREND formula.
What is the syntax of TREND
Now that you understand what a trend line is and how it's calculated, let's take a look at the Excel formula.
TREND(known_y's,known_x's,new_x's,const) Thereinto:
- known_y (required) is the dependent variable you already have that will help Excel calculate the trend. Without these, Excel can't calculate the trend, so your input will result in an error message. These values will be on the y-axis of the histogram.
- known_x (optional) are independent variables on the x-axis on the histogram, and their length (number of cells) must be the same as the reference of the known_y.
- new_x's (optional) is one or more sets of new independent variables on the x-axis for which you want Excel to calculate the trend. This command will tell Excel the number of new y values you want to add to the data.
- Const (optional) is a logical value that defines the intercept (value b in y=mx+b). FALSE forces the trend line to pass through 0 on the x-axis, while TRUE indicates that the intercept is applied normally. Omitting this value is the same as inserting TRUE (normal).
Calculate trends in existing data
Now it's time to put it into action. In the example below, we have revenue from the last few months, and we want to calculate the trend of that data.
To do this, we need to type the following formula into cell C2:
=TREND(B2:B14,A2:A14) where B2:B14 is the known y value and A2:A14 is the known x value. At this time, we don't need any new x-values because we haven't created any forecasted revenue data. Also, we don't need a constant value because setting the y-intercept to 0 will produce inaccurate data.
Press Enter after typing the formula, and you can see that the trend is displayed as an array.
Predict future data
Now that we understand the trends in the data, we're ready to use that information to predict the next few months. In other words, we want to complete the trend column in the table.
So, in cell C15, we'll type:
=TREND(B2:B14,A2:A14,A15:A18) where B2:B14 is the known y value, A2:A14 is the known x value, and A15:A18 is the new x value of the predicted trend that we are calculating. We didn't specify a constant value because we wanted the y-intercept to be calculated properly.
Press Enter to see the results.
Use TREND in charts
Now that we have all the data as of the current date, as well as the forecast trend line for the next month, we're ready to see what this looks like on the chart.
Select all the data in the table, including the headers, and then click 2D Column Chart in the Insert tab of the ribbon.
First, you'll see the independent variable (in our case, revenue) and the trend line on the x-axis, but we want the trend data to show as a line.
To modify this setting, click anywhere on the chart and select Change Chart Type in the Diagram Design tab of the ribbon.
Then, select the "Combo" chart type and make sure your trend line is set to "Straight Line".
Then, click "OK" to see your new chart, where the trend line is displayed as you expect, including a forecast for the next few months. We also clicked on the "+" in the corner of the chart to remove the data legend, as this legend is not needed for this chart.
If you don't want to see future predictions in your data, you can add a trend line to the chart without using the TREND function. Simply hover your cursor over the chart, click on the "+" in the top right corner, and check "Trend Line". Another way to see trends is to use Excel's Sparklines, which you can find in the "Insert" tab of the ribbon.