How to Do Predictive Analytics in Excel

Thinking about the future of your business doesn't have to be pure guesswork. You can use the data you already have to make strong, educated predictions about sales, website traffic, and more - all from within Microsoft Excel. This article will show you how to use Excel’s powerful built-in tools to perform predictive analysis, even if you’re not a data scientist.

What is Predictive Analytics? (A Simple Explanation)

Predictive analytics sounds complex, but the concept is straightforward. It’s the process of using historical data to find patterns and then using those patterns to predict future outcomes. You're already familiar with it in your daily life. When Netflix suggests a movie you might like, it's using predictive analytics based on your viewing history. When your weather app forecasts rain, it’s using historical weather patterns to predict the future.

For your business, this can translate into answering important questions like:

• How many units will we sell next quarter?
• Which customers are most likely to stop using our service?
• How much website traffic will our next marketing campaign generate?

By transforming historical data (what happened) into future insights (what might happen), you can make smarter, more data-driven decisions. And the good news is, you don’t need an expensive, specialized tool to get started. Excel has everything you need.

Laying the Groundwork: Preparing Your Data in Excel

Before you can make any predictions, you need organized, reliable data. Your analysis is only as good as the information you feed it, so spending a bit of time on preparation is essential. A messy dataset will lead to misleading predictions.

Follow this simple checklist to get your data ready for analysis:

1. Organize it in a Table: Structure your data in a clear tabular format. Make sure each column has a distinct header (like "Date," "Sales Amount," or "Ad Spend"), and each row represents a single record (like a day, a customer, or a transaction).
2. Check for Consistency: Ensure your formatting is consistent. Dates should all be in the same format (e.g., MM/DD/YYYY). Text fields should be standardized (e.g., use "USA" consistently, not a mix of "USA," "United States," and "US"). The TRIM function is great for removing accidental extra spaces.
3. Handle Missing Values: Blank cells can break your analysis. Decide on a strategy for them. You might be able to fill them in with a logical value (like 0 for sales if there were none), a column average, or you may need to remove the entire row if it’s missing critical information.
4. Remove Duplicates: Duplicate entries can skew your results. Use Excel's "Remove Duplicates" feature (found in the Data tab) to quickly clean these up.

Once your data is clean and structured, you're ready to start forecasting.

Method 1: Using Excel’s Built-in Forecast Functions

For predicting future values based on a historical time series - like sales over time - Excel’s forecast functions are the perfect starting point. They are easy to use and surprisingly powerful.

Let's imagine you have monthly sales data for the past year and you want to predict sales for the next three months.

Your data might look something like this:

Month | Sales Jan-23 | $10,500 Feb-23 | $11,200 Mar-23 | $12,000 ...and so on for 12 months.

FORECAST.LINEAR: Predicting Based on a Straight-Line Trend

The FORECAST.LINEAR function predicts a future value along a linear trend line. In simple terms, it draws the "line of best fit" through your historical data and extends it into the future. It’s best used when your data shows a relatively steady, consistent pattern of growth or decline.

The formula syntax is:

=FORECAST.LINEAR(target_date, known_ys, known_xs)

• target_date: The future date you want to predict the value for.
• known_ys: Your range of historical sales numbers (your dependent variable).
• known_xs: Your range of historical dates (your independent variable).

In our example, to predict sales for next month (Jan-24), you would input its date as the target and select your columns of past sales and dates. Simply drag the formula down for the next few months to quickly generate your forecast.

FORECAST.ETS: For Data with Seasonal Patterns

Businesses rarely grow in a perfectly straight line. You often have seasonal cycles - higher sales around the holidays, a dip in the summer, etc. For this, FORECAST.ETS is a much better choice. It accounts for seasonality and provides a more nuanced prediction.

The syntax is very similar:

=FORECAST.ETS(target_date, known_ys, known_xs, [seasonality], [data_completion], [aggregation])

The primary arguments are the same as FORECAST.LINEAR. The [seasonality] argument is optional but very useful. If you have monthly data for a business with an annual cycle, for example, you could input 12 to tell Excel your seasonal pattern repeats every 12 months. This helps the algorithm produce a more accurate forecast.

Visualizing Your Forecast

After creating your predictions, you should always visualize them. Highlight all your data - both historical and forecasted - and insert a Line Chart. This instantly shows you the historical trend and how your forecast fits into that pattern. You can even use the Forecast Sheet tool on the Data tab to have Excel automatically create the forecast numbers and a chart for you in one step.

Method 2: Regression Analysis with the Analysis ToolPak

What if you want to predict a value based on its relationship with other variables? For example, instead of just forecasting sales over time, maybe you want to predict sales based on your ad spend and website traffic. This is where linear regression comes in.

Regression analysis helps you understand and quantify the relationship between one dependent variable (the thing you want to predict, like sales) and one or more independent variables (the factors that influence it, like ad spend).

Step 1: Enable the Analysis ToolPak

First, you need to make sure this add-in is active.

1. Go to File > Options.
2. Click on Add-ins in the left-hand menu.
3. At the bottom of the window, next to "Manage," make sure Excel Add-ins is selected and click Go...
4. Check the box for "Analysis ToolPak" and click OK. You should now see a "Data Analysis" button in your Data tab.

Step 2: Run the Regression

Let’s say your data is organized into three columns: 'Monthly Sales' (Y), 'Ad Spend' (X1), and 'Website Traffic' (X2).

1. Click the Data Analysis button in the Data tab.
2. Select Regression from the list and click OK.
3. In the "Input Y Range" box, select your 'Monthly Sales' data.
4. In the "Input X Range" box, select both your 'Ad Spend' and 'Website Traffic' data.
5. Check the Labels box if you included the column headers in your selection.
6. Choose where you want the output to appear and click OK.

Excel will generate a summary output with several tables of statistical data.

Step 3: Interpret the Results and Make a Prediction

The regression output can look intimidating, but you only need a few key numbers to get started.

• R Square: This number tells you what percentage of the variation in your dependent variable (Sales) is explained by your independent variables (Ad Spend and Traffic). An R Square of 0.75 means that 75% of your sales fluctuations can be explained by your spending and traffic. A higher number is generally better.
• Coefficients: This is an essential table for making predictions. You will see a coefficient value for your Intercept and each of your variables (Ad Spend, Website Traffic). These are the numbers for your prediction equation.

The equation looks like this: Predicted Sales = Intercept Coefficient + (Ad Spend Coefficient * Ad Spend Amount) + (Website Traffic Coefficient * Traffic Amount)

To predict future sales, simply plug your expected ad spend and website traffic into this formula. For example, if you plan to spend $5,000 on ads and expect 20,000 visitors next month, you can input those values to get a data-backed sales forecast. This is much more robust than a simple time-series forecast because it connects results directly to your key business drivers.

Final Thoughts

With tools like forecast functions and the Analysis ToolPak, Excel becomes an incredibly accessible starting point for predictive analytics. These methods give you the ability to turn rafts of historical data into a coherent forecast, helping you plan better and guide your business with more confidence.

While Excel is great for manual, one-off analyses, this process can become time-consuming when you're managing data across many different platforms. That's a big reason why we built Graphed. We make it easy to connect all your marketing and sales data sources - like Google Analytics, Shopify, Facebook Ads, and Salesforce - in one place. You can use simple, conversational language to have our AI data analyst build real-time forecasting dashboards in seconds, so you get all the predictive power without any of the spreadsheet work. Give Graphed a try to see how simple data analysis can be.