Graphed LogoGraphed
Login

How to Use the TREND Function in Excel

Cody Schneider•

Forecasting future performance is a constant challenge, but Excel's TREND function can make it a lot easier. This powerful tool uses your historical data to predict future results with a simple formula, saving you from complex statistical guesswork. This article breaks down exactly how to use the TREND function with clear, step-by-step examples.

What Exactly Is the TREND Function?

At its core, the TREND function performs a linear regression analysis. That might sound technical, but the concept is simple. It looks at your existing data - say, monthly sales figures over the last year - and finds the single straight line that best fits that data. This is often called the "line of best fit."

Once it determines the mathematical equation for that line (in the form of y = mx + b), it can use it to project where future data points will land along that same trajectory. In short, it analyzes the "trend" in your past data and extends it into the future.

This is incredibly useful for all sorts of business forecasting:

  • Predicting future sales or revenue.

  • Estimating website traffic growth.

  • Forecasting inventory needs based on past demand.

  • Projecting project completion timelines based on progress so far.

Breaking Down the TREND Function Syntax

Before jumping into examples, let's look at the components of the formula. Understanding each part is the first step to using it correctly.

Let's break that down piece by piece:

  • known_y's (Required): This is your output data, or the dependent variable. It's the set of numbers you already have and want to project forward. Think of this as your historical results, like "monthly sales," "daily users," or "units sold."

  • known_x's (Optional): This is your input data, or the independent variable. It's the series that corresponds to your known_y's. In many cases, this is a time series, like an ordered list of months (1, 2, 3), years (2022, 2023, 2024), or specific dates. If you leave this argument blank, Excel automatically assumes a simple integer sequence like {1, 2, 3, 4, ...} that matches the size of your known_y's.

  • new_x's (Optional): These are the new input values for which you want to predict the corresponding y's. If your known_x's list covers months 1-12, your new_x's might be months 13, 14, and 15. The TREND function will use the line it calculated from your historical data to find the y values for these new x's.

  • const (Optional): This is a logical value (TRUE or FALSE) that tells the function how to treat the y-intercept (the b in y = mx + b).

    • TRUE (or omitted): The y-intercept b is calculated normally based on the data. This is what you'll use 99% of the time.

    • FALSE: This forces the trendline to pass through the origin by setting the y-intercept b to 0. This is only used in specific academic or scientific models where it's known that the y value must be zero when the x value is zero.

A Note on Dynamic Arrays in Newer Excel Versions

Excel has changed how it handles formulas that return multiple results.

  • In Excel for Microsoft 365 or Excel 2021, you can simply type the TREND formula into one cell, press Enter, and the results will automatically "spill" into the cells below it.

  • In Excel 2019 and older versions, you have to enter it as an "array formula." To do this, you must first select the entire range of cells where you want the results to go, then type your formula, and finally press Ctrl + Shift + Enter. Excel will add curly braces { } around the formula to show it's an array.

We'll walk through this in the examples.

How to Use the TREND Function: Step-by-Step Walkthrough

Theories are useful, but examples are better. Let's build a realistic forecast using a business scenario. Imagine you have the sales data for a product over the last 12 months, and you want to predict sales for the next three months.

Example 1: Basic Sales Forecasting

Here's our initial data setup in Excel:

Our goal is to populate cells C14, C15, and C16 with forecasted sales numbers.

Step-by-Step Instructions:

1. Select Your Output Range (for older Excel versions)

If you're using an older version of Excel, start by selecting the cells where you want your forecast to appear. In our case, that's C14:C16.

2. Enter the TREND Formula

With those cells selected (or just in cell C14 if using a newer version of Excel), type the following formula:

Let's map this back to our syntax:

  • known_y's is B2:B13 (our historical sales data).

  • known_x's is A2:A13 (the corresponding months).

  • new_x's is A14:A16 (the future months we want to forecast).

3. Execute the Formula

  • New Excel (Microsoft 365/2021): Simply press Enter. The results will automatically fill down from C14 to C16.

  • Old Excel (2019 and earlier): Press Ctrl + Shift + Enter. Your forecast will now appear in the selected cells.

Here's the result. The TREND function has calculated the forecasted sales for months 13, 14, and 15 based on the performance trend of the first 12 months.

Example 2: Visualizing Your Trend and Forecast

Sometimes, a forecast is much easier to understand when you can see it. Let's add a chart to our previous example to visualize both the historical data and our new forecast.

First, create a basic scatter plot or line chart using your original 12 months of historical data (ranges A1:B13).

Next, we can add a trendline to see what Excel is using for the projection.

  1. Right-click on the data series line on your chart.

  2. Select "Add Trendline..."

  3. In the menu that appears, ensure "Linear" is selected. You can also check the box for "Display Equation on chart" to see the underlying y = mx + b formula.

Now your chart should look something like this, showing the line of best fit through your historical data:

See how the straight line smooths out the ups and downs of the actual monthly data? The TREND function's forecasts will fall exactly on this line. You can now add the forecasted data points to your chart to see both historical and future predictions.

Example 3: Forecasting with Multiple Variables

The real power of TREND is its ability to perform multiple linear regression. What if your sales don't just depend on time, but also on how much you spent on advertising that month?

TREND can handle that. Let's adjust our data. Now, our known_x's will be comprised of two columns: Month and Ad Spend.

Our goal is to predict sales for the next two months (D14:D15), given a planned ad spend for those months.

Step-by-Step Instructions:

  1. Start by selecting D14:D15 (for older Excel versions) or just cell D14 (for newer versions).

  2. Enter this formula:

Notice the change:

  • known_y's remains our sales data: C2:C13.

  • known_x's is now a two-column range: A2:B13. This tells Excel to consider both the month and the ad spend when finding the trend.

  • new_x's is also a two-column range, A14:B15, providing the future inputs for both variables.

Press Enter (or Ctrl + Shift + Enter), and you'll get a forecast that takes both time and ad spend into account, providing a much more sophisticated prediction.

Common Mistakes and Practical Tips

The TREND function is reliable, but it's easy to make mistakes if you're not careful. Here are some common pitfalls and tips to get the most accurate forecasts.

Mistake 1: Mismatched Data Ranges

The known_y's and known_x's ranges must have the exact same number of rows and columns. If your known_y's have 12 values, your known_x's must also have 12. Excel will display a #VALUE! error if these do not align. Double-check your selections before executing a formula.

Tip 1: The Trend Is Purely Linear

Remember, this function only finds a straight-line trend. If your data follows a curve (like exponential growth), TREND will become increasingly inaccurate the further it gets from your data. Look at your data pattern first. For curved trends, consider using Excel's GROWTH function instead, which fits an exponential curve or the Forecast Sheet feature for more complex modeling.

Tip 2: Use Enough Data ("Garbage In, Garbage Out")

A trendline based on just two or three data points is notoriously unreliable. As a general rule, try to use at least five or six data points in your historical input - the more the better. The quality and quantity of your historical data will dictate how accurate your forecasting is.

Tip 3: Don't Mistake Projections for Reality

A forecast is an educated guess, not a guarantee. External factors like seasonality, marketing campaigns, new competitors, and economic changes will always impact the "on the ground" realities. Use Trend as a guideline instead of an unchangeable truth.

Final Thoughts

Excel's TREND function is an accessible and surprisingly powerful tool for making data-driven forecasts directly in your spreadsheet. By understanding how to define your historical inputs and your desired outputs, you can quickly project sales, traffic, or any other linear trend to help guide your future business decisions.

While mastering spreadsheet formulas is a fantastic skill, the biggest time-sink in reporting is often juggling data from different sources. For instance, putting Shopify sales, Google Analytics traffic, and Facebook Ads spend into one Excel sheet for a forecast is a manual process that has to be repeated constantly. We built Graphed to solve exactly this problem. We help you connect all your tools in one place, so you can ask for things like "forecast my sales for the next three months" using plain English and get an answer back instantly from live, auto-updating data, without ever touching a formula.