7 Essential ChatGPT Prompts for Excel

Key Takeaways:

ChatGPT can help write formulas, explain errors, clean data, and plan spreadsheet workflows.
Always include your Excel version, column names, sample rows, and expected output.
Test formulas on a small sample before applying them to important workbooks.
Newer Excel functions may not work in older versions, so ask for compatibility notes.
Avoid pasting sensitive business or customer data unless your tool is approved for it.

Excel work is full of small problems: formulas that almost work, messy exports, inconsistent dates, pivot tables that do not answer the real question, and repetitive steps that should not be manual.

ChatGPT can help, but it needs specifics. “Fix my spreadsheet” is too vague. “Write a formula for Excel 365 that returns the latest payment date by customer from these columns” is useful.

Use these seven prompts as reusable patterns.

The important rule is this: ChatGPT can help you reason through Excel, but Excel still needs testing. Formulas, Power Query steps, Office Scripts, and VBA can all produce wrong results if the workbook structure is different from what the prompt described. Use AI to draft and explain. Use small test cases to verify.

Prompt 1: Formula Builder

Prompt: “I use [Excel version]. My data is structured like this: [columns and sample rows]. I need a formula in [cell/column] that [desired result]. It should handle [blanks/errors/duplicates/zero values]. Give me the formula, explain it, and mention any version-specific functions.”

Use this for lookup, date, text, math, and conditional formulas.

Better version:

I use Excel for Microsoft 365.
Table name: SalesData.
Columns: Customer, InvoiceDate, Amount, Status.
Need: return the latest paid invoice date for the customer in A2.
Rules:
- Only include Status = "Paid".
- Ignore blank dates.
- Return "No paid invoice" if none exists.
- Explain the formula.
- Give an older Excel alternative if this uses dynamic arrays.

This prompt gives ChatGPT enough structure to choose a formula. It also asks for compatibility notes. Microsoft’s support docs note that functions such as XLOOKUP are not available in older Excel versions like Excel 2016 and Excel 2019, even though workbooks created in newer versions may contain them. That is why version context matters.

Prompt 2: Formula Debugger

Prompt: “This Excel formula is returning [error or wrong result]:

[Paste formula]

My data layout is [describe]. Expected result is [result]. Explain what is likely wrong, give a corrected formula, and show a small test case.”

Debugging works better when you provide the expected result.

When debugging, include:

The exact formula.
The exact error message.
The cell where the formula lives.
Sample input rows.
Expected output.
Whether data is in a table, range, or spill array.
Whether your locale uses commas or semicolons in formulas.

Do not paste a screenshot alone if the formula matters. ChatGPT needs the actual text of the formula to reason about parentheses, ranges, table references, and separators.

Prompt 3: Data Cleaning Plan

Prompt: “I have messy data with these issues: [extra spaces, inconsistent dates, duplicates, mixed currency formats, blank rows, etc.]. Data range: [range]. Columns: [columns]. Create a cleaning plan using Excel formulas or Power Query. Explain which approach is safer and why.”

Power Query is often better than formulas for repeatable cleaning.

Microsoft describes Power Query in Excel as a way to import or connect to data, shape it through steps such as removing columns or changing data types, combine sources, and load the result into Excel. Power Query keeps transformation steps so they can be refreshed later. That makes it better than manual cleanup when the same export arrives every week.

Use ChatGPT to plan the cleaning logic:

I receive this CSV every Monday.
Columns: [columns]
Problems: duplicate rows, inconsistent date formats, extra spaces, blank rows, currency symbols, and mixed customer names.

Design a Power Query cleaning workflow.
Return:
1. Steps in order.
2. What each step fixes.
3. What should be reviewed manually.
4. How to avoid changing the original source file.

Power Query is usually safer than a pile of one-off formulas because the process is documented as steps.

Prompt 4: Pivot Table Design

Prompt: “I want to answer this business question: [question]. My data has these columns: [columns]. Recommend a pivot table layout with rows, columns, values, filters, and any calculated fields. Also explain what the pivot will and will not tell me.”

Start with the question, not the pivot.

A pivot table should answer a business question. “Show sales by region” is less useful than “Which region had the largest month-over-month change in paid revenue, excluding refunds?”

Ask ChatGPT to design the pivot around the decision:

Business question: Which product categories are growing fastest among repeat customers?
Data columns: CustomerID, OrderDate, ProductCategory, OrderAmount, RefundFlag.
Recommend a pivot table layout.
Include filters, calculated fields or helper columns if needed, and explain what this pivot cannot prove.

The phrase “cannot prove” is useful. It prevents overinterpretation. A pivot can show a pattern, but it may not explain why the pattern happened.

Prompt 5: Conditional Logic Formula

Prompt: “Create an Excel formula that applies these rules in order:

[rules]

Data columns are [columns]. The formula should return [possible outputs]. Include handling for blanks and unexpected values.”

Rule order matters. Ask the model to explain the order.

For complex rules, ask for a truth table before asking for the final formula:

Before writing the formula, turn these rules into a table:
Condition, output, priority, edge cases.
Then write the Excel formula.

This helps catch contradictions. If two rules can both apply, the formula must know which one wins.

Prompt 6: Chart Recommendation

Prompt: “My data is [describe]. I want to show [trend/comparison/distribution/relationship]. Recommend the best chart type in Excel, explain why, and give setup steps. Also tell me what chart types would be misleading.”

This prevents chart choice from becoming decoration.

Good chart prompts include the message you want the reader to understand. For example:

Data: monthly churn rate by customer segment for 18 months.
Goal: show whether enterprise churn improved after onboarding changes.
Recommend the best Excel chart, setup steps, and what not to chart.

Line charts are usually better for trends over time. Bar charts are usually better for category comparisons. Scatter plots help relationships. Pie charts are often overused and can be misleading when there are many categories or small differences.

Prompt 7: Automation Planning Prompt

Prompt: “I repeat this Excel workflow: [steps]. Help me decide whether to automate it with formulas, Power Query, Office Scripts, VBA, or manual checklist. Consider my Excel version, whether the file is shared, security restrictions, and how easy it is to maintain.”

Not every task should become a macro. Choose maintainable automation.

Microsoft’s Office Scripts documentation says Office Scripts can automate repetitive Excel tasks, record actions, edit scripts in a code editor, share scripts with coworkers, and connect with Power Automate. That makes Office Scripts attractive for Microsoft 365 workflows. VBA is still common in many desktop workbooks, but it may be restricted by security policy and harder to maintain across teams.

Ask ChatGPT to compare options:

I repeat this workflow weekly:
[steps]

Environment:
- Excel version: [version]
- File stored in: [OneDrive/SharePoint/local drive]
- Users: [solo/team]
- Security restrictions: [rules]
- Need refresh: [yes/no]

Compare formulas, Power Query, Office Scripts, VBA, and manual checklist.
Recommend the safest maintainable option.

The “safest maintainable option” is often not the fanciest automation.

Prompt 8: Formula Explanation Prompt

Sometimes you inherit a workbook and do not know what a formula does.

Explain this Excel formula in plain English:
[formula]

Also tell me:
1. What each function does.
2. What assumptions the formula makes.
3. What inputs could break it.
4. How to test it with three sample rows.

This is useful for audits, handoffs, and cleaning up old reports. Do not change an inherited formula until you understand what business rule it encodes.

Prompt 9: Power Query M Helper

Power Query can be edited through the interface, but advanced users sometimes need M code.

I am using Power Query in Excel.
Current columns: [columns]
Goal: [transformation]
Sample rows: [sample]

Suggest Power Query steps and M code if needed.
Explain where to paste or create each step.
Avoid destructive changes to the source data.

Always test M code on a copy or sample query first. A small naming mismatch can break the query.

Prompt 10: Data Validation Prompt

Bad inputs cause many spreadsheet errors.

Design data validation rules for this worksheet.
Columns: [columns]
Allowed values: [rules]
Invalid examples: [examples]

Recommend dropdowns, date limits, number limits, required fields, and warning messages.

Data validation is not glamorous, but it prevents bad data from entering the model in the first place.

Prompt 11: Error Handling Prompt

Formula errors should be handled intentionally.

Review this formula for error handling.
Formula: [formula]
Desired behavior for blanks: [behavior]
Desired behavior for missing lookup values: [behavior]
Desired behavior for divide-by-zero: [behavior]

Suggest a corrected formula and explain any tradeoffs.

Do not hide every error with IFERROR blindly. Some errors are useful warnings that the workbook needs attention.

Prompt 12: Workbook Audit Prompt

For important files, use AI as an audit assistant.

Create an Excel workbook audit checklist for a file used for [purpose].
It contains formulas, pivots, imported data, and charts.
List checks for formulas, data sources, refresh steps, hidden sheets, named ranges, assumptions, and version compatibility.

This is helpful before sending a file to leadership, finance, a client, or another team.

How to Provide Sample Data Safely

You do not need to paste real customer records. Create fake rows that preserve the structure:

Customer | InvoiceDate | Amount | Status
Acme A | 2026-01-15 | 1200 | Paid
Beta B | blank | 450 | Pending
Acme A | 2026-02-10 | 800 | Paid

Keep the edge cases: blanks, duplicates, negative numbers, weird dates, zero amounts, and unexpected text. Remove names, emails, phone numbers, addresses, account IDs, and confidential amounts unless your organization has approved the tool.

Excel Version Compatibility

Excel is not one product experience for everyone. A formula that works in Microsoft 365 may fail in an older perpetual version. Web, Mac, and Windows support can also differ for advanced features.

Ask for alternatives:

Give me two versions:
1. Excel for Microsoft 365 using newer functions if helpful.
2. Older Excel-compatible version without XLOOKUP, FILTER, LET, or dynamic arrays.

This avoids sending a workbook to a colleague who cannot use the formulas.

Testing Workflow

Before trusting an AI-assisted Excel answer:

Test on three simple rows where you know the answer.
Test blanks and missing values.
Test duplicates.
Test zero and negative values.
Test the largest realistic row count.
Compare with a manual calculation.
Save a backup before applying to the real workbook.

This small discipline catches most formula mistakes.

Safety Checklist for Excel + AI

Remove sensitive rows before sharing examples.
Test formulas on copied data.
Save a backup before running any macro or script.
Ask for Excel version compatibility.
Watch for locale differences such as comma vs semicolon separators.
Verify date parsing, currency formats, and blank-cell behavior.
Do not run VBA from untrusted sources without review.

Add these checks for automation:

Know who owns the workbook.
Document where source data comes from.
Avoid hard-coded file paths when sharing.
Record refresh steps.
Check whether scripts are allowed by your organization.
Use Power Query for repeatable imports when possible.
Use comments or a documentation sheet for assumptions.

Frequently Asked Questions

Can ChatGPT write Excel formulas that work?

Often, yes, especially with clear data structure and expected output. Still test the result.

Should I use VBA, Office Scripts, or Power Query?

It depends on your Excel version, security policies, sharing needs, and workflow. Power Query is strong for repeatable data cleaning. Office Scripts are useful in Microsoft 365 web/cloud workflows. VBA remains common in desktop Excel but carries security and maintenance concerns.

What if my Excel version does not support a function?

Ask for an older-compatible alternative. Functions such as XLOOKUP, FILTER, LET, and dynamic arrays require newer Excel versions.

Can I paste real business data?

Only if your organization allows it. Otherwise, anonymize examples while preserving the structure.

Can ChatGPT replace an Excel expert?

No. It can speed up formula writing, explanation, and planning, but important workbooks still need human review. This is especially true for finance, payroll, legal, compliance, forecasting, and executive reporting.

Is Power Query better than formulas?

For repeatable import and cleanup workflows, often yes. For simple calculations inside a worksheet, formulas may be faster and easier. Use the tool that keeps the workbook understandable.

References

Conclusion

ChatGPT is useful for Excel when you give it the workbook context it cannot see: version, columns, sample rows, expected output, and edge cases.

Use it to draft formulas, explain errors, design pivots, plan charts, and choose automation carefully. Then test the result before trusting it.

7 Essential ChatGPT Prompts for Excel