How to Use ChatGPT to Write Excel Formulas
ChatGPT writes reliable Excel formulas — if you give it the right context. Here are 12 proven prompts across formula writing, debugging, explaining, and VBA generation.
The Basic Method
- Describe your data layout (which columns contain what)
- Say what you want the formula to do in plain English
- Mention any edge cases (blank cells, text-formatted numbers, date formats)
- Paste the result into Excel and test on 5–10 rows before applying to the full dataset
Why Most ChatGPT Excel Prompts Fail
The formula itself is rarely the problem — the data description is. ChatGPT cannot see your spreadsheet. When you write "sum the sales column", it has to guess what "sales column" means, whether the values are numbers or text-formatted numbers, and whether there are blank rows. That guessing is where errors come from.
The prompts below are designed to give ChatGPT exactly what it needs: column positions, data types, example values where relevant, and the specific output you want.
How Accurate Is ChatGPT for Excel Formulas?
For standard formulas — XLOOKUP, SUMIFS, nested IF, IFERROR, date maths — accuracy is high when you describe the data correctly. For complex array formulas or multi-sheet lookups with unusual edge cases, verify the output carefully. A safe workflow:
- Copy the formula into a test cell on a copy of your data
- Check the result against a few rows you can verify manually
- Then apply to the full range
12 Proven Prompt Templates
Writing formulas from scratch
Basic XLOOKUP
I have an Excel table. Column A is employee ID, column B is employee name. In a separate sheet, column D has employee IDs. Write an XLOOKUP formula in column E that returns the name from the first sheet. If no match is found, show "Not found".
Tip: Always describe your data layout — column letters and what each contains.
SUMIFS with multiple conditions
I have data in Excel. Column A is order date, column B is region (text: "North", "South", "East", "West"), column C is sales amount. Write a SUMIFS formula that sums column C only where column B is "North" AND column A is between 1 January 2026 and 31 March 2026.
Tip: Specify exact text values and date ranges — ChatGPT will use DATE() functions correctly.
Nested IF with multiple outcomes
Write an Excel formula for column D. If column C is greater than 10000, return "High". If column C is between 5000 and 10000, return "Medium". If column C is below 5000, return "Low". Use IFS instead of nested IF.
Tip: Asking for IFS instead of nested IF gets you cleaner, easier-to-read output.
Date calculation
Column A contains invoice dates, column B contains payment dates. Write a formula in column C that calculates the number of working days between each invoice date and payment date, excluding weekends and the UK bank holidays listed in a range called HolidayList on a sheet called "Config".
Tip: Named ranges and sheet references work well — ChatGPT knows NETWORKDAYS.INTL syntax.
Fixing broken formulas
Fix a #VALUE! error
This Excel formula returns #VALUE!: =SUMPRODUCT((A2:A100="North")*(B2:B100>=DATE(2026,1,1))*(C2:C100)). Column A is text, column B is dates formatted as text (e.g. "13/08/2026"), column C is numbers. What is wrong and how do I fix it?
Tip: Mentioning that dates are stored as text is the key detail that leads to the correct fix.
Fix a VLOOKUP returning wrong values
My VLOOKUP formula =VLOOKUP(A2,Sheet2!$A:$C,2,FALSE) returns the wrong name for some rows but correct for others. The lookup values in column A are product codes like "AB-123". What could cause partial mismatches?
Tip: ChatGPT will correctly identify trailing spaces and data type mismatches as the likely cause.
Debug a formula that returns 0 unexpectedly
My SUMIFS formula =SUMIFS(C:C,A:A,"North",B:B,">="&DATE(2026,1,1)) returns 0, but I can see matching rows in the data. Column A is text, column B contains dates, column C is numbers stored as currency format. What is wrong?
Tip: Describing the cell formats gives ChatGPT the context to identify text-formatted numbers.
Explaining formulas
Plain-English explanation
Explain what this Excel formula does in plain English, step by step: =IFERROR(INDEX($B$2:$B$500,MATCH(1,($A$2:$A$500=D2)*($C$2:$C$500="Active"),0)),"")
Tip: Useful for understanding inherited workbooks or formulas written by someone else.
Simplify a complex formula
This formula works but is very hard to read: =IF(AND(A2="Complete",B2<TODAY()),IF(C2>10000,"High Priority","Standard"),IF(A2="Pending","Waiting","N/A")). Can you rewrite it to be clearer, using IFS or a helper column approach?
Tip: ChatGPT is good at refactoring — ask for a helper column approach when formulas get unwieldy.
Writing VBA from a description
Automation macro
Write an Excel VBA macro that: 1) loops through every sheet in the workbook, 2) finds all cells in column A that contain the word "REVIEW", 3) copies those entire rows to a new sheet called "ReviewItems", 4) if "ReviewItems" already exists, clear it first before copying.
Tip: Break the task into numbered steps — VBA output is much more accurate when structured this way.
Format and export
Write Excel VBA that exports the active sheet as a PDF to the same folder as the workbook, using the workbook name plus today's date as the filename. The PDF should use landscape orientation.
Tip: File operations with dynamic paths are something ChatGPT handles well with VBA.
Formula errors hiding in your workbook?
AI can write new formulas, but it cannot audit an existing workbook. The ExcelErrorFinder audit tool scans every formula across all sheets and flags errors with the cell address and a plain-English explanation.
Frequently Asked Questions
Can ChatGPT write Excel formulas?
Yes — reliably for standard tasks: XLOOKUP, SUMIFS, nested IF, IFERROR, date calculations, text manipulation. It occasionally makes errors on very complex nested array formulas. Always test on a small range before applying to your full dataset.
How do I get ChatGPT to fix a broken Excel formula?
Paste the formula, the error it returns, and a brief description of what the formula is supposed to do. Mention column names, data types, and example values. ChatGPT is particularly good at diagnosing data type mismatches and range size errors.
Is Claude better than ChatGPT for Excel formulas?
Both produce reliable results for standard Excel formulas. Claude tends to give more explanation; ChatGPT is slightly faster for simple generation. For complex debugging, providing full context matters more than which AI you use.
Related Guides
Excel and AI: Complete Guide
Microsoft Copilot, Python in Excel, AI formula generators — the full landscape.
Read →
Microsoft Copilot in Excel
What the built-in Copilot actually does and whether it is worth the subscription.
Read →
Excel Formulas Hub
Understanding formula syntax makes AI output much easier to verify.
Read →
Excel Error Solutions
Fix #VALUE!, #REF!, #N/A, and every other formula error.
Read →