How to Do Predictive Analysis in Excel

Wondering what your sales will look like next quarter or which marketing channels might perform best in the holiday season? You don't need a complex, expensive data science platform to start finding answers. This article will show you how to use a tool you already have - Microsoft Excel - to perform powerful predictive analysis. We’ll walk through several straightforward methods to help you turn historical data into forward-looking insights.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

What is Predictive Analysis (and Why Use Excel for It?)

Predictive analysis is the practice of using historical data, combined with statistical techniques, to make predictions about future outcomes. It’s about moving beyond reporting on what happened and starting to forecast what is likely to happen. Businesses use it to anticipate customer behavior, forecast inventory needs, project revenue, and much more.

While specialized tools like R or Python are the go-to for data scientists, Excel is an incredibly capable and accessible starting point for several reasons:

• It's Familiar: Most business professionals already have Excel and know how to navigate it. The learning curve is much gentler than starting with a new programming language.
• It's Visual: Excel makes it easy to create charts and graphs, helping you see trends and relationships in your data visually before you even start building a model.
• It's Immediate: You can start right away. There's no complex setup or need for an IT team. For many common business forecasting needs, Excel has all the firepower you need.

Before You Begin: Prepping Your Data is Crucial

The golden rule of any data analysis is "garbage in, garbage out." The accuracy of your predictions depends entirely on the quality of your data. Before diving into the methods below, take a moment to ensure your data is clean and organized.

• Structure Your Data Properly: Your data should be in a simple tabular format. Each column represents a variable (like 'Date' or 'Sales'), and each row is an observation or record. Avoid merged cells or complicated layouts.
• Ensure Chronological Order: For time-series forecasting, make sure your dates or time periods are sorted from oldest to newest.
• Check for Errors and Blanks: Scan your data for obvious typos, missing values, or outliers that don't make sense. How you handle blanks (deleting the row, estimating the value) depends on your specific needs, but being aware of them is the first step.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Method 1: Visual Forecasting with Trendlines and Regression

The simplest way to start making predictions in Excel is by visualizing your data and adding a trendline. This method is excellent for identifying linear relationships - where an increase in one variable corresponds to a predictable increase or decrease in another.

Let's imagine you track your monthly advertising spend and the corresponding sales. You want to predict future sales based on a planned ad budget.

Step-by-Step Guide:

1. Create a Scatter Plot: Highlight your two columns of data (e.g., 'Ad Spend' and 'Sales'). Go to the Insert tab, click on the Scatter chart icon, and select the first option. Ad Spend should be your X-axis (the independent variable) and Sales your Y-axis (the dependent variable).
2. Add a Trendline: Click on your new chart to select it. A 'Chart Design' and 'Format' tab will appear. Click the plus icon (+) on the right side of the chart called 'Chart Elements,' hover over Trendline, and click the arrow to select More Options....
3. Display the Equation and R-squared: In the 'Format Trendline' pane that appears on the right, ensure 'Linear' is selected. More importantly, scroll down and check the boxes for Display Equation on chart and Display R-squared value on chart.

Interpreting the Results:

You’ll now see a dotted line running through your data points, along with an equation that looks something like y = 5.21x + 1500 and an R-squared value like R² = 0.92.

• The Equation (y = mx + b): This is the formula for your prediction! In this example, 'y' is the 'Sales' you want to predict, and 'x' is your 'Ad Spend'. To predict sales for a future ad spend of, say, $2,000, you plug it into the formula: Sales = 5.21 * 2000 + 1500.
• R-squared (R²): This value tells you how well the line fits your data, on a scale of 0 to 1. An R² of 0.92 is very high and means that 92% of the variation in sales can be explained by the variation in ad spend. A higher number (closer to 1) means your model is a better fit and your predictions are likely more reliable.

Method 2: Using Excel's Built-in FORECAST Functions

If you don't need a chart and want to get straight to the numbers, Excel provides several powerful functions designed specifically for forecasting. These are fantastic for time-series predictions, where you forecast future values based on a historical timeline.

For Simple Linear Trends: FORECAST.LINEAR

This function does the same mathematical work as the linear trendline but gives you the result directly in a cell. It's best used when your data follows a relatively straight line over time.

The syntax is:

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

• x: The future date or data point for which you want a prediction (e.g., the 13th month).
• known_y's: Your range of historical outcome data (e.g., your sales figures for the past 12 months).
• known_x's: Your range of historical time data (e.g., your month numbers 1 through 12).

For example, if you have sales data from January to December in cells B2:B13 and the corresponding months (1 to 12) in cells A2:A13, you could predict the sales for the next month (month 13) with this formula:

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

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

For Data with Seasonality: FORECAST.ETS

What if your sales aren't linear? What if you sell more ice cream in the summer and coffee in the winter? This is seasonality, a repeating pattern in your data. The FORECAST.ETS function is designed specifically for this, using an algorithm called Exponential Triple Smoothing (ETS) to account for trends and seasonal cycles.

The syntax is a bit more complex, but the core parts are straightforward:

=FORECAST.ETS(target_date, values, timeline, [seasonality])

• target_date: The date for which you want to make a forecast.
• values: Your historical outcome data (e.g., sales).
• timeline: Your range of historical dates.
• [seasonality]: An optional but powerful number indicating the length of the seasonal cycle. For monthly data with a yearly pattern, you would use 12. For quarterly data, you'd use 4. If you leave it blank, Excel will try to detect the seasonality automatically.

This function works best when you have at least a few full seasonal cycles of data (e.g., 2-3 years of monthly data) so it can accurately identify the recurring patterns.

Method 3: Exploring Possibilities with What-If Analysis

Predictive analysis isn't always about forecasting a timeline. Sometimes, it's about exploring potential outcomes by changing your starting assumptions. Excel's 'What-If Analysis' tools are perfect for this.

Goal Seek: Working Backwards to Your Target

Goal Seek is brilliant for when you know the result you want but need to figure out how to get there. Instead of asking "What if we spend $5,000 on ads?", you ask, "How much must we spend on ads to reach $100,000 in revenue?"

To use it:

1. First, set up a simple model in your spreadsheet with at least one input cell and one formula cell. For example, cell B1 could be 'Ad Spend' (your input) and cell B2 could have the formula =B1 * 0.05 to calculate 'Leads' (your output).
2. Go to the Data tab, click What-If Analysis, and select Goal Seek.
3. A small dialog box will pop up:
4. Click OK. Excel will instantly calculate the required 'Ad Spend' to generate 500 leads.

Graphed

Still Building Reports Manually?

Watch how growth teams are getting answers in seconds — not days.

Watch the Demo

Scenario Manager: Comparing Different Futures

The world isn't predictable. Your costs might go up, a new competitor might enter the market, or a marketing campaign might go viral. Scenario Manager lets you build and compare different versions of your forecast: a "best case," a "worst case," and a "most likely" case.

To use it:

1. Set up your model with several input cells (e.g., Unit Price, Cost Per Unit, Units Sold).
2. Go to Data > What-If Analysis > Scenario Manager.
3. Click Add... to create your first scenario (e.g., "Pessimistic Case"). In the next window, select the changing cells (your inputs) and then enter the pessimistic values for each.
4. Repeat the process to add "Optimistic Case" and "Likely Case" scenarios with different values.
5. Once your scenarios are built, click the Summary... button. Excel will generate a brand new worksheet that neatly summarizes all the scenarios side-by-side, making it easy to compare potential outcomes.

Final Thoughts

Excel is an outstanding tool for making predictive analysis accessible. By using features like trendlines, forecast functions, and What-If scenarios, you can go beyond just looking at past performance and start making data-driven predictions about the future. This builds your analytical skills and helps you make smarter, more proactive business decisions.

Of course, the most challenging part of this process is often the manual work of gathering, cleaning, and consolidating your data before you can even begin the analysis - especially when it's scattered across platforms like Shopify, Google Analytics, and Facebook Ads. As we automated our own reporting, we created a way to streamline this. With Graphed you can centralize your marketing and sales data sources automatically. From there, you use natural language to create real-time forecasting dashboards and get immediate answers without spending hours wrangling spreadsheets.