How to Do Predictive Analysis in Excel with ChatGPT
You don't need a data science degree or expensive software to start making smarter business forecasts. If you have Microsoft Excel and access to ChatGPT, you can build a simple yet powerful predictive model right now. This article will show you exactly how to combine these two tools to analyze past performance and predict future outcomes, one step at a time.
What Exactly is Predictive Analysis?
At its core, predictive analysis is simply the practice of using historical data to make educated guesses about the future. You're looking for patterns and relationships in what has already happened to forecast what is likely to happen next.
Businesses use it to answer questions like:
How many units are we likely to sell next quarter based on our ad spend?
Which customers are most likely to churn in the next 30 days?
What will our website traffic be next month if we continue our current marketing efforts?
While data scientists use complex tools like Python or R for this, Excel is a surprisingly capable and accessible starting point. Nearly everyone already has it, and its built-in features are more than enough to handle basic predictive modeling. When you pair Excel with an AI assistant like ChatGPT, the process becomes even easier.
How ChatGPT Fits Into Your Excel Workflow
Let's be clear: ChatGPT won't directly access your Excel file and perform the analysis for you. Instead, you should think of it as your personal data analysis tutor, available 24/7.
Here’s how it helps:
It provides the roadmap: You can describe your data and your goal, and ChatGPT will suggest the right type of model and outline the steps to take in Excel.
It writes the formulas: Stuck on how to structure a specific calculation? Just ask, and it will generate the Excel formula you need.
It interprets the results: After you run an analysis, Excel spits out a table of statistics that can look like gobbledygook. ChatGPT can translate those numbers into plain English, telling you what actually matters.
Now, let's walk through an example. We'll build a model to predict a company's monthly sales based on its advertising spend and website traffic.
Step 1: Get Your Data Organized in Excel
The success of any predictive model hinges entirely on the quality and structure of your data. This is the most important step, and there’s no way around it. Remember the old saying: "garbage in, garbage out."
First, gather your historical data. For our example, we need monthly sales, total digital ad spend, and website sessions for the last 24 months. Once you have it, structure it in a clean table in Excel. Each column represents a variable, and each row represents a time period (in our case, a month).
Your table should look something like this:
Before you proceed, do a quick data health check:
Are there any empty cells? For this simple model, it’s best if every cell has a value. If a row has missing data, you might want to exclude it from your analysis.
Are there duplicates? Ensure you don’t have the same month’s data entered twice.
Are there obvious outliers? Did you have one month where ad spend was $1,000,000 while it was usually around $5,000? Huge outliers can skew your results. For now, just be aware of them.
Step 2: Ask ChatGPT to Build a Plan
With your data prepped, it's time to bring in your AI assistant. You don't need to know the name of a specific statistical model, you just need to describe what you have and what you want to achieve.
Head to ChatGPT and give it a prompt like this:
"I have an Excel sheet with 24 months of historical data. The columns are 'Total Ad Spend', 'Website Sessions', and 'Total Sales'. I want to use the ad spend and website session data to predict future 'Total Sales'. What is the best statistical method for this in Excel, and can you please give me step-by-step instructions to do it?"
ChatGPT will recognize this as a classic prediction problem and will likely recommend using a Multiple Linear Regression analysis. It will then provide a detailed plan, which will almost certainly involve a built-in Excel feature called the Data Analysis ToolPak.
Step 3: Build the Model Using the Data Analysis ToolPak
The Analysis ToolPak is a free Excel add-in that unlocks advanced statistical functions. If you've never used it, you'll need to enable it first. It only takes a minute.
How to Enable the Analysis ToolPak:
Go to File > Options.
Click on Add-ins in the left-hand menu.
Near the bottom, where it says "Manage," make sure Excel Add-ins is selected and click Go…
In the new pop-up box, check the box next to Analysis ToolPak and click OK.
You'll now see a "Data Analysis" button on the far right of your Data tab in Excel.
Now, follow the instructions from ChatGPT to run the regression:
Click the Data Analysis button on the Data tab.
Scroll down and select Regression from the list, then click OK.
A configuration window will appear. This is where you tell Excel what's what:
Input Y Range: This is the outcome you want to predict. Highlight your Total Sales data, including the header.
Input X Range: These are the variables you're using to make the prediction. Highlight both the Ad Spend and Website Sessions columns together, including the headers.
Labels: Check this box because you included the column headers in your selection. This makes the output much easier to read.
Output Range: Choose a spot in your sheet where you want Excel to place the results. Just select a single empty cell.
Click OK.
Step 4: Have ChatGPT Interpret the Results
Excel will instantly generate a 'SUMMARY OUTPUT' table with a lot of statistical information. For someone new to analysis, this can feel completely overwhelming. But don't worry - this is where ChatGPT shines.
Simply copy the key parts of that output table and paste them back into ChatGPT with a prompt like this:
"I ran the regression analysis in Excel and got this output. Can you explain what the most important parts mean in simple terms, and tell me if my model is decent?"
ChatGPT will break it all down for you. Here are the key numbers it will focus on:
R Square: This tells you how much of the variation in your sales can be explained by your input variables (ad spend and traffic). An R Square of 0.81 means that 81% of the changes in sales can be explained by changes in ad spend and website sessions. That’s a pretty good model!
P-value: This checks if a variable is statistically significant. A rule of thumb is that if the P-value is less than 0.05, the variable has a meaningful impact on your outcome. In our example output, both "Ad Spend" and "Website Sessions" likely have very low P-values, meaning they are both important predictors.
Coefficients: These are the magic numbers for your predictive formula. They tell you exactly how much 'Total Sales' is expected to change for every one-unit increase in an input variable, assuming the other variables stay constant.
Step 5: Use the Model to Make Predictions
Now for the fun part. You are going to use the coefficients from the output to create a predictive formula.
The formula structure for a multiple linear regression is:Predicted Value = Intercept + (Coefficient_A * Variable_A) + (Coefficient_B * Variable_B)
Find the coefficients for "Intercept," "Ad Spend," and "Website Sessions" in your summary output. Let’s say they are:
Intercept: 15,250
Ad Spend Coefficient: 5.75
Website Sessions Coefficient: 2.40
Your prediction formula is:
Predicted Sales = 15250 + (5.75 * Ad_Spend) + (2.40 * Website_Sessions)
Now you can build a simple calculator in your Excel sheet to use this model. Set up some cells where you can input hypothetical future numbers for ad spend and website traffic, then use your formula to see the predicted sales.
This allows you to play with scenarios. What happens to sales if you boost ad spend to $10,000 next month? What if traffic dips to 20,000 sessions? Your model gives you a data-backed answer.
Final Thoughts
Combining the number-crunching capability of Excel with the clarifying power of ChatGPT is a fantastic way for anyone to dip their toes into predictive analytics. It helps you move from relying on gut feelings to making forecasts grounded in your own historical data, giving you a better handle on your business's future.
While this process is incredibly empowering, you can see how downloading CSVs, cleaning data, and manually running analyses in spreadsheets can become time-consuming, especially when your data lives across many different platforms. Here at Graphed, we've built a way to streamline all of this. Within minutes, we connect to all your key data sources — like Google Analytics, Shopify, Facebook Ads, and Salesforce — and our AI data analyst allows you to build real-time dashboards and get answers just by asking questions in plain English. This eliminates the manual legwork so you can get straight to the insights, turning hours of analysis into a 30-second conversation inside Graphed.