ChatGPT with Excel: Two Ways to Stop Fighting Formulas

There is a moment in almost every Excel class I teach where somebody says the same thing out loud: “I know Excel can do this, I just can’t remember how.” Usually it’s a lookup. Sometimes it’s a nested IF that grew three levels deep and quietly stopped making sense. Using ChatGPT with Excel fixes that particular kind of stuck faster than anything else I have added to my own workflow in years.

What it does not do is magically know your spreadsheet. That is the part people get wrong, and it is why so many folks try it once and give up. ChatGPT cannot see your workbook. It has no idea your dates are stored as text, or that column F is empty for half your rows, or that you are still on Excel 2016 and don’t have XLOOKUP. Ask a vague question and you get a plausible-looking formula that returns #VALUE!, and you conclude the whole thing is hype.

The fix is to hand it the context it is missing. Here are two ways to do that, with the exact wording. Both take about ten minutes to try on a file you already have open.

Tip 1: Using ChatGPT with Excel to write the formula you keep looking up

Before you type a word, give it three things: your column headers, a few sample rows, and a plain-English description of the answer you want. That’s it. Those three things turn a coin flip into a reliable tool.

Here is the shape I use. Copy it and swap in your own details:

I have an Excel table on Sheet1. Row 1 is headers: A = Order ID, B = Order Date, C = Customer, D = Region, E = Product, F = Units, G = Unit Price. Data runs from row 2 to row 4318. Write an Excel formula for cell J2 that totals column F for rows where Region is "Northeast" and Order Date falls in 2026. I am on Microsoft 365. Explain in one sentence what the formula does.

You will get back a SUMIFS with proper date boundaries, plus a sentence telling you why the criteria are written the way they are. Compare that to what most people actually do: filter the sheet by Region, filter again by date, look at the status bar, write the number down on a sticky note, and repeat it next month. The formula updates itself. The sticky note does not.

The real payoff comes on the follow-up. Once you have a working formula, ask for the version you actually want to live with:

• Now rewrite it so the region name comes from cell J1 instead of being typed inside the formula.
• Wrap that in IFERROR so it shows a blank instead of an error.
• Explain what each argument does, as if I have never used SUMIFS.

That last one is worth more than the formula. You are getting a private tutor on the exact function you were avoiding.

The gotcha: always name your Excel version. XLOOKUP, FILTER, and TEXTSPLIT do not exist before Excel 2019 and Microsoft 365, and ChatGPT will hand you one without warning you. If you leave the version out and the formula comes back with a #NAME? error, that is almost always why. And test on ten rows before you trust it on four thousand.

Tip 2: Let it write the cleanup macro you would never write yourself

Most people have a file they clean up by hand every month. Delete the blank rows the export leaves behind, trim the stray spaces, convert a column of text that is pretending to be dates, freeze the top row. Forty minutes of clicking, every single month, forever. Nobody learns VBA to fix it because learning VBA to fix it costs more than the forty minutes.

You don’t have to learn it anymore. You just have to describe it precisely:

Write Excel VBA for a macro named CleanImport that, on the active sheet: deletes any completely blank row, trims leading and trailing spaces from every cell in columns A through G, converts column B from text to real dates in M/D/YYYY format, and freezes the top row. Add comments so I can tell what each block does.

To run what comes back: press Alt+F11 to open the Visual Basic Editor, choose Insert > Module, paste the code into the blank window, then press F5. After that it lives in the file, and you can run it from Developer > Macros or put a button on the sheet.

The gotchas here matter more. Macros are not undoable — once the code deletes those rows, Ctrl+Z will not bring them back, so run it on a copy the first time. You also have to save the file as .xlsm or the macro disappears when you close it. And if your company blocks macros entirely, which many now do, ask for the same job a different way: Give me these same steps as Power Query transformations instead of VBA, and tell me where to click. Power Query is built into Excel, it refreshes with one button, and IT departments have no objection to it.

Plenty more on the calendar

If you want to see this done live on real files, I’m teaching Using ChatGPT with Excel on Tuesday, September 1 at 1:00 PM Eastern. We go through formula writing, cleaning up imported data, building summaries, and the prompts that get useful answers instead of confident nonsense — all in Excel, on the kind of files you already work with.

The full current schedule is on the front page of PCWebinars.com, and new dates go up as they are confirmed. Register for this one, or have a look at what else is coming up — there is usually something on Excel, Power BI, Access, Copilot, or Claude within the next week or two.

About your trainer

I’m Tom Fragale. I have been a computer trainer and consultant for over 30 years, and I have taught well over 30,000 people how to get more out of the software they already own — Excel, Access, Power BI, Word, Outlook, and now the AI tools that sit on top of all of it: Claude, ChatGPT, and Microsoft Copilot. My background is applied, not theoretical. Alongside the teaching I build real databases and reporting systems for clients in Excel, Access, and Power BI, so the examples I bring to class are the problems I ran into last week, not textbook exercises. I also publish free tutorials on YouTube and longer courses online.

If this was useful, there is a lot more where it came from over at PCWebinars.com.