Why this matters
You know what you want from the sheet: the cost of one carrier's delivered loads in October. You do not know that the formula is called SUMIFS, or how to write a date range inside it. Searching for it takes longer than adding the rows by hand.
ChatGPT can write the formula if you tell it which column holds what and which cell the answer goes in. The part most people skip is the second message: make it list the rows it counted, so you can check the total yourself before anyone relies on it.
Practice
Do every step yourself in a practice copy of the real tool. It checks your work, and “Do it for me” finishes any step you get stuck on.
What you’ll build
You are a dispatcher at Harbor Line Freight (made up) with a 14-row October shipments export. You get three formulas: a Total Cost per load, a SUMIFS for one carrier's delivered October loads, and an XLOOKUP that shows "Not found" for a missing Load ID.
Then ChatGPT checks its own work: $4,950.70 for Summit Trucking, five loads counted and nine left out with the reason for each, plus one way each formula could break next month.
What you need
- Nothing to start the practiceThe lesson is a practice copy of ChatGPT. The freight broker and its loads are made up.
- A ChatGPT account, to do it for realAny plan that lets you attach a file. The answers shown came from a Plus account.
- Excel for Microsoft 365 or Excel for the webXLOOKUP needs a current version of Excel. Tell ChatGPT which version you have.
Concepts
Say where things are, not what the formula is called
The request names the columns (miles, rate per mile, fuel surcharge, status), the cells for the answers (I, L2, L5) and the version of Excel. It never says SUMIFS or XLOOKUP. ChatGPT picks the function; you describe the job.
"Explain each part in one short line" matters as much as the formula. A formula you cannot read is one you cannot fix next month.
Make it show its work
The first request ends with "Don't calculate the answers for me yet." The second asks what L2 should show and which loads were counted and left out. Now you have a short list you can add up by hand: three loads at $996.90 and two at $980.00 is $4,950.70.
One load was left out because it was still in transit, and one because it shipped on November 2. Those are exactly the rows a hand count gets wrong.
What it warned about
The export stores dates as text like 2026-10-01. If Excel does not turn them into real dates when it opens the file, the SUMIFS can return $0. ChatGPT said to check by changing B2 to General format: a real date becomes a five-digit number.
The ranges stop at row 15. Next month's export will have a different number of rows, so the ranges must grow with it.
Do it for real
Now repeat it in your own account. Tick each step as you go.
Downloads
- shipments-october.csvThe same 14-row export used in the lesson. Made up.
Quick check
3 questions to check you’ve got it.
1. What do you tell ChatGPT so it can write the formula?
- The name of the Excel function you want
- Which column holds what, where the answer goes, and your Excel version
- Only the total you expect
Show the answer
Which column holds what, where the answer goes, and your Excel version. You describe the job and the layout. ChatGPT picks the function and explains each part.
2. Why ask which loads it counted and which it left out?
- So you can check the total by hand
- It makes the formula faster
- Excel needs that list
Show the answer
So you can check the total by hand. A short list of counted and excluded rows is something you can verify. A bare total is not.
3. The SUMIFS returns $0 but the rows are clearly there. What is the likely cause?
- The carrier name is too long
- Excel read the ship dates as text, not dates
- SUMIFS cannot use two conditions
Show the answer
Excel read the ship dates as text, not dates. A date range only works on real dates. Set B2 to General: a real date turns into a five-digit number.
ChatGPT: Clean Up a Messy Spreadsheet Export Without Guessing
The formula works when the data is clean. Next, a real export: duplicates, mixed dates and broken emails.
Start module 2 →