How to Use AI in Excel and Google Sheets

AI in Excel and Google Sheets is most useful for messy text, formula help, and first-pass analysis. It is least useful as an unsupervised bookkeeper. A model can suggest a SUMIFS or classify support tickets; it can also invent a total that does not match the cells.

This guide covers practical workflows in Microsoft 365 Copilot/Excel and Gemini for Google Sheets, plus using ChatGPT or Claude beside the file. Practice the job-first habit in AI for Excel. Related: data and analytics tools, small-business AI tools, and prompt engineering.

What spreadsheet AI is good at

If the sheet is the system of record for money, inventory, or grades, keep formulas deterministic. Use AI to write the formula, then lock it down.

Excel: Copilot and classic techniques

In Microsoft 365, Copilot can draft formulas, create columns, and summarize tables when your tenant and license allow it. Availability varies; the skills below still apply if Copilot is off.

High-value asks

• “Add a column that flags invoices over 30 days past due using columns D and E.”
• “Explain this formula in plain English and list failure cases.”
• “Propose a PivotTable layout for revenue by region and month.”

Safer pattern: ask for the formula text, paste it into a test sheet, then promote it.

Power Query remains the right tool for repeatable imports. AI can suggest steps; you should still have a refreshable query, not a one-off generated dump.

Google Sheets: Gemini and built-in helpers

Gemini features in Google Workspace can generate formulas, analyze selected ranges, and draft side-panel insights. Again, plans and admin settings decide what you see.

High-value asks

• “Create a formula to extract the domain from email addresses in A2:A.”
• “Find outliers in this spend column and explain the method.”
• “Build a chart brief: what to plot and what not to claim.”

For Apps Script, ask a model to draft a function, then run it on a copy. Never grant a generated script broad Drive access without review.

Using ChatGPT or Claude beside the spreadsheet

When in-app AI is missing or weak, export a sample (not the whole customer database):

1. Copy 15 representative rows plus headers.
2. State types (currency, ISO dates, IDs).
3. Ask for a formula or a cleanup plan.
4. Apply locally.
5. Diff against the original.

ChatGPT is often convenient for formula iteration. Claude is strong when you paste a long, ugly formula and want a careful rewrite. See ChatGPT vs Claude vs Gemini.

Do not paste payroll, health, or unpublished financials into a consumer chat.

A weekend cleanup workflow

1. Snapshot. Duplicate the file. Work on v2.

2. Define the grain. One row = one invoice, one student, one SKU. AI cannot fix a sheet that mixes those.

3. Normalize. Dates, currencies, and categories first. Ask AI for a mapping table; you approve the map.

4. Formulas on a sample. Compute by hand for 5 rows. Only then fill down.

5. Pivot last. Summaries on dirty data look professional and are wrong.

6. Document. A one-tab “Data dictionary” beats a clever prompt you will forget.

Prompts that produce usable formulas

Include:

• exact column letters and header names;
• the spreadsheet product (Excel vs Sheets—functions differ);
• locale (decimal commas, mm/dd vs dd/mm);
• what to do with blanks and errors;
• a worked example: input → expected output.

Example:

Excel 365. Table Sales with [Date], [Rep], [Amount]. Return year-to-date amount for the rep in G2 as of today. Use SUMIFS. If Date is blank, ignore the row. Show one example.

Analysis without hallucinated insights

When you ask for “insights,” require this structure:

1. Metric and exact cell/range.
2. Comparison (vs last period, vs target).
3. Possible causes that are hypotheses.
4. A chart you can reproduce.

If the model cannot point to a range, discard the sentence. This is the spreadsheet version of reducing hallucinations.

Worked example: aging invoices

Suppose columns are InvoiceDate, Amount, Status. You want “Open and older than 30 days.”

Ask the model for a formula that uses those headers, then test on three rows you computed by hand. In Excel 365 a typical shape is FILTER or SUMIFS with a date threshold. In Sheets, confirm TODAY() and date serials behave as you expect.

Then ask: “What happens if Status is blank? If InvoiceDate is text?” Put IFERROR or a validation column next to the smart formula. The extra column is your audit trail.

Automation after the sheet

Once cleanup is reliable, you can trigger alerts with n8n, Zapier, or Make. Keep the calculation in the spreadsheet or a warehouse; let automation only notify.

FAQ

Is there a free AI for Excel formulas?

ChatGPT, Claude, and Gemini free tiers can write formulas from a pasted sample. In-grid Copilot/Gemini features usually need a paid workspace plan. Always test on a copy.

Can AI build my entire financial model?

It can scaffold labels and simple projections. It should not be the only reviewer of a model you will use to hire, price, or raise money.

Why does the AI formula work in Excel but not Sheets?

Function names, array behavior, and date systems differ. State the product in the prompt and re-test.

Is it safe to upload a workbook to Copilot?

Follow your employer’s data rules. Tenant-grounded Copilot is not the same as a consumer chatbot, but you should still minimize sensitive columns.

Bottom line

Use AI in Excel and Google Sheets to write formulas, clean categories, and draft explanations—then verify against cells. Keep money math deterministic, work on copies, and never paste regulated data into a personal chat. Practice in AI for Excel, then continue with data AI tools, AI automation workflows, and guides.

Sources and further reading

• Microsoft: Copilot in Excel
• Google: Gemini in Sheets
• Microsoft: Excel functions
• Google: Sheets function list