How to Use AI in Excel and Google Sheets: A Practical Guide With Copy-Ready Prompts

On this page 12

1. Two ways to get AI help with a spreadsheet
2. Three rules before you paste anything
3. How to describe your sheet so AI gets it right
4. Write a formula from a plain-English description
5. Explain or fix a formula you inherited
6. Clean messy data
7. Summarise a table and spot patterns
8. Build a simple pivot table or chart
9. Write Apps Script or VBA snippets with a chat assistant
10. Copilot in Excel and Gemini in Google Sheets: what to expect
11. Which approach to use for each task
12. A quick check before you trust the result

The short answer

There are two ways to use AI with a spreadsheet. You can ask a general chat assistant (ChatGPT, Claude, the Gemini app or Copilot chat) to write formulas, cleaning steps and scripts from a description of your sheet. Or, if your plan includes one, you can use a built-in assistant that works inside the file (Copilot in Excel or Gemini in Google Sheets). Either way, describe your columns with made-up sample rows instead of real data, test every result on rows where you already know the answer, and keep a copy of the file before running any script.

• A free chat assistant handles most formula, cleaning and script jobs.
• Built-in assistants mainly save copy-paste time on summaries, charts and quick analysis. Whether you have one depends on your plan.
• AI drafts the formula. You check it against known answers.

Two ways to get AI help with a spreadsheet

Option 1: a general chat assistant. This means ChatGPT, Claude, the Gemini app or Copilot chat, open in a separate tab. You describe the sheet, the assistant writes a formula or script, and you paste it back yourself. It works with any version of Excel or Sheets and with any plan, including free tiers. If you're weighing up a paid plan, see our comparison of ChatGPT Plus, Claude Pro and Google AI Pro.

Option 2: a built-in assistant. Copilot in Excel and Gemini in Google Sheets sit inside the file. They can read the data and act on it, for example by suggesting formulas, adding columns, summarising or creating charts. Whether you have access depends on your Microsoft 365 or Google Workspace plan, and sometimes on your region and your admin's settings.

Most formula and script jobs work fine with a chat assistant. A built-in assistant earns its place when you want quick answers, summaries or charts from data that is already in the file, without copying anything back and forth.

Three rules before you paste anything

1. Keep real data out of tools you haven't checked. Customer names, emails, salaries, invoice amounts and bank details should not go into a chat assistant whose data terms you haven't read. Describe the columns and use made-up sample rows instead. The AI needs the structure, not the contents.
2. Test on a small sample first. Before filling a formula down thousands of rows, try it on three to five rows where you already know the right answer.
3. Save a copy before running any macro or script. Changes made by a script often cannot be undone with Ctrl+Z. Make a copy of the file, or confirm you can restore an earlier version from version history.

If staff use AI on company spreadsheets, agree the rules in writing first. Our AI acceptable use policy template for small businesses covers what can and cannot be shared.

How to describe your sheet so AI gets it right

Most bad AI formulas come from a vague request. The fix is one reusable habit: describe your sheet the same way every time.

Start with the app. Excel and Google Sheets share many functions but not all of them. QUERY and ARRAYFORMULA exist only in Google Sheets. Newer Excel functions such as TEXTSPLIT are missing from older Excel versions, and VBA macros do not run in Sheets at all. If you use Excel and know your version (Microsoft 365, 2021, 2019), say so.

The reusable sheet description template

Give the sheet name, the column letters with their headers, the row where data starts, and a few fake rows. Keep this template in a notes file and fill it in each time:

I'm using [Google Sheets / Excel for Microsoft 365 / Excel 2019].
Sheet name: Invoices
Row 1 has headers. Data starts in row 2.
Dates are in YYYY-MM-DD format.

Columns:
A = Invoice ID (text, e.g. INV-001)
B = Customer (text)
C = Due date (date)
D = Amount (number, 2 decimals)
E = Paid date (date, blank if unpaid)

Fake sample rows (not real data):
INV-001 | Customer A | 2025-01-10 | 120.00 | 2025-01-08
INV-002 | Customer B | 2025-01-15 | 450.00 | (blank)
INV-003 | Customer C | 2030-12-20 | 80.00  | (blank)

Task: [describe what you want]
Expected result for one row: [e.g. INV-002 should show "Overdue"]

Write a formula from a plain-English description

This is the most common job and the one chat assistants handle best. Paste your sheet description, then add the task:

[Paste your sheet description]

Write a formula for column F that shows "Overdue" when the due date
in column C is before today and the paid date in column E is blank.
Otherwise leave the cell empty.

- Give me the formula for F2 so I can fill it down.
- Explain each part in one line.
- Tell me what happens if C is blank or contains text.

A typical answer is =IF(AND(E2="",C2<TODAY()),"Overdue",""). The last line of the prompt surfaces an edge case: a blank due date counts as zero, so that row would be flagged "Overdue". A safer version adds a check that C2 is not empty, such as =IF(AND(C2<>"",E2="",C2<TODAY()),"Overdue","").

Swap the task line for lookups and totals

The same prompt pattern works for other everyday formulas. Replace the task line with something like:

• "Look up the customer's email from the Customers sheet using the Customer name in column B" (XLOOKUP, or VLOOKUP in older Excel).
• "Total the Amount in column D for invoices that are unpaid and due in March 2025" (SUMIFS).

Decide before hiding errors with IFERROR

You can ask for a version wrapped in IFERROR so it never shows #N/A. Decide first whether you want that. A hidden error can mean a customer is missing from your lookup table, and you'd rather know. For lookups, a visible "Not found" message is often better than a blank.

Check it: fill the formula down your sample rows. INV-002 should show "Overdue"; INV-001 and INV-003 should stay empty. If they don't, paste the wrong result back and ask why.

Explain or fix a formula you inherited

Old spreadsheets often contain long nested formulas nobody remembers writing. AI is good at turning them into plain English and at diagnosing errors.

Explain a long nested formula

I'm using [Excel / Google Sheets]. Explain this formula step by step
in plain English, from the innermost part outwards. Then tell me in
one sentence what it does overall.

Columns it refers to: [A = Region, C = Sales, ...]

Formula:
[paste formula]

Fix #N/A, #VALUE! or #REF! errors

Give the AI the formula, the exact error and a description of what the referenced cells contain:

I'm using [Excel / Google Sheets]. This formula returns #N/A in some
rows: [paste formula]

Column A on this sheet holds product codes like "P-1001".
The lookup range on the Products sheet holds the same codes.
The error appears even when the code looks identical in both places.

List the likely causes and your assumptions (for example numbers
stored as text, extra spaces, or the wrong range), then give a fixed
formula.

The usual suspects: #N/A means a lookup found no match, often because of trailing spaces or numbers stored as text. #VALUE! usually means text where a number or date was expected. #REF! means the formula points to cells that were deleted. Asking the AI to list its assumptions makes it easier to spot when it has guessed wrong.

Check it: for an explanation, work through one row by hand using the AI's steps and confirm you get the value in the cell. For a fix, the error should be gone and a row whose answer you know should still be correct.

Clean messy data

Imported data is rarely tidy. Typical jobs include trimming extra spaces, fixing capitalisation, splitting full names, standardising dates and phone numbers, and finding duplicates.

Ask for a helper-column formula, not rewritten data

A formula in a helper column is repeatable and checkable, and your data never leaves the sheet. Common building blocks:

• TRIM removes extra spaces; CLEAN removes non-printing characters.
• PROPER capitalises each word, but turns "McDonald" into "Mcdonald", so check names.
• TEXTSPLIT (newer Excel) or SPLIT (Sheets) breaks text into columns.
• REGEXEXTRACT in Google Sheets pulls out patterns such as postcodes or digits. Newer Excel versions have been adding regex functions too, so check yours.
• COUNTIF can flag duplicates: a count above 1 means the value appears more than once.

[Paste your sheet description]

Column B holds customer names imported from our booking system.
Fake examples: "  jane SMITH", "o'brien, mark", "Lee  Wong ".

Write a formula for a helper column that:
- removes extra spaces,
- puts the name in "First Last" order,
- capitalises each word.
Tell me which examples your formula would still get wrong.

Repeat imports: let Power Query store the steps

If you import the same messy file every week in Excel, consider Power Query. It stores the cleaning steps and reapplies them on refresh. A chat assistant can explain each step if you paste the step list in.

Check it: keep the original column and compare it side by side with the cleaned one, and confirm both columns have the same number of rows. Only then paste the cleaned values over the original, or hide it.

Summarise a table and spot patterns

How you approach analysis depends on which kind of assistant you have.

With a chat assistant: ask how, not what

Ask how to answer the question rather than asking for the answer:

[Paste your sheet description: Date, Product category, Units, Revenue]

I want to know which product category grew most month on month.
Which formulas or pivot table layout would show this? Give me the
steps, and tell me how to calculate growth as a percentage.

You then build it yourself, with your real data staying in the sheet.

With a built-in assistant: ask the file directly

Copilot in Excel or Gemini in Sheets lets you ask questions of the data in the file. It tends to work best on a clean table: one header row, no merged cells, no blank rows in the middle and no subtotals mixed into the data.

Turn calculated results into a written summary

Once you have calculated the numbers, AI can help turn them into words, for example for an email to a partner:

Write a 5-sentence summary for a business partner using only these
figures. Don't add any numbers that aren't listed.

- Q1 revenue: [figure]
- Q2 revenue: [figure]
- Best category: [name, growth %]
- Weakest category: [name, growth %]

For tone and structure, see how to write AI prompts for business emails.

Build a simple pivot table or chart

The hard part of a pivot table is deciding what goes where. AI can sort that out in one prompt:

[Paste your sheet description]

I want to see total revenue by region for each month, and filter by
product category. Recommend a pivot table layout: what goes in rows,
columns, values and filters, and whether to use sum, count or average.
Then suggest one chart type for this and explain why.

As a rule of thumb, a line chart suits trends over time, a bar chart suits comparisons between categories, and a pie chart only works for a few parts of one whole.

In both Excel and Google Sheets, the Insert menu has options for pivot tables and charts. Built-in assistants may be able to create the pivot or chart for you, depending on your plan and which features have reached your account.

Check it: the pivot's grand total should equal a plain SUM of the source column. If it doesn't, the pivot range probably misses some rows.

Write Apps Script or VBA snippets with a chat assistant

Scripts automate the boring parts: copying rows, sending reminders, tidying tabs. You don't need to code, but you do need a careful routine.

Which script type your app uses

• Google Apps Script for Google Sheets.
• VBA macros for desktop Excel. To keep a macro in the file, save it in a macro-enabled format (.xlsm).
• Office Scripts for Excel on the web, if your plan includes them. Check Microsoft's documentation.

Prompt for an Apps Script reminder

Keep the job small and specific. This example assumes you've added a column G for the customer's email:

Write a Google Apps Script for my sheet "Invoices" (headers in row 1,
columns as described: [paste description], plus G = Customer email).

For each row where Paid date (E) is blank and Due date (C) is more
than 7 days ago, send a reminder email to the address in column G
using this template: [paste template].

- Add comments explaining each part.
- Before the code, list exactly what it reads, changes and sends,
  and what permissions it will ask for.
- Add a test mode that logs the emails instead of sending them.

Prompt for a VBA macro

Write a VBA macro for desktop Excel. On the sheet "Orders" (headers
in row 1, data from row 2), copy every row where column D says
"Shipped" to the sheet "Archive", below any existing rows.
Do not delete anything from "Orders".

Add comments to every block and tell me what the macro changes.

Run every script on a copy first

If you would rather avoid code altogether, see how to automate repetitive admin tasks with AI without coding.

Copilot in Excel and Gemini in Google Sheets: what to expect

Both built-in assistants aim to do similar things inside the file. They can help write formulas, answer questions about your data, suggest charts and summarise a table. The exact features change often, so treat any list (including this one) as a starting point.

The things to verify before you count on one:

• Your plan. Access depends on your Microsoft 365 or Google Workspace tier, any add-ons, and whether you use a personal or business account.
• Region and admin settings. Some features roll out by region, and a workplace admin can switch them off.
• Where the file lives. The file may need to be saved in the cloud (OneDrive or SharePoint for Excel, Google Drive for Sheets).
• How the data is laid out. Some features work best, or only, when the data is formatted as a table.

Both vendors have also been adding AI functions you type into a cell, like an ordinary formula. These may be in preview or limited to certain plans, so check the vendor's current documentation before relying on them.

Which approach to use for each task

Quick decision

• You need a formula, a cleaning step or a scriptuse a free chat assistant with a sheet description and fake rows.
• Your plan includes Copilot or Gemini and the data is already in a clean tableuse the built-in assistant for quick questions, summaries and charts.
• The job is removing duplicates or splitting a predictable patterntry a plain feature first: Remove duplicates, Flash Fill in Excel or Smart Fill in Sheets. No AI needed.
• The data is sensitive and you haven't checked the tool's termsstick to describing structure, or use a plain feature.

If you're choosing between chat assistants, our ChatGPT vs Claude vs Gemini comparison covers the main differences for small-business work.

A quick check before you trust the result

Run through this list before an AI-written formula, summary or script touches your real work:

• Sample rows return the answers you worked out in advance.
• Totals reconcile with a plain SUM of the same column.
• Edge cases behave: blank cells, zero, text in a number column, dates at the start and end of a month.
• References still point to the right cells after filling down (dollar signs on anything that should stay fixed).
• No IFERROR is quietly hiding real problems.
• Every number in a written summary can be found in the sheet.
• Any script or macro ran on a copy first, and you checked what it changed.

AI drafts; you check. That one habit is what makes AI help in a spreadsheet worth having.

Frequently asked questions

Can ChatGPT or Claude read my Excel or Google Sheets file?

Many chat assistants accept file uploads on some plans, but uploading sends the data to that service under your plan's terms. For most formula, cleaning and script tasks, describing the columns and adding a few fake sample rows works just as well. Read Is it safe to put customer data into ChatGPT? before uploading anything real.

Is Copilot in Excel free?

It depends on your Microsoft 365 plan and any add-ons, and availability and pricing change often, so check Microsoft's current plan page. If you don't have it, a free chat assistant can still write and explain Excel formulas and VBA from a description of your sheet.

Can I use Gemini in Google Sheets with a free Google account?

Features differ between personal Google accounts and Google Workspace plans, and Workspace admins can switch them on or off. Check Google's current Workspace plan information and support pages to see what your account includes.

Are AI-generated formulas reliable?

They are often correct, but they can use a function your app or version doesn't support, misread your column layout, or quietly mishandle blank cells. Always test the formula on sample rows where you already know the right answer before filling it down.

Will a formula written for Excel work in Google Sheets?

Many common functions work in both, but not all. QUERY and ARRAYFORMULA exist only in Google Sheets, some newer Excel functions are missing from older versions, and VBA macros do not run in Sheets. Always tell the AI which app and version you use.

Is it safe to run a macro or script that AI wrote?

Only after making a copy of the file, asking the AI to explain what the code reads and changes, reading the permission prompt carefully, and testing it on the copy. Don't run code you cannot get explained.

Do I need to know how to code to use AI for VBA or Apps Script?

No, but you do need to follow a careful routine: work on a copy, ask for comments in the code, test with fake rows and check the result. If you'd rather avoid code entirely, see how to automate repetitive admin tasks with AI without coding.