AI for Excel + Google Sheets: 9 things Claude/ChatGPT do that change your week

The nine workflows

Each in detail.

1. “Write me this formula”

The bread and butter. Stop Googling, just ask.

Prompt template:

Write me an Excel formula for [thing]. My data is in [columns/rows]. The output should go in [cell]. Australian English, comma separator (not semicolon).

Real example:

Write me an Excel formula. I have customer orders in column A (date), column B (customer name), column C (order amount in AUD), column D (status). I want column E to show “FOLLOW-UP” if status is “Pending” AND the order date in column A is more than 14 days ago. Otherwise blank.

Result: =IF(AND(D2="Pending",TODAY()-A2>14),"FOLLOW-UP","")

Paste, copy down the column, done. 15 seconds of work that would have been 10 minutes of trial and error.

2. “Why is this formula broken?”

The companion workflow. Excel error messages are useless. AI is great at debugging them.

Prompt template:

Here’s a formula that’s throwing [error]: [paste formula]

The data context is: [explain what cells/columns are]

What’s wrong and how do I fix it?

Real example:

Here’s a formula throwing #N/A: =VLOOKUP(B2,'Customers'!A:F,3,FALSE). My Customers sheet has customer names in column A, email in B, phone in C, etc. B2 in my current sheet contains “Sarah Khan”. Why isn’t VLOOKUP finding her?

Result: The AI checks four likely causes: (1) trailing whitespace in either lookup value or source, (2) case sensitivity (VLOOKUP is case-insensitive but data type isn’t), (3) the source column might not actually be column A, (4) “Sarah Khan” might have a non-breaking space character.

Saves you 30 minutes of squinting at your own formula.

3. Clean messy data

You exported a CSV from a tool. It’s a mess. Names are inconsistent, dates are wrong format, half the phone numbers have country codes and half don’t. AI can fix this in one prompt.

Prompt template:

I’m pasting messy data. Clean it according to these rules:

• [Rule 1]
• [Rule 2]
• [Rule 3]

Output a clean version as a markdown table.

Data: [paste]

Real example:

I’m pasting customer data exported from our old CRM. Clean it:

• Names: title case, e.g. “sarah khan” → “Sarah Khan”
• Phone numbers: convert to +61 format, e.g. “0412345678” → “+61 412 345 678”
• Email: lowercase
• Remove duplicate rows where email matches
• Sort by name A-Z

Output as a markdown table.

Data: [paste 50 rows]

You get a cleaned table back in 10 seconds. Copy back into Excel. For volumes over 1000 rows, upload as an Excel file instead of pasting.

4. Build pivot tables in plain English

Pivot tables are great when you know how to build them. They’re frustrating when you don’t.

Prompt template:

I have an Excel file with [describe data]. Walk me through building a pivot table that shows [what you want]. Step by step, with exact menu clicks.

Real example:

I have 18 months of Shopify orders exported as a single Excel sheet. Columns: Date, Order ID, Customer email, Product, Quantity, Total AUD. I want a pivot table that shows total revenue by month for each product. Walk me through it step by step.

Result: Click-by-click instructions for Excel 2024+ on Mac and Windows. Done in 2 minutes instead of 20.

5. Explain what someone else’s spreadsheet does

The hardest spreadsheet task: inheriting a model someone else built and trying to figure out what it does.

Prompt template:

Here’s a formula from a spreadsheet I inherited: [paste]

Explain in plain English: what is this calculating, what assumptions does it make, and what would I need to know to modify it?

Real example:

Here’s a formula from my predecessor’s financial model:

=IF(EOMONTH(B2,0)<=$E$1,IFERROR(INDEX('Rates'!$B:$B,MATCH(YEAR(B2)&MONTH(B2),'Rates'!$A:$A,0)),"")*C2*(1-D2),"")

Explain what it does and what each piece references.

Result: A clear walkthrough explaining it’s calculating monthly billable amounts using a date-keyed rate lookup, applying a discount factor from column D, and gating the whole thing to only fire for months before a cutoff date in E1. Plus a note that the formula will silently produce empty cells if the rate lookup fails — a bug worth flagging.

15 minutes of confused spreadsheet archaeology becomes a one-minute conversation.

6. Generate test data

You’re building a new spreadsheet model. You need realistic test data to validate it.

Prompt template:

Generate me a realistic test dataset:

• [N rows]
• Columns: [list with formats]
• Distribution: [any constraints, e.g. “skew towards smaller orders”]
• Australian context (suburbs, names, AUD amounts)

Output as a markdown table.

Real example:

Generate 30 rows of realistic test data for a Melbourne hospitality business:

• Date (April 2026, daily)
• Reservations (1-12, weighted higher on Fri/Sat)
• Average spend per cover (AUD, $40-$80 weighted around $55)
• Daily revenue
• Online vs walk-in split (60/40 ratio)

You get a clean test dataset in 10 seconds. Far faster than typing it.

7. Build charts from messy data

Excel’s chart wizard works. It’s just slow. AI gives you the chart design in one prompt.

Prompt template:

I have data: [paste summary or upload file]. I want a chart that shows [insight]. What chart type is best, and walk me through building it in Excel.

The model tells you the right chart type (e.g. “stacked column for revenue mix by channel, with a line overlay for total”), the cells to select, and the formatting tips. Saves the experimentation.

8. Sensitivity + scenario analysis

This is where AI shines. Plain-English scenario modelling.

Prompt template:

I have a financial model with these inputs: [list values]. Walk me through a sensitivity analysis:

• What happens if [variable A] changes by ±20%?
• What happens if [variable B] changes by ±50%?
• What’s the break-even point on [metric]?

Show your reasoning, then give me the Excel formula or workflow to do this in-sheet.

You get a structured analysis with break-even points, sensitivity tables, and the formulas to drop into your model.

9. Convert between Excel and Google Sheets

Often there’s no native equivalent. You need a translation.

Prompt template:

Convert this Excel formula to Google Sheets: [paste]. The data context is [describe]. Australian regional setup (commas as separators).

Excel’s XLOOKUP becomes Sheets’ formula combination. Excel’s LAMBDA becomes Sheets’ LAMBDA. Excel’s structured table references become A1 references. AI handles it in seconds.

The privacy bit

Two things to know before uploading your real spreadsheets:

1. Paid plans only for sensitive data. Free Claude.ai and free ChatGPT may use your uploads for training. Plus/Pro/Team/Pro plans don’t. Pay the $30 AUD.
2. Anonymise PII first if it’s regulated. Client names, contact info, financial account numbers, anything covered by Privacy Act. Replace with placeholders for the upload, fix back in the source.

For Australian businesses handling regulated data (health, legal, finance), use the API or Azure OpenAI Service Australia East region. Same models, data stays onshore.

What’s NOT in this guide

• Power BI / Power Query: AI is competent here too, but the workflow is different (DAX formulas, M code). Coming as a separate piece.
• Apps Script for Google Sheets: AI is excellent at writing Apps Script. See our Claude Code guide for the IDE-driven flow.
• Building full models from scratch: AI helps section by section. Not reliable for end-to-end financial models with circular references.
• Real-time data refresh: out of scope for prompt-based AI. Use Power Query or Sheets Apps Script for live API pulls.

What’s next

• How to write better AI prompts for the foundational prompt-engineering patterns.
• How to use AI for SEO for marketing-side AI workflows.
• AI for Australian accountants for the bookkeeping deep-dive.

If you want help setting up AI-powered spreadsheet workflows for your team (Custom GPTs trained on your data + processes), our Quick Start build at $497 AUD includes that as a standard scope.