You don’t need a Microsoft Copilot subscription to get AI help with Excel formulas. ChatGPT — available free at chat.openai.com — is excellent at writing, fixing, and explaining Excel formulas, and it works for anyone with a browser. This guide covers the workflow, the best prompts to use, and how to get reliable results every time.
Why ChatGPT Is Great for Excel Formulas
The traditional way to learn a new Excel formula was to search online, wade through documentation, and hope that the example matched your situation. ChatGPT changes this. You describe your specific problem in plain English, and it writes a formula for your exact data — and explains exactly what each part does.
Unlike static web pages, you can ask follow-up questions. If the formula doesn’t work, you paste it back and say “this is returning #N/A — why?” and ChatGPT will diagnose the problem. It works for complete beginners and experienced users alike, and the free version handles formula tasks very well.
The Basic Workflow
- Describe your data. Tell ChatGPT which columns you have and what they contain. The more specific you are, the better the formula.
- Describe what you want the formula to do. Write this in plain English — you don’t need to know the function name.
- Paste the formula into Excel. Click the cell where you want the result, type
=, then paste the formula (without the leading = if ChatGPT already included it). - Test it and adjust. If it doesn’t work, paste the formula and the error back into ChatGPT and ask what’s wrong.
How to Describe Your Data for Better Results
The most common reason a ChatGPT-generated formula doesn’t work straight away is that the description was too vague. Tell ChatGPT:
- Which columns contain which data (e.g. “Column A has product names, Column B has sales figures, Column C has the region”)
- Which row your data starts on (e.g. “Row 1 is the header, data starts from row 2”)
- Any specific conditions or exceptions
For example, instead of asking “write a VLOOKUP formula”, try: “I have a spreadsheet where column A contains order IDs and column B contains customer names. On a separate sheet, column D has order IDs and column E has delivery dates. Write a formula in column C on sheet 1 that looks up the order ID from column A and returns the delivery date from sheet 2.”
10 Ready-to-Use ChatGPT Prompts for Excel
1. Write a VLOOKUP
“Write a VLOOKUP formula for Excel. Column A on Sheet1 has employee IDs. Sheet2 has employee IDs in column A and salaries in column C. I want a formula in Sheet1 column B that returns the salary for each employee ID.”
2. Fix a formula returning #N/A
“My VLOOKUP formula is returning #N/A but I can see the value exists in the lookup table. The formula is =VLOOKUP(A2,Sheet2!A:C,2,FALSE). What are the most likely causes and how do I fix it?”
3. Explain what a formula does
“Explain what this Excel formula does in plain English: =IFERROR(INDEX(B:B,MATCH(1,(A:A=E2)*(C:C=”Completed”),0)),”Not found”)”
4. Count how many times a value appears
“Write a formula to count how many times the word ‘Pending’ appears in column D of my spreadsheet. Data runs from D2 to D500.”
5. Sum values based on a condition
“I have sales data in column C and regions in column B. Write a formula to sum all sales where the region in column B is ‘North’.”
6. Highlight cells based on a condition
“Write a conditional formatting formula for Excel that highlights a row in red if the value in column E is more than 30 days past today’s date.”
7. Combine text from multiple cells
“Write an Excel formula to combine the first name in column A, a space, and the last name in column B into a single full name in column C.”
8. Extract part of a text string
“Column A contains email addresses like [email protected]. Write a formula to extract just the domain (everything after the @ symbol).”
9. Calculate a running total
“Write an Excel formula for a running total in column C, where column B contains daily sales figures starting from B2.”
10. Nest an IF with multiple conditions
“Write an Excel formula that checks the value in column B and returns ‘High’ if it’s over 1000, ‘Medium’ if it’s between 500 and 1000, and ‘Low’ if it’s below 500.”
How to Ask ChatGPT to Explain a Formula You Don’t Understand
If you’ve inherited a spreadsheet with complex formulas and you need to understand what they do, ChatGPT can explain them line by line. Just paste the formula and ask:
“Can you explain what this Excel formula does, step by step, in plain English? =SUMPRODUCT((MONTH(A2:A100)=3)*(YEAR(A2:A100)=2024)*B2:B100)”
ChatGPT will break down each function, explain the logic, and tell you what the formula ultimately returns. This is much faster than trying to decode it yourself using documentation.
Using ChatGPT to Debug a Formula That Returns the Wrong Result
If your formula runs without an error but returns the wrong number, ChatGPT can help debug it. Give it as much context as possible:
- Paste the formula
- Describe what it should return
- Describe what it’s actually returning
- Give a sample of the data (e.g. “Column A contains: Apple, Banana, Apple. Column B contains: 10, 20, 30. The formula should return 40 but returns 60.”)
Example prompt: “My formula =SUMIF(A:A,”Apple”,B:B) is returning 60 but should return 40. Column A has Apple, Banana, Apple and column B has 10, 20, 30. Can you see what might be wrong?”
ChatGPT will often spot issues like the criteria being case-sensitive, the range including the header row, or the formula picking up hidden rows.
Getting Consistent Results: A Few Tips
- Paste the actual formula back if it doesn’t work — don’t just describe the problem. Seeing the exact formula makes it much easier for ChatGPT to diagnose.
- Tell it your Excel version if you’re on an older version — some functions like XLOOKUP and FILTER aren’t available before Excel 2019 or Microsoft 365.
- Ask for an alternative if the formula uses functions you don’t have: “Can you write this without XLOOKUP — I’m on Excel 2016?”
- Ask ChatGPT to add error handling — for example, “wrap it in IFERROR so it shows blank instead of an error code”.
ChatGPT won’t always get the formula right on the first attempt, particularly for complex multi-condition logic, but the back-and-forth conversation approach makes it much faster to arrive at a working formula than searching documentation or forums.
