AI for Excel: formulas, cleaning and analysis you can check

What can AI do in Excel?

AI can write formulas from a plain-language description, explain a formula you inherited, suggest how to clean messy data, propose a pivot table or chart and describe patterns in a range. The tasks it helps with most are the ones where you know what you want but not the exact syntax, such as a conditional sum across several criteria or a lookup that handles missing values.

What it cannot do is know whether your data is right. If a column of dates is stored as text, a region is spelled three ways or a total row sits inside the range, the assistant may produce a formula that is syntactically perfect and returns the wrong number. Spreadsheet skill still matters; AI changes which part of it you spend time on.

Which AI tools work with Excel and Google Sheets?

The main options are Copilot in Excel, Gemini in Google Sheets, and general assistants such as ChatGPT or Claude used alongside the spreadsheet. Which one you use should depend on what your company has approved and what your plan includes.

Microsoft's support page describes Copilot in Excel as having three modes. Edit mode changes the workbook directly and suits multi-step work. Plan mode proposes an approach for you to confirm before anything changes. Chat only mode gives insights without modifying the workbook. The page notes that Copilot might not be included with your Microsoft 365 subscription or may be limited by your organisation's settings. For training, plan or chat mode is a sensible starting point, because a learner sees the proposal before the sheet changes.

Google's help page says Gemini in Sheets can create tables and formulas, generate analysis and insights, build charts and pivot tables and apply conditional formatting. It requires an eligible Google Workspace or Google AI plan, warns that Gemini may suggest inaccurate information, and notes that content Gemini adds to a shared spreadsheet may be visible to people outside your domain.

One change worth knowing: Microsoft's page for the in-cell COPILOT function says that from 14 September 2026 the function is no longer available in Excel. Previously calculated results stay as cached values, but cells that recalculate return a #NAME? error. If your team built sheets around that function, check them. Search results also point to a ChatGPT add-in for Excel and Google Sheets, but we could not open OpenAI's help page when preparing this guide, so we do not describe its plans or price here; check OpenAI's own documentation.

How do you get AI to write an Excel formula you can trust?

Describe the sheet's layout precisely, state the exact criteria and then test the formula against a hand check. Here is a worked example from a fictional events company, Oakridge Events. The sheet has column A for invoice date, B for category, C for region and D for amount, with headers in row 1 and data in rows 2 to 500. The manager wants total income from the Workshops category in July to September 2026.

Vague prompt: Give me a formula for total workshop income last quarter. Typical answer: =SUMIF(B:B,"Workshop",D:D). It looks fine and is wrong twice. The category is spelled Workshops in the sheet, so the criterion matches nothing, and the formula has no date condition at all, so even with the spelling fixed it would sum every workshop invoice ever recorded.

Precise prompt: In Excel, column A has invoice dates (real dates, not text), B has category, D has amount, rows 2 to 500, headers in row 1. Write a formula for the sum of D where B is exactly Workshops and A is from 1 July 2026 up to and including 30 September 2026. Explain each part. Answer: =SUMIFS(D2:D500,B2:B500,"Workshops",A2:A500,">="&DATE(2026,7,1),A2:A500,"<"&DATE(2026,10,1)). Using less than 1 October rather than less than or equal to 30 September avoids missing invoices with a time stamp on the last day.

The check: filter the sheet to Workshops and to July to September, select the visible amounts and compare the sum shown in the status bar with the formula result. If they differ, look for dates stored as text, trailing spaces in the category or rows outside the range. A formula is only right when it matches a check that does not depend on the AI.

Can AI explain a formula someone else wrote?

Yes, and this is one of the safest and most useful things to practise. Paste the formula and the relevant column headers and ask for a plain-language explanation of each part and of what happens in edge cases. You keep the original formula, so nothing is lost if the explanation is imperfect.

Example: =IFERROR(VLOOKUP(F2,Rates!A:B,2,FALSE),0). A good explanation says it looks up the code in F2 in the first column of the Rates sheet, returns the matching value from the second column, requires an exact match and returns 0 if anything goes wrong. The important follow-up question is the last part: does returning 0 hide a missing rate? Often it does, which means an unrecognised code silently becomes a zero charge. Ask the tool for a version that returns a visible flag instead, such as the text Missing rate, and decide which behaviour you actually want.

How can AI help clean spreadsheet data?

AI is good at suggesting the steps to clean data and the formulas to do it, such as trimming spaces, splitting a full name into two columns, standardising region names or converting text dates. Ask it to propose the steps first, then apply them to a copy of the sheet, not the original.

After cleaning, check counts. The number of rows should be the same as before unless you meant to remove some. The number of distinct values in a cleaned column should make sense: if you standardised regions, there should be the expected handful, not dozens. Spot-check a few rows that were messy before to confirm they are now right.

Can you put a spreadsheet into ChatGPT?

You can paste data or upload a file to many assistants, but whether you should depends on what is in the sheet and what your company has approved. A spreadsheet of customer names, salaries or client financials is usually not something to paste into an unapproved tool. Often you do not need the real data at all: describe the columns and paste five rows of made-up values in the same format, get the formula, then apply it to the real sheet yourself.

Built-in assistants such as Copilot in Excel work on the file in place, which can be more convenient, but the same question applies: is this tool approved for this data? Check your company's AI policy before using real figures.

Will AI replace Excel skills?

No. AI changes which Excel skills matter most, but it does not remove the need for them. You need to know enough to describe the layout precisely, recognise when a formula does not fit the data, and check a result by an independent route. Someone who cannot tell SUMIF from SUMIFS can still get the right formula from an assistant, but cannot tell when they have got the wrong one.

The skills worth training are the ones in this guide: describing a sheet precisely, testing a formula against a hand check, asking about edge cases and keeping an untouched copy of the original data.

When should you not rely on AI in a spreadsheet?

Do not rely on AI-generated formulas for financial reporting, payroll, tax, pricing that goes to customers or any figure someone else will sign off, unless a competent person has checked the formula and tested it. Use AI to draft and explain, then review it like any other formula. For very large or business-critical models, a person with spreadsheet modelling experience is still the right reviewer.

AI also adds little when the problem is the data rather than the formula. If nobody agrees what counts as a workshop, or invoices are entered inconsistently, fix the process first.

Sources and scope

Microsoft Support, Get started with Copilot in Excel, accessed 16 October 2026 ↗

Microsoft Support, COPILOT function, accessed 16 October 2026 ↗

Google Docs Editors Help, Collaborate with Gemini in Google Sheets, accessed 16 October 2026 ↗