How to Do Predictive Analysis in Excel with AI
Predicting the future in business doesn't require a crystal ball - it just requires your existing data and a few powerful tools hidden inside Excel. By using historical data to forecast future trends, you can make smarter decisions about inventory, marketing spend, sales goals, and more. This article will show you how to perform predictive analysis in Excel using its classic built-in features and how to supercharge your results with AI.
What is Predictive Analysis, Anyway?
Predictive analysis is the practice of using historical data to identify patterns and predict future outcomes. Think of it as making an educated guess based on evidence. Instead of relying on gut feelings, you're using real numbers to forecast what might happen next. It helps you answer questions like:
How much inventory will we need for the holiday season?
Which sales leads are most likely to close this quarter?
What will our revenue look like in the next six months?
Excel has long been the go-to tool for this kind of work. It’s familiar, flexible, and powerful enough to handle sophisticated analysis without requiring you to learn a complex new program. And now, with AI integrations, it's even more capable.
Step 1: Get Your Data Ready for Analysis
Before you can predict anything, your data needs to be clean, organized, and ready for analysis. This is the most important step - if you start with messy data, you’ll get messy predictions. The principle of "garbage in, garbage out" is very real here.
Data Cleaning Checklist:
Remove Duplicates: Go to the Data tab and click Remove Duplicates. This prevents single events from being counted multiple times.
Handle Missing Values: Look for blank cells. You can either delete the entire row, or if it makes sense, fill the blank with a zero or an average value from the column. Be thoughtful about this, as it can skew results.
Standardize Formatting: Ensure dates are all in the same format (e.g., MM/DD/YYYY), numbers are formatted as numbers, and text is consistent (e.g., "USA" vs "United States").
Use Excel Tables: Select your data range and press Ctrl + T (or Cmd + T on Mac) to turn it into an official Excel Table. Tables make your data much easier to manage, sort, and apply formulas to.
Step 2: Using Excel’s Traditional Predictive Tools
Excel comes with several impressive predictive features right out of the box. These tools are perfect for getting started and handling common forecasting needs.
The Forecast Sheet Tool
The easiest way to create a forecast in Excel is by using the Forecast Sheet tool. It's designed for time-series data, which is just a list of dates with corresponding values (like daily sales, monthly website traffic, or quarterly revenue).
Let’s say you have two columns: one for Date and one for Sales.
Select both columns of your data.
Go to the Data tab on the Ribbon.
Click on Forecast Sheet.
A dialog box will pop up, showing you a preview of the forecast.
You can click Options at the bottom to adjust settings like the forecast end date, seasonality, and the confidence interval (the range in which the actual values are likely to fall).
Click Create.
Excel will instantly generate a new sheet with your original data, a beautifully formatted line chart, and three new columns: the forecasted sales figure, and lower and upper confidence bounds. It's a quick and powerful way to visualize future trends based on past performance.
What-If Analysis with Goal Seek and Scenario Manager
While not a pure forecasting tool, "What-If Analysis" helps you predict outcomes based on changing variables. It's perfect for scenario planning.
Goal Seek: Use this when you know the result you want, but aren't sure which input you need to change to get there. For example: "If my profit goal is $50,000, how many units do I need to sell?" You can find it under Data > What-If Analysis > Goal Seek.
Scenario Manager: This lets you create and compare different groups of inputs (scenarios) to see how they affect your final numbers. For instance, you could model "best case," "worst case," and "most likely" sales scenarios by changing variables like ad spend and conversion rate. It's also found under Data > What-If Analysis.
Regression Analysis with the Analysis ToolPak
If you want to understand the relationship between different variables, you can use regression analysis. For example, can you predict sales revenue based on your marketing ad spend? Or can you predict customer churn based on their product usage and support tickets?
To do this, you first need to enable the Analysis ToolPak add-in:
Go to File > Options > Add-ins.
At the bottom, manage Excel Add-ins and click Go.
Check the box next to Analysis ToolPak and click OK.
Now, you'll see a Data Analysis button on your Data tab. Here’s how to run a simple regression:
Click Data > Data Analysis > Regression.
For the "Input Y Range," select your dependent variable (what you want to predict, e.g., Sales).
For the "Input X Range," select your independent variable (what you think influences the outcome, e.g., Ad Spend).
Choose an output location for your results table and click OK.
Excel will produce a summary report filled with statistics. The most important numbers for a beginner are the R Square (which tells you how well your model fits the data) and the coefficients (which are used to build your prediction formula).
Step 3: Leveraging AI in Excel for Deeper Insights
Excel’s traditional tools are great, but AI unlocks a new level of analysis, allowing you to ask questions in plain English and uncover patterns you might have otherwise missed.
Excel’s Built-in “Ideas” (Analyze Data)
Microsoft has integrated AI directly into Excel through a feature called "Ideas" (sometimes labeled "Analyze Data" on the Home tab). This feature automatically analyzes your dataset and suggests interesting trends, charts, and pivot tables.
Simply click anywhere in your data table, go to the Home tab, and click Ideas. A panel will open with dozens of insights. Even better, you can use the text box at the top to ask a question in natural language, like "show total sales by month as a line chart" or "which product category had the highest sales in Q4?".
Using ChatGPT as a Co-worker for Excel
For more complex tasks, you can use an external AI like ChatGPT or Microsoft Copilot as a data analysis assistant. This method allows you to get expert-level help without being an expert yourself. Here's a common workflow:
Provide Context: Explain your goal to the AI. For example: "I am a marketing manager trying to forecast website traffic for the next three months based on historical data from the past two years."
Share Anonymized Data: Copy and paste a small but representative sample of your data. Never paste sensitive customer or financial information. Just give it enough to understand the structure.
Ask for a Solution: Ask a direct question. You could ask it to generate an Excel formula, write a VBA script for an automation task, or suggest an approach for analyzing the data.
Example Prompt for ChatGPT
The AI will not only give you the formula but also break down what each part does, helping you learn in the process. You can then copy this formula directly into your Excel sheet. This method is incredibly useful for getting past tricky syntax or discovering functions you didn't even know existed.
Integrating with AI Add-ins for Excel
The demand for AI in spreadsheets has led to a growing market of third-party Add-ins. You can find these in the Insert > Get Add-ins menu. Many of them provide direct connections to models like GPT-4, allowing you to run AI analysis on your data directly within a cell without having to copy-paste back and forth. They essentially bring the power of ChatGPT right into your workbook.
Final Thoughts
In short, Excel remains an outstanding tool for predictive analysis, blending user-friendly features like the Forecast Sheet with more advanced statistical techniques. By incorporating AI assistants and natural language features, you can move from manually crunching numbers to asking questions and discovering insights faster than ever before.
While these methods are powerful, setting up data, toggling between Excel and your browser, and refreshing analyses manually can still take time. At Graphed, we decided to automate this entire workflow. Our platform connects directly to your live data sources and an analyst so you can create dashboards and get forecasts using plain English commands, like "Forecast our Shopify sales for next quarter." It turns hours of spreadsheet work into a 30-second conversation, giving you the power of predictive analysis without the technical heavy lifting.