How to Use AI in Excel and Google Sheets: Formulas, Cleanup and Summaries

Most people use about a tenth of what their spreadsheet can do, mostly because the rest requires remembering exact formula syntax. AI changes that. You can now describe what you want in plain English, such as “total sales by region for 2026” or “flag duplicate emails,” and let the spreadsheet build it.

Both Microsoft Excel and Google Sheets have AI assistants built in. This guide walks through the most useful tasks in each: writing formulas, cleaning messy data and summarizing results, plus the checks that keep AI from quietly putting a wrong number in your report.

What you need before you start

For Excel (Copilot in Excel):

• A qualifying plan. Microsoft lists options including a commercial Microsoft 365 Copilot subscription, a Copilot Chat-eligible Microsoft 365 business or enterprise subscription, Microsoft 365 Premium, or Microsoft 365 Personal or Family with an AI credits plan. Features vary by plan.
• A file in the cloud. Save the workbook to OneDrive or SharePoint.
• A modern file format such as .xlsx. Strict Open XML files are not supported.
• Automatic calculation turned on, which Copilot needs to edit your workbook.

For Google Sheets (Gemini in Sheets):

• An eligible Google Workspace or Google AI plan.
• A native Google Sheets file. Google says Gemini works best with native Sheets files, so convert .xlsx files first with File, Save as Google Sheets.
• Smart features enabled. In a work account, your admin controls this.

If your company is still deciding on Microsoft’s AI licensing, see our guide on whether Microsoft 365 Copilot is worth it.

A quick note on the retired COPILOT function

If you read older tutorials, you may see a =COPILOT() cell formula for Excel. Microsoft retired it: as of September 14, 2026, the COPILOT function is no longer available. Existing results stay as cached values, but cells that recalculate show a #NAME? error. Microsoft recommends asking Copilot in Excel in plain language instead. If you have workbooks that used it, replace those cells before they recalculate.

Google Sheets still offers a cell-level AI function, covered below.

What each tool can do

Microsoft also describes 3 modes in Copilot in Excel: edit (changes the workbook directly), plan (proposes steps before editing) and chat (answers in the pane without changing anything). Start with plan or chat until you trust the results.

Walkthrough 1: Write a formula you cannot remember

In Excel:

1. Open your workbook and select the Copilot icon.
2. Describe the result, naming the columns: “Add a column that calculates total revenue for each row as Quantity times Unit Price, minus Discount.”
3. Review the formula Copilot proposes and the explanation. Accept it if it looks right.
4. Spot-check 3 rows by hand.

In Google Sheets:

1. Open the side panel with Ask Gemini.
2. Ask: “Write a formula in column F that returns ‘Overdue’ if the due date in column D is before today and status in column E is not ‘Paid’.”
3. Insert the result and check it against a few rows where you know the answer.

Prompt tips:

• Name columns by header, not just letter.
• State edge cases: “Ignore blank rows” or “Treat empty discount as 0.”
• Ask it to explain the formula. If the explanation does not match your intent, the formula will not either.

Walkthrough 2: Clean up messy data

Imported data is rarely tidy. AI handles the tedious parts well.

Common cleanup requests (work in either tool):

• “Highlight duplicate email addresses in column C.”
• “Split the Full Name column into First Name and Last Name.”
• “Standardize the State column to 2-letter abbreviations.”
• “Convert the Date column to the format MM/DD/YYYY.”
• “Flag rows where Phone is missing or has fewer than 10 digits.”

Using the =AI() function in Google Sheets for row-by-row tasks:

The AI function takes a prompt and an optional range. For example, in cell C2:

=AI(“Categorize this customer comment as Praise, Complaint or Question”, B2)

Then fill down. Google’s help notes that only the first 200 selected cells with AI functions are generated at a time, so work in batches on larger sheets.

Key takeaway: Always work on a copy of your data when you let AI clean or transform it, and spot-check results against rows you know. AI cleanup is fast, but a silent error copied down 5,000 rows is expensive.

Walkthrough 3: Summarize and analyze

This is where AI saves the most time for managers and owners.

Example requests:

• “Summarize sales by region and month in a PivotTable, then chart the top 5 regions.”
• “What are the 3 biggest changes between Q2 and Q3 in this data?”
• “Which products have declining sales for 3 months in a row?”
• “Write a 5-bullet summary of this sheet for my weekly report.”

Steps in either tool:

1. Format your data as a table with clear headers and no merged cells.
2. Ask a specific question rather than “analyze this.”
3. Ask the AI to show its work: the formula, filter or pivot it used.
4. Verify the headline numbers with a quick SUM or filter of your own.

A known limit in Google Sheets: charts Gemini creates in the side panel are not linked to the source data and do not update when data changes. Insert them, then rebuild as a regular chart if you need it to stay current. Also, Gemini’s side panel conversation is lost if you reload or close the sheet, so insert useful output promptly.

Get better results: prepare your spreadsheet

AI reads your sheet much like a new colleague would. Make it easy:

• 1 header row with clear, unique column names
• No merged cells or blank rows in the middle of data
• Consistent formats within each column (dates as dates, numbers as numbers)
• Data in tables (Excel: Insert, Table) where possible
• Separate raw data from reports on different tabs

Privacy and accuracy checks

• Use work accounts for work data. Business plans from Microsoft and Google come with commercial data protections. Do not paste company spreadsheets into personal AI accounts.
• Watch for sensitive data. Customer financial or health details should only go into tools your company has approved for them.
• Verify every number you will act on. AI can misread a column, skip blanks or apply the wrong filter. Google’s own help warns that Gemini’s suggestions may be inaccurate.
• Keep formulas visible. Prefer AI-generated formulas you can inspect over pasted static values, so others can audit the work.

For more on everyday AI review habits, see how to use AI at work.

Where to start: 5 tasks to try this week

1. Pick a spreadsheet you update every week, such as a sales tracker or expense log, and make a copy.
2. Ask AI to explain the most complex formula in it. This builds trust and helps you learn.
3. Have it clean 1 column, such as standardizing dates or splitting names.
4. Ask for a summary or PivotTable you would normally build by hand.
5. Compare time and accuracy with your usual method, then save the prompts that worked.

Not sure which assistant your team should standardize on? Our comparison of ChatGPT vs Claude vs Gemini covers plans and data controls, and our glossary explains common AI terms.

The bottom line

AI in Excel and Google Sheets turns spreadsheet work from remembering syntax into describing results. Use Copilot in Excel on cloud-saved workbooks and Gemini or the =AI() function in Sheets for formulas, cleanup and summaries. Prepare your data with clear headers, work on copies and check results against numbers you know. Done that way, you get faster reports without trading away accuracy.

Sources

• Frequently asked questions about Copilot in Excel (Microsoft Support) — capabilities, modes, license and file requirements
• COPILOT function (Microsoft Support) — retirement on September 14, 2026 and recommended alternative
• Collaborate with Gemini in Google Sheets (Google Docs Editors Help) — Gemini side panel capabilities and limits
• Use the AI function in Google Sheets (Google Docs Editors Help) — =AI() syntax, plan requirements and 200-cell generation limit