An assistant cannot reliably guess your columns, row ranges or business rules from ‘fix my spreadsheet’. Give it a small example with a known answer, then test its formula in a copy of the sheet. This guide uses a four-row expense example so you can check every result yourself. It works as a text conversation; you do not need an add-in or an uploaded workbook.
Name your spreadsheet app, define the columns and paste a small anonymized sample. State the result you expect and the rules for blanks, duplicates and errors. Ask for a formula plus a short explanation, run it in a copy, and compare it with a hand-calculated answer before extending it to your real rows.
Go straight to the stepsBefore you begin
Use a copy and preserve the source data. Tell the assistant which app and version you use, along with the function language and argument separator if you know them. Examples here use English function names and commas. Some installations require different separators. Do not paste customer details, account numbers or private business data just to explain a formula.
What you’ll need
- Excel or Google Sheets
- A blank sheet or a copy of your workbook
- A few fictional or anonymized rows
- The expected result for those rows
- ChatGPT, Gemini or Claude
Let’s do this
Step by step
- 01
Describe the shape of the sheet
Write the column letters and headers, the first data row, and the last row in your sample. Explain whether numbers are actual numeric cells or imported text. Include where you want the result. This removes an easy source of mistakes: a formula can look sensible while adding the wrong column or including the header.
- 02
State the calculation in plain language
For this example, column A is Category and column B is Amount, with headers in row 1. You want the total amount for rows whose category is exactly Office. Say whether categories with extra spaces should be corrected, and whether repeated rows are legitimate transactions. Those are data rules, not details the assistant should decide silently.
- 03
Build the four-row practice sample
Enter Office and 12.50 in row 2, Travel and 30 in row 3, Office and 7.50 in row 4, then Office and 0 in row 5. Put the amounts in numeric cells. Before asking for a formula, calculate the Office total yourself: 12.50 + 7.50 + 0 = 20. The Travel row must not contribute.
- 04
Ask for one formula and its ranges
Request a formula for E2 and a brief description of each argument. For the sample, =SUMIF(A2:A5,"Office",B2:B5) returns 20. The category range is tested and the corresponding amount range is added. If the assistant suggests a different function, check that it follows the same rule instead of accepting a more complicated solution by default.
- 05
Challenge the result with a controlled edit
Change B4 from 7.50 to 8.50 and confirm the total becomes 21, then undo the change. Change the category in A3 from Travel to Office and confirm the total becomes 50, then undo again. These checks show that the expected cells influence the result. A formula returning 20 once could still have hard-coded or incomplete logic.
- 06
Extend the formula and reconcile the totals
Adjust both ranges to include the real data, keeping their shapes aligned. Decide how later rows will be included. Check a small subset manually and compare category totals with the overall total when every row belongs to one category. Investigate differences before using the sheet for a decision or sharing the result.
Put it into practice
Prompts you can copy and try
Replace bracketed placeholders with your own details. The examples are starting points; check the replies against your actual task.
Write a formula from a complete small example
Try this in a blank workbook before substituting your own columns.
I use [Excel version or Google Sheets] with English function names and comma separators. A1 is Category and B1 is Amount. Rows 2–5 are: Office | 12.50 Travel | 30 Office | 7.50 Office | 0 Give me one formula for E2 that adds amounts where Category equals Office. My expected result is 20. Explain the ranges briefly and give two temporary data edits with expected results so I can test it. Do not use a macro, script or add-in. State any assumptions about the cells.
Then try this follow-up
My real data ends at row 250. Update both ranges and explain what will happen if I later add a transaction in row 251.
Diagnose a formula that returns the wrong total
Include the actual formula and a sample that reproduces the mismatch.
Help me diagnose a spreadsheet formula without changing my source data. App and version: [app] Column headers and sample rows: [paste] Formula: [paste exact formula] Expected result: [number and how I calculated it] Actual result or exact error: [paste] Suggest the most useful first check, such as ranges, numeric text, category spaces or separators. Explain how I can check it in a spare cell. Wait for that result before suggesting a second change.
Then try this follow-up
The range is correct, but one amount is stored as text. Show me how to confirm which cell is affected and how to correct it in a copy.
Audit duplicates before deleting anything
Define what makes a record a duplicate; a repeated amount is not enough.
I need to inspect possible duplicate expense rows. A repeated transaction ID is a candidate duplicate, but separate transactions may have identical dates and amounts. Columns: [list] Sample rows: [paste anonymized rows] Suggest a helper-column formula for my spreadsheet app [app/version] to flag repeated transaction IDs. Keep all rows. Explain how blank IDs should be handled, and ask if my rule is unclear. Do not recommend deleting rows solely because their amounts match.
Then try this follow-up
Blank transaction IDs should say ‘review missing ID’, not ‘duplicate’. Adapt the formula and show expected labels for three sample rows.
Ask for checks before trusting a spreadsheet answer
Apply this to a proposed formula or analysis, including one that looks correct.
Review this spreadsheet calculation: [formula or proposed method]. The intended rule is [plain-language rule]. My sample is [paste] and my expected result is [result]. List five concrete checks, including an ordinary case, zero, a missing value, a row outside the current range and a potential duplicate. For each check, explain what should happen under my rule. If the rule does not determine the answer, ask me rather than making an assumption. Keep the original sheet unchanged.
Then try this follow-up
I have run the checks. Here are the results: [paste]. Which mismatch should I investigate first, and what evidence would distinguish the possible causes?
Why a correct-looking formula can still be wrong
The formula may be valid but answer a different question. A monthly total may accidentally include the previous year; a duplicate-removal rule may erase separate purchases; a range may stop before the newest rows. Put those choices into the prompt as explicit rules. An assistant's explanation is useful, but the cells and your intended calculation remain the evidence.
For the example above, Microsoft documents SUMIF as a conditional sum and recommends aligned criteria and sum ranges. Our four invented transactions make the rule visible: three Office rows contribute 20 and one Travel row contributes nothing. The companion tests deliberately change one value or one category so you can see the calculation respond.
If the formula gives an error or zero
First copy the exact error into your chat rather than describing it as ‘broken’. Check the function name, straight quotation marks and the separator your spreadsheet expects. If it returns zero, examine the category spelling and whether the amounts are numeric. Test one suspected cell instead of wrapping the whole formula in error suppression and hiding the cause.
Once your small example works, change one thing at a time as you adapt it. Keep a note of the original formula and the expected total. If you need an assistant to inspect a full file, use a permitted, anonymized copy and confirm which sheet it used. File access does not replace testing the final formula in your own workbook.
Practice data to enter in A1:B5
| Sheet row | Column A: Category | Column B: Amount |
|---|---|---|
| 1 | Category | Amount |
| 2 | Office | 12.50 |
| 3 | Travel | 30 |
| 4 | Office | 7.50 |
| 5 | Office | 0 |
A quick final check
- The app, columns, ranges and expected result are explicit.
- The sample returns 20 before temporary edits.
- The value-change test returns 21 and the category-change test returns 50.
- Temporary edits have been undone.
- Real-data ranges and missing-value rules are checked.
Common questions & sticking points
Can ChatGPT write Excel formulas without seeing my whole file?
Yes. Describe the column letters, headers, relevant rows, spreadsheet version and desired result, then paste a small anonymized example. Include a result you can calculate yourself. Test the suggested formula in your spreadsheet; a plausible explanation alone does not show that the formula references the right data.
Why does an AI formula work in Excel but fail in Google Sheets?
The suggested formula may use a feature, function or reference style that differs between the apps, or your locale may use different separators. Tell the assistant the exact app and paste the error. Ask for a compatible formula using your sample, then check the result rather than changing several settings at once.
How do I check that an AI spreadsheet total is correct?
Start with a few rows and add the relevant values by hand. Then change one contributing amount and one category to confirm that the formula responds as expected. Check the first and last included rows, blanks, numeric text and duplicate rules before extending the formula to the full dataset.
Should I upload my customer spreadsheet to get formula help?
Usually a made-up sample with the same columns is enough for formula advice. Use that first. If a task requires a real file, follow your organization's data-sharing rules and remove information the task does not need. Work on a copy and confirm which sheet and range the assistant actually analyzed.
Sources & further reading Optional
References used to check this guide. Follow your product’s own safety instructions where its design differs.
- Microsoft: SUMIF syntax, criteria and matching ranges
- OpenAI: Providing context and specifying the output