How to Make a Projection Graph in Excel

Creating a projection graph in Excel turns your historical data from a rearview mirror into a roadmap for the future. Instead of just looking at what has already happened, you can make an educated guess about where your sales, traffic, or other key metrics are headed. This article will guide you through two straightforward methods for creating forecast charts in Excel, helping you plan and strategize with more confidence.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

What Exactly Is a Projection Graph?

A projection graph, also known as a forecast graph, is simply a visual representation that estimates future data points based on your historical data. By analyzing the trend in your past performance - whether it’s sales figures, website users, or inventory levels - Excel can extend that trend line into the future.

For businesses, this is invaluable. It helps with:

• Budgeting and Financial Planning: Estimate future revenue to allocate resources effectively.
• Goal Setting: Set realistic sales targets or marketing KPIs based on projected growth.
• Inventory Management: Predict future demand to avoid stockouts or overstocking.
• Strategic Decision-Making: Identify potential growth opportunities or warning signs that require a change in strategy.

At its core, a projection graph isn’t a magical prediction. It's a data-driven tool that provides a baseline for what you can expect if current trends continue, allowing you to prepare for what’s next.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Step 1: Gather and Prepare Your Data

The quality of your forecast depends entirely on the quality of your historical data. Before you can build anything, you need to gather accurate, consistent data and organize it in a way Excel can understand.

For a reliable forecast, you should have a good amount of historical data. The more data points you have, the more accurately Excel can detect the underlying trend. Aim for at least 15-20 data points if possible.

Your data needs to be structured in two simple columns:

1. A time-series column (e.g., dates, months, or years).
2. A values column (e.g., sales, visitors, or units sold).

Make sure your time-series data is formatted correctly as dates in Excel and sorted chronologically, from oldest to newest. Inconsistent intervals (like mixing daily and monthly data) can confuse the forecasting algorithm.

Here’s an example of well-structured data for tracking monthly website traffic:

Step 2: Method 1 - Adding a Forecast Trendline to a Line Chart

This is the classic, quick-and-dirty method for creating a simple linear projection. It’s perfect when you need a fast visualization of where your current trend is heading.

Create a Basic Line Chart

First, you need to visualize your historical data. A line chart is the perfect way to do this.

1. Highlight your entire data set, including the headers (e.g., from cell A1 to B13 in our example).
2. Go to the Insert tab on the Excel ribbon.
3. In the Charts group, click on the icon for Insert Line or Area Chart.
4. Select the first option under 2-D Line.

Excel will instantly generate a line chart that shows your website traffic over the past year. This line is the foundation of your forecast.

Graphed

Build AI Agents for Marketing

Build virtual employees that run your go to market. Connect your data sources, deploy autonomous agents, and grow your company.

Build Your First Agent

Add the Forecast Trendline

Now, let's extend this line into the future.

1. Click on your newly created chart to select it.
2. With the chart selected, click the green “+” icon (Chart Elements) that appears on the right side of the chart.
3. Hover over Trendline and click the small arrow that appears next to it. Select More Options.... This will open the Format Trendline pane on the right side of your screen. (Alternatively, you can right-click the blue line on your chart and select Add Trendline).

In the Format Trendline pane:

• Under Trendline Options, you'll see several types like Linear, Exponential, and Moving Average. For most simple business projections, Linear is a good starting point as it assumes steady, consistent growth.
• Scroll down to the Forecast section. In the Forward box, type the number of future periods you want to project. Since our data is monthly, entering 6 will forecast the next six months.
• Press Enter. You’ll immediately see a dotted line extending from your historical data, showing the projection.

For a little extra insight, you can check the box for Display R-squared value on chart. The R-squared value (from 0 to 1) tells you how well the trendline fits your actual data. A value closer to 1 means a very strong fit, giving you more confidence in the projection.

Step 3: Method 2 - Using Excel’s Forecast Sheet Feature

For more advanced and statistically robust projections, newer versions of Excel (2016 and later) have a powerful one-click tool called Forecast Sheet. This feature not only projects future values but also detects seasonality and provides confidence intervals, which show a best-case and worst-case scenario.

1. Select your two columns of historical data (the date/time-series and the values).
2. Go to the Data tab on the ribbon.
3. In the Forecast group, click Forecast Sheet.

A dialog box will appear, showing a preview of your forecast. Here, you can customize the projection:

• Forecast End: Choose the date you want your forecast to extend to.
• Advanced Options: Click the "Options" dropdown at the bottom left for more control.

Once you are happy with the settings, click Create. Excel will generate a brand new worksheet containing:

1. A table with your original data plus three new columns: the forecasted values, a lower confidence bound (pessimistic outcome), and an upper confidence bound (optimistic outcome).
2. A professional-looking line chart that visualizes the historical data, the forecast, and the confidence interval range.

This method is far more comprehensive than a simple trendline because it gives you a range of potential outcomes, which is much more realistic for business planning.

AI Agents for Marketing

Crash Course

Graphed

Free PDF · the crash course

AI Agents for Marketing Crash Course

Learn how to deploy AI marketing agents across your go-to-market — the best tools, prompts, and workflows to turn your data into autonomous execution without writing code.

Download the PDF

Tips for Better and More Meaningful Projections

Creating the graph is just the beginning. To make your projections truly useful, keep these tips in mind:

• Sufficient Data is Crucial: A forecast based on only three months of data is little more than a wild guess. The more historical data you provide, the more reliable your trend will be.
• Mind the Outliers: Did one month have a massive, unrepeatable spike because of a viral event or a data entry error? Such outliers can throw off your projection. Consider either removing them or adjusting the value to be more typical before creating your forecast.
• Projections Are Not Guarantees: A forecast assumes that the future will behave like the past. External factors like new competition, market shifts, or a change in your own strategy will impact actual results. Always treat a forecast as a guide, not a certainty.
• Communicate Clearly: When sharing your projection graph, make sure it’s clearly labeled. Distinguish between the "Actual" data and the "Forecasted" data. The confidence interval from the Forecast Sheet tool is excellent for managing expectations and presenting a range of possibilities.
• Update Your Forecast Regularly: As new data comes in each month or quarter, add it to your historical data and regenerate the forecast. This will keep your projections relevant and allow you to adjust your plans as conditions change.

Final Thoughts

Creating a projection graph in Excel transforms your spreadsheet from a simple record of the past into a powerful strategic planning tool. By using either a quick trendline or the more detailed Forecast Sheet feature, you can build a data-driven view of what's ahead, helping you make smarter decisions about your business.

Of course, the first step is always getting your data ready, which often means manually downloading CSVs from different platforms like Shopify, Google Analytics, and your advertising accounts. We built Graphed to eliminate that time-intensive work. We provide one-click integrations with your key data sources, allowing you to connect your marketing and sales platforms in seconds. From there, you can ask for reports, dashboards, and projections using plain English and get live, interactive charts instantly, freeing you to focus on analyzing insights instead of building reports.