Data skills
Check an AI spreadsheet formula before trusting the total
Before using an AI-generated formula on a full spreadsheet, test it on a tiny dataset whose answer you can calculate yourself. Check the business definition, ranges, dates, blanks, and duplicate rules, then reconcile the result against the original total.
In this article
A formula can run correctly and answer the wrong question
The most dangerous spreadsheet error is often a believable total. The formula runs, no cell shows an error, and the chart looks sensible. But perhaps the formula counts an order twice, treats a refund as a sale, or includes the first day of the next reporting period. The spreadsheet is calculating exactly what you asked, while the request itself is wrong.
AI can help write formulas and explain spreadsheet logic. I still want a small, independent check before applying any generated formula to a working report. The check starts with the business question: what, precisely, should this number mean? Only then do I inspect the formula syntax.
This tutorial uses a fictional order export. It is a learning example, not an accounting model. We want completed sales for a particular month, where each row represents one order and the amount excludes tax. Those assumptions are deliberately explicit. If your source uses line items, payment attempts, mixed currencies, or refund records, the calculation needs a different definition.
You can follow the process in Excel or another spreadsheet tool. The exact formulas below use Excel-style comma separators and English function names. Locale settings may use different separators. The principle is portable: make a tiny dataset, work out the answer yourself, and test whether the model's formula matches that answer for the right reasons.
Define the number before asking for a formula
Write the metric in one sentence. For example: Sum the amount of completed orders dated in September, excluding canceled orders. Then specify what an order, a date, and an amount mean in your file. A formula cannot infer whether an amount includes tax or whether two rows with the same order ID are valid line items.
The next useful detail is the row grain: what does one row represent? If every row is an order, adding Amount may be appropriate. If every row is a product line and the order total is repeated on each line, summing that total inflates revenue. No clever use of SUMIFS can rescue a misunderstood data structure.
Also define how changes appear. Does a canceled order replace the original status, or create a second event row? Does a refund reduce the original amount or appear as a negative transaction later? Decide whether the report groups by order date or refund date. These are reporting choices you must supply, not decisions to delegate silently to a model.
Give the assistant the column names and a few synthetic examples after writing those rules. Synthetic rows protect unnecessary customer details and make the pattern easier to inspect. They should preserve the structure and edge cases of the real data, including missing values and repeated IDs, rather than showing only perfect records.
Help write an Excel formula after checking my metric definition. One row represents one order. Columns are A: OrderID, B: OrderDate, C: Status, D: Amount. Amount excludes tax and all rows use one currency. I need the sum of completed orders dated in September 2026. Before giving a formula, list any ambiguity in this definition. Do not silently remove repeated order IDs.
Build a five-row example with an obvious answer
Use a tiny table that includes ordinary rows and exceptions. In our fictional example, order A101 is completed on September 1 for 120. A102 is canceled on September 3 for 80. A103 is completed on September 30 for 200. A104 is completed on October 1 for 90. A105 is completed on September 15 for 50.
The expected September completed-order total is 370: 120 plus 200 plus 50. The canceled order does not count, and the October order does not count. Write 370 in a separate control cell before asking AI for the formula. You now have a target produced independently of the model's explanation.
Use actual spreadsheet dates, not text that only looks like a date. If your import creates text dates, fix that issue in a copy of the source and document the conversion. A formula can fail to match text dates even when they appear visually identical to real dates. Formatting a cell as Date does not always convert its underlying value.
Keep the test sheet visible while you work. It should be easy to point to each included row and explain why it qualifies. If you cannot explain the expected answer without reading the generated formula, the test is not independent enough. Simplify the example until the calculation is obvious.
OrderID | OrderDate | Status | Amount A101 | 2026-09-01 | Completed | 120 A102 | 2026-09-03 | Canceled | 80 A103 | 2026-09-30 | Completed | 200 A104 | 2026-10-01 | Completed | 90 A105 | 2026-09-15 | Completed | 50 Expected September completed total: 370
Read the formula as a sentence
For the sample in rows 2 through 6, a suitable formula sums D2:D6 where C2:C6 equals Completed, B2:B6 is on or after September 1, and B2:B6 is before October 1. The formula is shown below. It should return 370 on the test data, assuming the dates and amounts are stored correctly.
The upper boundary is deliberately before October 1 rather than less than or equal to September 30. If a source contains timestamps, an entry late on September 30 may be greater than the midnight value represented by that date. A half-open date interval is often easier to reason about for monthly reporting.
Microsoft documents SUMIFS as adding values that meet multiple criteria. Its sum range comes first, and each criteria range must align with it. That alignment is a useful review habit: make sure every range starts and ends on the same data rows. An offset range can pair one order's date with another order's amount.
Ask the assistant to explain each condition in ordinary language. Then compare that explanation with your original metric definition. A fluent explanation does not prove the formula is right, but mismatches often become obvious when you read the conditions aloud. You should be able to describe exactly why each of the five rows is included or excluded.
=SUMIFS(D2:D6,C2:C6,"Completed",B2:B6,">="&DATE(2026,9,1),B2:B6,"<"&DATE(2026,10,1))
Change one row at a time and predict the result
A formula that passes one example may still fail in realistic conditions. Change the October date to September 29. The expected total becomes 460. Change the canceled order to Completed. Starting from the original table, the expected total becomes 450. Make each prediction before looking at the spreadsheet result.
Next, change the last amount from 50 to zero. The original total should become 320. Change it to a negative 50 and the arithmetic becomes 270, although whether a negative amount belongs in this metric depends on your reporting definition. This distinction is important: a mathematically correct result may still violate the business rule.
Try a blank amount and a status with trailing spaces. These cases reveal how the imported data behaves. Do not immediately ask AI to make the formula tolerate everything. Sometimes the right fix is a data-quality warning, not a more permissive calculation that hides inconsistent source values.
Record the cases in a small table with scenario, expected result, actual result, and conclusion. A handful of meaningful tests is more valuable than a large collection of similar normal rows. Include at least one boundary date, one excluded status, one missing value, and one repeated identifier if those conditions occur in your real workflow.
Decide what repeated IDs mean before removing them
Duplicate removal is an especially risky instruction to give casually. If you ask an assistant to clean duplicate orders, it may remove legitimate rows. A customer can place two orders for the same amount on the same day. An order can have several products. Two payment attempts can refer to one order but represent different events.
In our fictional export, one row is supposed to represent one order. A repeated order ID therefore needs investigation. But even here, do not simply keep the first row. The later row could contain a corrected status or amount. Compare the records and identify which field establishes the current version.
Ask the model to flag candidate duplicates and explain the matching rule. Order ID is stronger than matching customer name and amount, but it still depends on the source system. Preserve the original rows and add a review column while investigating. That gives you a reversible process and a record of why a row was excluded.
After resolving duplicates, reconcile the removed or superseded amounts. If the total changes by 200, identify the exact record responsible and the reason. An unexplained change is a signal to stop. The goal of cleaning is a more faithful representation of the source, not a spreadsheet that happens to contain fewer rows.
Move to the full file with visible control totals
Once the small test works, apply the logic to a copy of the full dataset. Keep the original export unchanged. Make sure the formula covers every data row and no summary rows. A common error is to include a manually inserted subtotal inside a range that already sums the underlying transactions.
Excel tables can make range management easier because structured references expand with new records. If you use them, confirm that the generated formula names the correct table and columns. If you use fixed ranges, check what happens when new rows are appended next month. A correct formula on today's file can become incomplete after an import.
Add control totals for all amounts, included amounts, and excluded amounts where the categories are exhaustive and non-overlapping. Included plus excluded should reconcile to the overall total under those conditions. If it does not, look for records that are counted twice, omitted, or stored as text.
Inspect a few records from each category manually. A total that reconciles can still use the wrong classification rule. For example, everything may add up while pending orders are incorrectly labeled completed. Reconciliation checks arithmetic coverage; row review checks meaning. Both are useful because they detect different kinds of mistakes.
Ask AI to diagnose discrepancies, not force agreement
When a result differs from your control, describe the discrepancy precisely. Include the expected result, actual result, formula, sample rows, and metric definition. Ask for possible causes in priority order. Do not say make the result equal 370, because that encourages a patch that fits the number rather than the logic.
For example, if the sample returns 460, the October row may be included. If it returns 320, the September 15 record may have a text date, a mismatched status, or a missing amount. These are hypotheses to test. The assistant should connect each hypothesis to a specific cell or condition you can inspect.
Ask for the smallest diagnostic step first. Checking whether a date is numeric can be more useful than replacing the entire formula. Showing a helper column for each condition can reveal which rule excludes a record. Temporary helper columns are not inelegant; they make the calculation visible while you investigate.
Once the issue is understood, remove unnecessary complexity from the final formula. Keep the explanation in a notes sheet, including the metric definition and important exclusions. A report that another person can maintain next month is more valuable than a compact formula nobody can explain.
My expected total is [NUMBER], but this formula returns [NUMBER]. Here are the metric definition, formula, and sample rows. List the most likely causes and one small check for each. Do not alter source values or hard-code the expected total. Start with date types, status matches, range alignment, and duplicate assumptions.
Keep a small test sheet with every reusable report
The most useful artifact from this exercise is the test sheet. Save it with the report template so future edits can be checked against the same known cases. If someone changes the date logic or imports a different export format, they can rerun the small tests before sending the result onward.
Add new tests when you discover a real failure. If a timestamp caused a month-end omission, preserve a timestamp example. If a trailing space caused a status mismatch, keep that case and document whether the source should be cleaned or rejected. Your tests then reflect the actual data rather than imagined perfection.
This approach also makes AI assistance more effective over time. You can give the model the current formula, the definition, and the test cases whenever you need a revision. Ask it to preserve the passing cases and explain any intentional change. The conversation becomes a specific engineering task instead of an open-ended request to fix the spreadsheet.
Before using the next generated formula, answer three questions yourself: What does one row mean? What result should a tiny example produce? How will I reconcile the final total? Those questions take less time than rebuilding a report after a misleading chart has already reached the team.
The aim is not to distrust every calculation. It is to make trust earned and repeatable. A short set of controls turns an AI suggestion into a calculation you understand, can explain, and can safely revisit when the next file arrives.
Zero and missing data should not look identical
One final test deserves its own line: what happens when no rows match? A total of zero may be mathematically correct for an empty range, but a manager can misread it as confirmed zero activity. Pair the total with a matching-row count and an import check. If the expected file did not arrive, the report should say data unavailable rather than suggest that no orders occurred.
For the fictional five-order example, note that three September completed orders contribute to the result. If the amount remains zero while the matching count is positive, inspect the amounts and their types. If both are zero, inspect the date range, status spelling, and source coverage. These checks do not identify every error, but they separate several very different situations that a single total hides.