How to Extrapolate a Graph in Excel

Ever wished you could see into the future of your data? Extrapolating a graph in Excel comes pretty close, allowing you to make educated forecasts based on existing trends. This type of analysis is essential for everything from predicting next quarter's sales to estimating future website traffic. This article will show you two straightforward methods to project future values in Excel: a quick visual approach using trendlines and a formula-based method for getting precise numbers.

What Exactly is Extrapolation?

In simple terms, extrapolation is the process of estimating future values by extending a known trend or pattern in your data. You look at the path your data has taken so far and project it forward to see where it might go next. It’s like seeing a car driving down a straight road and predicting where it will be in the next 10 seconds based on its current speed and direction.

For example, if your company's revenue has steadily increased by $5,000 every month for the past year, you could extrapolate that it will increase by another $5,000 next month.

It's important not to confuse extrapolation with its counterpart, interpolation. Here’s the difference:

• Extrapolation: Predicting values outside your current range of data (e.g., forecasting for tomorrow based on yesterday's data).
• Interpolation: Estimating a value within your current range of data (e.g., estimating sales on the 15th of the month when you only have data for the 1st and the 30th).

While both are useful, today we're focusing on looking into the future with extrapolation.

When To Use Extrapolation (And When To Be Cautious)

Extrapolation is a powerful tool for planning and forecasting. Marketers might use it to project campaign performance, sales teams might forecast future revenue to set goals, and operations managers might estimate inventory needs. It helps turn historical data into an actionable roadmap for the future.

However, it comes with a critical warning: Extrapolation assumes that the conditions that created the past trend will continue into the future. This is rarely a guarantee. A new competitor, a shift in the market, a viral social media post, or even a global pandemic can completely change the trajectory of your data. The further into the future you extrapolate, the more uncertain your prediction becomes. Think of it as a helpful guide, not a crystal ball.

Method 1: The Visual Approach with Chart Trendlines

This is the quickest and most intuitive way to extrapolate in Excel. It’s perfect for presentations and reports where you want to visually communicate a future trend without necessarily needing to calculate specific data points. The result is a line on your graph that extends into the future.

Let's say you have data on your website's monthly unique visitors for the past year and you want to project the next three months.

Your data might look something like this: Month: Jan (1), Feb (2), Mar (3), ... , Dec (12) Visitors: 10,500, 11,200, 12,100, ... , 21,500

Step-by-Step Instructions:

1. Create a chart. The best charts for this are Line or Scatter charts. Select your data (both the month and visitor columns), go to the Insert tab, and choose either a Scatter with Straight Lines or a Line chart.
2. Add a Trendline. Once your chart is created, select the data series line on the chart. Right-click it and choose "Add Trendline" from the menu. This will add a line that best fits your existing data points.
3. Set Your Forecast Period. When you add the trendline, a "Format Trendline" pane will appear on the right side of your screen. Look for the Forecast section. Here, you'll see "Forward" and "Backward" options. To extrapolate into the future, enter the number of periods you want to project in the "Forward" box. Since we want to forecast three months ahead, we'll type "3" in the box and press Enter.

Instantly, you’ll see the trendline on your chart extend three data points into the future, giving you a visual representation of the projected visitor growth.

Choosing the Right Trendline Type

In the "Format Trendline" pane, Excel offers several types of trendlines. The two most common are:

• Linear: This is the default and most common option. It assumes your data is increasing or decreasing at a steady, constant rate (like adding 1,000 visitors each month). This is great for simple, straight-line trends.
• Exponential: Use this when your data is growing at a compounding rate (e.g., increasing by 5% each month). The growth starts slow and then gets dramatically faster over time.

There are others like Logarithmic or Polynomial, but it's best to start with Linear unless you have a specific reason to believe your growth is exponential or follows another pattern. To check how well your trendline fits your data, you can check the box for "Display R-squared value on chart." A value closer to 1.0 indicates a better fit.

Method 2: The Formula Approach with FORECAST.LINEAR

Visual extrapolation is great for presentations, but what if you need the actual forecasted numbers? Maybe you need to use those projected sales figures in a budget or calculate future ad spend estimates. For that, you'll need a formula.

The go-to function for this in modern versions of Excel is FORECAST.LINEAR. (Older Excel versions used the FORECAST function, which works similarly but may be less accurate.) This function analyzes the linear relationship between two sets of data (known x's and known y's) to predict a future y-value for a given x-value.

Understanding the FORECAST.LINEAR Syntax

The formula looks like this:

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

Let's break down each part:

• x: The future data point you want to predict a value for. This is your x-axis value (e.g., the period you want to forecast, like month 13).
• known_y's: Your range of existing numeric dependent data. This is your existing y-axis data (e.g., your past monthly visitor counts).
• known_x's: Your range of existing numeric independent data. This is your existing x-axis data (e.g., the past months, numbered 1 through 12).

Step-by-Step Example

Let's use our website visitor data again. Imagine your data is in two columns: A (Month Number) and B (Visitors). Your data for the first 12 months is in rows 2 through 13.

• A2:A13 contains the numbers 1 through 12.
• B2:B13 contains the visitor counts for each month.

Now, let's predict the visitors for Month 13 (the first month of our forecast).

1. Set up a cell for your future x-value. In cell A14, type "13".
2. Enter the FORECAST.LINEAR formula. In cell B14, right next to it, enter the formula:

=FORECAST.LINEAR(A14, B2:B13, A2:A13)

• A14 is the future month (13) we want to predict a value for.
• B2:B13 is our range of known visitor numbers.
• A2:A13 is our range of known months (1-12).

When you press Enter, Excel will calculate the projected visitor count for the 13th month based on the linear trend of the previous 12 months.

Pro Tip: If you want to forecast for months 14, 15, and 16, you can make the formula easier to drag down by locking the ranges for your known data using dollar signs ($). The modified formula would be:

=FORECAST.LINEAR(A14, $B$2:$B$13, $A$2:$A$13)

Now, you can type 14 in cell A15, 15 in A16, and simply drag the formula down from B14 to get your forecasts for the next periods instantly.

Forecasting with a Dose of Reality

While these Excel tools are incredibly useful, they simply perform a mathematical calculation, they don't understand your business or the world around it. The final and most important step in any extrapolation is to apply your own judgment and context.

Best Practices for Responsible Forecasting:

• Don't Extrapolate Too Far Ahead: Projecting for the next quarter is often reasonable. Projecting for the next five years based on three months of data is a recipe for disaster. The shorter the forecast period, the more reliable it tends to be.
• Use Clean, Consistent Data: Your forecast is only as good as the data you base it on. Ensure your historical data is accurate and doesn't contain errors or anomalous spikes that could throw off the trend.
• Consider External Factors: Is there a new industry trend? A looming economic downturn? A big marketing campaign planned? Your forecast should be a starting point, which you should then adjust based on real-world knowledge.
• Update Your Forecasts Regularly: As new data comes in, your projections should be refined. Revisiting your forecasts monthly or quarterly helps them stay relevant and accurate.

Final Thoughts

Mastering both the visual trendline and the formula-based approaches to extrapolation in Excel gives you a powerful way to look ahead. Whether you're quickly showing a trend in a meeting or calculating specific financial projections for a new budget, you have the tools to turn your historical data into a forward-looking strategy.

Creating these forecasts in Excel is useful, but it often involves wrestling with formulas and chart settings, especially when your analytics are spread across different platforms. At Graphed, we've made this entire process almost instant. Instead of manually pulling data and building forecasts, you can connect your tools and ask a simple question like, "Project my Shopify sales for the next three months based on last year's trend." We deliver a live, updating dashboard so you can spend less time building reports and more time acting on what's next.