Graphed LogoGraphed
Login

How to Make a Prediction Graph in Excel

Cody Schneider•

Predicting future sales, website traffic, or inventory needs can feel like guesswork, but with Microsoft Excel, you can turn historical data into a clear visual forecast. Creating a prediction graph in Excel helps you visualize a trend line of future performance based on what’s already happened. This guide will walk you through several methods to build an accurate and easy-to-understand prediction graph, from using simple formulas to leveraging Excel's automated forecasting tools.

First, Prepare Your Data

Before you can forecast anything, your data needs to be organized properly. For any time-series forecast, you need two columns:

  • A timeline column with consistent intervals (e.g., days, months, quarters, or years).

  • A values column with the corresponding historical data (e.g., sales, pageviews, or units sold).

For a forecast to be accurate, your timeline needs to have consistent steps between each data point. For example, if you're using monthly data, make sure you don’t skip any months. Missing data points can throw off the prediction.

In this example, Column A contains the months (our timeline), and Column B contains the sales figures for each month (our values). You'll also need to add future dates for the period you want to predict, leaving the sales column blank for those dates. This gives Excel a place to put the forecasted values we're about to calculate.

Method 1: Forecasting with Trendlines (The Quick Visual Method)

The fastest way to get a visual prediction is by adding a trendline directly to a chart. This method is excellent for a quick look at the likely direction of your data without adding any formulas to your worksheet.

Step 1: Create a Basic Line Chart

Start by creating a chart from your historical data. Don't include the empty future date cells just yet.

  1. Highlight your historical data (in our example, cells A1:B13).

  2. Go to the Insert tab on the Ribbon.

  3. In the Charts section, click on Insert Line or Area Chart and select the first 2-D line chart option.

Excel will generate a simple line chart showing your monthly sales for the past year.

Step 2: Add a Trendline to the Chart

Now, let's add the forecast component to the chart.

  1. Click on your newly created chart to select it. You should see a Chart Design tab and a plus (+) icon appear.

  2. Click the plus (+) icon (Chart Elements) on the top right of the chart.

  3. Hover over Trendline and click the small arrow that appears to its right. Select Linear or More Options.

If you select More Options, a "Format Trendline" pane will open on the right side of your screen. Here, you can customize the forecast:

  • Under Forecast, find the Forward option. Enter the number of future periods you want to predict. For our example, let's forecast the next 3 months.

  • You can also select options like "Display Equation on chart" and "Display R-squared value on chart" to see the formula behind the line and how closely the data fits the trend.

Your chart will now show a dotted line extending past your historical data, giving you a visual forecast.

Method 2: Using the FORECAST.LINEAR Function

While the trendline method is fast, you might want the actual forecasted numbers in your cells. This is where formulas come in handy. The FORECAST.LINEAR function predicts a value along a linear trend.

This function works best for data that shows a fairly consistent, straight-line trend without a lot of seasonality (predictable spikes or dips, like holiday sales).

Understanding the Formula Syntax

The syntax for the function is:

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

  • x: The date or time-period for which you want to predict a value. This should be a single cell from your future dates.

  • known_y's: Your range of historical numeric data values (e.g., your sales figures). This is B2:B13 in our example.

  • known_x's: Your range of historical dates or time periods. This is A2:A13 in our example.

Step-by-Step Instructions

  1. In your data table, click on the first empty sales cell next to your first future date (cell B14 in our example).

  2. Type in the formula. For our example dataset, the formula to predict sales for Jan-25 would be:

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

Important Tip: Use absolute references (with dollar signs, like $B$2:$B$13) for your historical known_y's and known_x's ranges. This locks the ranges so they don't shift when you drag the formula down to fill other cells.

  1. Press Enter. Excel will calculate the forecasted value for that month.

  2. Click back on the cell with the formula, then click and drag the small square (the fill handle) at the bottom-right corner of the cell down to automatically apply the formula for the other future months.

Now that you have the numbers, you can easily create a prediction graph by selecting your entire dataset (including historical and forecasted values) and inserting a line chart as you did in the first method.

Method 3: Using the Forecast Sheet Feature (The Easiest Way)

Excel has a powerful, automated tool called Forecast Sheet that handles all the calculations and chart creation for you. This tool is often more accurate than simple linear forecasting because it can automatically detect seasonality in your data and account for it using the Exponential Triple Smoothing (ETS) algorithm.

Step-by-Step Instructions

  1. Select your historical data table (for our example, A1:B13).

  2. Go to the Data tab on the Ribbon.

  3. In the Forecast group, click Forecast Sheet.

  4. A dialog box will appear, showing you a preview of the forecast graph.

In this "Create Forecast Worksheet" window, you have a few options to customize:

  • Forecast End: Choose the date you want the forecast to end.

  • Confidence Interval: This is a really useful feature. It creates upper and lower bound lines on your graph, showing a range in which the actual values are likely to fall. You can adjust the confidence percentage (e.g., 90%, 95%). A higher percentage creates a wider, more cautious range.

  • Seasonality: Excel usually detects this automatically, but you can also set it manually if you know your data follows a specific cycle (e.g., a 12-month cycle for yearly sales).

Once you're happy with the settings, click Create. Excel will instantly generate a new worksheet containing a table with your historical data, the forecasted values, and the confidence interval values. Alongside the table is a professional-looking prediction graph that visualizes all this data for you.

Tips for a Better Prediction Graph

Once your graph is created, a few simple tweaks can make it easier to read and present.

  • Differentiate the Forecast: Click on the forecast section of the line in your chart. In the Format Data Series pane, you can change the line color or set the Dash type to a dotted or dashed line. This creates a clear visual break between what actually happened and what you’re predicting.

  • Add Titles and Labels: Always give your chart a descriptive title, like "Monthly Sales Forecast." Label your X and Y axes (e.g., "Month" and "Sales ($)") so everyone knows what they're looking at.

  • Use a Relevant Chart Type: A line chart or a scatter plot with lines are typically the best choices for time-series data. Avoid using pie charts or bar charts for this kind of forecast.

Final Thoughts

Creating a prediction graph in Excel empowers you to make data-informed decisions by translating past performance into a visual forecast. Whether you use a quick trendline, a precise formula like FORECAST.LINEAR, or the one-click Forecast Sheet feature, you can easily project future trends and better prepare for what's ahead.

For many teams, the challenge isn't just creating the forecast in Excel, but managing all the data needed to make it accurate. Manually exporting data from Google Analytics, Shopify, Facebook Ads, and your CRM can be a weekly chore. When we built Graphed, we wanted to eliminate that friction completely. You can connect your marketing and sales accounts in seconds, and instead of building complex formulas, you just ask questions in plain English like, "Show me a forecast of my Shopify revenue for the next 3 months based on last year's trend." We instantly generate a live, shareable dashboard that updates automatically, helping you move from data prep to decision-making much faster.