How to Use ChatGPT for Excel Formulas—and Verify the Logic

Try a formula in two minutes

Suppose B contains units, C contains unit price, and D should show the amount. Paste this request into ChatGPT: “In Excel, B2 is units and C2 is unit price. Give me a formula for D2 that stays blank if either input is blank, treats zero units as a valid zero, rounds the amount to two decimals, and can be filled down. Explain the references.” One formula to review is:

=IF(OR(B2="",C2=""),"",ROUND(B2*C2,2))

That is an editorial example, not a quoted ChatGPT reply. Put it in D2 and fill down. With B2 = 3 and C2 = 19.99, expect 59.97. Leave B3 empty and D3 should stay blank; set B4 to 0 with a valid price and D4 should show 0. If a price is stored as text or the amount needs a currency-specific rounding rule, settle that rule before using the formula on real data.

Choose how ChatGPT will work with the sheet

For a small task, you can describe the columns in a regular ChatGPT conversation and copy the proposed formula into Excel yourself. That is the route used for the captured response in this guide. If the task depends on many sheets or actual cell contents, ChatGPT can analyze an uploaded .xlsx file; give it clear headers and name the sheets and ranges you want it to inspect. File support can vary by plan and workspace settings, so check the current OpenAI data analysis guidance before relying on an upload workflow. Use a copy with sensitive information removed when the real data is not needed to write the formula.

There is also ChatGPT for Excel, which works inside Excel through an add-in. In that route, specify the sheet and cells it may change, ask it to explain the formula and list changed cells, then review the workbook before saving. The rest of this guide uses ordinary ChatGPT web chat, where you control when any formula is pasted into the file.

Write the rule before asking for syntax

Our practice sheet has units in B, unit price in C, discount code in D, and tax rate in E. The first data row is 5. A separate Rules sheet maps NONE to 0%, SAVE10 to 10%, and SAVE20 to 20% in A5:B7. The intended calculation is:

1. Multiply units by unit price and round the subtotal to two decimals.
2. Look up the discount code using an exact match. A blank or unknown code means no discount; the valid code NONE also has a rate of zero.
3. Apply the discount to the rounded subtotal and round the net amount to two decimals.
4. Calculate tax on that rounded net amount and round tax to two decimals.

These choices matter more than the function name. Tax before discount, a missing-code error instead of a zero fallback, or rounding only at the end would produce a different answer while still looking like a valid formula. Decide those rules with whoever owns the spreadsheet before asking ChatGPT to implement them.

Give ChatGPT a cell-by-cell request

The following request was sent to ChatGPT web chat on September 30, 2026, using only synthetic data. It named both the output cells and the separate expected-value cells so that the proposed calculation could be checked:

I have a synthetic Excel test sheet, not real customer data. Columns B-E are Units, Unit price, Discount code, and Tax rate. Expected values are in F, H, and J. Formula outputs are G, I, and K. Rules!A5:A7 has unique discount codes NONE, SAVE10, SAVE20; Rules!B5:B7 has numeric rates 0, 0.10, 0.20. Give copy-down formulas for G5 (subtotal), I5 (net after discount), K5 (tax), and L5 (PASS only when each difference from its expected cell is strictly less than 0.001). Round money to two decimals after each stage. Blank or unknown discount code means zero discount; a matched zero rate is valid. Use exact matches and bounded lookup ranges; tell me which Excel versions support the lookup function you choose. Show all four formulas in full, explain each reference and fallback, and list tests that could falsify the formulas. Do not claim you ran Excel.

For your own file, replace every column, sheet name, first row, lookup range, and missing-data rule. If you want an unknown code to stop the calculation, say so. If your Excel version does not support XLOOKUP, request an INDEX/MATCH version before accepting an answer.

Read the returned formulas before pasting them

ChatGPT's captured reply gave these four formulas. They are unreviewed model output, reproduced here to show what needs checking:

• G5 subtotal: =ROUND(B5*C5,2). It uses the row's units and price, then rounds before the next stage.
• I5 net: =ROUND(G5*(1-IFERROR(XLOOKUP(D5,Rules!$A$5:$A$7,Rules!$B$5:$B$7,0,0),0)),2). The lookup is exact and bounded, but the outer IFERROR can hide errors other than an unknown code.
• K5 tax: =ROUND(I5*E5,2). It uses the discounted, rounded net amount.
• L5 check: =IF(AND(ABS(G5-F5)<0.001,ABS(I5-H5)<0.001,ABS(K5-J5)<0.001),"PASS",""). It returns a blank on failure, while this workbook uses an explicit FAIL.

The $ signs keep the discount table fixed when I5 is filled down; D5 and G5 must become D6 and G6. The fifth XLOOKUP argument, 0, requests an exact match. Its fourth argument already returns zero when no code is found, so the surrounding IFERROR(...,0) adds no useful missing-code handling. Microsoft warns that IFERROR hides all errors. A broken lookup should remain visible, not quietly become a full-price sale.

For Excel versions with XLOOKUP, a reviewed replacement for I5 under this specific rule is:

=ROUND(G5*(1-XLOOKUP(D5,Rules!$A$5:$A$7,Rules!$B$5:$B$7,0,0)),2)

The built-in not-found value handles blank and unknown codes, while unrelated errors can surface. Microsoft says XLOOKUP is unavailable in Excel 2016 and 2019. The downloadable workbook uses an INDEX/MATCH version for those releases.

IFNA handles only #N/A, but its position matters. Wrapping the entire INDEX/MATCH result also hides #N/A in a matched rate cell. The workbook now applies IFNA only to MATCH, so a missing code receives zero discount while a damaged rate remains visible:

=ROUND(G5*(1-IF(IFNA(MATCH(D5,Rules!$A$5:$A$7,0),0)=0,0,INDEX(Rules!$B$5:$B$7,MATCH(D5,Rules!$A$5:$A$7,0)))),2)

The repeated MATCH makes this older-Excel version longer, but keeps the fallback tied to the missing key. Both versions assume unique discount codes and numeric matched rates. A blank matched rate can still behave like zero, so inspect the Rules sheet as part of the review. If an unknown code must stop the calculation, remove the zero fallback from either version.

Paste, fill down, and check a real answer

Work in a copy of the spreadsheet. Put the subtotal formula in G5, the reviewed net formula in I5, and the tax formula in K5. In this test layout, F, H, and J hold expected answers calculated separately; they are not input cells for the business formulas. Fill the formula cells down through the test rows, then inspect one middle row and the last row to confirm the row references moved while Rules!$A$5:$B$7 stayed fixed.

For the SAVE10 row, three units at 19.99 give a subtotal of 59.97. Ten percent off leaves 53.97 after rounding, and 8% tax on 53.97 rounds to 4.32. Those three values should appear in the formula columns without borrowing from F, H, or J. The table below shows other checks with a reason for each:

Make a failed check visible. The request sent to ChatGPT asked for differences strictly below 0.001, and the captured reply is shown above exactly as returned. For this workbook, both the formula results and independently entered money expectations are in cents, so the edited L5 check compares those amounts directly:

=IF(AND(G5=F5,I5=H5,K5=J5),"PASS","FAIL")

The ninth workbook row deliberately expects 10.01 when the subtotal formula returns 10.00. L13 should read FAIL; the separate test-result cell should read PASS because the check caught the one-cent fault. Do not round a difference before comparing it to a strict threshold: a value just below the threshold can round up and be rejected. If your work genuinely needs sub-cent tolerance, define that rule separately and test values on both sides of its boundary.

Make sure the review can detect an error

Download the formula test workbook. Formula Tests separates inputs, independently entered expected values, formula outputs, and checks. Rules holds the discount mapping; Review counts every test that did not pass, including errors. In the local spreadsheet calculation tool, all nine expected test results passed. In disposable copies, replacing a discount rate with NA() or 1/0 left a visible error and changed Review to REPAIR FORMULAS. The download retains the original numeric rates.

These checks used a local calculation tool, not Microsoft Excel. The captured ChatGPT XLOOKUP formula was reviewed as text; the download uses the edited INDEX/MATCH version. Before adopting it, repeat the copy-down and error checks in your Excel version and locale.

Use the same method for other formula jobs

Sum records for a month and region

Suppose A2:A5 holds real Excel dates, B2:B5 holds regions, and C2:C5 holds amounts. The rows are September 1/West/50, September 30/West/70, October 1/West/90, and September 15/East/30. Ask: “Give me a single-cell formula for the West total in September 2026. Dates in A may include times. Use an inclusive September 1 lower bound and an exclusive October 1 upper bound; keep all ranges the same size.” One editorial candidate to inspect is:

=SUMIFS($C$2:$C$5,$B$2:$B$5,"West",$A$2:$A$5,">="&DATE(2026,9,1),$A$2:$A$5,"<"&DATE(2026,10,1))

The expected total is 120: both September West rows count, while October West and September East do not. Changing September 30 to a date with a time should keep 120. A date stored as text needs cleaning, since a displayed date is not proof that Excel stored a date value. The SUMIFS documentation covers conditional sums; check that every criteria range has the same height as the amount range. If this list grows, use an Excel Table so new rows enter the referenced range.

Keep blank, zero, and text inputs separate

Suppose A2 contains a score and B2 must say MISSING for a blank, CHECK INPUT for text, PASS for a numeric score of at least 70, and REVIEW for a lower numeric score. Ask: “Write B2 for those four outcomes in that order, and make it safe to fill down. Zero is a valid score; text '75' is not a numeric score.” Review this editorial candidate:

=IF(A2="","MISSING",IF(NOT(ISNUMBER(A2)),"CHECK INPUT",IF(A2>=70,"PASS","REVIEW")))

Expect MISSING, REVIEW, CHECK INPUT, REVIEW, and PASS for blank, numeric zero, text "75", 69, and 70 respectively. A displayed 75 can still be text. A formula returning an empty string will also be treated as missing here; change that rule if your source distinguishes it from a truly empty cell.

Keep a formula working when rows are added

For an order list that changes every week, select the data and create an Excel Table with headers Units, Unit price, and Amount. Ask ChatGPT: “Write a calculated-column formula for Amount using the current row's Units and Unit price. Leave Amount blank if either input is blank, keep zero units as zero, and round to cents. Use structured references so the formula follows new rows.” A formula to review in the Amount column is:

=IF(OR([@Units]="",[@[Unit price]]=""),"",ROUND([@Units]*[@[Unit price]],2))

Check 3 units at 19.99, a blank unit count, and zero units, then add a new row and confirm Excel fills the calculated column. The column names must match your headers exactly. Microsoft explains that structured references adjust as table rows are added or removed. This is an editorial example; it was not tested in Microsoft Excel for this revision.

Repair a formula that works on someone else's Excel

If a shared workbook shows #NAME? around XLOOKUP, check the recipient's Excel version before asking ChatGPT to rewrite the formula. Give it the current formula, the exact lookup and return ranges, the required match behavior, and the missing-code rule. Ask for an INDEX/MATCH alternative and a short explanation of each reference. Then test first and last valid codes, an unknown code, duplicate codes, and one corrupted rate. A replacement that produces the expected answer in only one row is not enough.

Diagnose the failure you actually see

Use Excel's Formulas > Evaluate Formula to step through a difficult expression, and Trace Precedents to see which cells feed it. Once the formula passes normal and failure cases, keep the approved formula, the rule it implements, the Excel version, and a few independently calculated examples together. When the rule changes, rerun those examples before filling a live table.