How To Use the Excel Trend Function in 3 Steps (With Examples)

By Indeed Editorial Team

Published March 25, 2022

The Indeed Editorial Team comprises a diverse and talented team of writers, researchers and subject matter experts equipped with Indeed's data and insights to deliver useful tips to help guide your career journey.

Professionals who work with data often typically learn different methods for visualizing and analyzing data. Data visualization and analysis provide different methods for evaluating large amounts of information, as well as finding trends or gaps in data sets. If you work with data often or plan to pursue these types of positions, it may be helpful to learn how to use the trend function in Excel. In this article, we define the Excel trend function, explain when to use it, review how to use it and share examples to reference when attempting to use this function effectively.

Related: Data Analysis: Purpose and Techniques

What is the Excel trend function?

The Excel trend function is a built-in Excel formula that calculates a linear trend line using numerical data points to predict a value. It's a statistical function using the least square method, which is a standard approach in regression analysis. This formula takes the average values of "x" and predictive values of "y" to return numbers in a linear trend that matches the data points a user has inputted.

The trend function can also make future predictions based on the projection of the linear trend and current data points. For example, a university can take national test scores from the current year to predict the trends for average scores in upcoming years. In this way, the trend function in Excel is similar to the forecast function.

Related: How To Graph Functions in Excel (With Tips and FAQ)

When to use the Excel trend function

Consider using the Excel trend function when:

  • Reviewing revenue, costs and investments

  • Predicting purchasing trends for a new product or service development

  • Measuring financial performance

  • Creating comparisons of multiple sets of data

  • Analyzing profitability and liquidation predictions

  • Predicting trends in the stock market

  • Reviewing seasonal trends

Related: 10 Top Excel Accounting Formulas and Functions (With Examples)

How to use the Excel trend function

To use the trend function in Excel, consider the following three steps:

1. Open your Excel sheet

To start, open the Excel sheet containing the linear data. You can do this from your desktop by double-clicking on the file you want with the left mouse button or right-clicking on the file once and selecting "Open." If you want to open your file using Excel, you can do this on a Windows device by opening Excel, clicking "File" and then "Open." On a Mac, you can accomplish this by opening Excel and selecting your file from the list provided under the "Recent" section or by clicking the "Open" tab to the left.

2. Input the "TREND" formula into a cell

You can add the trend function into any cell on your spreadsheet. Regardless of its position, it intakes corresponding data from the sheet. The trend function has the following required and optional arguments in its syntax, "=TREND(known_y's, [known_x's], [new_x's], [const])." For example, if you have known values for "y" in the first four rows of column B, known values for "x" in the first four rows of column A, new values for "x" in rows five through eight in column A and want your constant as TRUE, you can write the formula in one of two ways:

  • Option 1: "=TREND(B1:B4, A1:A4, A5-A8, TRUE)"

  • Option 2: "=TREND(B1:B4, A1:A4, A5-A8)"

Here's a breakdown of each component in the formula:

=TREND

This is how you refer to the trend function. You type this first to let Excel know you're adding the trend formula to your data. It's a required component for the trend function.

known_y's

This is a logical value that you've already placed within your linear data to run the formula. It's a required part of the formula when using the trend function. If the known values of "y" are in one column or row, each column or row of known values of "x" become a separate variable.

known_x's

This is a logical value that may be part of your data for values of "x." It can include more than one set of "x" values. If there's one set of "x" values, they can be ranges of any shape as long as their dimensions are equal. If there are multiple sets of "x" values, the "y" values require a height and width of at least one row or column, known as a vector.

This section of the formula is often optional. If there are no known values for "x," it automatically creates an array of numbers, starting at one.

new_x's

This is a logical value where you can add new values for "x" in which you want the trend function to return corresponding values for "y." Adding new values for "x" requires a new column or row for each independent variable, such as the known values of "x." It also requires the same number of columns and rows as known values of "x" and "y."

This is an optional part of the formula, so if it's not included, the formula only considers the known "x" values. If you have no known or new values of "x" in the formula, the trend function creates an array of numbers that starts at the number one. Each number continues to increase in sequential order, such as one, two, three and so on for the number of columns or rows containing known "y" values.

const

This is a logical value that specifies whether the constant "b," or the slope of the trend line, becomes zero. If you add "const" as "TRUE" or don't add it to the formula, the "b" calculates normally with the formula. If you add "const" as "FALSE," value "b" sets to zero. This is an optional part of the formula.

Related: Basic Excel Formulas and How To Use Them

3. Running your formula

Once you type your formula into a cell, there are a few ways to run it, including:

  • Press "Enter" on your keyboard if on a Windows operating system

  • Press "Return" on your keyboard if on a Mac operating system

  • Press "Ctrl + Shift + Enter" in sequential order and hold them down until pressing the last button on your keyboard if on a Windows operating system

  • Press "Command + Shift + Return" in sequential and hold them down until pressing the last button on your keyboard if on a Mac operating system

Example uses of trend functions in Excel

The following are example scenarios to reference when using the Excel trend function with your linear data:

Assessing sales and services of your business

Your business wants to assess sales of long-term services to learn if they might still generate revenue in the future. You have data in your sheet from the last two years that include "x" values in cells A1 to A24 and the number of sales as "y" values in cells B1 to B24. You then want to see additional trends using new "x" values in A25 to A48. Your formula is "=TREND(B1:B24, A1:A24, A25:A48)" or "=TREND(B1:B24, A1:A24, A25:A48, TRUE)."

Related: 7 Effective Methods of Analyzing Data

Predicting trends in the stock market

Your business wants to understand stock market trends for a particular company. It can be your own company or a similar company to products and services your business provides. You have linear data values for "y" in cells B1 to B10 but have no known values for "x". Your formula is "=TREND(B1:B10, TRUE)" or "=TREND(B1:B10)."

Determining potential future test scores of prospective students

Your university wants to learn about SAT scores of current accepted students to project what future scores may be for the upcoming school years. You have scores from the last five years for "y" values in cells B1 to B5, relative years as known "x" values in cells A1 to A5 and have the next five years listed in cells A6 to A10. Your formula is "=TREND(B1:B5, A1:A5, A6:10)" or "=TREND(B1:B5, A1:A5, A6:10, TRUE)."

Please note that none of the companies or products mentioned in this article are affiliated with Indeed.

Explore more articles