How to Use AI in Excel to Analyze Data (Copilot & ChatGPT)

A sales file with 3,000 rows, a household budget you’ve kept for two years, sign-ups for a sports club, an export of survey responses: we all have Excel spreadsheets full of data we barely use. Writing formulas, building a PivotTable or spotting a trend takes time and a bit of know-how. Using AI in Excel to analyze your data changes the game: you ask your question in plain English, and the AI suggests a formula, a chart or a first read of the numbers.

But a spreadsheet is data, and often sensitive data: salaries, customers, health, personal finances. In this guide, I’ll explain how to analyze Excel data with AI, which tools to use, the method I follow, ready-to-copy prompts and, above all, the precautions you need to protect your information and check the results.

Contents

1. First things first: keeping your data private
2. Which tools to use
3. The step-by-step method
4. Prompts for common situations
5. A complete example: Leah and her shop's sales
6. Mistakes to avoid and limitations
7. Frequently asked questions
8. The bottom line

First things first: keeping your data private

I’m starting with this because it’s the most important point. When you ask an AI to analyze a workbook, its contents are processed by that service. And spreadsheets very often contain information you wouldn’t email to a stranger.

Here are the rules I stick to:

• Personal data: names, addresses, phone numbers, emails, dates of birth, Social Security numbers. I delete them or replace them with IDs (Customer 001, Customer 002).
• Sensitive data: health, salaries, family situation, opinions. I don’t send them to a personal tool, period.
• Company data: revenue, margins, customer lists. I only use the tool my employer approves, often a business version, and I follow internal policy.
• Other people’s data: if the file is about members, students or patients, I ask myself whether those people agreed to this kind of processing. When in doubt, I work on an anonymized copy.

A simple trick: make a working copy of the workbook, delete the columns you don’t need for the analysis, replace names with codes, and only give the AI that copy. You can also just describe the table’s structure (column names, data types) and ask for a formula, without sending a single real row.

Here's the structure of my table, without the data: column A = order date, B = region, C = product category, D = quantity, E = unit price. Write the Excel formula that calculates total revenue by region, and explain it line by line.

To dig deeper into these settings, my guide to ChatGPT memory explains what the assistant keeps from one conversation to the next.

Which tools to use

Copilot in Excel

Copilot, Microsoft’s assistant, is included with eligible Microsoft 365 and Copilot subscriptions. It can help you analyze, understand and work with your data right in Excel. For individuals, Microsoft 365 Personal is listed at $9.99 a month or $99.99 a year. For businesses, Microsoft 365 Copilot is built into Excel, Word, PowerPoint, Outlook and Teams, among others, and listed at $30 per user per month, billed yearly, on top of an eligible Microsoft 365 license. Availability depends on your subscription and account: check in your own Excel.

The main advantage, in my view: the data stays in your workbook, and the assistant works on your cells. For a fuller overview of the assistant, read my Microsoft Copilot guide.

ChatGPT for Excel

OpenAI launched ChatGPT for Excel in beta on March 5, 2026, then made it available on all plans on May 5, 2026, as an Excel add-in. According to OpenAI, it lets you build and edit models, work from cells and formulas, run scenarios and produce or analyze results. On personal plans, extra credits can be used once the included quota runs out; on Business and Enterprise plans, usage in Excel is billed per token or per credit depending on the model used. So I recommend keeping an eye on your usage. To compare plans, see my free ChatGPT guide.

A regular chat assistant

Without an add-in, you can also upload a file or paste part of a table into ChatGPT, Claude or Gemini, or simply describe your columns to get a formula. It’s the most flexible option, but also the one where you need to be most careful about what you share.

Tool	Where it works	Strength	Watch out for
Copilot in Excel	Inside your workbook	Built-in integration	Availability depends on subscription and account
ChatGPT for Excel	Add-in inside Excel	Models, scenarios, formulas	Quota and credits depending on plan
Chat assistant	Outside Excel	Flexibility, explanations	Only share anonymized data

The step-by-step method

Here’s the method I use, whatever the tool.

1. Prepare and clean an anonymized copy.
2. Describe the context and the question.
3. Explore the data.
4. Get formulas and summary tables.
5. Visualize.
6. Check, then draw conclusions.

Step 1: Prepare the data

AI analyzes a clean table better: one header row, one value per cell, no merged cells, dates in the right format. Start there.

Here are the headers and 10 sample rows (anonymized) from my table. Spot any data quality issues: possible duplicates, inconsistent date formats, empty cells, outliers, mixed units. Suggest a step-by-step cleanup method in Excel.

Step 2: Set the context

As with any prompt, context makes all the difference. Say what the data represents and what decision you’re trying to make. My guide on how to write a good prompt explains this approach in detail.

This table contains 24 months of sales from a small online store. I want to know which products are driving revenue growth and which ones are falling off, so I can decide on my orders for next quarter. Start by telling me which analyses would be useful, without calculating anything yet.

Step 3: Explore

Ask for a first read, and insist that every claim be based on a calculation you can redo.

Do a first exploration of this data: totals, averages, month-over-month changes, main gaps. For each finding, give the formula or method used and the cell range involved, so I can check it.

Step 4: Formulas and summary tables

This is where AI saves you the most time, especially if you’re not comfortable with advanced functions.

Write a formula that returns, for each region in column B, the total of the amounts in column F for the year 2025 only. Explain each argument, and tell me what error I might get if some cells contain text.

My XLOOKUP formula returns #N/A on some rows. Here's the formula and an example of a lookup value. Explain the possible causes and how to fix them.

Walk me through, step by step, how to create a PivotTable that shows sales by category and by quarter, with the percentage of the total.

Step 5: Visualize

What type of chart works best to show monthly sales trends for three categories? Explain why, then give me the steps to create it in Excel and the reading mistakes to avoid (axis that doesn't start at zero, too many series).

If these charts then need to be presented in a meeting, my guide on making a PowerPoint presentation with AI picks up from there.

Step 6: Check and conclude

Review your analysis critically. Which conclusions are solid, and which are shaky (too little data, correlation without causation, too short a period)? What should I check before making a decision?

This simple question heads off a lot of hasty conclusions.

Prompts for common situations

Personal budget

Here are the spending categories from my budget over 12 months (amounts only, no bank transaction descriptions). Identify the categories growing the fastest, calculate the monthly average per category and suggest three realistic ways to save money.

I always remove bank transaction descriptions, which reveal a lot about a person.

Sales tracking

Using this anonymized table (customer code, date, amount, product), calculate the average order value per month, the number of repeat customers and the products most often bought together. Give me the formulas you used.

Survey or questionnaire

Here are the anonymized responses to a satisfaction survey (ratings from 1 to 5 and open-ended comments). Calculate the average per question, identify the lowest-rated questions and group the open-ended comments into 5 themes, with the number of responses per theme.

Nonprofit or club

Here's the number of members per activity and per season over 4 years (no names). Show the trends, flag the activities that are declining and suggest a simple chart for the annual members' meeting.

Scenarios

Build a small forecasting model: monthly revenue = number of sales × average price. Create three scenarios (conservative, base case, optimistic) over 12 months, with the assumptions in separate cells so I can change them.

Understanding an inherited workbook

Here's a workbook I didn't create. Explain what each tab is for, what the main formulas do and where the error risks are (hard-coded references, incomplete ranges).

A complete example: Leah and her shop’s sales

To make the method concrete, let’s take an illustrative case. Leah runs a small shop selling handmade goods and exports her sales to Excel every month. She wants to understand why some months are better than others.

She starts by making a copy of the file, in which she deletes her customers’ names, emails and shipping addresses, replacing them with a simple code. She keeps only the date, product category, quantity and amount.

Then she describes her goal:

This table contains my sales over 18 months. I want to understand the seasonality of my business and which categories drive my best months. First suggest a 5-step analysis plan, then give me the formulas and the PivotTable I need for each step.

The AI suggests calculating sales by month and by category, comparing each month to the average, then building a line chart. Leah applies the formulas, checks one total by hand, and spots that a negative refund row was skewing an entire month. She points it out:

Some rows are refunds with a negative amount. Adjust the formulas to handle them separately, and show me the impact on the results.

That’s exactly the kind of back-and-forth I recommend: the AI suggests, you check, you correct, and the analysis gets sharper with each exchange.

Mistakes to avoid and limitations

Sending the whole file without thinking

This is the most common mistake. A working spreadsheet often contains hidden tabs, contact info columns or comments. Anonymize, remove what isn’t needed, and follow your employer’s rules.

Trusting the numbers without recalculating

AI can pick the wrong range, skip rows, mix up units (dollars and thousands of dollars, hours and minutes) or count a duplicate twice. Redo at least one key calculation yourself, and compare a total with Excel’s status bar.

Confusing correlation with causation

Two lines rising together don’t prove that one causes the other. Be wary of overly confident statistical conclusions, especially on small samples.

Pasting formulas you don’t understand

A formula you don’t understand is a formula you won’t be able to fix. Always ask for the explanation, and test it on a few rows where you already know the result.

Forgetting about age and accounts

ChatGPT is available from age 13 (or the local minimum age), with parental permission under 18. For Microsoft accounts in the US, minors under 13 need a parent to manage the account.

Verification checklist

• I worked on an anonymized copy
• No sensitive or third-party data was shared without permission
• I checked the ranges in every formula
• The units are consistent
• Duplicates have been handled
• I recalculated at least one key result by hand
• My conclusions separate findings from assumptions

Frequently asked questions

Is Copilot included in Excel?

Copilot is included with eligible Microsoft 365 and Copilot subscriptions, including Microsoft 365 Personal ($9.99 a month or $99.99 a year). Exact availability depends on your subscription and account: check in your Excel.

What is ChatGPT for Excel?

It’s an Excel add-in from OpenAI, available on all plans since May 5, 2026. It lets you work with cells and formulas, build models and run scenarios. Usage may consume credits depending on your plan.

Is my data safe if I send it to an AI?

It’s processed by the service you use. The best protection is still not to send anything that isn’t necessary: anonymize, remove sensitive data and, at work, only use the approved tool.

Can AI write Excel formulas?

Yes, and it’s one of its most useful applications. Describe your columns and the result you want, then ask for an explanation of the formula. Always test it on a few rows you know.

Can I analyze data without sending it?

Yes, partly: describe only the table’s structure (column names and types) and ask for formulas or a method. Then you apply them yourself in Excel.

Does AI replace a data analyst?

No. It speeds up the work, but checking formulas, ranges, units, duplicates and conclusions is still essential.

The bottom line

Using AI in Excel is first and foremost about saving time on formulas, cleanup and a first read of the numbers. Copilot in Excel and ChatGPT for Excel work right in your workbook; a regular assistant is still handy for explanations. But the golden rule doesn’t change: protect your data before you share it, and check every result before you decide. Used well, AI doesn’t let you skip the thinking; it gives you a better foundation to think from.

And if you work on the same spreadsheets every week, learn how to go further with AI automation.