You don't need to learn VBA, Power Query, or complex nested formulas to get real work done in Excel or Google Sheets. You need the right prompts. This library gives you 40 ready-to-use prompts across formula writing, data cleaning, pivot tables, charts, automation, and troubleshooting, so you can hand your spreadsheet problem to ChatGPT, Claude, or Copilot and get a usable answer in one shot.
Most people use AI for spreadsheets the way they use Google: type a vague question, get a vague answer, give up, and go back to manually dragging cells. The prompts below are built differently. Each one gives the AI enough structure (your data shape, your goal, your constraints) that it can return something you can paste in and actually use.
How to use this list well
A few habits separate people who get useful formulas from people who get broken ones.
- Always describe your actual data structure. Tell the AI your column headers, sheet names, and roughly how many rows you have. "Column A is Date, Column B is Region, Column C is Revenue" beats "I have a sales sheet."
- Always test formulas on a copy of your sheet first. Duplicate the tab before you paste anything in. AI-written formulas can reference the wrong range or misunderstand your intent.
- Never paste sensitive or confidential business data into consumer AI tools. Describe your data structure and give sample or dummy values instead of pasting real customer, financial, or HR records.
- Treat every AI-suggested formula as a draft. Check it against a result you can calculate by hand or already know is correct, especially before it goes into a report someone else will rely on.
Each prompt below is copy-pasteable. Swap out the bracketed placeholders with your own details before sending it.
Formula writing
"I need an Excel formula that looks up [value] in [column/sheet name] and returns [what you want]. My data is structured as: [describe columns]. Give me the formula and explain what each part does."
For general lookup formulas (VLOOKUP, INDEX/MATCH, XLOOKUP) — fill in your actual column letters and what you're matching on.
"Write a formula that calculates [metric, e.g. month-over-month growth] using the data in columns [X] and [Y]. Explain any assumptions you're making."
For calculated metrics — describe what "growth" or "change" means in your context so the AI doesn't guess.
"I want to combine text from columns A, B, and C into one cell, separated by [comma/space/dash]. What formula should I use, and does it differ between Excel and Google Sheets?"
For concatenation — useful because the syntax genuinely differs between the two tools.
"Write a formula that flags rows where [condition, e.g. Revenue is below target] with a YES/NO or TRUE/FALSE result. My columns are: [list them]."
For conditional flagging — good starting point before building full conditional formatting.
"I have dates in column [X] in [format, e.g. DD/MM/YYYY]. Write a formula to calculate the number of days/months/years between this date and today."
For date math — always state your date format, since Excel/Sheets interpret ambiguous dates differently by region.
"Write a nested IF formula (or suggest a cleaner alternative) that categorizes values in column [X] into these buckets: [list your buckets and their ranges]."
For tiered categorization — ask explicitly for a "cleaner alternative" since nested IFs get unreadable past 3-4 conditions.
"I need to sum/count values in [column] only when [column A] equals [value] and [column B] equals [value]. What formula handles multiple conditions like this?"
For multi-condition SUMIFS/COUNTIFS — spell out every condition, don't assume the AI will infer "and" vs "or" logic.
"Explain what this formula does, step by step, in plain English: [paste formula]."
For understanding a formula someone else wrote (or one AI just gave you) before you trust it.
Data cleaning
"I have a column of names/addresses/text in [format, e.g. 'Last, First'] that I need converted to [target format]. What formula or steps should I use?"
For reformatting text — paste 2-3 sample rows so the AI sees the exact pattern.
"My column [X] has inconsistent capitalization (some ALL CAPS, some lowercase). Give me a formula to standardize it to [Proper Case/UPPER/lower]."
For case normalization — a one-line fix most people don't know exists.
"I have extra spaces and invisible characters in column [X] that are breaking my lookups. What formula or steps will clean this up?"
For the classic "why isn't my VLOOKUP matching" problem, usually caused by trailing spaces.
"Write a formula or set of steps to find and remove exact duplicate rows in my data, keeping only the first occurrence. My data spans columns [A to X]."
For deduplication — specify the column range so the AI doesn't assume duplicates mean "duplicate in column A only."
"My column [X] mixes text and numbers (e.g. '5 units', '10kg'). Write a formula to extract just the numeric part."
For pulling numbers out of messy text fields, common with exported data from other systems.
"I have blank cells scattered through column [X] that should either be filled with [0/the value above/a specific value]. What's the best way to do this in Excel?"
For handling gaps — tell the AI your fill logic since "blank" can mean different things depending on context.
"Give me a formula to standardize phone numbers/dates/IDs in column [X] into a single consistent format: [describe target format]."
For standardizing messy identifiers before merging datasets from different sources.
"I'm merging two lists (columns [A] and [B] from different sheets) and need to identify which rows exist in one but not the other. What's the best approach?"
For reconciliation tasks — describe both sheets' structure so the AI picks the right comparison method.
Pivot tables and summarization
"I have a dataset with columns [list them]. I want a summary showing [metric] broken down by [dimension]. Walk me through setting up a pivot table for this, step by step."
For a from-scratch pivot table walkthrough when you've never built one before.
"My pivot table is showing [describe the problem, e.g. blank rows / wrong totals / dates grouped oddly]. Here's how I set it up: [describe]. What's likely wrong?"
For debugging an existing pivot table — describe your setup rather than just the symptom.
"Without using a pivot table, write a formula that gives me the same result as grouping [column] and summing [column] for each group."
For when you want a live formula-based summary (SUMIF/UNIQUE combo) instead of a pivot that needs manual refreshing.
"I need to summarize [dataset] by [time period, e.g. week/month/quarter] even though my data only has daily dates. How do I set that up?"
For time-based rollups — a common pivot table stumbling block.
"What's the difference between using a pivot table versus SUMIFS/COUNTIFS for [my use case]? Which is better for my situation: [describe your goal and how often the data updates]?"
For deciding which approach fits — genuinely useful since the "right" answer depends on your workflow.
"Write a formula to calculate the top 5 [items, e.g. products/regions] by [metric] from my dataset in columns [X]."
For ranking/top-N summaries without building a full pivot table.
"I want a running/cumulative total of [column] over time, grouped by [category]. What formula should I use?"
For running totals — specify whether it should reset per category or run continuously.
Charts and visualization
"I want to show [what you're trying to communicate, e.g. trend over time / comparison across categories] using data in columns [X]. What chart type fits best, and why?"
For chart-type selection — describe the story, not just the data, since that's what determines the right chart.
"Walk me through creating a chart in Excel/Sheets that shows [metric A] and [metric B] on the same chart but with different scales."
For dual-axis charts — a common need (e.g. revenue vs. growth %) that trips people up.
"My chart legend/axis labels are showing [describe the problem]. My data is set up as [describe]. How do I fix it?"
For chart troubleshooting — describe both the symptom and your data layout.
"I want my chart to automatically update as I add new rows to my data. How do I set that up?"
For dynamic charts using tables/named ranges instead of static ranges.
"Suggest a way to visually highlight [condition, e.g. months below target] directly in my data using conditional formatting, without a separate chart."
For in-cell visualization (data bars, color scales, icon sets) as a lighter alternative to a chart.
"I have [number] categories to compare across [number] time periods. What chart type avoids clutter, and how do I build it?"
For dense comparisons — useful when a normal bar chart turns into visual noise.
Automation with macros and Power Query, explained simply
Macros and Power Query intimidate people because they sound like programming. In plain terms: a macro records a series of clicks and repeats them for you; Power Query is a built-in tool that automatically cleans and reshapes data from a messy source every time it refreshes, so you don't redo the cleanup manually. You still don't need to write code. You can ask AI to write the steps or the underlying script for you.
"I repeat the same manual steps every week: [list the exact steps, e.g. filter column A, delete rows where B is blank, sort by C]. Can this be turned into a macro? Write the VBA code and explain how to add it."
For automating a repetitive manual task — list your steps in the exact order you do them.
"Explain what Power Query is and whether it fits my situation: I import a CSV/report every [frequency] that needs [cleaning steps] before I can use it."
For deciding if Power Query is worth learning for your specific recurring task.
"Write step-by-step instructions (not code) for setting up a Power Query that combines data from [multiple sheets/files] into one clean table."
For a plain-English walkthrough when you want to avoid VBA entirely.
"I recorded a macro but it breaks when [describe the issue, e.g. row count changes]. Here's the VBA: [paste it]. What's causing this and how do I fix it?"
For debugging a recorded macro that isn't holding up to real-world changes in your data.
"What's the simplest way to automatically refresh a report every time I open the file, without using VBA?"
For lightweight automation using built-in refresh settings instead of code.
"I want to build a button that runs a specific set of formatting/cleanup steps when clicked. Write the macro and explain how to attach it to a button."
For a one-click automation for a task you and colleagues repeat often.
Troubleshooting and auditing formulas
"This formula is returning [error, e.g. #N/A, #REF!, #VALUE!]: [paste formula]. My data looks like: [describe]. What's going wrong?"
For decoding specific error codes — paste the exact formula and error, not a description of it.
"I inherited this spreadsheet and this formula looks overly complex: [paste formula]. Can you explain what it does and suggest a simpler version?"
For auditing someone else's work before you trust or extend it.
"My formula gives a different result depending on which row I copy it to. Here's the formula and where I'm pasting it: [describe]. Why, and how do I fix the referencing?"
For relative vs. absolute reference issues — a very common silent bug.
"Check this formula for logical errors, not just syntax errors: [paste formula]. Here's what I expect it to calculate: [describe]. Does the logic match?"
For catching formulas that run without an error but calculate the wrong thing — the most dangerous kind of mistake.
"I need to trace where the value in cell [X] is coming from across a chain of formulas. Walk me through how to audit this in Excel."
For untangling a formula chain across multiple sheets before you make a change.
"Compare these two formulas and tell me if they'll always produce the same result or if there's an edge case where they differ: [paste both]."
For sanity-checking two approaches before picking one for a report that others will use.
Honest limits
- AI-generated formulas can look completely correct (right syntax, right structure) and still return the wrong number. Always test against a result you already know is correct before trusting it.
- Never paste confidential financial, HR, or customer data into consumer AI tools. Describe your data structure and use dummy values instead.
- Complex automation like macros and Power Query should be reviewed by someone who understands the logic before it's deployed broadly, especially if other people will rely on the output.
This list gets you moving faster on formulas, cleanup, and reporting without touching a line of code. But a prompt library is a starting toolkit, not a substitute for understanding how to actually work with AI. That's the gap that why 50 AI tools courses don't work gets into: collecting prompts and tools isn't the same as knowing how to think with AI on an unfamiliar problem.
Where to build these skills
Garage Labs Tech has trained 150,000+ professionals across 17+ countries, with a 49,000+ member community, and has collaborated with IIT Delhi, IIM Lucknow, Masters' Union, and the Harvard Business School Alumni Association. If prompts like these are useful but you want to go deeper than spreadsheets, two programmes are built for exactly this:
AI Fluency is a 6-week live, no-code programme (₹32,000+GST, roughly ₹37,760) for professionals who want to use AI well across their actual job, not just in Excel. The Applied AI Accelerator Bootcamp is a 10-week live, no-code programme (₹75,000+GST, roughly ₹88,500) where you ship 10+ AI agents, including RAG (Retrieval-Augmented Generation) pipelines, and present them at a Demo Day. Not sure where you stand? Take the free AI readiness quiz first. You can also browse our broader take on AI courses in India or start with the basics in Learn AI.
Frequently asked questions
Can ChatGPT or Claude actually write correct Excel formulas?
Usually yes for common tasks like lookups, conditional sums, and text cleanup, especially when you describe your column structure clearly. But "usually correct" isn't "always correct": test every formula against a result you can verify before relying on it.
Do I need to know VBA to use AI for Excel automation?
No. You can describe the repetitive steps you take in plain English and ask AI to write the VBA code for you, along with instructions for where to paste it. You still won't "know" VBA, but you don't need to for most one-off automations.
Is it safe to paste my company's spreadsheet data into ChatGPT?
Not if it contains confidential financial, customer, or HR information. Consumer AI tools aren't built to guarantee data privacy for sensitive business data. Describe your data structure and use dummy or sample values instead.
What's the difference between Power Query and a macro?
A macro records and replays a sequence of manual actions. Power Query is a dedicated tool for importing, cleaning, and reshaping data automatically every time it refreshes, without you repeating the cleanup steps by hand. For recurring data-cleanup jobs, Power Query is usually the more durable choice.
Why does the same formula work in Excel but not in Google Sheets?
The two tools share most formula syntax but differ on some functions (like array handling, IMPORTRANGE-type functions, and certain date functions). When you ask AI for a formula, specify which tool you're using so it doesn't give you syntax that only works in one.
If you want to turn scattered prompt tricks into an actual working fluency with AI, explore our programmes or start with the free AI readiness quiz.