How to Extrapolate Trendline in Excel

Trying to predict the future based on past performance can feel like guesswork, but with Excel, you can make surprisingly accurate, data-driven forecasts. This guide will walk you through exactly how to extrapolate a trendline in Excel, turning your historical data into a simple visual prediction of what might come next.

First, Get Your Data Ready

Before you can forecast anything, you need some data to work with. Trendline extrapolation works best with time-series data, where you have values recorded over consistent intervals.

Think about things like:

• Monthly sales figures
• Weekly website traffic
• Daily user signups
• Quarterly profits

The key is consistency. Your two columns should represent a time interval (the independent variable, or X-axis) and a corresponding value (the dependent variable, or Y-axis).

For our example, let's use a simple dataset of a startup's monthly user growth over the last six months.

Example Data:

In column A, list the periods (e.g., "Month 1", "Month 2"). In column B, list the corresponding number of users.

Step 1: Create a Chart in Excel

Visualizing your data is the first step toward creating a forecast. A scatter plot or a line chart is perfect for this task.

1. Select your data: Click and drag to highlight your entire data range, including the headers (in our case, A1 to B7).
2. Insert a chart: Go to the Insert tab on Excel's ribbon. In the 'Charts' group, click on Insert Scatter (X, Y) or Bubble Chart. A scatter plot with just the points is a good start, or you can choose 'Scatter with Straight Lines'.

You should now see a chart showing your user growth over the past six months. This visual representation of your historical data is the foundation for your forecast.

Step 2: Add a Trendline to Your Chart

A trendline is a straight or curved line in your chart that shows the general direction or pattern of your data. Adding one is simple.

1. Click anywhere vacant inside your chart to select it. You'll see Chart Design and Format tabs appear at the top.
2. Click on the Chart Elements button, which looks like a green plus sign (+) located at the top-right corner of the chart.
3. In the menu that appears, hover your mouse over Trendline and click the small arrow that appears to the right. Select More Options....

This action will add a default linear trendline to your chart and open the Format Trendline pane on the right side of your Excel window. This pane is where all the forecasting magic happens.

Which Trendline Type Should You Choose?

Excel offers several types of trendlines. For most business forecasting, Linear is a great starting point, but here's a quick rundown:

• Linear: The best fit for data that rises or falls at a relatively steady rate. This is the most common and simplest type of trend.
• Exponential: Use this when your data values rise or fall at increasingly higher rates. It's great for modeling things like viral growth.
• Logarithmic: Ideal when the rate of change in your data increases or decreases quickly and then levels off.
• Polynomial: Use for data that fluctuates. It’s useful for analyzing a large dataset with clear peaks and valleys.
• Power: Best for datasets that increase at a specific rate, often seen in scientific measurements.
• Moving Average: Smooths out fluctuations in data to show the pattern or trend more clearly. It's not used for forecasting forward.

Unsure which one to pick? Click on a few different options and see which line best fits the general path of your existing data points. For our simple user growth example, Linear works perfectly.

Step 3: Extrapolate Your Trendline to Forecast Future Values

Now we get to the core task. With the Format Trendline pane still open, you can easily project this trend into the future.

1. In the Format Trendline pane, look for the Forecast section.
2. You will see two boxes: Forward and Backward. To predict future values, you'll use the Forward box.
3. Enter the number of future periods you want to predict. In our example, we have six months of data and want to forecast the next three. So, you would type 3 into the Forward box.

As soon as you enter the number and press Enter, Excel will extend the dotted trendline three periods into the future on your chart. You now have a visual forecast for Months 7, 8, and 9 based on your existing trend.

An Alternative Method: Using the FORECAST.LINEAR Function

If you prefer a formula-based approach or need specific numeric predictions without creating a chart, Excel's FORECAST.LINEAR function is an excellent tool. It calculates or predicts a future value along a linear trend.

The syntax for the function is:

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

• x: The data point for which you want to predict a value. This would be your future period, like 'Month 7'.
• known_y's: Your range of historical dependent data (the user numbers, so B2:B7).
• known_x's: Your range of historical independent data (the month numbers, which you should represent numerically like 1, 2, 3, etc., so A2:A7).

Example Using FORECAST.LINEAR

Let's find the predicted user count for Month 7 using our data.

First, make sure your lookup period ('Month 7') is represented by a number. If 'Month 1' is 1, then 'Month 7' is 7.

1. In an empty cell (e.g., A8), type 7 (representing Month 7).
2. In the cell next to it (B8), type the following formula:

=FORECAST.LINEAR(A8, $B$2:$B$7, $A$2:$A$7)

(Note: Using dollar signs ($) makes the known_y's and known_x's absolute references, which is helpful if you plan to drag the formula down to calculate for Months 8 and 9.)

When you press Enter, Excel will calculate the predicted number of users for Month 7 based on the linear trend of your past data. You can then do the same for Months 8 and 9. This gives you exact numbers to go along with your visual chart forecast.

A Few Important Considerations

Extrapolation is powerful, but it’s not infallible. It's a mathematical prediction based purely on a historical pattern. Here are a few things to keep in mind to make your forecasts more reliable:

• It's a straight line, not a crystal ball. The forecast assumes that the conditions that created your past trend will continue unchanged. It doesn't account for new marketing campaigns, changes in the market, seasonality, or competitive moves.
• More data is better. A trendline based on three data points is far less reliable than one based on thirty. The more historical data you have, the more stable your trend will be.
• Don't forecast too far ahead. The further out you extrapolate, the less certain the prediction becomes. Short-term forecasts (e.g., predicting the next 2-3 periods based on 12 historical periods) are generally more reliable than long-term ones.
• Choose the right trendline. If your data points are clearly curving upward, a Linear trendline may under-forecast your future values. Try an Exponential trendline instead to see if it provides a better fit.

Final Thoughts

Using Excel to extrapolate a trendline - either visually on a chart or numerically with the FORECAST.LINEAR function - is an incredibly useful skill. It transforms your raw data into an actionable forecast, helping you set goals, allocate resources, and make smarter, data-driven decisions for your business.

Of course, the first big challenge in forecasting is often just getting all your data in one place. Pulling reports from Google Analytics, your ad platforms, your CRM, and Shopify before you even get to Excel can take hours. To solve this, we built Graphed. It connects all your data sources and allows you to create dashboards and get forecasts instantly using simple language, automating the manual work so you can spend your time acting on insights, not just chasing them down.