AI for Excel: Write Formulas, Clean Data, and Check Results

You've inherited a spreadsheet with a formula nobody can explain, a customer list where the same company is typed five different ways, and a report due Friday. AI can help with all three, but only if you know which tool to use and how to check what it hands back. In this guide you'll learn how to get AI to explain and write Excel formulas, clean messy data, use the Copilot features built into Excel, and verify the results before they end up in front of your manager.

Two ways to use AI with your spreadsheets

You have two main options, and they suit different jobs:

• Copilot inside Excel. It can see your workbook and, in some modes, change it directly.
• A general chat assistant such as ChatGPT, Claude, or Gemini in a browser tab. It can't see your file unless you paste in a formula or sample rows, but it works with any spreadsheet and any plan.

Copilot saves you copying and pasting. The chat assistant is a good fallback when you don't have Copilot, or when you only need help understanding a formula. If you work in Google Sheets, the chat-assistant approach and every prompt in this guide work there too; just say you're using Sheets. If you're still choosing an assistant, see Which AI Assistant Should I Use?.

Get any formula explained in plain English

This is the quickest win. Copy the formula from the formula bar, paste it into Copilot's chat mode or a chat assistant, and ask what it does. Tell it what the columns contain, because a formula that says B:B means nothing without context.

I'm working in Excel. Column A has order dates, column B has region names, and column D has order totals. Explain what this formula does, step by step, in plain English:

=SUMIFS(D:D,B:B,"East",A:A,">="&DATE(2026,1,1))

Then tell me: what happens if a date is typed as text, and what happens if a region has a trailing space, like "East "?

The second part of that prompt matters. Asking "what could go wrong?" often surfaces the real reason a report is off, such as text that looks like a date or a stray space that makes "East" and "East " count as different regions.

Have AI write formulas for you

To get a working formula on the first try, give the AI four things: which program you're using, what each relevant column contains, a few sample rows, and the exact result you want. Spreadsheet programs share many functions but not all of them, so always say which one you're in.

I'm using Excel. Column A is customer name, column C is invoice date, and column E is amount due. Here are three sample rows:

Acme Corp | 2026-09-14 | 1,250
Bright Dental | 2026-10-02 | 480
Acme Corp | 2026-10-20 | 300

In column G, I want a formula that shows "Overdue" if the invoice is more than 30 days old and the amount due is above zero, and leaves the cell blank otherwise. Give me the formula for row 2, explain how it works in two or three sentences, and tell me how to copy it down the column.

If colleagues use an older version of Excel, add "avoid functions that only work in recent versions of Excel" to your prompt. And if the formula returns an error, paste the error and the formula back in and ask what's wrong. That back-and-forth is normal. For more on structuring requests like this, see How to Write a Good AI Prompt.

Clean messy data without hours of find-and-replace

Most spreadsheet pain is messy data: extra spaces, names in different formats, dates stored as text, duplicates, and full names crammed into one cell. You can use AI in two ways here.

• Ask it to do the cleanup. Copilot's edit mode can make changes in your workbook from a plain-language instruction, which can replace a lot of repetitive find-and-replace work.
• Ask it for the steps or formulas. You then apply them yourself. This is slower but you see every change, and it works with any chat assistant.

Either way, be specific about the result you want. A vague request like "clean this up" invites the AI to guess. A clear one, in the style of Office Watch's example "Add a column showing each region's growth over last year and highlight anything under 5%," tells it exactly what done looks like.

In the Customers sheet, column B has company names typed inconsistently (for example "Acme Corp", "ACME Corporation", "acme corp."). Don't change column B. Instead, add a new column C called "Company (clean)" that:
1. Removes extra spaces at the start, end, and middle.
2. Uses consistent capitalization.
3. Groups obvious variations of the same company under one name.

Then list every group of variations you merged so I can review them.

Notice the prompt says "don't change column B." Keeping the original next to the cleaned version lets you compare them, and the list of merged names lets you catch mistakes such as two different companies with similar names being combined.

Copilot in Excel: edit, plan, and chat modes

As of November 2026, Copilot in Excel offers three modes: edit, plan, and chat. It opens in edit mode by default, and in edit mode it can change your workbook directly based on your request.

• Edit: Copilot makes the changes in your workbook. Good for small, clear tasks.
• Plan: Copilot lays out what it intends to do before editing, so you can review the approach first.
• Chat: Copilot answers questions and explains, without changing anything.

If an older tutorial tells you to pick "Agent Mode," that feature is now edit mode. Office Watch describes the button as "Edit with Copilot."

Which mode to pick

Microsoft's own Excel tutorial for screen reader users gives a simple rule for choosing a mode: use Chat only mode when you're exploring, Plan mode when you're building, and Allow editing when the task is small. It also recommends starting in Chat only mode when the workbook matters and you didn't build it, so you can understand it before anything changes.

According to Microsoft's FAQ, Copilot in Excel is available on Windows, Mac, the web, iPad, iPhone, and Android, with all three modes on Windows, Mac, and the web.

In March 2026, Microsoft announced on its Microsoft 365 Insider blog that agent mode can work with modern workbooks (.xlsx, .xlsb, .xlsm) stored locally on Windows and Mac desktop, not just files in the cloud. Insider announcements often start with preview builds, so if this doesn't work for you, save the file to OneDrive or SharePoint, or ask IT whether your version has it yet.

Check whether you have access

What you get depends on your plan. On Microsoft's business pricing page, US list prices per user per month were: Microsoft 365 Business Premium with Copilot at $32.00 paid yearly ($38.40 monthly), Business Standard with Copilot at $23.50 paid yearly ($28.20 monthly), and the Microsoft 365 Copilot Business add-on at $25.20 paid monthly. Prices change often, so confirm on that page.

Also note that starting April 15, 2026, Microsoft began removing free Copilot Chat inside Excel and other Office apps for many unlicensed business users. Reports differ on exactly which organizations are affected, so if Copilot disappeared from your Excel, ask your IT team what your license includes.

Check the results before you trust them

AI can write a formula that looks right and returns the wrong number, or merge two customers who aren't the same. Spreadsheet errors are quiet: nothing turns red, the total just ends up slightly off. Build these checks into every AI-assisted task:

1. Work on a copy. Because Copilot's edit mode changes your workbook directly, duplicate important files or tabs first.
2. Preview bigger changes. Use plan mode in Excel, or ask any AI to describe its steps before it acts.
3. Read the reasoning. Copilot's edit mode shows its work in the Copilot pane as it plans, runs, and checks each step. Skim it before you accept the result.
4. Spot-check by hand. Pick three or four rows and work out the answer yourself.
5. Compare totals and row counts. Totals and the number of rows should match before and after cleanup unless you meant to remove duplicates.
6. Test the awkward cases. Look at rows with blanks, zeros, negative numbers, or text in a number column.

You just added a formula in column G that flags overdue invoices. Before I rely on it, give me a short checklist to verify it: which specific rows should I check by hand, what edge cases could break it (blank dates, zero amounts, text in the amount column), and a simple formula I can put in a spare cell to count how many rows are flagged so I can compare it with my own count.

Asking AI to help check its own work is useful, but it doesn't replace your own spot checks. You know what the numbers should roughly look like; the AI doesn't. If you're just getting started with AI tools generally, How to Start Using AI at Work has a simple first-week plan.

Written with AI assistance and checked against the sources above. AI tools change quickly, so confirm features and prices on each vendor's site.