How to Project Data in Excel

Figuring out where your business is headed is much easier when you can project future trends based on your past data. Excel is a fantastic tool for this, allowing you to turn a simple list of historical sales or traffic numbers into a powerful forecast. This article will show you how to prepare your data correctly and then use Excel’s built-in tools, including the FORECAST.LINEAR function and the automated Forecast Sheet, to create reliable data projections.

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 is Data Projection and Why Is It So Valuable?

Data projection, or forecasting, is the process of using historical data to estimate future numbers. It's essentially an educated guess - but one that's backed by a statistical model. Instead of relying purely on gut feeling, you’re using patterns in your existing data to predict what’s likely to happen next.

For any business, this is a game-changer. You can use data projections to:

• Set Realistic Goals: Understand your current growth trajectory to set sales quotas or marketing KPIs that are ambitious yet achievable.
• Manage Budgets and Resources: Forecast future revenue to make smarter decisions about hiring, ad spend, and other expenses.
• Optimize Inventory: E-commerce businesses can project future sales to know how much stock to order, avoiding both stockouts and overstocking.
• Anticipate Business Needs: Projecting an increase in website traffic or customer support queries can help you scale your infrastructure or team before you get overwhelmed.

At its core, forecasting helps you move from being reactive to proactive, giving you the foresight to prepare for the future instead of just responding to it.

First, Get Your Data Ready for Forecasting

A projection is only as good as the data you put into it. Before you start using any of Excel's forecasting functions, you need to make sure your data is clean, organized, and properly structured. Skipping this step is the most common reason for inaccurate forecasts.

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

Ensure You Have Consistent Time Intervals

For a time-series forecast to work, your data points must be recorded at consistent intervals. This means your data should be logged daily, weekly, monthly, quarterly, or yearly - without any gaps.

• Good: January Sales, February Sales, March Sales...
• Bad: January Sales, February Sales, March Sales, May Sales...

If Excel’s forecasting tool detects inconsistent time intervals, it will try to average them out, but this can skew your results. It's always best to standardize your timestamps first.

Clean Your Data from Outliers or Errors

Take a quick look at your historical data. Do you see any unusual spikes or dips? An outlier could be a data-entry error (like an extra zero), or it could be a real but one-time event (like a Black Friday sale that caused an abnormal sales spike).

If a strange data point is a genuine error, correct it. If it was a one-off event that's unlikely to repeat, you might consider adjusting it to be closer to the average of the surrounding periods. Leaving major, non-recurring outliers in your dataset can throw off your projection significantly, as the model will assume that spike might happen again.

Structure Your Data in Two Columns

The simplest structure for a forecast in Excel is just two columns:

1. Your timeline or independent variable (we'll call this the x-value). This is usually a series of dates or time periods (e.g., 1, 2, 3 representing Month 1, Month 2, Month 3).
2. Your metric or dependent variable (we'll call this the y-value). This is the number you want to project (e.g., Sales, Website Sessions, New Customers).

Here’s how a clean dataset for projecting monthly website traffic might look:

Method 1: Projecting Data with a Simple Trendline (FORECAST.LINEAR)

The simplest way to project data in Excel is by using a linear trendline. This method assumes that your data is growing or declining at a relatively steady rate. Excel’s FORECAST.LINEAR function is perfect for this.

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

Understanding the FORECAST.LINEAR Function

The function’s syntax looks like this:

=FORECAST.LINEAR(x, known_ys, known_xs)

Let's break that down:

• x: This is the future data point you want to predict a value for. In our example above, if we want to predict traffic for the 13th month, then our x would be 13.
• known_ys: These are your historical metric values - the actual numbers you're basing the forecast on. This would be your range of past "Sessions" data (Column B).
• known_xs: These are your historical time periods that correspond with your metric values. This would be your list of "Months" (Column A).

Step-by-Step Example: Projecting Monthly Sales

Imagine you have 12 months of sales data and want to project sales for the next three months (Month 13, 14, and 15).

Your setup in Excel:

• A2:A13 contains the numbers 1 through 12, representing the months.
• B2:B13 contains the sales figures for each of those months.
• A14, A15, A16 contain the numbers 13, 14, and 15, for the months you want to project.

Here’s how to write the formula to forecast sales for Month 13:

1. Click on cell B14, where you want your first projection to appear.
2. Type the following formula:

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

Why the Dollar Signs ($)?

The dollar signs ($) lock the cell ranges. This is extremely important. When you drag the formula from cell B14 down to B15 and B16, you want Excel to keep referencing your original historical data ($B$2:$B$13 and $A$2:$A$13). Without the dollar signs, Excel would change the ranges relative to the new cell, breaking your formula. The reference to A14 is left unlocked so that when you drag the formula down, it will update to A15, A16, and so on, giving you the projections for each future period.

Visualizing Your Projection

A forecast is even more powerful when you can see it. To create a chart:

1. Highlight ALL of your data, including both the historical numbers and the new projections you just made.
2. Go to the Insert tab in Excel's ribbon.
3. Choose a Line Chart or a Scatter with Straight Lines chart.

You’ll now have a visual that clearly shows your past performance and the projected future trend, making it easy to see where your business is heading at a glance.

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

Method 2: Using Excel's Automated Forecast Sheet for Advanced Projections

For more complex data, especially data with seasonality (like a retail business that always has a huge spike in December), a simple linear forecast might not be accurate enough. This is where Excel's Forecast Sheet tool comes in. It's more sophisticated and can automatically detect seasonal patterns in your data.

This feature uses a more advanced algorithm called Exponential Triple Smoothing (ETS), which accounts for both overall trend and seasonality.

Step-by-Step Guide to Using the Forecast Sheet

1. Organize Your Data: Make sure your data is in two columns, just like before. One column must be a date/time series (e.g., 1/1/2023, 2/1/2023), and the other column has your corresponding values. The columns must have headers.
2. Select Your Data: Click anywhere within your data table.
3. Create the Forecast:
4. Customize Your Forecast: A window will pop up showing a preview of the projection. Here you can tweak the settings:
5. Click "Create": Excel will generate a brand new worksheet containing a table with your historical data, the forecasted values, and the confidence interval ranges. It will also create a professional-looking line chart that visualizes everything for you.

Tips for Better and More Accurate Projections

Creating a forecast isn't a one-and-done task. Here are a few final tips to keep in mind:

• The More Data, The Better: A projection based on 24 months of data will almost always be more reliable than one based on just six months. The more history you can provide the model, the better it can detect the underlying patterns.
• It's a Projection, Not a Promise: Remember that a forecast is a statistical estimate, not a guarantee. Use it as a guide for planning, but be prepared to adjust as new data becomes available.
• Update Your Forecasts Regularly: As you get new real-world data, add it to your historical data set and re-run the forecast. This will keep your projections fresh and reflective of your business's most recent performance.

Final Thoughts

Mastering data projection in Excel, whether through the straightforward FORECAST.LINEAR function or the more powerful Forecast Sheet, lets you make informed, data-driven decisions that can guide your business's strategy. By taking the time to prepare your data correctly and choose the right method, you can transform historical results into a valuable roadmap for the future.

While Excel is great for these types of analyses, the process can become time-consuming, especially when you have to constantly download new CSVs and update your spreadsheets. For this reason, we designed Graphed to automate the entire reporting process. We connect directly to your data sources - like Google Analytics, Shopify, or Facebook Ads - and allow you to build live dashboards and get forecasts using simple, conversational language. It turns the manual work of projecting data into a quick, real-time conversation, freeing you up to act on the insights instead of just calculating them.