AI in Excel and Google Sheets: Copilot, Gemini and formula help

To use AI in Excel, open Copilot in Excel and describe what you want in plain words, or ask a chat assistant to write the exact formula for you. Google Sheets offers the same through Gemini and the =AI() function. This guide covers what each tool does today, copy-paste prompts and formulas, data cleaning, pivots, and the checks that keep your numbers right.

How do you use AI in Excel?

You use AI in Excel in one of two ways: ask Copilot in Excel, Microsoft's built-in assistant, to work on your sheet directly, or ask a separate chat assistant such as ChatGPT or Claude to write formulas and explain data you paste or upload. Both work. The built-in assistant can change your workbook; the chat assistant is often better for learning why a formula works.

Here's how the main options compare at the time of writing (October 2026):

Plans and features shift often in this area. Microsoft's plan comparison page lists Microsoft 365 Personal at $9.99 a month, Family at $12.99 and Premium at $19.99 at the time of writing, each with Copilot at different usage levels.

What can Copilot in Excel do?

Copilot in Excel is a chat pane you open from the Copilot icon. You describe a task in plain words and it reads your data, then suggests or applies formulas, formatting, charts, pivots and summaries. It works best on data formatted as a table with clear headers.

Requests that work well:

• "Add a column that shows the profit margin for each row."
• "Highlight orders over $5,000 in green."
• "Summarize the feedback in A2:A200 into five themes with counts."
• "Make a pivot table of revenue by region and quarter."
• "Which three products had the biggest drop in sales from Q2 to Q3?"
• "Explain what the formula in F2 does, step by step."

The COPILOT function was retired

If you read older tutorials, you'll see an in-cell formula called COPILOT. Microsoft retired the COPILOT function on September 14, 2026. Results already in your workbook stay as cached values, but any cell that recalculates now shows #NAME?. Microsoft's replacement advice is to ask Copilot in Excel in the chat pane, for example "Classify each of the sentiment entries in B2:B100 and add the result in a new column."

How does AI work in Google Sheets?

Google Sheets offers Gemini in a side panel for questions about your sheet, plus an =AI() function that runs a prompt for each row. The function is the Sheets feature without a direct Excel equivalent today, and it's useful for text-heavy columns.

According to Google's help page, the syntax is:

=AI("prompt", [optional range])

You can also type =Gemini(). After entering the function, select the cell or cells and click Generate and Insert. Some examples:

=AI("Categorize the customer inquiry as a compliment, exchange request, or return request.", C2)=AI("Write a one sentence summary of this customer's feedback.", A2:D2)=AI("Classify the email as positive, negative, or neutral.", D2)

Things to know before you rely on it:

• Responses are text only.
• The function only sees the range you pass, not the whole spreadsheet or your Drive, so always include the range.
• There are short-term and long-term generation limits. If you hit the long-term limit, Google says to wait 24 hours.
• It needs an eligible Workspace or Google AI plan, and admins can turn it off.
• When the source data changes, results don't always refresh automatically; regenerate when you need current values.

Tables in Sheets can also get an AI column, and Gemini can fill a column from a few examples or a prompt, which saves time on tasks like tagging rows or extracting a value from free text.

Which AI add-ins work inside Excel and Sheets?

If you already pay for Claude or ChatGPT, you can bring that assistant into your spreadsheet instead of copying data back and forth. This is useful when your company uses Excel but you prefer a different model, or when you want one assistant across chat and spreadsheets.

• Claude for Excel is an add-in from the Microsoft marketplace for Excel on the web, Windows and Mac. According to Anthropic's help page, it answers questions about the open workbook with cell-level citations, adjusts assumptions while keeping formula relationships intact, traces errors to their root cause and builds models across tabs. It needs a Pro, Max, Team or Enterprise plan.
• ChatGPT lists Excel, Word, PowerPoint and Google Sheets extensions on its pricing page, with limited use on the free plan and full use on paid plans at the time of writing.

Company-managed Microsoft 365 accounts often need an admin to approve add-ins, so check with IT before you install one on a work machine.

How do you get AI to write Excel formulas?

Give the AI your sheet's layout, not only your goal. A chat assistant can't see your file unless you upload it, so tell it the column letters, header names, a few sample rows and what the correct output looks like for one of them.

A weak prompt and a strong one:

Formula to find overdue invoices

I'm using Excel for Microsoft 365. Sheet "Invoices", headers in row 1:A = Invoice ID, B = Client, C = Due date (real dates), D = Amount,E = Status ("Paid" or "Open"). Data in rows 2 to 500.I need a formula for column F that shows "Overdue" when the status is"Open" and the due date is more than 30 days before today, otherwiseleave it blank. Example: row 2 is Open, due 2026-08-01, so F2 = "Overdue".Give me the formula for F2, explain it in two lines, and tell meif it behaves differently in Google Sheets.

The answer you should get back:

=IF(AND(E2="Open", TODAY()-C2>30), "Overdue", "")

Formulas that AI assistants write well and that are worth knowing by name:

Always say which app and version you use. Excel for Microsoft 365, older Excel versions and Google Sheets support different functions, and the AI will guess if you don't say.

For SQL-style questions on bigger data, our guide to writing SQL with AI applies the same prompting approach to databases.

How do you clean messy spreadsheet data with AI?

Describe the mess precisely, ask the AI for a cleaning plan before any changes, and keep the original data on a separate sheet. AI is good at spotting inconsistent formats and writing the formulas or steps to fix them.

A cleaning workflow that works in Excel, Sheets or a chat assistant:

1. Duplicate the raw data to a new sheet named "raw" and never edit it.
2. Describe the problems. For example: names in mixed case, phone numbers in five formats, dates stored as text, duplicate customer rows with slightly different spellings.
3. Ask for a plan first. "List the cleaning steps you'd take, in order, and which columns each affects. Don't change anything yet."
4. Apply one step at a time with helper columns, so each change is visible and reversible.
5. Spot-check 10 random rows against the raw sheet after each step.
6. Replace formulas with values once you're happy, then delete the helper columns.

A reusable prompt for a chat assistant with an uploaded file:

Here is a CSV export of our customer list. Before changing anything:1. Describe the columns and their data types.2. List data quality problems you see, with example rows and counts.3. Propose cleaning steps in order.Then wait for my OK. When I approve, return a cleaned CSV and achange log listing every transformation and how many rows it affected.

The change log matters because it lets you verify the AI did what it said. Our longer guide to AI for data analysis covers sanity checks and reproducible analysis in more depth.

Can AI build pivot tables and charts for you?

Yes. Copilot in Excel and Gemini in Sheets both create pivot tables and charts from plain requests, and chat assistants can produce them from an uploaded file or walk you through the clicks. The key is to name the rows, columns, values and filter you want.

Good pivot requests are specific:

Create a pivot table from the Sales table on a new sheet.Rows: Region. Columns: Month (grouped by month, Jan to Dec 2026).Values: Sum of Revenue, formatted as currency with no decimals.Filter: Channel = "Online". Sort regions by total revenue, highest first.Then add a clustered column chart of the same data.

If you prefer to build it yourself, ask for steps instead: "Walk me through creating this pivot in Excel for Mac, one click at a time." Then compare the grand total with a plain =SUM() over the source column. If they don't match, a filter, a blank row or text-formatted numbers are the usual culprits.

What are the limits of AI in spreadsheets?

AI in spreadsheets is reliable for writing formulas, explaining logic and handling text, and unreliable as a calculator. Microsoft's own documentation for the retired COPILOT function advised against using it for numerical calculations and recommended native formulas like SUM and AVERAGE instead. That advice applies to every AI tool.

Rules that keep your numbers right:

• Let formulas do the math. Ask AI to write the formula, then let Excel or Sheets compute it.
• Test on known rows. Before filling a column, check the result on three rows you can verify by hand, including an edge case such as a blank cell.
• Watch for silent truncation. Large uploads can be cut off; ask the assistant how many rows it read and compare with your row count.
• Expect variation. Text functions like =AI() may give slightly different wording for similar inputs. For categories, give the exact list of allowed labels.
• Mind privacy. Customer data, salaries and financials may not belong in a consumer AI chat. Check your company's policy; our guide on whether it's safe to use AI at work covers what to ask.
• Check confident answers. AI can invent function names or arguments that don't exist in your version. If a formula errors, paste the exact error back and name your version.

How to start using AI in your spreadsheets today

1. Check what you already have: Copilot in Excel with your Microsoft 365 plan, Gemini in Sheets with your Google plan, or a chat assistant.
2. Format your data as a table with one header row and no merged cells.
3. Pick one recurring task, such as a weekly report or a messy import.
4. Write a prompt with your column layout, a sample row and the expected result.
5. Test the formula or output on rows you can verify by hand.
6. Save the working prompt in a note so the next run takes seconds. Our ChatGPT prompts for work has more templates to adapt.
7. Replace any old COPILOT formulas with values before they recalculate.

If your spreadsheet reports outgrow Excel and turn into scripts or dashboards built by coding agents, Orkio's Data & reports kit gives those agents an always-on cloud box to work on.

Frequently asked questions