The 25 Best AI Prompts for Excel and Google Sheets Formulas

Most people who use spreadsheets have hit the wall where they know what they want the data to do, but they cannot remember whether it is VLOOKUP, INDEX MATCH, XLOOKUP, or a nested mess of IF statements. The wall used to mean an hour of Stack Overflow. It now means asking an AI to write the formula, and then about half the time rewriting the AI's answer because it confused Excel syntax with Google Sheets syntax. The prompts below are designed to shorten that loop: clear descriptions in, specific formulas out, and a habit of always telling the AI which tool you are actually in.

Below are 25 prompts I run against spreadsheet problems. Data samples, broken formulas, raw CSVs, and rough descriptions all go into {{clipboard}}. Find the ones that match the shape of problem you hit most and keep them a keystroke away so you are not writing a new prompt every time a VLOOKUP breaks.

---

Download Free for macOS

Writing formulas from plain English

1. Generate a formula from a plain English description

I need a spreadsheet formula. I will describe what I want in plain English and paste a small sample of the data so you can see the shape. Tell me the formula and explain it.

Here is the description, the tool (Excel or Google Sheets), and the sample data:

{{clipboard}}

Produce:

1. The formula, ready to paste. Use absolute references ($A$1) where they should be absolute.
2. A one sentence explanation of what the formula does in plain English.
3. A note on which cell to put it in, and whether it should be copied down or across.
4. Any assumption about the data (sorted, no blanks, specific format) that the formula depends on.
5. A shorter alternative if the formula is long, or a longer but more readable alternative if the short version is cryptic.
6. A flag if the formula only works in one of the two tools (Excel vs Google Sheets) and why.

If my description is ambiguous, ask one clarifying question before writing the formula.

2. Translate a formula between Excel and Google Sheets

I have a formula that works in one tool and I need the equivalent in the other.

Here is the formula, which tool it is from, and which tool I need it for:

{{clipboard}}

Produce:

1. The equivalent formula in the target tool.
2. Any function that does not have a direct equivalent and the workaround I need.
3. Any syntax difference that trips people up (comma vs semicolon, array handling, function name changes).
4. A note on whether the behavior is truly identical or just close, and what edge case might differ.
5. A short test case I could use to verify the translated formula behaves the same.

If the formula cannot be cleanly translated, explain why and give me the closest alternative.

3. Pick the right function for a task

I am not sure which function to use. I have tried a few and none quite do what I want. Tell me which function is the right one for the job.

Here is what I am trying to do and the data:

{{clipboard}}

Produce:

1. The single best function for the task, with a one line justification.
2. Two alternative functions that could also work and their tradeoffs.
3. A formula using the best function.
4. A note on the edge cases where the best function fails and a fallback formula for those cases.
5. A warning about any function I should avoid here that looks like it would fit but actually does not (common mistake).

Explain briefly why I do not want to use VLOOKUP if that is what I have been reaching for.

Debugging broken formulas

4. Fix a formula that is returning an error

I have a formula that is returning an error (#N/A, #VALUE!, #REF!, #DIV/0!, #NAME?, #NULL!, #NUM!, or similar) and I cannot figure out why.

Here is the formula, the error, and a sample of the data:

{{clipboard}}

Walk me through:

1. What the error code usually means in this tool.
2. The most likely cause of the error given my formula and data.
3. A test to confirm the cause (for example, "evaluate this subformula in a spare cell and check the result").
4. A fix, with the corrected formula.
5. A defensive version that handles the error gracefully (IFERROR or equivalent) without hiding the real problem.
6. A root cause note: was this a formula error, a data error, or a spreadsheet design issue?

Do not just wrap everything in IFERROR. Find the cause first.

5. Explain what a complex formula actually does

I inherited a spreadsheet with a formula I cannot read. I want to understand what it does so I can either trust it or rewrite it.

Here is the formula and any context I have about what the spreadsheet is supposed to do:

{{clipboard}}

Produce:

1. A plain English description of what the formula does, starting from the innermost function and working outward.
2. An annotated version of the formula with inline comments (if the tool supports them) or a breakdown showing each layer.
3. Any piece of the formula that does not make sense for the stated purpose (dead code, unnecessary nesting, redundant checks).
4. A simpler equivalent formula if one exists.
5. A list of the assumptions the formula is making about the data.
6. A note on whether the formula is likely to break if the data shape changes.

6. Debug a formula that returns the wrong number

My formula does not return an error, but it returns the wrong number. Help me figure out why.

Here is the formula, the expected result, the actual result, and a sample of the data:

{{clipboard}}

Walk me through:

1. Trace the formula step by step with my specific data. Show intermediate values.
2. The point at which the result diverges from what I expected.
3. The single component of the formula that is most likely wrong: a range reference, a condition, a missing absolute reference, or a function behavior I did not expect.
4. A fix.
5. A second pass verification: run the fix with my sample and confirm the expected output.
6. A note on any similar formulas in my spreadsheet that might have the same bug.

Lookups and joins

7. Join data across two sheets

I have data in two sheets and I need to combine them on a common key. I am not sure whether to use VLOOKUP, INDEX MATCH, or XLOOKUP.

Here are the two data samples and the key I want to join on:

{{clipboard}}

Produce:

1. The recommended function for this specific case, with justification.
2. The formula, using real column references based on the samples.
3. How to handle missing matches (return blank, return a default, return an error).
4. A note on whether the key needs to be unique and what happens if there are duplicates.
5. An alternative formula using a different function, so I can see the tradeoffs.
6. Any data cleaning I should do first (trim whitespace, fix case, handle leading zeros).

If the join is better done with a query function or pivot table rather than a lookup, say so.

8. Look up a value based on multiple criteria

I need to look up a value where the match requires multiple columns, not just one. Single column VLOOKUP does not cut it.

Here is the data, the criteria, and what I want to return:

{{clipboard}}

Produce:

1. The formula that does a multi criteria lookup in my tool.
2. An explanation of why single column VLOOKUP does not work here.
3. An alternative formula using a different approach (FILTER, SUMPRODUCT, or array formulas).
4. A note on performance if the data is large.
5. A warning about any ambiguous case: what happens if multiple rows match all the criteria.
6. A test to verify the formula returns the right value for a specific input.

9. Do a "left join" or "full join" style combination

I have two datasets and I want to combine them the way a SQL left join would: all rows from one side, matching rows from the other where available, and nulls or blanks where there is no match.

Here are the two datasets and the join key:

{{clipboard}}

Produce:

1. A formula or approach that replicates a left join in the spreadsheet.
2. Whether I should use a helper column, an array formula, or a Query/FILTER function.
3. How to format the result so missing matches are visible (blank vs "N/A" vs zero).
4. A note on whether this is better done in a dedicated tool (BigQuery, Python, a pivot) if the data is large.
5. A quick verification step: how to count matches and non matches to make sure the join worked.

Pivot and aggregation

10. Summarize a dataset by a category

I have a raw dataset and I need to summarize it by a category. Totals, averages, counts, or a mix.

Here is the data and the summary I want:

{{clipboard}}

Produce:

1. A formula based summary using SUMIFS, AVERAGEIFS, COUNTIFS, or the equivalent.
2. A pivot table alternative with a description of the rows, columns, and values I should set up.
3. A note on which approach is better for my situation: dynamic formula that updates when data changes, or a pivot that needs to be refreshed.
4. How to handle blanks or errors in the data that might skew the summary.
5. A way to verify the totals by cross checking against another calculation.

11. Build a pivot table structure from raw data

I want to build a pivot table but I am not sure what rows, columns, values, and filters to use.

Here is the data and the question I am trying to answer:

{{clipboard}}

Produce:

1. The specific pivot table structure: rows, columns, values (with aggregation functions), and filters.
2. Any calculated field I should add.
3. The number format for each value (currency, percent, thousand separators).
4. A note on sorting: which column to sort by and in which direction.
5. A flag for any data cleaning that would make the pivot cleaner (standardize categories, fix dates, remove totals rows).
6. A second pivot option if the first one does not quite answer my question.

12. Create a running total or cumulative column

I need a column that shows a running total based on a date or sequence.

Here is the data and the column I want the running total on:

{{clipboard}}

Produce:

1. A formula for the running total that works with my data layout.
2. An explanation of the absolute vs relative references so I can copy it down.
3. A variant that resets the running total at specific points (for example, at the start of each month or each category).
4. A note on whether the data needs to be sorted first, and how to sort it.
5. An alternative approach using SUMIFS or a query if the formula version is messy.

Data cleaning

13. Clean a column of messy names or emails

I have a column of data (names, emails, product codes, whatever) that is messy: different capitalization, whitespace, typos, inconsistent formatting. I want a cleaned version.

Here is the sample:

{{clipboard}}

Produce:

1. A formula that normalizes the column: trim, proper case or lower case, remove invisible characters, standardize separators.
2. A separate pass for detecting near duplicates that might be the same value typed differently.
3. For emails: a format check and a flag for invalid ones.
4. For names: a split into first and last name if that is useful.
5. A list of the specific changes the formula makes, so I can review them.
6. An alternative approach using find and replace or a script if the formula is too fragile.

14. Find and flag duplicate rows

I have a dataset with possible duplicates and I need to identify them. The duplicates might be exact matches or near matches.

Here is the data:

{{clipboard}}

Produce:

1. A formula that flags exact duplicate rows based on all columns or a subset.
2. A formula that flags near duplicates: case insensitive, whitespace insensitive, or matching on a subset of columns.
3. A way to mark the first occurrence as "keep" and the rest as "duplicate."
4. A count of how many duplicates exist so I know the scale.
5. A strategy for handling them: delete, merge, or flag for review.

15. Parse a date or number column that is stored as text

A column in my spreadsheet has dates or numbers, but the tool is treating them as text and I cannot filter or calculate with them.

Here is the column:

{{clipboard}}

Produce:

1. A diagnosis of why the column is being treated as text: specific characters, invisible whitespace, wrong locale, leading apostrophe.
2. A formula to convert the values to real dates or numbers.
3. A note on how to handle entries that cannot be converted (for example, "TBD" or blank cells).
4. A permanent fix: format the column, or paste special values.
5. A warning about any date format ambiguity (03/04/2025 could be March 4 or April 3 depending on locale).

Conditional logic and nested IFs

16. Rewrite a nested IF into something readable

I have a nested IF formula that is four or five levels deep and I cannot read it. Rewrite it as something cleaner.

Here is the formula:

{{clipboard}}

Produce:

1. A rewrite using IFS (Excel and Google Sheets both support it) if that simplifies the structure.
2. A rewrite using a lookup table if the conditions are really "if value X, return Y" for a finite list.
3. An explanation of which approach is better for my specific case.
4. The corresponding lookup table if the formula becomes a LOOKUP or VLOOKUP.
5. A shorter, commented version of the original nested IF in case the rewrite is not feasible.

17. Build a conditional that handles multiple outcomes

I have a rule with multiple branches: if A and B, do X; if C or D, do Y; otherwise do Z. I need a formula for it.

Here are the rules and the data:

{{clipboard}}

Produce:

1. A formula that captures all the branches.
2. An order of evaluation that handles overlapping conditions correctly.
3. A check for the "otherwise" case so nothing falls through unexpectedly.
4. A simpler version using CHOOSE or a lookup if the branches are really picking from a finite list.
5. A test case for each branch so I can verify the formula works for all combinations.

Text and string manipulation

18. Extract part of a string

I have a column of text and I need to extract a specific part: a substring, a number, everything before a delimiter, everything after.

Here is the sample and what I want to extract:

{{clipboard}}

Produce:

1. A formula using LEFT, RIGHT, MID, FIND, SEARCH, or a combination.
2. A more robust version using REGEXEXTRACT if my tool supports it.
3. A note on what happens if the string does not contain the delimiter.
4. A fallback for entries that do not match the expected pattern.
5. A second formula that handles the inverse (extract the rest of the string).

19. Split a column into multiple columns

I need to split one column into multiple based on a delimiter, a fixed width, or a pattern.

Here is the sample:

{{clipboard}}

Produce:

1. A formula approach using SPLIT, TEXTSPLIT, or a combination of LEFT/RIGHT/MID.
2. A built in tool approach (Text to Columns in Excel, Data > Split Text in Google Sheets) with the steps.
3. Which approach is better if I want the split to update automatically when the source changes.
4. How to handle rows where the split produces a different number of parts.
5. A note on headers for the new columns.

20. Combine text from multiple cells with formatting

I need to combine text from several cells into one cell with specific formatting: a name with commas, a sentence with proper spacing, or a joined list.

Here is the input and the desired output:

{{clipboard}}

Produce:

1. A formula using CONCATENATE, TEXTJOIN, or & depending on the tool and the layout.
2. Handling for blank cells so I do not get double commas or leading spaces.
3. A version with line breaks using CHAR(10) for multi line output.
4. A version that conditionally excludes certain cells based on another column.
5. A verification step to check the result against the expected output.

Charts and visualization

21. Pick the right chart type for the data

I have data and I need to visualize it, but I am not sure which chart type is best.

Here is the data and what I want the chart to communicate:

{{clipboard}}

Produce:

1. The recommended chart type with a one sentence justification.
2. Two alternative chart types and when they would be better.
3. The specific columns to use as the X axis, Y axis, and series.
4. A note on sorting, filtering, or grouping the data before charting.
5. Any chart type I should avoid for this data (pie charts for more than five categories, stacked bars for time series, etc.).
6. A note on whether a table would communicate the point better than a chart for my audience.

22. Clean up a chart that looks bad

I made a chart and it looks bad. Help me fix it without starting from scratch.

Here is a description of the chart and the problem:

{{clipboard}}

Produce:

1. The most likely reason it looks bad: wrong chart type, too many series, bad axis scale, cluttered labels, distracting gridlines, or inconsistent colors.
2. A fix for each issue, with the specific setting to change.
3. A color and style recommendation appropriate for the audience (professional deck, internal dashboard, user facing report).
4. A recommendation on whether to remove any decoration (3D effects, shadows, extra legends).
5. A note on the one change that would have the biggest visual improvement.

Automation and scripting

23. Write a script to automate a repetitive task

I have a task I do in spreadsheets over and over and I want to automate it with Apps Script (Google Sheets) or VBA (Excel).

Here is the task and the tool:

{{clipboard}}

Produce:

1. A script that automates the task, ready to paste into the script editor.
2. A comment at the top explaining what the script does.
3. Instructions on where to paste it and how to trigger it (menu item, button, scheduled trigger).
4. Any permission or authorization step I need to complete before the script will run.
5. A safety check so the script does not destroy data if something goes wrong.
6. A note on what to test first on a small sample before running it against the full dataset.

24. Build a simple dashboard from raw data

I want to build a simple dashboard: a few charts, a few key numbers, filterable by date or category. I am not a power user, so keep it clean.

Here is the data and what I want to see:

{{clipboard}}

Produce:

1. A structure: a separate sheet for the dashboard, a separate sheet for the raw data, and a separate sheet for calculations.
2. The key metrics to surface at the top, and the formulas to calculate them.
3. Two or three charts with the specific source ranges.
4. A filter control (slicer, dropdown, or data validation) that lets me slice the data.
5. A layout recommendation: what goes where, what sizes, what labels.
6. A maintenance note: what I need to update each week or month to keep the dashboard current.

Keep it simple. A dashboard I cannot maintain is worse than no dashboard.

25. Write a query using QUERY or FILTER functions

I want to pull a specific slice of data from a larger dataset using QUERY (Google Sheets) or FILTER (both tools).

Here is the data and what I want to pull:

{{clipboard}}

Produce:

1. A QUERY or FILTER formula that returns the specific rows and columns I want.
2. Sort and group logic if I need it.
3. A version that handles an empty result gracefully.
4. A note on how the query changes if the source data changes shape (columns added or removed).
5. A second version using a pivot or lookup if the query version is fragile.
6. A verification step: a simple count to make sure the result has the expected number of rows.