AI for Excel and Google Sheets: a workflow that gets the number right

1. 1

   Which AI assistant works in your spreadsheet?

   Start with the app your data already lives in and the plan you already pay for. As of Nov 2026, each maker’s own help page says the following.

   One change trips people up: Microsoft retired the =COPILOT() cell formula. Its help page says: “Starting September 14, 2026, the COPILOT function is no longer available in Microsoft Excel.” Old results stay as values, but a recalculated cell shows #NAME?. Use the Copilot pane instead.

   How to open each one: Copilot from its icon at the bottom right of Excel; Gemini from “Ask Gemini” in Sheets; Claude by installing “Claude for Microsoft 365” from Microsoft’s add-in store; ChatGPT from the install link on OpenAI’s page. If a feature is missing, your plan or your workplace admin has not turned it on.

   Makers’ help pages · 4 assistants

   All four can edit a workbook; the plan you already have decides where to start

   As of Nov 2026	Copilot in ExcelMicrosoft	Gemini in SheetsGoogle	Claude for ExcelAnthropic add-in	ChatGPT for Exceland for Google Sheets
   Works in	Windows, Mac, web; iPad chat and plan; phones chat only	Google Sheets; convert .xlsx files first	Excel on web, Windows, Mac; not iPad or Android	Excel and Google Sheets add-ins
   Edits the sheet for you	Yes, in edit mode (the default)	Yes: formulas, pivots, formatting	Yes, warns before overwriting	Yes, asks first; edits can be undone
   AI in a cell formula	=COPILOT() retired 14 Sep 2026	=AI(), text answers only	—No value	—No value
   Plans that include it	Most work M365 plans; Premium; Personal/Family with AI credits	Eligible Workspace or Google AI plan	Claude Pro, Max, Team, Enterprise	All ChatGPT plans (since 5 May 2026)
   The maker’s own warning	“can sometimes make mistakes”	“may suggest inaccurate” information	Not for audit-critical maths unchecked	Complex formulas may need manual refinement

   Key idea

   Pick the assistant built into the app and plan you already have. The checking habit matters more than the brand.

2. 2

   The workflow: from a question to a number you can defend

   Here is one real task done end to end on a 10-row practice sheet (the company and people are made up). Download it from “Try it” below and follow along in Excel or Google Sheets with any assistant. In Excel, save it as .xlsx first (File, Save As): Copilot needs a modern workbook format.

   The rule that makes it work: ask for a formula you can see, not just an answer. A formula can be checked cell by cell; a sentence in a chat cannot.

   Worked workflow

   October revenue by region for Kestrel Stationery

   Input

   A sheet with 10 orders. Columns: A Order ID, B Order date, C Region, D Rep, E Units, F Unit price (INR). Question from your manager: “What was October revenue by region, and who was our top rep?”

   1. 1

      Describe the sheet and ask for formulas, not answers

      Tell the assistant where the data is, what each column means, and what you need. Ask it to wait before changing anything.

      Copy

      My data is in A1:F11 (header in row 1).
      Columns: Order ID, Order date, Region,
      Rep, Units, Unit price (INR).
      1. Add a Revenue column (Units x Price).
      2. Give me one formula per region for
         October 2026 revenue.
      3. Explain each part of the formula.
      Show me the plan before you edit.

   2. 2

      Read the formula it writes

      A typical first answer looks like this (we wrote it to show the trap; some assistants flag the bad rows, many do not). It adds Revenue in G2 as =E2*F2, filled down, then sums by region and date window. It looks right. For North it returns ₹5,400.

      Copy

      =SUMIFS(G2:G11,C2:C11,"North",B2:B11,">="&DATE(2026,10,1),B2:B11,"<"&DATE(2026,11,1))

   3. 3

      Run two checks before you trust it

      Check 1: =ROWS(B2:B11) says 10 rows, but =COUNT(B2:B11) says 9 dates, so one date is text. Check 2: add the three region totals for all dates (no date filter) with the formula below. It gives ₹60,600, but =SUM(G2:G11) gives ₹66,600, so ₹6,000 sits under a region name that does not match.

      Copy

      =SUMIFS(G2:G11,C2:C11,"North")+SUMIFS(G2:G11,C2:C11,"South")+SUMIFS(G2:G11,C2:C11,"West")

   4. 4

      Fix the data, not the answer

      Order K-103 says “North ” with a trailing space, and K-107’s date was typed as 21.10.2026, which English-language settings usually keep as text. Type headers in H1 (Region clean) and I1 (Date clean). In H2 put =TRIM(C2). In I2 put the formula below and format column I as a date. Fill both down, then change the SUMIFS ranges from C and B to H and I.

      Copy

      =IF(ISNUMBER(B2),B2,DATE(RIGHT(B2,4),MID(B2,4,2),LEFT(B2,2)))

   5. 5

      Answer the second question with a pivot table

      Ask the assistant to build a PivotTable of Revenue by Rep, filtered to October (use the clean date column). Check that its grand total is ₹62,550, the same as the three region totals added together.

   Output

   October revenue: North ₹26,400, South ₹23,850, West ₹12,300; total ₹62,550 (order K-110 is November, so it is left out). Top rep: Ravi, ₹17,100. The first formula reported North as ₹5,400.

3. 3

   Two checks that catch silent formula errors

   The dangerous AI spreadsheet mistake is not an error message. It is a formula that runs, returns a tidy number, and quietly skips rows. Neither check needs you to know the right answer in advance.

   Run them every time an AI gives you a total, whichever assistant you use. The chart shows what the first formula would have told your manager.

   • Rows check: compare ROWS (or COUNTA) with COUNT on a date or number column. A gap means some cells are text.
   • Reconcile check: the parts (regions, reps, months) must add up to the plain SUM of the column. A gap means a label does not match: spaces, spelling or case.
   • If either check fails, ask the AI to find the rows behind the gap. Then fix the data with TRIM or a clean date column.

   Explain this formula part by part, and tell me which rows it skips: [paste] Why does this cell return #N/A? Formula: [paste]. Data: [describe]. Write an XLOOKUP that finds [what] in [range], and say what it returns when there is no match.

   Worked example · October · 10 fictional orders · INR

   Fixed, North is the top region at ₹26,400; the first formula ranked it last at ₹5,400

   • North, after fixes₹26,400
   • South (same in both)₹23,850
   • West (same in both)₹12,300
   • North, first formula₹5,400Skips K-103 (“North ” with a space) and K-107 (date stored as text): ₹21,000

   View as table

   Item	Value	Note
   North, after fixes	₹26,400
   South (same in both)	₹23,850
   West (same in both)	₹12,300
   North, first formula	₹5,400	Skips K-103 (“North ” with a space) and K-107 (date stored as text): ₹21,000

   Key idea

   If the parts do not add up to the total, the formula is wrong, however confident it looks.

4. 4

   Tag a text column with =AI() in Google Sheets

   For words rather than numbers, Google Sheets has a cell formula: =AI("prompt", range). Google says it can generate text, summarise, categorise and analyse sentiment, with answers “limited to text”.

   Know its limits before you rely on it. Only the first 350 selected cells generate at once, it cannot see your whole sheet, and it cannot sit inside another function such as IF.

   Worked workflow

   Sort 40 customer comments into themes

   Input

   Column B holds 40 short customer comments (B2:B41). You want a theme for each, then a count per theme.

   1. 1

      Write a closed list into the prompt

      Put this in C2. Give the exact labels and an “Other” bucket, and ask for one word only. Open-ended prompts return a different wording on every row.

      Copy

      =AI("Classify this comment as Delivery, Price, Quality or Other. Reply with one word only.", B2)

   2. 2

      Test on 5 rows before filling down

      Fill C2 down to C6, select C2:C6 and click “Generate and insert”; nothing appears until you do. Read the 5 answers against the comments, tighten the labels if two themes overlap, then fill down to C41 and generate again.

   3. 3

      Count strays and spot-check

      This returns 0 when every answer is one of your four labels. Then read 10 random rows yourself.

      Copy

      =COUNTA(C2:C41)-SUMPRODUCT(COUNTIF(C2:C41,{"Delivery","Price","Quality","Other"}))

   4. 4

      Freeze the results

      Copy column C and paste values only, so a later refresh cannot change the labels your report used.

   Output

   A theme for every comment, zero strays, 10 rows checked by hand, and a COUNTIF table per theme you can chart.

5. 5

   How accurate is AI at spreadsheet work?

   Researchers measured it. SpreadsheetBench collected 912 real questions from Excel forums and checked each answer against several test sheets. In the 2024 paper, the best model solved 18.35% in one try, Copilot in Excel 20.00% on a hand-checked sample, and Excel experts 71.33% on a 50-question subset.

   Tools have improved fast. In April 2026 Google said Gemini in Sheets reached a 70.48% success rate on the full benchmark. That is the maker’s own test, so treat it as a claim, not a guarantee for your sheet.

   SpreadsheetBench · different setups

   AI went from about 1 in 5 spreadsheet tasks (2024) to a reported 7 in 10 (2026)

   • Excel experts (2024)71.33%
   • Gemini in Sheets (2026)70.48%
   • Copilot in Excel (2024)20.00%
   • GPT-4o, one try (2024)18.35%

   View as table

   Item	Value	Note
   Excel experts (2024)	71.33%
   Gemini in Sheets (2026)	70.48%
   Copilot in Excel (2024)	20.00%
   GPT-4o, one try (2024)	18.35%

   Key idea

   Even at 7 in 10, three tasks in ten go wrong. The checks are how you find which three.

6. 6

   Keep your workbook and your data safe

   An assistant that can edit a workbook can also be misled by one. Anthropic’s page says: “Only use Claude for Excel with trusted spreadsheets.” Files from outside can hide instructions that try to make the AI copy or change data.

   Treat any upload or add-in like sharing the file with a vendor. Use your employer’s approved tool for company data, and remove names, phone numbers and IDs from practice files.

   • Work on a copy before you let an AI edit widely.
   • Read every “confirm” prompt before it changes data.
   • Keep the formulas visible, so a reviewer can follow the maths.

From the people who built it

Straight from the source

The original idea, then the same idea taken apart, then how you use it today.

Zeyao Ma, Jie Tang and colleagues (SpreadsheetBench)

Researchers at Renmin University of China, Tsinghua University and Zhipu AI who built the main test of AI on real spreadsheet tasks.

SpreadsheetBench: Towards Challenging Real World Spreadsheet Manipulation (arXiv, 2024)

1 · Understand

They took 912 real questions people asked on Excel forums and scored AI answers against several test sheets per question. Their finding: “Even the most advanced spreadsheet agent, Copilot in Excel, only achieves an accuracy of roughly 20%.”

2 · Break it down

• Real forum questions are messy: odd layouts, several tables, unclear wording.
• Each answer is tested on more than one sheet, so a formula that works only by luck fails.
• Excel experts scored 71.33%, so even people miss some; the gap was the point.

3 · Use it

Test any AI formula the way they did: run it on a second, slightly different sheet, or reconcile it against a plain total.

5 terms

Words to know

SUMIFS

An Excel and Sheets formula that adds the cells in one column when other columns meet your conditions, such as a region and a date window.

Reconcile

Check that the parts of a breakdown add up to the plain total of the same column.

Helper column

An extra column that cleans or reshapes data, like TRIM on a name, so formulas can read it reliably.

=AI() function

A Google Sheets formula that sends a prompt and a cell range to Gemini and returns text into the cell.

Add-in

An app that runs inside Excel or Sheets, like Claude for Excel or ChatGPT for Excel, with access to the open workbook.

1 video · plays here

Watch

Hand-picked from the people who explain this best. The video loads only when you press play.

Hands-on · 10 minutes or less

Try it

Run the workflow on the practice sheet (15 minutes)

1. 1Download the 10-row Kestrel Stationery CSV (fictional data). In Excel, save it as .xlsx; in Google Sheets, use File, Import.
2. 2Open your assistant (Copilot, Gemini, Claude or ChatGPT) and paste the first prompt from the workflow above.
3. 3Before you accept any total, run the rows check and the reconcile check yourself.
4. 4Ask the assistant to find the rows behind any gap, fix them with helper columns, and recompute.
5. 5Compare with the answers above: North ₹26,400, South ₹23,850, West ₹12,300, top rep Ravi ₹17,100.

Download the practice sheet (CSV)

Practise this lesson with an AI tutor

It checks what you understood, then gives you a small real task to try.

Open in Claude Open in ChatGPT Copy prompt

Read the prompt

Be my patient tutor. I just studied this lesson: "AI for Excel and Google Sheets". Summary: Every big assistant now works inside a spreadsheet: Copilot in Excel, Gemini in Google Sheets, and the Claude and ChatGPT add-ins. Ask the AI for a formula you can see, not just an answer, then run two quick checks: do the rows count right, and do the parts add up to the total? In our practice sheet, a correct-looking formula missed ₹21,000 of ₹26,400 in sales until those checks caught it. No paid plan? OpenAI says its Excel and Sheets add-ins work on all ChatGPT plans.
Key terms: SUMIFS, Reconcile, Helper column, =AI() function, Add-in.

Do this, one step at a time, waiting for me each time:
1. Ask me to explain the main idea in my own words. Correct me kindly with one concrete example.
2. Ask 4 questions, easy to hard. After each answer, say what was right and what was missing.
3. Give me one small workflow I can try in 10 minutes that uses this idea in real work or study. Walk me through it step by step and check my result.
4. End with 3 lines: what I now know, what I should practise, what to learn next.

3 questions · instant feedback

Check yourself

Pick an answer to see if you are right, and why.

Question 1

Question 1 of 3

Region totals for all dates add up to ₹60,600, but the plain SUM of the revenue column is ₹66,600. What is the most likely cause?

AExcel roundingBSome rows have a region label that does not match exactly, such as a trailing spaceCThe AI used the wrong currencyDNothing; totals never match

Question 2

Question 2 of 3

You type =COPILOT("Summarize this", A2:A20) in Excel in November 2026. What happens?

AIt summarises the rangeBIt works only on MacCIt returns #NAME?, because Microsoft retired the function on 14 Sep 2026DIt opens ChatGPT

Question 3

Question 3 of 3

Which use of =AI() in Google Sheets is safest?

AAsking it for the total of a revenue columnBNesting it inside IF to decide which rows to keepCClassifying comments into a closed list of labels, testing 5 rows first, then counting straysDRunning it on 5,000 cells at once without checking

Lesson 19 of 40

Done? Keep going

Mark this lesson complete to keep your place. Progress is saved on this device, no login.

Mark lesson complete

Up next · Lesson 20 · 9 minWhat is an API?An API (application programming interface) is a set of requests one program can make to another, plus the rules for asking. When an app uses Claude, ChatGPT or Gemini behind the scenes, it sends a request to the model’s API over the internet and gets a response back. You prove who you are with a secret API key, and you pay for the tokens you send and receive.

Roles that need this lesson · 5

• Data Analyst interview guideExcel and Sheets rounds test SUMIFS and pivots. Use AI for the formula, then reconcile it.Open the guide
• Strategy & Operations interview guideOps models live in spreadsheets. Get AI to write the formula, then make the parts add up.Open the guide
• Founder's Office interview guideBoard and investor numbers come from a sheet. Two checks stop a wrong total going out.Open the guide
• Chief of Staff interview guideYou own the numbers in the leadership review. Use AI for speed and reconcile before sending.Open the guide
• Growth Manager interview guideTag thousands of survey comments with =AI() and count themes without a data team.Open the guide

← Previous: AI for data analysis

People also ask

Questions people ask

How do I use AI in Excel?+

Open the Copilot pane in Excel (it needs a qualifying Microsoft 365 plan), or install the Claude for Excel or ChatGPT for Excel add-in. Describe your data and ask for a formula you can see, then check that the row count is right and the parts add up to the total before you use the number. Without a paid Microsoft plan, OpenAI says ChatGPT for Excel works on all ChatGPT plans, or you can upload the file to a chatbot (see our AI for data analysis lesson).

Why does the COPILOT function in Excel show #NAME?+

Microsoft retired the =COPILOT() function on September 14, 2026. Results already calculated stay as cached values, but a cell that recalculates returns #NAME?. Microsoft points users to the Copilot pane in Excel instead.

Is there an AI function in Google Sheets?+

Yes. Type =AI("your prompt", range) to generate, summarise or categorise text with Gemini. It needs an eligible Workspace or Google AI plan, returns text only, generates up to 350 selected cells at a time and cannot be nested inside other functions.

10 sources · checked Nov 2026

Sources and further reading

• Microsoft Support: COPILOT function (retired Sep 14, 2026)
• Microsoft Support: Copilot in Excel
• Microsoft Support: Copilot in Excel FAQ
• Google Docs Editors Help: Use the AI function in Google Sheets
• Google Docs Editors Help: Gemini in Google Sheets
• Google Workspace Updates: Build and edit spreadsheets with Gemini (Apr 22, 2026)
• Claude docs: Use Claude for Excel
• OpenAI: ChatGPT for Excel (Mar 5, 2026; updated May 5, 2026)
• Ma et al.: SpreadsheetBench (arXiv 2406.14991v2, 2024)
• Ben Collins: How to use the AI Function in Google Sheets (Aug 25, 2025)

Related on this site

Previous: AI for data analysisNext: What is an API?Why AI makes things upData analyst interview guideData analyst concepts to know