Using AI with Google Sheets: formulas, cleanup and reports

Google Sheets is where a lot of small-business and team work lives: pipelines, budgets, schedules, inventory, survey results. It is also where a lot of hours vanish into fixing formulas and tidying imports. AI assistants are well suited to both jobs, because you can describe what you want in plain English and get the formula back.

This guide, current as of September 2026, works with any chat assistant such as Claude or ChatGPT, and it also applies if your Google Workspace plan includes an assistant built into Sheets. Menu names and plan availability for built-in features change, so this guide doesn’t depend on them. Check Google’s own help pages for what your account has. The method is the same either way.

Step 1: Decide how to share the data

There are two ways to give an assistant your sheet.

Describe the structure. You paste only the headers and two or three sample rows, then ask for a formula. Nothing sensitive leaves your sheet, and for formulas this is usually enough. Do this by default.

Share a copy of the data. For cleanup or analysis, the assistant needs the actual values. Export the tab with File → Download → Comma Separated values (.csv) and upload it to your assistant, or paste a section. Check your company’s rules first, and remove columns you don’t need, such as personal contact details. See our company AI policy template.

If your account has an assistant inside Sheets, the same rules apply: know what data it can see, and read your admin’s settings.

Step 2: Prepare the sheet

Two minutes of prep prevents most errors.

1. Make a copy. File → Make a copy. Test on the copy.
2. One header row. Put clear column names in row 1, no merged cells.
3. Freeze the header with View → Freeze → 1 row.
4. Write down a known answer. Note the row count and one total you trust, for example the sum of the Amount column. You’ll use it to check the results.

Step 3: Get formulas written for you

The key is to give the assistant the structure and say exactly what you want.

I'm using Google Sheets. Here are my columns (row 1) and two sample rows:

A: Order ID | B: Customer | C: Region | D: Order Date | E: Amount | F: Status
1041 | Acme Corp | East | 2026-09-03 | 1240.00 | Paid
1042 | Brightline LLC | West | 2026-09-04 | 310.50 | Open

Write a formula for cell H2 that returns total Amount for Region "East" where Status is "Paid" and Order Date is in September 2026. Explain each part in one sentence. Tell me what to change if my data goes past row 500.

A good answer will be something like a SUMIFS with ranges for each condition and date bounds. Ask for the explanation every time. It teaches you the pattern and makes mistakes easier to see.

Useful requests to try:

• “Return the customer name from the Customers tab for each Customer ID in column B. If not found, show ‘Not found’.” (A lookup formula such as XLOOKUP or VLOOKUP.)
• “Make one formula in row 1 that fills a whole column so I don’t have to copy it down.” (An ARRAYFORMULA.)
• “Build a summary table of total Amount by Region and Status.” (QUERY, which lets you filter and group with a short database-style instruction, or a pivot table.)
• “This formula gives #REF! [paste it]. Explain why and fix it.”
• “Split ‘Smith, Jane’ in column B into first and last name in separate columns.”

Sheets uses commas or semicolons between arguments depending on your locale settings. If a formula errors on paste, tell the assistant which you use.

Step 4: Test each formula before you trust it

A formula that returns a number is not a formula that returns the right number.

1. Test on rows you know. Pick three rows or a small filtered group where you can compute the answer by hand or by eye.
2. Compare to the known total. Your unfiltered total should match your earlier note.
3. Break it deliberately. Type a blank, a text value in a number column, an unexpected region. Does it behave sensibly?
4. Check the ranges. Formulas that reference A2:A100 silently ignore row 101. Use full-column references such as A2:A where appropriate, or ask the assistant to make the range grow with the data.
5. Look at the dates. Text that looks like a date is not a date. If date conditions return zero, select the column and check whether the cells are left-aligned text.

A prompt that helps here:

Give me three test cases for this formula: one normal, one edge case, and one that should return zero. Tell me the exact result I should see in each case.

Step 5: Clean messy data

Cleaning is where a copy and a flag-first approach matter most.

Start by asking what’s wrong:

Here is a sample of my data [paste 30 rows]. List every data quality problem you can see: inconsistent capitalization, extra spaces, mixed date formats, duplicates, text stored as numbers, blanks. Don't fix anything yet.

Then fix issues one at a time, in new columns:

• “Write a formula for a new column that trims extra spaces and converts the Customer column to Proper Case.”
• “Write a formula that converts mixed dates in column D to real dates. They’re US format, month first. Return blank if it can’t convert.”
• “Add a column that flags duplicates by Order ID, marking every copy after the first.”

Sheets also has built-in helpers under Data → Data cleanup, such as removing duplicates and trimming whitespace, and you can use them next to what the assistant writes. When you’re done, paste the new columns over the old ones as values with Ctrl+Shift+V (Cmd+Shift+V on Mac), not as formulas, so the cleaned data stays put. Keep the original tab as a backup.

Flag, don’t delete. Removing rows is the one step that is hard to notice afterwards.

Step 6: Build a summary and a written readout

Once the data is clean, ask for a structure for the report:

Using the columns above, suggest a one-tab summary for my manager: a table of total Amount by Region and month, a chart type that suits it, and three questions the manager might ask. Then give me the exact steps or formulas to build each in Google Sheets.

Follow it with the readout. Paste the summary table and ask:

Write three plain-English bullets on what changed between August and September, using only the numbers in the table below. After each bullet, name the row and column each number came from.

[paste table]

Then verify every number against the sheet. The requirement to name the source cell is what makes verification fast. If you rebuild this report every week, our guide on automating your weekly report with AI shows how to turn it into a routine.

Step 7: Automate a repeat job with Apps Script

If a job recurs, such as emailing a summary, sorting new rows or copying data between tabs, Google’s scripting tool, Apps Script, can handle it. You open it from Extensions → Apps Script. You don’t have to write the code: describe the job to an assistant and ask for a script plus step-by-step instructions to install it.

Write a Google Apps Script for my spreadsheet. Every Monday at 8am, it should copy last week's rows from the "Raw" tab to the "Archive" tab and send me an email with the row count. Explain each step for someone who has never used Apps Script, including how to authorize it and set the trigger. Make it log what it did.

Before you run any script:

• Ask the assistant to explain what it does in plain English and what permissions it needs.
• Run it on a copy first.
• Approve only the permissions you understand. A script that only reads and writes your sheet shouldn’t need to reach your whole Drive.

The Gmail guide walks through the same Apps Script pattern in more detail, and Claude Code for non-programmers shows how to build bigger tools for spreadsheet work.

What Google’s documentation says

Facts about the built-in AI function and Apps Script, taken from Google’s help and developer pages.

• Google says that in Sheets you enter an AI function in a cell, using either =AI() or =Gemini().
• In Google Sheets, Google recommends using the optional range argument, because it improves output quality.
• In Google Sheets, Google says you can’t undo or redo an AI function and can regenerate the output instead.
• Google says time-driven triggers in Apps Script allow scripts to execute at specific times or intervals.
• Google lists a total trigger runtime of 90 minutes a day for consumer accounts and 6 hours a day for Google Workspace accounts.
• The SpreadsheetLLM paper on arXiv says spreadsheets have extensive two-dimensional grids, flexible layouts, and varied formatting options that pose significant challenges for large language models.
• The SpreadsheetLLM paper on arXiv describes a vanilla serialization approach that incorporates cell addresses, values, and formats.

Common mistakes

• Pasting confidential data casually. Share structure, not customer lists, unless approved.
• Accepting a formula you can’t explain. Ask for the explanation or ask for a simpler approach.
• Leaving formulas live after cleaning. Convert to values once verified.
• Ignoring locale. Comma versus semicolon separators and date formats cause many errors.
• Skipping the known-total check. It catches the silent failures.

For the short answer, see how to use AI with Google Sheets. If you use Excel too, read AI with Excel. Clearer prompts also produce better formulas, so how to write AI prompts that work is worth a read.

Spreadsheet work is a hands-on session in our two-day class, where you clean and analyze a provided workbook with AI tools.

Sources

• Google Docs Editors Help, Use the AI function in Google Sheets: “In a cell, enter an AI function”
• Google Docs Editors Help, Use the AI function in Google Sheets: “use the optional range argument. This is highly recommended to improve output quality”
• Google Docs Editors Help, Use the AI function in Google Sheets: “undo or redo your function. You can regenerate your output instead”
• Google for Developers, Installable Triggers (Apps Script): “Time-driven triggers allow scripts to execute at specific times or intervals”
• Google for Developers, Quotas for Google Services (Apps Script): “Triggers total runtime 90 min / day 6 hr / day”
• arXiv, SpreadsheetLLM: Encoding Spreadsheets for Large Language Models: “Spreadsheets are characterized by their extensive two-dimensional grids, flexible layouts, and varied formatting options, which pose significant challenges for large language models”
• arXiv, SpreadsheetLLM: Encoding Spreadsheets for Large Language Models: “we propose a vanilla serialization approach that incorporates cell addresses, values, and formats”