Using AI with Excel: cleaning data, formulas and reports

If you work in spreadsheets, you probably spend more time preparing data than analyzing it: fixing dates that came in as text, splitting names, hunting duplicates, writing the same lookup formula for the hundredth time. Those are the jobs AI handles well, as long as you set it up properly and check what it does.

This guide covers the options available in September 2026, then walks through cleaning, formulas and reports with prompts you can copy.

Pick your assistant

There are two ways to get an AI assistant inside Excel itself, plus a fallback that works anywhere.

Copilot in Excel (Microsoft). Built in if your Microsoft 365 plan includes Copilot. Open it with the Copilot button on the Home tab (in newer builds there’s also a Copilot icon in the lower-right corner of the window). The pane has modes: a chat-only mode that answers questions without touching your sheet, and an editing mode that changes the workbook for you. Microsoft has renamed the editing mode several times (Agent Mode, then Edit with Copilot, and now Allow editing), so pick whichever option in the mode menu lets Copilot make changes. Copilot needs the file saved to OneDrive or SharePoint with AutoSave on, in a modern format (.xlsx, .xlsb or .xlsm).

One recent change: Microsoft retired the =COPILOT() worksheet function on September 14, 2026. If an older tutorial tells you to type it into a cell, use the Copilot pane instead.

Claude for Excel (Anthropic). An add-in available on Claude’s Pro, Max, Team and Enterprise plans. Install it from the Microsoft Marketplace listing called Claude for Microsoft 365 (click Get it now), then open it in Excel from Home → Add-ins on Windows or Tools → Add-ins on Mac and sign in with your Claude account. It works in Excel on the web, Microsoft 365 Excel on Windows, and recent Excel for Mac. It doesn’t run in Excel 2016 or 2019, or on iPad or Android. Its answers cite the cells they came from, which you can click to jump to, and it warns you before overwriting existing data.

No add-in? Use a chat. Upload a copy of the file to Claude or ChatGPT, ask for the cleanup, and ask it to give you back a cleaned file. It’s slower to iterate on, but fine for one-off jobs.

The prompts below work in any of them. Where a step only works in one tool, it says so.

Before you start: three minutes of setup

AI assistants do much better with tidy input. Before you ask for anything:

1. Work on a copy. Save a duplicate of the workbook. You’re about to let software edit it.
2. One header row, no merged cells. Each column gets one clear name in row 1: Order Date, not a two-row header with a merged “Q3” above it.
3. Turn the range into a table. Click inside the data and press Ctrl+T (Cmd+T on Mac), then name the table on the Table Design tab, something like Sales. Tables give the assistant clear boundaries and let formulas refer to columns by name.
4. Write down one known number. Note the row count and one total you trust, such as the sum of the Amount column. You’ll use these to check the AI’s work.

Part 1: Cleaning messy data

Start by asking what’s wrong before asking it to fix anything:

“Look at the Sales table. List every data quality problem you can find: inconsistent formats, blanks, duplicates, text stored as numbers, extra spaces. Don’t change anything yet. For each problem, tell me how many rows are affected.”

This gives you a to-do list and a baseline. Then fix one problem at a time, with the assistant in its editing mode:

• “In the Customer column, remove leading and trailing spaces and make the capitalization consistent (Title Case). Put the cleaned values in a new column next to the original.”
• “The Order Date column mixes formats like 03/09/2026, 2026-09-03 and ‘Sept 3’. Convert them all to real Excel dates in a new column. They’re US format, month first. List any you couldn’t convert.”
• “Split the Full Name column into First Name and Last Name. Leave rows with more than two names for me to review and highlight them.”
• “Find duplicate orders, meaning the same Order ID. Don’t delete them. Add a column that says ‘Duplicate’ on every copy after the first.”

Two habits make this safe. First, new columns, not overwrites, until you’ve checked the result. Second, flag, don’t delete. Deleting rows is the one step that’s hard to spot afterwards.

When a cleanup is done, check it against your known numbers. The row count should match (or differ by exactly the duplicates you flagged), and the Amount total should be unchanged unless you expected it to change. A formula like =SUM(Sales[Amount]) makes that a two-second check.

Part 2: Formulas you don’t have to memorize

Formulas are where AI saves the most frustration, because you describe the logic and it handles the syntax. The trick is to describe your data precisely.

“In the Orders sheet, add a Region column. Look up each Customer ID in the Customers table and return that customer’s region. If the ID isn’t found, show ‘Not found’.”

A good answer is a single formula filled down the column, something like:

=XLOOKUP([@[Customer ID]], Customers[Customer ID], Customers[Region], "Not found")

More requests worth trying:

• “Total the Amount column for the East region for September 2026 only.” You’re after a SUMIFS that combines a region condition with a date range.
• “Add a column that says ‘Late’ if Delivered Date is more than 5 days after Order Date, otherwise ‘On time’. Leave it blank if there’s no Delivered Date yet.”
• “Cell F12 shows #N/A. Explain why in plain English and fix it.” Claude for Excel can trace an error back to the cell that caused it.

Always ask for an explanation alongside the formula: “Explain this formula in one sentence per part.” You’ll learn the patterns, and you’ll catch the times the AI misunderstood what you meant. Then test it on two or three rows where you already know the answer.

If a formula references another sheet or a table, check the names match exactly. A wrong reference won’t always cause an error. It can quietly return the wrong column.

Part 3: Reports and summaries

Once the data is clean, reports take minutes instead of an afternoon.

“Create a new sheet called Summary. Add a PivotTable of total Amount by Region and Month, and a clustered column chart of the same data. Sort regions by total, largest first.”

Then ask for the written part:

“Write three short bullet points for my manager describing what changed between August and September. Use only numbers from the Summary sheet and tell me which cell each number came from.”

That last sentence matters most. It makes the assistant ground every claim in a cell you can check, and Claude for Excel shows those cell references as clickable citations. Read the bullets, click through, and correct anything that doesn’t match before it goes in an email.

For a report you rebuild every week or month, write the steps down once as a single prompt (“Refresh the Summary sheet from the new data, rebuild the pivot, update the chart, redo the three bullets”) and save it somewhere you’ll find it. Claude for Excel also has a settings field for Instructions that apply to every conversation, which is a good home for preferences like number formats and how you like headers styled.

What to watch out for

• Check totals against something you trust. AI assistants are fast and fluent, and occasionally wrong with total confidence.
• Be careful with files from outside. Anthropic warns that spreadsheets from external sources can contain hidden instructions aimed at the AI. Stick to files you trust, and read any confirmation it asks you for.
• Know the gaps. Claude for Excel doesn’t do data tables or macros and VBA. For those, ask for step-by-step instructions instead.
• Follow your company’s data rules. Some organizations restrict which AI tools can see customer or financial data. Check before you open a sensitive workbook.

What the vendors’ help pages say

These facts come from Microsoft and Anthropic documentation as of November 2026.

• Microsoft says that starting September 14, 2026, the COPILOT function is no longer available in Microsoft Excel.
• Microsoft says a cell that contains the COPILOT function returns a #NAME? error when it recalculates.
• Microsoft says to use Copilot in Excel and describe what you want in your own words.
• Anthropic says Claude for Excel is generally available to Pro, Max, Team, and Enterprise plans.
• Anthropic says Claude for Excel answers questions about your workbook with cell-level citations.
• Anthropic lists macros and VBA operations as unsupported capabilities of Claude for Excel.
• Anthropic says the Claude for Excel add-in does not run on these Excel versions: Excel 2016 and 2019 perpetual or volume license.
• Anthropic says to only use Claude for Excel with trusted spreadsheets.
• The SpreadsheetLLM paper on arXiv says spreadsheets have extensive two-dimensional grids, flexible layouts, and varied formatting options that pose significant challenges for large language models.

Keep going

If most of your spreadsheets start life as email attachments, pair this with how to automate Gmail with Claude. If you’d like a small tool that does a whole spreadsheet routine for you outside Excel, Claude Code for non-programmers walks through building one.

Excel and spreadsheet automations are a hands-on session in our two-day class, built on a provided workbook with Copilot and Claude Code.

Sources

• Microsoft Support, COPILOT function: “Starting September 14, 2026, the COPILOT function is no longer available in Microsoft Excel”
• Microsoft Support, COPILOT function: “returns a #NAME? error”
• Microsoft Support, COPILOT function: “Use Copilot in Excel and describe what you want in your own words”
• Anthropic Help Center, Use Claude for Excel: “generally available to Pro, Max, Team, and Enterprise plans”
• Anthropic Help Center, Use Claude for Excel: “answers with cell-level citations”
• Anthropic Help Center, Use Claude for Excel: “Macros and VBA operations”
• Anthropic Help Center, Use Claude for Excel: “The add-in does not run on these Excel versions. Excel 2016 and 2019 perpetual or volume license”
• Anthropic Help Center, Use Claude for Excel: “Only use Claude for Excel with trusted spreadsheets”
• arXiv, SpreadsheetLLM: Encoding Spreadsheets for Large Language Models: “Spreadsheets are characterized by their extensive two-dimensional grids, flexible layouts, and varied formatting options, which pose significant challenges for large language models”