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.
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.
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
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:
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.
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.
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.
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.
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.
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)."
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
- What are Stock Options? A Complete Guide To Employee Stock Options
- How To Get Cloud Computing Training in 5 Simple Steps
- What Are CGMP Guidelines? (Plus Tips for Adhering to Them)
- What Is a Master's of Environmental Management? (Plus Jobs)
- Steps for Recruiting Quality Candidates To Startups
- An Outlook Into Competitive Intelligence
- What Is Culture Management and What Are Its Benefits?
- How To Calculate Net Profit Margin
- What Are MRO Inventories? (Plus Examples, Tips and FAQs)
- How To Cross Promote Effectively
- Early Childhood Education Degrees: Definition and Types