Spreadsheets, without the formula pain.

The best way to use AI for Excel or Google Sheets is to show it your data, not just your question. Give it the layout, a few sample rows and the answer you expect for them. Then test the formula on rows where you already know the result, before you copy it down ten thousand rows.

Both apps now have assistants that can edit the workbook for you. They still misread your definitions sometimes, and a spreadsheet never warns you when a total is quietly off. This guide covers what each tool does in 2026, prompts that work, and the checks that catch bad numbers.

01What AI can do in Excel and Google Sheets now

Both built-in assistants now act on the workbook itself, instead of only answering in a side panel.

Microsoft made this agentic Copilot the default experience in April 2026. Google added whole-spreadsheet building the same month. If you do not see the Copilot or Gemini button, your plan or your organization’s settings may not include it.

You do not need a built-in assistant, though. Any chat assistant can write formulas if you paste the headers and a few rows, and most can analyze an uploaded file by running code. The method below works in all of them.

02AI for Excel formulas: show the data, not just the question

A vague request gets a plausible formula for a sheet the AI imagined. It guesses your columns, your date format and what “this month” means. Four details remove most of the guessing:

• Your app and version. Newer functions such as XLOOKUP are missing from Excel 2016 and 2019.

• The exact layout. Sheet names, column letters, the header row and where the data ends.

• Three to five sample rows, plus the answer you expect. Swap in fake names if the data is private.

• Your definitions. What counts as February, whether refunds count, what a blank cell means.

Here is a worked example with an eight-row order sheet.

I use Excel for Microsoft 365 with US settings.
Sheet "Orders" has headers in row 1 and data in A2:E9.
Columns: A Order ID, B Date, C Customer, D Region, E Amount.
Sample rows:
1003 | 02/02/2026 | Acme Ltd | West | 300
1005 | 02/28/2026 | birch & co | East | 150
1006 | 03/03/2026 | Dune LLC | West | 700
F2 holds a region name. Write one formula for G2 that totals Amount for that region in February 2026 only.
Explain each part, then tell me which order IDs it should include when F2 is West.

A good answer looks like this:

=SUMIFS($E$2:$E$9,$D$2:$D$9,F2,$B$2:$B$9,">="&DATE(2026,2,1),$B$2:$B$9,"<"&DATE(2026,3,1))

Two details make it sturdy. The $ signs keep the ranges fixed when you copy the formula down. And “before March 1” catches all of February 28, even dates that carry a time, which “on or before February 28” would miss.

Now test it where you know the answer. Only order 1003 is a February sale in the West, so G2 should say 300. On the sample sheet, the formula returns:

The formula is right, and North is still wrong. Order 1004’s date was imported as text, so SUMIFS skipped it. No error, no warning, just a smaller number. That is why the next two sections matter more than the prompt.

03Clean up messy data without breaking it

AI is good at spotting mess and writing the fix. The danger is a fix that changes data you meant to keep. Work on a copy, put results in new columns, and keep the original until you have checked.

Here are the headers and 20 sample rows from my sheet.
Before changing anything, list every data problem you see: extra spaces, inconsistent names, dates or numbers stored as text, duplicates, blanks and values in the wrong column.
For each problem, give a formula that flags it in a new column. Do not change the original columns.

Here is how the fixes work on the sample sheet:

1. 01

   Find dates and numbers stored as text

   In a column that should hold only dates or numbers, =COUNTA(B2:B9)-COUNT(B2:B9) counts the entries that are text. Here it returns 1: order 1004. =DATEVALUE(B5) turns a text date like 2026-02-11 into a real one. Paste the result over B5 as a value, format it as a date, and North’s February total becomes 980.

2. 02

   Trim spaces before you match

   “Acme Ltd “ with a trailing space does not match “Acme Ltd”. In column H, =XLOOKUP(C2,Customers!$A$2:$A$5,Customers!$B$2:$B$5,"Not found") returns “Not found” for that row, and =COUNTIF(H2:H9,"Not found") returns 1. Change the first argument to TRIM(C2) and the count drops to 0.

3. 03

   Match names to a master list, not to PROPER

   =PROPER(TRIM(C2)) fixes “acme ltd”, but it also turns “Dune LLC” into “Dune Llc”. Look each name up in your customer list instead, and return the official spelling:

   =XLOOKUP(TRIM(C2),Customers!$A$2:$A$5,Customers!$A$2:$A$5,"Check")

   XLOOKUP ignores capital letters, so “birch & co” comes back as “Birch & Co”. Any name that is not on the list shows “Check”.

4. 04

   Flag duplicates, then decide yourself

   =COUNTIF($A$2:$A$9,A2)>1 shows TRUE on both rows for order 1006, a double import worth 700. Choose which row to delete by hand. Deleting duplicates by formula is how real orders go missing.

04Ask for analysis, then ask how it got the number

Both assistants can answer “which region grew fastest?” with a chart or a PivotTable. The chart is the easy part. What you need is the method: which rows went in, which filters applied and how each total was calculated.

Using the Orders table, show monthly sales by region for January to March 2026 as a PivotTable and a line chart.
Then tell me how many rows you used, which rows you excluded and why, and the formula or steps behind each total.
Flag anything that looks like a data problem instead of fixing it silently.

If the row count it used does not match yours, stop there. Put the formula behind any number you plan to share next to the chart, so a colleague can check it too.

If the same cleanup comes back every week, turn it into a script instead of a monthly chat. Our guide to automating repetitive tasks shows how. And if your data lives in a database, the same show-the-data method works for writing SQL with AI.

05How to check AI spreadsheet results

AI formulas fail the way other AI answers do: confidently, with no warning. Our guide to why AI makes things up explains the cause. The fix here is cheap, because a spreadsheet can check itself.

One trap is worth seeing. On the sample sheet, the three region totals add up to 4,700, exactly matching =SUM(E2:E9). Yet 700 of that is the duplicate order. Reconciling catches rows that fell through the cracks. It does not catch a row counted twice.

Keep customer data out of consumer chat apps unless your company allows it. Our AI privacy guide covers the settings that matter.

06FAQ

Read next: how to write SQL with AI, or hand repetitive computer work to an agent.