AI for Excel and spreadsheets: formulas, cleanup, and the never-trust-the-total rule

The one habit that fixes most spreadsheet prompts

Paste the column headers and three real-looking rows. Every time.

"Write a formula to calculate commission" produces a formula for a spreadsheet the model imagined. "Column A is Rep, B is Region, C is Q3 Sales as a number, D is Quota; here are three rows; write a formula for E that pays 5 percent below quota and 8 percent at or above" produces one for yours. Most formula failures come from the model guessing at your layout: text where it expected numbers, dates stored as strings, a header row it did not know about, merged cells.

This guide covers formulas, cleanup, explaining an inherited sheet, code (Power Query, Apps Script, VBA), the AI built into Excel and Sheets, uploading files to chat tools, and the habit that keeps you employed: never trust the total.

Formulas

A prompt that gets a working formula

You are an Excel expert. I am using [EXCEL 365 / EXCEL 2019 / GOOGLE SHEETS].

My sheet is named [SHEET NAME]. Headers are in row 1, data starts in row 2. Columns:
A: [HEADER, DATA TYPE, EXAMPLE VALUE]
B: [HEADER, DATA TYPE, EXAMPLE VALUE]
C: [HEADER, DATA TYPE, EXAMPLE VALUE]

Three sample rows:
[PASTE 3 ROWS, TAB OR COMMA SEPARATED]

I want a formula in column [LETTER] that: [PLAIN-ENGLISH RULE, INCLUDING WHAT TO DO WHEN DATA IS BLANK OR INVALID].

Give me:
1. The formula for row 2, ready to fill down.
2. A one-line explanation of each function used.
3. The expected result for each of my three sample rows so I can check it.
4. Anything about my data that could break the formula (numbers stored as text, blanks, dates as text).

If anything is unclear, ask before answering.

Point 3 is the trick. When the model shows what it expects for your sample rows, you can compare against what the sheet actually returns. Disagreement means either the formula or the model's understanding of your data is wrong, and both are worth knowing.

Worked example: tiered commissions

A sales manager has a sheet with Rep, Region, Q3 Sales, and Quota. Commission is 4 percent below quota, 6 percent from quota up to 120 percent of quota, and 8 percent above that, calculated on the whole amount.

With the headers and three rows in the prompt, the model returns a formula built on IFS, with the attainment ratio computed once using LET and an IFERROR wrapper for blank quotas. It also shows the three expected values. She fills the formula down and checks the three rows: two match, one does not. The mismatch row has Q3 Sales stored as text (a stray space from the export). The model's point 4 had warned about exactly that. Ten seconds with VALUE and TRIM, and the column ties out.

Without the sample rows, she would have had a formula that was right in theory and wrong on the one row that mattered.

Explaining a formula you inherited

Paste the formula and ask: "Explain this formula in plain English, one clause at a time. Then tell me what it returns when [CONDITION] and what could make it return an error." For monster formulas with nested IFs, add "rewrite it using LET so the intermediate steps have names." The rewrite is usually easier to audit than the original.

Cleaning messy data

Exports are where the mess lives: leading spaces, dates as text, "N/A" in numeric columns, names in ALL CAPS, one column holding city and state together.

Describe the mess with examples and ask for a fix in the tool you have:

• Formulas for one-off fixes: TRIM and CLEAN for whitespace and hidden characters, VALUE and DATEVALUE for text that should be numbers or dates, TEXTSPLIT (Excel 365) or SPLIT (Sheets) for combined columns, PROPER for casing.
• Power Query (Excel: Data tab, Get and Transform Data) for anything you will repeat monthly. Ask the AI for the sequence of steps by name ("Trim, Change Type to Date using locale en-US, Split Column by Delimiter") or for the M code to paste into the Advanced Editor. The step list is easier to verify than the code.
• A chat tool with file upload for a one-time deep clean. Upload the CSV and ask for a cleaned file plus a change log (what was changed, in which rows, and why). Read the change log before the file.

Always keep the raw export untouched in its own sheet or file. Cleaning is destructive; the original is your only way to prove what was done.

Explaining a whole workbook

You inherit a workbook with nine tabs and no documentation. Two ways to get oriented:

1. Upload the file to a chat tool and ask: "Map this workbook. For each sheet: what it contains, which sheets it pulls from, and which formulas reference other sheets. Then list every hard-coded number that sits inside a formula, with its location, because those are the assumptions somebody typed in and forgot."
2. If you cannot upload the file, switch on Show Formulas (Formulas tab), copy the formulas from the tab that matters with their cell addresses, and ask the same question about that tab.

The hard-coded-numbers list is often the most valuable output. A tax rate typed into a formula in 2022 is a landmine, and the model will find it faster than you will.

Writing code: Power Query, Apps Script, and VBA

AI is very good at writing spreadsheet code, because the languages are well documented and the tasks are repetitive. The rules: say exactly which environment, give the layout, ask for comments, and run it on a copy.

Apps Script for Google Sheets

Open Extensions, then Apps Script, and paste what the model gives you. A prompt that works:

Write a Google Apps Script function for a Google Sheet. The sheet "Orders" has headers in row 1: Order ID (A), Customer Email (B), Status (C), Ship Date (D). Every weekday at 8 am, find rows where Status is "Shipped" and Ship Date is yesterday, and send each customer one email using the template below. Add a column E "Notified" and write a timestamp there so no customer is emailed twice. Include a function that installs the time-based trigger. Comment every block. Do not use any library outside the built-in Apps Script services.

Email template:
[PASTE TEMPLATE WITH PLACEHOLDERS]

Test with your own email address in column B first. Email-sending scripts are the classic way to send 400 messages you did not mean to send.

Power Query M

Ask for the M code for a specific transformation and paste it into the Advanced Editor (Home tab in the Power Query window). If it errors, paste the error message back verbatim; the model fixes M errors well because the messages are descriptive.

VBA

VBA still runs a great deal of business. Ask for a macro with the sheet layout, the exact trigger (a button, opening the file, a keyboard shortcut), and error handling. Then, before running it:

• Save a copy of the workbook. Macros that delete or overwrite cannot be undone with Ctrl+Z.
• Read the code for anything touching other files, the file system, or Outlook. Ask "what does this macro do outside this workbook?" and expect the answer "nothing."
• Save as .xlsm, and expect IT's macro security settings to have an opinion; many organizations block unsigned macros, reasonably.

Copilot in Excel

Microsoft 365 Copilot in Excel sits in a side pane and works best when your data is a formatted table (select the range, press Ctrl+T). It suggests and inserts formula columns, sorts, filters, highlights, adds conditional formatting, proposes charts and pivot tables, and answers questions about the table. With Python in Excel, it can also run analysis in Python inside the workbook. Microsoft has also been rolling out a COPILOT function that runs a prompt inside a cell; availability depends on your version and tenant, so check what you have.

Where it is strongest: "add a column that flags orders over 30 days old," "highlight the top 10 percent by margin," "show me sales by region as a bar chart." Where it is weaker: anything spanning multiple sheets, unstructured ranges, and the messy exports above. Fix the data first, then ask.

Copilot inserts formulas you can read. Read them; it is a suggestion engine, wrong in the same ways any assistant is wrong.

Gemini in Google Sheets

Gemini in Sheets (the side panel, on Workspace plans that include it) generates formulas from a description, builds tables from a prompt, and analyzes a selected range with charts and observations. Google has also added an AI function that runs a prompt in a cell against a range (classify, summarize, generate text); availability varies by plan, so check the Sheets help pages for your account.

Same rules as Copilot: structured data in, and read every formula and chart number before trusting it.

Uploading CSVs to chat tools

For real analysis (segmenting customers, finding outliers, comparing two exports, building a pivot you cannot picture), upload the file to a chat tool.

ChatGPT runs Python on uploaded files and can show its code. Claude reads spreadsheets and can produce charts and calculations, and Anthropic also offers a Claude add-in for Excel on some plans. Julius AI is built specifically for this and is comfortable for non-programmers. The workflow is the same in all of them:

1. Upload the CSV or workbook.
2. Ask it to describe the file first: row count, columns, data types, blanks, duplicates, obvious problems. Compare the row count with your own.
3. Ask your question, and ask for the method along with the answer ("show the steps or code you used").
4. Ask for the result as a table you can paste back into your sheet, and re-derive at least one figure yourself.

A prompt for step 2:

"Profile this file before doing anything else: number of rows and columns, each column's name, inferred type, count of blanks, count of distinct values, and min and max for numbers and dates. Flag any column where the type looks inconsistent. Do not analyze yet."

If the row count it reports is not the row count you exported, stop. Something was truncated or misread.

Never trust the total

This is the rule that matters most, and it is the one people skip when the output looks polished.

AI tools produce numbers three ways: by writing a formula (reliable if the formula is right and the data is clean), by running code (reliable if the code did what you think), and by "reading" the data and answering in prose (unreliable; the model is estimating, not calculating). You often cannot tell which one happened. So verify every number that leaves the sheet:

• Rebuild the headline number yourself. A plain SUM, COUNT, or pivot table on the source column. If it does not match the AI's figure, the AI's figure is wrong until proven otherwise.
• Tie out row counts. Rows in the export, rows after cleaning, rows in the analysis. Any drop needs an explanation you can name.
• Cross-foot. Do the parts add to the whole? Regions sum to the company total, months sum to the year.
• Check one row end to end. Pick a row, follow it through every formula and step, and confirm the final value by hand.
• Ask the model to audit itself, then ignore its reassurance. "List every assumption you made and every row you excluded" produces a useful list. "Are you sure?" produces "Yes." Only the first is worth asking.

If a number goes into a report, a board deck, an invoice, or a filing, a human rebuilds it from the source. That is not distrust of the tool; it is the rule you would apply to a new analyst's first week.

Privacy and compliance

Spreadsheets are where the sensitive data is: customer lists, payroll, financials, patient schedules. Never paste confidential data into a consumer AI tool unless your organization has approved it. Prefer the AI built into your licensed Microsoft 365 or Google Workspace tenant, because the data stays under your organization's existing controls, and check whether a chat tool trains on uploads.

For finance and accounting teams, remember that numbers feeding audited statements fall under controls (SOX for public companies), and a spreadsheet produced by an unvetted tool is a control gap. For anything with personal data (PII), strip the identifying columns before upload: the analysis rarely needs the names. See AI privacy at work for the policy side.

Next steps

• Copy ready-made prompts from data and spreadsheets.
• Read the tool pages for Microsoft Copilot, Gemini, and Julius AI.
• See the role playbooks for accountants, financial analysts, and data analysts.
• Learn the verification routine in how to fact-check AI output, and why models get arithmetic wrong in AI hallucinations explained.
• Turn a monthly cleanup into a hands-off process with automate your workflow with AI.
• The course has a full data, spreadsheets, and analysis module with exercises.