Stop Typing Formulas: 12 AI Prompts That Turn Excel and Google Sheets Into an Analyst's Copilot
Every spreadsheet user knows the feeling: a report is due in an hour, and you are still debugging a nested IF wrapped in SUMIFS while your cursor blinks accusingly. According to Microsoft's own documentation, Excel now ships over 500 functions — and nobody memorizes them all. Meanwhile, Google Sheets supports a slightly different subset, so a formula that works in one tool often breaks in the other. That gap is exactly where large language models (LLMs) like ChatGPT, Claude, and Gemini earn their keep: they do not replace your judgment, but they draft the syntax, explain the logic, and catch the edge cases you forgot.
This guide is a working cheat sheet for analysts, marketers, and finance teams. Each prompt below is copy-paste ready. You paste your real column names and sample rows, and the model returns a formula, a pivot layout, or a dashboard plan — plus a plain-English explanation so you can trust what you ship. We follow the same discipline that Microsoft and Google recommend in their official function references: name your ranges, test on a small sample, and never trust an AI formula blindly.
A quick word on trust: LLMs hallucinate function names. Always verify against the Microsoft Excel function reference or the Google Sheets function list. Treat the prompt as a first draft, not gospel.
1. The VLOOKUP-to-XLOOKUP Translator
Task: Modernize legacy lookup formulas. XLOOKUP (Excel 365) and its Sheets cousin are faster and do not break when you insert a column.
Prompt:
You are an Excel expert. I have this legacy formula: =VLOOKUP(A2,Sheet2!$A:$D,4,FALSE).
Rewrite it as XLOOKUP. Explain the if_not_found argument and the match_mode options. Then give me the Google Sheets equivalent.
Example: Column A holds OrderID, Sheet2 maps OrderID → Region. The model returns =XLOOKUP(A2,Sheet2!A:A,Sheet2!D:D,"Not found"). In Sheets you would use =IFERROR(VLOOKUP(...),"Not found") or =XLOOKUP if the function is enabled.
2. Multi-Condition Sums Without Tears
Task: SUMIFS / COUNTIFS with date ranges and wildcards.
Prompt:
Write a SUMIFS that sums column F (Revenue) where column B (Region) = "EMEA", column C (Status) = "Closed Won", and column A (Date) is between 2026-01-01 and 2026-03-31. Show the same in Google Sheets.
Example: =SUMIFS(F:F,B:B,"EMEA",C:C,"Closed Won",A:A,">="&DATE(2026,1,1),A:A,"<="&DATE(2026,3,31)). The AI will remind you that date criteria must be concatenated with &, a classic beginner trap.
3. Dynamic Arrays: FILTER, SORT, UNIQUE
Task: Build a self-updating list instead of dragging formulas down 10,000 rows.
Prompt:
I have a table with columns Customer, Country, Revenue. Give me a dynamic array formula that returns the top 10 customers by revenue, sorted descending, with no blanks. Works in Excel 365 and Google Sheets.
Example: =SORT(FILTER(A2:C1000,C2:C1000<>""),3,-1) gives a sorted non-empty table; wrap in TAKE(...,10) in Excel 365 for the top ten. Sheets supports SORT and FILTER natively.
4. Pivot Table Blueprint
Task: You know the question but not the field layout.
Prompt:
I have sales data: Date, Region, Product, Salesperson, Units, Revenue.
Design a pivot table that shows monthly revenue by region with a % of grand total column. Tell me exactly which field goes in Rows, Columns, Values, and Filters.
Example: Rows = Region, Columns = Month (grouped from Date), Values = Sum of Revenue, then add a calculated field or "Show Values As → % of Grand Total". The AI walks you through the Field Settings dialog step by step.
5. Data Cleaning: Dedupe and Normalize
Task: Messy CRM exports with trailing spaces, mixed case, and duplicate emails.
Prompt:
My column A has customer emails with trailing spaces and inconsistent case. Give me a formula to normalize them and a helper column that flags duplicates. Explain each step.
Example: =LOWER(TRIM(A2)) for normalization, then =COUNTIF(B:B,B2)>1 to flag duplicates. For fuzzy matching, the AI can suggest =IF(ISNUMBER(SEARCH("gmail",A2)),"Gmail","Other").
6. Merging Two Tables Without Power Query
Task: Combine two exports on a shared key when you cannot install add-ins.
Prompt:
I have Table1 (ID, Name) and Table2 (ID, Department). Give me a formula-only way to produce a merged table with Name and Department side by side, skipping IDs that appear in only one table.
Example: Use =FILTER(Table1,ISNUMBER(MATCH(Table1[ID],Table2[ID],0))) and a parallel XLOOKUP for the department. The model explains why MATCH with 0 is the safe exact-match mode.
7. Conditional Formatting Rules
Task: Highlight rows where a deadline is within 7 days.
Prompt:
Write a conditional formatting formula for Excel that highlights an entire row (A:F) when the date in column D is within the next 7 days and column F is not "Done". Give the Google Sheets version too.
Example: =AND($D2<=TODAY()+7,$D2>=TODAY(),$F2<>"Done"). The AI reminds you that the row reference must be relative ($D2, not $D$2) so the rule applies down the sheet.
8. Dashboard Layout Planner
Task: Turn a raw dataset into a one-screen KPI dashboard.
Prompt:
I have monthly sales data for 3 regions. Design a single-sheet dashboard with: KPI cards (total revenue, MoM growth, top region), a line chart of revenue over time, and a bar chart by region. List the helper cells and chart configs I need.
Example: KPI card in B2: =SUM(Sales[Revenue]); MoM growth in B3: =(B2-PREVIOUS)/PREVIOUS using a helper cell for last month. The AI specifies chart types, axis labels, and where to place slicers.
9. Excel → Google Sheets Migration Check
Task: Your workbook uses functions Sheets does not support identically.
Prompt:
Here is my Excel formula list. Tell me which ones break in Google Sheets and give the Sheets replacement for each: ARRAYFORMULA, QUERY, IMPORTRANGE, and any Excel-only functions.
Example: Excel's SUMPRODUCT works in Sheets, but ARRAYFORMULA wraps whole-column logic differently. IMPORTRANGE has no direct Excel equivalent, and QUERY is Sheets-only. The AI produces a side-by-side table.
10. VBA to Apps Script Translation
Task: Automate a recurring report without a local macro.
Prompt:
Convert this VBA macro to Google Apps Script. It loops through rows, copies rows where Status = "Overdue" to a new sheet, and emails a summary. Keep the logic identical and add comments.
Example: VBA's For Each cell In Range becomes sheet.getRange(...).getValues().forEach(...) in Apps Script. The model flags that MailApp.sendEmail requires authorization and daily quotas.
11. Formula Debugger
Task: A formula returns #N/A or #VALUE! and you cannot see why.
Prompt:
This formula returns #VALUE!: =SUMPRODUCT((A2:A100="EMEA")*(B2:B100)). Explain the likely cause and give a corrected version.
Example: Multiplying a boolean array by text causes the error; the fix coerces with -- or uses SUMIFS. The AI explains error types (#N/A, #REF!, #DIV/0!) one by one.
12. Recurring Report Template Generator
Task: Build a reusable monthly report skeleton.
Prompt:
Generate a monthly reporting template for a marketing team: sheet tabs for Raw Data, Cleaned Data, Pivot, and Dashboard. For each tab, list the headers, key formulas, and update steps.
Example: The AI returns a four-tab blueprint with =QUERY('Raw Data'!A:F,"select A, sum(F) group by A") for the Pivot tab and a checklist for the monthly refresh. This is the prompt that saves the most time, because it standardizes the whole workflow.
Practical Guardrails
A few rules from real deployments: always test on 10 rows before applying to 100,000; keep a backup copy before letting an AI rewrite a sheet; and never paste confidential data into a public model unless your company policy allows it. The OWASP Top 10 for LLM Applications lists prompt injection and data leakage as real risks — relevant when your spreadsheet holds payroll or customer PII.
Used this way, AI becomes a translator between your intent and syntax, not a replacement for understanding your data. The best analysts still decide what the numbers mean; the model just removes the typing.
Try one prompt today — start with the pivot blueprint (#4) or the recurring template (#12). Both give immediate, visible wins, and once you see the pattern, you will never go back to hand-typing VLOOKUP again.
Comments