How to Do a Linear Trend in Excel

Trying to forecast future sales or understand performance trends directly in Excel is a must-have skill, and adding a linear trendline is a simple way to see the big picture. This article will show you exactly how to do it using three different methods, from a quick visual chart to a more advanced formula-based approach.

What is a Linear Trend Anyway?

In simple terms, a linear trend uses a straight line to show the general direction of your data over time. Think of it as drawing a "line of best fit" through a series of data points to see whether they're trending up, down, or staying flat. It helps you smooth out the day-to-day or week-to-week noise and focus on the underlying pattern.

For example, you can use a linear trend to:

• Forecast Future Performance: Predict next quarter's revenue based on the last two years of sales data.
• Analyze Marketing Impact: See if your average customer order value is trending up since you started a new ad campaign.
• Track Operational Efficiency: Determine if your website's page load speed is generally improving over time.

The trend is based on a classic mathematical equation you might remember from school: Y = mx + b. Don't worry, you don't need to do the math yourself! Excel handles it all. Here’s what it means in a business context:

• Y is the value you're trying to predict (e.g., future sales).
• m is the slope of the line, showing how steeply it's going up or down. A positive slope means growth, a negative slope means decline.
• x is the time period you're looking at (e.g., the specific month or quarter).
• b is the intercept, or the value where the line would start at "time zero."

Method 1: Using an Excel Chart Trendline (The Visual Method)

This is by far the quickest and most common way to visualize a linear trend. It's perfect for presentations and dashboards where a picture tells the story better than a table of numbers.

Step 1: Set Up Your Data

First, organize your data into two columns. The left column should be your time component (e.g., Day, Week, Month, Quarter) and the right column should be the metric you want to measure (e.g., Sales, Users, Clicks).

For our example, let's look at monthly website sessions for the first half of the year:

Column A: Month January February March April May June

Column B: Sessions 12,050 12,800 13,500 12,900 14,100 14,850

Pro Tip: It often works best if you convert your time periods into simple numbers (1, 2, 3, 4...). This makes the forecasting formulas in the next methods easier to manage.

Step 2: Create a Scatter Plot or Line Chart

Now, let's turn this data into a chart.

1. Highlight all your data, including the headers (cells A1 through B7 in our example).
2. Go to the Insert tab on the Excel ribbon.
3. In the Charts section, choose either a Scatter with Straight Lines or a standard Line chart. A scatter plot is often technically more accurate for this, but a line chart is visually cleaner and works just as well.

Excel will instantly generate a chart. You’ll see your session numbers plotted out, showing the month-to-month fluctuations.

Step 3: Add the Linear Trendline

This is where the magic happens. We'll add a layer over top of our monthly data to show the overall trend.

1. Click once on the data line within your chart to select it. You should see little circles appear at each data point.
2. Right-click on the data line. A context menu will appear.
3. Select Add Trendline... from the menu.

Excel will immediately draw a dotted straight line over your data. By default, it uses the "Linear" option, which is exactly what we want.

Already, you can see a clear upward drift in website traffic, even with the small dip in April.

Step 4: Display the Equation and R-squared Value

To make this trendline more useful, we can tell Excel to show us the math behind it.

1. With the Trendline still selected, look for the Format Trendline pane that appeared on the right side of your screen. (If it disappeared, just double-click the trendline to bring it back).
2. Scroll down and check the boxes for:

Two small text boxes will appear on your chart. The Equation shows you the "m" (slope) and "b" (intercept) values. The R-squared value tells you how well the line fits your data - a number closer to 1.0 means an excellent fit.

Bonus Step: Forecast into the Future

You can also use the trendline to make a quick visual forecast.

1. In the Format Trendline pane, find the Forecast section.
2. In the Forward box, type the number of future periods you want to predict. If you have 6 months of data and want to predict the next 3, you'd type "3" into the box.

Excel will extend the dotted trendline out, giving you a visual estimate of where your sessions might be three months from now.

Method 2: Using the FORECAST.LINEAR Function

The chart method is great for visuals, but sometimes you just need the exact number. Maybe you want to put a sales forecast in a specific cell in your financial model. For this, the FORECAST.LINEAR function is perfect.

The function’s job is to calculate a future value along a linear trend.

The syntax is:

=FORECAST.LINEAR(x, known_y's, known_x's)

• x: The new data point you want to predict a value for (e.g., month 7).
• known_y's: Your range of existing numeric values (e.g., your past sales data). This is the dependent variable.
• known_x's: Your range of time periods (e.g., months 1 through 6). This is the independent variable.

Example in Action

Let's use our same website traffic data, but this time, we'll replace the month names with numbers from 1 to 6 in Column A. Let's say we want to predict the traffic for July (Month 7).

A: Period | B: Sessions 1, 12,050 2, 12,800 3, 13,500 4, 12,900 5, 14,100 6, 14,850

In any empty cell, type the following formula:

=FORECAST.LINEAR(7, B2:B7, A2:A7)

• 7 is the month we want to predict.
• B2:B7 is our range of known sessions (the y-values).
• A2:A7 is our range of known months (the x-values).

When you press Enter, Excel will calculate the projected sessions for Month 7 based on the existing trend, which comes out to 15,191.4. This gives you a specific, quantifiable forecast you can use in other calculations.

Method 3: Using the LINEST Function for a Deeper Dive

The LINEST function is the most advanced of the three methods. It doesn't give you a visual or a single forecast. Instead, it returns the statistical components of the trendline itself - the slope (m) and the y-intercept (b). This is useful if you want to use these components to build your own forecasting models.

The syntax is:

=LINEST(known_y's, known_x's)

Using LINEST in Practice

This is an array formula, which means it returns multiple values at once (the slope and the intercept). How you enter it depends on your version of Excel.

1. Select two adjacent cells, for example, D2 and E2. One cell will hold the slope, and the other will hold the intercept.
2. In the formula bar, type the following formula but don't press Enter yet:

=LINEST(B2:B7, A2:A7)

After entering the formula, you will see two numbers:

• The cell with the slope (m) will display approximately 534.28. This means that, on average, sessions are increasing by about 534 each month.
• The cell with the y-intercept (b) will display approximately 11,451.4. This is the theoretical starting point of your trendline at "Month 0."

Final Thoughts

Understanding linear trends gives you a powerful way to make sense of your data, moving from simply looking at numbers to generating actionable forecasts. Whether you prefer the visual approach of a chart trendline, the quick number-crunching of FORECAST.LINEAR, or the statistical detail from LINEST, Excel provides flexible tools to get the job done. Each method helps cut through the noise and reveal the story your data is trying to tell.

Figuring this out in Excel is powerful, but the setup - exporting CSVs from different platforms, cleaning the data, and rebuilding reports - is manual and slow. At Graphed, we automate all that boring stuff. Simply connect your data sources like Google Analytics, Shopify, or HubSpot once, and then ask for an analysis in plain English. For instance, you could just ask, "Show me a trendline of my website sessions from Google Analytics this year," and we'll instantly generate the chart, freeing you up to focus on strategy instead of spreadsheets.