TaskbyTask.Practical AI guides for work
ChatGPT · Practical walkthrough

Get ChatGPT to write your spreadsheet formula

You know exactly what you want the cell to add up. You just don't know how to say it in SUMIFS, and "overdue" sounds obvious until two people disagree about invoices due today.

Task by Task13 min read + practice
A paper worksheet with arithmetic symbols and a caliper checking a lime cell, with a blank cell set aside for review.

What you'll make

one working formula for your sheet, with a plain-English explanation and a few test rows that prove it does what you meant.

What you'll need

ChatGPT (Claude, Gemini or Copilot work too), your spreadsheet app, your column headings and a few non-sensitive sample rows, or the fictional invoice sheet. About 15 minutes.

Example result.

=SUMIFS($C$2:$C$7,$D$2:$D$7,"Open",$B$2:$B$7,"<"&$H$1,$B$2:$B$7,">0")
Check the £100 totalExample data
InvoiceAmountWhy
I01+£120Open, overdue
I05−£20Credit
I02, I03, I04, I06ExcludedDue today, paid, no date or later

£120 − £20 = £100 · I04 still needs a date.

1. Show ChatGPT the relevant columns

Open a new chat and paste your column headings and a few example rows. Say whether you use Excel or Google Sheets, since their formulas aren’t always interchangeable. Pasted text is enough; you don’t need an add-in or an upload.

The practice sheet has invoice names in column A, due dates in B, amounts in C and statuses in D, in rows 2–7. Put the reporting date, 1 October 2026, in H1. Check that the dates are real dates and the amounts real numbers: a date-looking cell can contain text, which Excel treats differently.

Why this works: the formula has to point at your cells, and it can only do that if it knows where they are and what they hold.

What you should see: headings, ranges and sample rows in the chat, with the app named.

2. Ask for the calculation you actually need

Help me write a formula for [app and locale].
Calculation in ordinary words: [the result I want].
Headings and ranges: [heading → range].
Rules: [what counts, what's excluded, and how blanks and boundaries work].
Ask about missing rules before choosing a formula. Then give me one candidate formula, where to put it and a plain-English explanation. Flag data that needs review. Don't change my data. Suggest a normal, an excluded and a boundary case I can check.

[app and locale] is, for example, “Excel, English, comma separators”. For the practice sheet, the rule is: “Count only Open invoices due before H1. Due today doesn’t count. Exclude blank dates and flag them. Credits reduce the total.”

Why this works: the app and region change formula syntax; the ranges connect your words to cells; and the rules define the answer. “Overdue” needs a cut-off, a status and a blank rule before it means one thing.

What you should see: a formula, a cell to put it in, an explanation and three suggested test cases.

3. Try the formula in a copy

Put the formula in an empty cell in a copy of the workbook, such as H3. For the practice sheet, the expected total is £100: the overdue £120 less the £20 credit. The £80 invoice due on 1 October is excluded, and so is the £60 invoice with no date, which still needs attention.

Why this works: a copy lets you test freely, and a total you worked out yourself is firmer than “that looks about right”.

What you should see: £100 for the practice sheet, or the answer you predicted for yours.

4. Change one input at a time

Predict the new answer, change one input, recalculate, then restore it. For the practice sheet:

  1. Change I02’s due date to 30 September: £180.
  2. Restore it; change I01 to Paid: −£20.
  3. Restore it; give I04 a due date of 30 September: £160.
  4. Restore it; change I05’s amount to 0: £120.

Why this works: a total can look right while two errors cancel out. Changing one thing at a time shows each rule working.

What you should see: every predicted answer, then £100 again once everything’s restored.

Check the result

  • An ordinary row, an excluded row and the row due on the reporting date each behave as the rule says.
  • Credits reduce the total and paid invoices stay out.
  • The ranges cover every row, including one you add later. This example stops at row 7.
  • Blank or text dates are flagged, not silently counted.

The usual failure: the cut-off date. A formula using <= instead of < counts invoices due today, and the total still looks plausible. Spot it by checking the row due exactly on the reporting date.

Diagnose this formula before replacing it.
App and locale: [app and locale]. Formula and relevant rows: [paste them].
Expected: [value and the rule behind it]. Observed: [what the sheet returned].
Explain which row, range or rule causes the mismatch, propose the smallest repair and list tests to rerun. Don't change the data.

“The result was £180 and the invoice due today should be excluded” is more useful than “Excel hates me”, however understandable the sentiment.

Worked example: the invoice due today

Fictional example. The practice sheet:

Invoice  Due date    Amount GBP  Status
I01      2026-09-30  120         Open
I02      2026-10-01  80          Open
I03      2026-09-15  50          Paid
I04      (blank)     60          Open
I05      2026-09-10  -20         Open
I06      2026-10-15  0           Open
H1: 2026-10-01

The first formula returned £180:

=SUMIFS($C$2:$C$7,$D$2:$D$7,"Open",$B$2:$B$7,"<="&$H$1,$B$2:$B$7,">0")

£180 is £100 plus I02’s £80, and I02 is due on 1 October, the reporting date itself. The diagnosis prompt, with “expected £100, invoices due today don’t count” and “observed £180”, pointed to "<=" and proposed "<". The repaired formula is the one in the example result at the top, and the four changed-input tests then all matched.

  • The total was plausible. £180 isn’t an odd number; only the row due on 1 October exposed it.
  • The rule was in the prompt, and it was still missed. Stating a rule makes it checkable; it doesn’t make it followed.
  • The blank date stayed out both times because of the ">0" condition. Test 3 proves that condition is doing its job.

Task kit for this job: Download (ZIP) · Reusable instructions

Go deeper

See which rows are included

A helper column shows the rows behind the total. Put Check in E1 and this in E2, then fill down to E7:

=IF(D2<>"Open","",IF(B2="","REVIEW",IF(B2<$H$1,"OVERDUE","")))

Expect I01 and I05 OVERDUE, I04 REVIEW and the rest blank. D2 and B2 are relative and move as you fill down; the dollar signs keep $H$1 fixed. If any part of a formula is unfamiliar, ask ChatGPT to explain it using your headings, including what changes when you fill it down, rather than pasting something you can’t read.

Make it work in a growing workbook

Fixed ranges like C2:C7 miss new rows. An Excel Table expands as you add them.

Adapt the checked formula to an Excel Table with columns [column names]. Keep the agreed inclusion and exclusion rules. Explain the structured references and show how to test a newly added row with a known answer. Don't overwrite my original.

Add an eighth invoice with a known amount and check the total moves. Decide how you’ll handle stray spaces in statuses, text amounts and invalid dates; a catch-all IFERROR can hide the problem behind a reassuring zero. Keep the expected results with the test sheet.

Do it inside Excel or Google Sheets

Copilot in Excel has three modes: Allow editing (the default, which changes the workbook), Plan (proposes steps and waits for you to confirm) and Chat only (advice without edits). Work in a copy and use Plan or Chat only first, then run the same tests.

Gemini in Google Sheets: select Ask Gemini at the top right and choose the relevant tabs in the prompt box. For an Excel file, use File › Save as Google Sheets first. See use Gemini in Sheets to build and check a small table.

Sources

Optional analytics

With your permission, Google Analytics uses cookies to measure visits and which guides people read. It stays off until you accept. Use Analytics choices in the footer to change your choice. Rejecting after accepting refreshes this page. Privacy details.