ChatGPT for Excel: Two Ways to Stop Fighting Formulas

Every Excel class I teach has the same moment in it. Somebody needs a formula they have written forty times before, they cannot remember whether the lookup value comes first or the table does, and they open a browser tab to go find out. Ten minutes later they are reading a forum post from 2013 about a function that behaves differently now. Using ChatGPT for Excel gets those ten minutes back, and it does it without you having to memorize any more syntax.

The catch is that most people try it exactly backwards. They type “help me with Excel,” get a paragraph of general encouragement, and decide the whole thing is overrated. The trick is the opposite: be very specific about your data, and completely vague about the method. Tell it what your columns are called and what answer you want out the other end, and let it choose the function.

Here are the two techniques I show first in class, because both of them pay for themselves the same afternoon.

ChatGPT for Excel Formulas: Describe the Answer, Not the Syntax

Do not ask “what is the syntax for SUMIFS.” Instead, hand over the shape of your worksheet and say out loud what you want.

Look at row 1 of your sheet, then type a prompt like this one:

My Excel sheet has headers in row 1: A = Order Date, B = Rep, C = Region, D = Product, E = Amount. Data runs from row 2 to row 4000. In cell H2 I want the total of column E where Region is "East" and the Order Date falls in the current month. I am on Microsoft 365. Give me one formula and tell me exactly what to put in H2.

Four details in that prompt do all the work: the real column letters, the real header names, where the data stops, and which version of Excel you own. What comes back is a finished formula you can paste straight in, something along the lines of:

=SUMIFS($E$2:$E$4000,$C$2:$C$4000,"East",$A$2:$A$4000,">="&EOMONTH(TODAY(),-1)+1,$A$2:$A$4000,"<="&EOMONTH(TODAY(),0))

That beats looking it up because you were never going to remember the EOMONTH trick for “first day of this month.” You would have typed a date in by hand and then had to change it again in November.

• The gotcha: naming your version is not optional. Leave it out and you will often get XLOOKUP, TEXTSPLIT, or LET handed back, and none of those exist in Excel 2016 or 2019. If you are on an older version, say so in the prompt and ask for a formula that works there.
• The other gotcha: if your description is fuzzy, it will cheerfully invent a column you do not have. Always check one row by hand against what you know the answer should be before you trust it on four thousand.

Paste the Formula You Inherited and Ask What It Does

Everyone has that workbook. The one built by someone who left the company in 2019, with a formula in column N that is seven IFs deep and nobody will touch because nobody knows what it does.

Get the formula out as text first. Click the cell and copy from the formula bar, or, cleaner, put this in any empty cell: =FORMULATEXT(N2). That returns the formula as plain text you can copy without Excel trying to recalculate anything.

Then ask:

Here is a formula from a spreadsheet I inherited. Explain in plain English, step by step, what it does. Then rewrite it as something shorter that is easier to maintain. I am on Microsoft 365. Formula: [paste it here]

You get back a numbered walkthrough of each nested condition, usually followed by a tidier version built on IFS or LET. The explanation is the valuable half. Ten minutes of reading and you can finally say what that column actually calculates, which means you can defend the number in a meeting.

• The gotcha: a rewrite can quietly change edge-case behavior. Nested IFs that fall through to nothing return FALSE; the IFS version returns #N/A. Add a follow-up question: “list any inputs where your version returns something different from the original.” It will usually find them.
• And the rule I repeat every single class: paste formulas, never data. Structure and logic are fine to share. Customer names, salaries, account numbers and anything else your company would not want on the open internet stay in the workbook.

Plenty more on the calendar

If you would rather watch this happen on real sheets than read about it, Using ChatGPT with Excel runs live on Tuesday, October 27. We work through formula writing, cleaning up a messy export, building a summary from raw data, and the prompt patterns that keep ChatGPT from guessing. Bring your own problem files and ask about them while we are in there.

The full current schedule is on the front page of PCWebinars.com, and new dates go up as they are confirmed. Sign up for this one, or have a browse through the rest and pick whichever is closest to the thing slowing you down this month.

About your trainer

I’m Tom Fragale. I have been a computer trainer and consultant for more than 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, including Claude, ChatGPT, and Microsoft Copilot. My background is applied rather than theoretical. Alongside the teaching, I build real databases and reporting systems for clients in Excel, Access, and Power BI, so the examples I use in class are the problems I ran into last week, not textbook exercises. I also publish free tutorials on YouTube and longer courses online.

You will find the full schedule, the recordings, and the rest of the free material over at PCWebinars.com.