How to Calculate Trendline in Excel

Adding a trendline to a chart in Excel is an incredibly useful way to see patterns in your data and forecast future results. You can quickly visualize whether sales are on an upward climb, if website traffic is dipping, or predict inventory needs for the next quarter. This article will guide you through adding and interpreting trendlines on your charts, understanding the different types, and even calculating the underlying forecast values using Excel formulas.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Exactly Is a Trendline?

A trendline, also known as a line of best fit, is a straight or curved line through the data points on your chart that shows the general direction or pattern of the data. Instead of getting lost in the daily or monthly ups and downs, the trendline smooths out the noise and reveals the underlying movement over time. It essentially makes a statistical guess about the path your data is taking.

Businesses use them constantly for all kinds of analysis:

• Sales and Revenue: To see if monthly sales are generally increasing and to forecast performance for the next few quarters.
• Marketing Analytics: To track the growth of website traffic, social media followers, or campaign performance over time.
• Financial Analysis: To analyze the general trend of a stock price, ignoring day-to-day volatility.
• Operations: To predict future demand for a product, helping with inventory management.

How to Add a Trendline to an Excel Chart

The easiest way to get started with trend analysis is by adding a line directly to an existing chart. You’ll need a chart with time-series data, like a line chart or a scatter plot, to do this. For this example, let's use a simple dataset of website traffic over six months.

Here’s our sample data:

Month | Visitors 1 (Jan) | 10,200 2 (Feb) | 11,500 3 (Mar) | 11,300 4 (Apr) | 12,800 5 (May) | 13,500 6 (Jun) | 14,100

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Step-by-Step Instructions

1. Create Your Chart: If you don’t already have a chart, create one. Highlight your data (in this case, both columns), go to the Insert tab, and choose either a Line Chart or a Scatter Chart.
2. Add Chart Element: Click on your newly created chart to select it. You’ll see a green plus sign (+) icon appear in the upper-right corner. This is the Chart Elements shortcut.
3. Select Trendline: Click the plus icon (+). A menu with options like "Axes," "Chart Title," and "Gridlines" will appear. Find Trendline and check the box next to it.

Just like that, Excel adds a default linear trendline to your chart, instantly showing you the upward trend in website visitors.

Choosing the Right Type of Trendline

Excel defaults to a "Linear" trendline, which is a straight line. This works perfectly for data that increases or decreases at a relatively steady rate, like our example. But what if your data's pattern isn't a straight line? Excel offers several other types of trendlines to accurately reflect different patterns.

To access these options, click the small arrow next to "Trendline" in the Chart Elements menu and select More Options.... This will open the "Format Trendline" pane on the right side of your screen.

Here are the most common types and when to use them:

• Linear: The best choice for data sets that follow a straight line. It shows data increasing or decreasing at a steady clip. (Example: A car driving at a constant speed).
• Exponential: Use this when your data values rise or fall at increasingly higher rates. The line is a sharp curve that gets steeper over time. It’s perfect for modeling things with compounding growth. (Example: Population growth or viral campaign reach).
• Logarithmic: Ideal for data that increases or decreases rapidly at first and then starts to level out. Think of it as the opposite of an exponential curve. (Example: Product adoption where initial growth is fast but slows as the market becomes saturated).
• Polynomial: This is for more complex data that has fluctuations (hills and valleys). The line is wavy and can be adjusted to fit multiple peaks and troughs in your data. You can set the “Order” to change its complexity (2 having one peak or valley, 3 having two, etc.), but be careful not to overcomplicate it. (Example: Seasonal sales that rise and fall at regular intervals throughout the year).
• Moving Average: This trendline isn't about predicting a future value based on a formula. Instead, it smooths out volatile data by averaging the data points over a specific number of periods (e.g., a two-month moving average). This helps you see the underlying trend more clearly, devoid of sharp fluctuations. (Example: Daily stock market data).

Displaying the Equation and Forecast Accuracy (R-squared)

Visualizing the trend is great, but the real power comes from understanding the math behind it. Excel can display the equation it used to create the trendline right on your chart.

In the "Format Trendline" pane, scroll to the bottom and check these two boxes:

• Display Equation on chart
• Display R-squared value on chart

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What Do These Mean?

The Equation: For a linear trendline, this will look something like y = 1234x + 9100. This is the classic y = mx + b formula from school. • 'y' is the value you’re trying to predict (Visitors). • 'm' is the slope of the line (how much y increases for every one-unit increase in x). For us, it's 1234 - meaning visitor count goes up by about 1,234 each month. • 'x' is the period (the month number). • 'b' is the y-intercept (the starting point of the line when x=0). For us, it's 9,100.

The R-squared (R²) Value: This number, from 0 to 1, tells you how well the trendline fits your data. An R² value of 1 means a perfect fit, while 0 means no fit at all. A good rule of thumb is that anything over 0.9 is a very strong fit. Our example has an R² of 0.89, which is quite good!

How to Use a Trendline to Forecast Future Values

Option 1: Extending the Line on the Chart

The quickest way to forecast is to visually extend the trendline into the future.

1. Open the "Format Trendline" pane.
2. Look for the Forecast section.
3. In the Forward box, type the number of future periods you want to predict. For example, typing "3" will extend the line three more months into the future.

This provides an immediate visual representation of where your trend is heading.

Option 2: Using the Trendline Equation

For more precise forecasts, you can plug future values into the equation on your chart. Let's use our equation: y = 1234x + 9100.

If we want to forecast visitor numbers for the 8th Month (x=8), we just plug that into the formula:

y = 1234 * (8) + 9100 y = 9872 + 9100 y = 18,972

This tells us that if the trend continues, we can expect roughly 18,972 visitors in Month 8.

Calculating a Trendline with Excel Formulas (No Chart Needed)

Sometimes you just need the numbers without creating a chart. Excel has dedicated functions that let you calculate trend forecasts directly in a cell.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

FORECAST.LINEAR Function

This is the most direct way to get a single forecasted value. The syntax is:

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

• x: The new data point you want to forecast (e.g., Month 8).
• known_y's: Your range of existing dependent values (e.g., your Visitor numbers).
• known_x's: Your range of existing independent values (e.g., your Month numbers).

For our example, the formula to predict Month 8 would be in a blank cell like this:

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

This formula will return the exact same value we calculated manually: 18,972.

SLOPE and INTERCEPT Functions

You can also use formulas to find the individual components of the trendline (m and b).

• To find the slope (m):

=SLOPE(B2:B7, A2:A7)

• To find the y-intercept (b):

=INTERCEPT(B2:B7, A2:A7)

These functions let you build the y=mx+b equation yourself if you need to calculate a series of different forecasts.

Final Thoughts

Mastering trendlines in Excel moves you from simply reporting on past data to intelligently forecasting future performance. Whether by quickly adding a visual line to a chart or using formulas like FORECAST.LINEAR for precise predictions, it's a fundamental tool for making data-driven decisions for your business.

Spending hours pulling data from platforms like Shopify or Google Ads and creating forecasts in Excel is a common but time-consuming routine. At our company, we built Graphed to solve this. Instead of manual data entry, we connect directly to your marketing and sales tools and let you create real-time forecasting dashboards in seconds with simple language. It turns the entire manual reporting process into a quick, automated conversation.