Excel and Google Sheets Formulas with AI

Spreadsheets are still how most of the world does analysis. A mid-sized company will have hundreds of Excel workbooks and Google Sheets running alongside any "proper" BI tool, and analysts are expected to write formulas that work the first time. AI is unusually good at spreadsheet formulas — both because the syntax is well-documented and because the problems are self-contained.

This lesson shows how to use ChatGPT, Gemini, and Microsoft Copilot inside your spreadsheet for formulas, pivots, and workbook automation.

What You'll Learn

• How to describe a sheet to AI so it writes a working formula first try
• Advanced patterns: LAMBDA, LET, dynamic arrays, and XLOOKUP
• Using Microsoft Copilot in Excel and Gemini in Google Sheets for native help
• Cleaning and restructuring messy sheets with AI

Describing a Sheet to AI

The most common failure when prompting for a formula is failing to describe the sheet. AI cannot see your workbook unless you are using in-product Copilot. You need to tell it:

• The tool (Excel or Google Sheets)
• The sheet name and the range
• Column headers and the data types in each
• The cell where the formula should live
• The exact output you want

Template:

Tool: Google Sheets.

Sheet: Orders.

Columns: A = OrderID (text), B = OrderDate (date), C = Customer (text), D = Product (text), E = Revenue (number, USD), F = Status (text: completed, refunded, pending).

Data starts in row 2. About 4,500 rows.

I want a formula in cell H2 that returns total completed revenue in March 2026, excluding refunded orders. The customer must be in the VIP_List named range.

The answer is a one-shot SUMIFS that works, and the AI will explain each argument.

Advanced Patterns That Save Hours

XLOOKUP over VLOOKUP

If you are still writing VLOOKUP, AI will helpfully upgrade you. Prompt:

Rewrite this VLOOKUP as an XLOOKUP with the following improvements: support match on text that might have trailing whitespace, return the product name (column D) from a match in column B, and return "Not found" instead of #N/A if nothing matches.

LAMBDA for reusable helpers

Both Excel and Google Sheets support LAMBDA, which lets you define reusable formula functions. Example:

I calculate "days between two dates excluding weekends and US holidays" in 15 places in my workbook. Write a LAMBDA named BIZ_DAYS that takes a start date, end date, and optional holiday range. Show me how to save it with Name Manager in Excel and with Named Functions in Google Sheets. Include three test cases with expected outputs.

Dynamic arrays

Dynamic arrays (FILTER, SORT, UNIQUE, SEQUENCE) are the modern way to build reports that grow automatically. Prompt:

Using dynamic arrays in Google Sheets, write a formula in cell J2 that returns the top 10 products by total revenue in Q1 2026, sorted descending. Output should be two columns: product name and revenue, with the header row included.

LET for readability

Long formulas become unreadable. LET lets you name intermediate variables:

Rewrite this 180-character SUMIFS formula using LET so each argument has a named variable. The formula should still return the same number. Add a comment at the top explaining what each variable represents.

Pivot Tables from Natural Language

Both Microsoft Copilot in Excel and Gemini in Google Sheets can build pivots from a description:

In Excel, using Copilot, build a pivot table from Orders that shows total revenue by product (rows) and month (columns) for 2026, with a grand total row and column. Format revenue as currency with no decimals.

If you are without in-product AI, describe the shape and ask for step-by-step instructions:

Give me click-by-click instructions to build a pivot in Google Sheets with products as rows, quarters as columns, completed revenue as values, and a slicer for country. Include the menu paths.

Cleaning Messy Sheets

Analysts inherit horrific spreadsheets. Typical rescue prompt:

I have a Google Sheet where someone entered dates as text in this format: Mon 14 Mar 2026. Write a formula in a new column B that converts the text in column A into a real date value that I can sort and filter. Handle edge cases: leading/trailing spaces, capitalization variants, and occasional typos where the weekday does not match the date.

Other common cleanup tasks AI is great at:

• Splitting "Last, First" into separate first and last name columns with TEXTSPLIT or SPLIT
• Removing non-numeric characters from "revenue" cells that contain $ and ,
• Flattening a pivoted range back into a long-format table
• Detecting duplicate rows with fuzzy matching on key columns

Using Microsoft Copilot in Excel

If your organization has Microsoft 365 Copilot, you have a formula and analysis assistant built into Excel. Good prompts:

• "Highlight cells in column E that are outliers using a conditional format."
• "Add a column that calculates month-over-month growth for the Revenue column."
• "Summarize what this sheet shows in three bullet points I can paste into an email."
• "Sort the Orders table by customer, then by date descending, and highlight the first row per customer."

Copilot sees the actual data and can modify the sheet in place. This is safer and faster than pasting data into ChatGPT for simple tasks.

Using Gemini in Google Sheets

Gemini integrated into Google Workspace can read the active sheet and help similarly. Access it via the sidebar ("Help me organize" / "Help me analyze") or by typing @Gemini in a cell:

• "Create a summary of this sheet's key metrics with charts."
• "Suggest three different ways to visualize this data."
• "Write a formula that returns the median order value per country."

Auditing Formulas You Inherit

When you open a workbook and find a cell with a 300-character nested INDEX/MATCH/IFERROR, paste it into Claude:

Explain this Excel formula in plain English, step by step, as if teaching a new analyst. Identify any edge cases where it would return an incorrect result. Suggest a cleaner rewrite using modern functions (XLOOKUP, LET, FILTER) and explain the improvements.

This converts inherited workbook archaeology from a half-day task into a 10-minute review.

When to Leave the Spreadsheet

A warning: AI can make you comfortable inside spreadsheets far past the point where you should have moved to SQL or pandas. Signs you should migrate:

• The workbook is larger than 50MB or has more than 500,000 rows
• Formulas take more than a few seconds to recalculate
• You are manually refreshing data from multiple sources
• Multiple people edit the same workbook at once and overwrite each other
• You are doing joins across more than three tables

At that point, use AI to generate the SQL or pandas equivalent of your spreadsheet logic and move the analysis upstream.

Key Takeaways

• Describe the sheet fully — tool, range, columns, types, output cell
• Use LAMBDA, LET, dynamic arrays, and XLOOKUP for cleaner modern formulas
• In-product Copilot (Excel, Sheets) is faster and safer for tasks on live data
• AI excels at rewriting inherited mystery formulas into readable ones
• Know when to leave the spreadsheet for SQL or pandas