Before accepting an AI-generated spreadsheet total, decide whether it should follow the current view or a fixed business rule. “Total the North records” and “total the rows I can currently see” can produce different answers, even when nobody changes a source value.
Check filters and manually hidden rows separately. Then inspect the saved workbook in the spreadsheet application used for the report. A number that matches a screenshot may still represent the wrong population when someone changes the view.
This guide uses an invented four-record example. We checked its contributing IDs and arithmetic with Python. We did not execute the formulas in Excel, test an AI spreadsheet product or validate an operational workbook. The expected formula results below are derived from documentation and are proposed acceptance checks, not observed Excel test results.
Name the population before choosing the function
Our guide to testing AI spreadsheet formulas starts with row-level inclusion rules and tests changed inputs. Here the additional issue is changing the display while leaving the underlying records unchanged.
An analyst may filter a list to investigate one region, then hide a distracting row while reviewing it. Those are useful working actions. They are not automatically changes to the business definition of a monthly report.
Ask the report owner which of three questions the result should answer: all detail records in the stated source range, records meeting explicit criteria, or records selected by the current view. If the last question is intended, define whether a manually hidden row should still count.
Give the assistant that decision explicitly. For a fixed reporting rule, a useful instruction is “total records whose Region is North and Status is Accepted, regardless of the reviewer’s filter or manually hidden rows.” For an interactive worklist, the intended instruction might instead be “show the sum of currently visible detail rows, excluding rows hidden manually as well as by the filter.”
These are proposed task descriptions, not tested prompts. Neither is universally better. The problem is allowing one meaning to stand in for the other without telling the reader.
Excel distinguishes filtering from manual hiding
Microsoft’s current SUBTOTAL documentation specifies that filtered-out rows are excluded for both function-number groups. Numbers 1–11 include manually hidden rows; 101–111 exclude them. For summation, the relevant numbers are 9 and 109.
For a vertical numeric detail range D2:D5, the proposed formulas are =SUBTOTAL(9,D2:D5) and =SUBTOTAL(109,D2:D5). They differ in their handling of manual row hiding. The documentation also says nested subtotals are ignored and explains that hiding columns does not provide the same behavior for a horizontal range. This example therefore uses only numeric detail cells in one column, with no embedded subtotal rows.
Jon Acampora’s February 2017 Excel Campus tutorial illustrates the distinction between filtered and manually hidden rows and describes using SUBTOTAL for filtered-range calculations. It is practitioner instruction, not an independent test of today’s AI assistants. Our function-behavior claims use Microsoft’s documentation; the reporting decision remains yours.
Do not infer the formula’s meaning from a heading such as “Total.” Inspect the actual expression, its range and the view conditions under which it will be used.
An invented four-record report
Suppose the following fictional detail records occupy rows 2–5. The headers occupy row 1, and Units is column D. The formulas sit outside the detail range.
| ID | Region | Status | Units |
|---|---|---|---|
| A | North | Accepted | 10 |
| B | North | Pending | 20 |
| C | South | Accepted | 30 |
| D | North | Accepted | 40 |
All records total 100 units. The explicit North-and-Accepted rule selects A and D, totaling 50. Filtering to North selects A, B and D, totaling 70. If the row containing record D is then manually hidden, the rows remaining visible are A and B, totaling 30.
That last value is arithmetically correct for the visible selection. It is not the North-and-Accepted total. In particular, the visible selection still contains the Pending record B and excludes the Accepted record D. The display operation and the business criterion are different selectors.
Keep the contributing IDs alongside the expected totals while developing the check. A matching number alone can hide a wrong selection: the visible North rows A and B happen to sum to the same 30 units as the single South record C.
Change the view without changing the data
The following sequence is a proposed Excel test. Each state is described explicitly; apply it to a copy of the fixture and record the actual results in your application.
| View state | Expected SUBTOTAL 9 | Expected SUBTOTAL 109 | Fixed North-and-Accepted total |
|---|---|---|---|
| All rows visible; no filter | 100 | 100 | 50 |
| Filter Region to North; no manual hiding | 70 | 70 | 50 |
| Keep North filter; manually hide record D’s row | 70 | 30 | 50 |
| No filter; manually hide record D’s row | 100 | 60 | 50 |
The numbers are invented expected outcomes, not product measurements. Python assertions checked the record sets and their sums: A+B+C+D = 100, A+B+D = 70, A+B = 30, A+B+C = 60, and A+D = 50. That exercise did not run an Excel calculation engine.
The third state distinguishes the two function numbers. Establish the fourth state explicitly: remove the filter, inspect visibility and manually hide record D’s row again if necessary. Do not assume how a filter command preserves or resets manual hiding. Restore the baseline before another independent test, or record exactly which view changes remain active.
A stable 50 in the final column is the expected outcome for the explicit criterion in this exercise. It is not an instruction to type 50 into the report. The production formula still needs to implement the rule, cover the intended source range and handle later data changes.
Make the assistant’s inspection scope visible
If an assistant reads a workbook through a tool, establish what that tool supplied: the full detail range, a selected range, values only or a view-dependent extract. Record limitations when the interface does not expose them. Do not assume a screenshot proves what hidden records contain.
For a fixed-rule summary, give the assistant the relevant detail data and approved criteria. If the available extract excludes records that could qualify, the right next step is obtaining adequate data or reporting that the requested total cannot yet be established. Filling the gap with an estimated value would change the task.
For a view-dependent worklist, preserve enough selection context to explain the result. That can include the filter criteria, manual hiding state, selected IDs, source revision and time of preparation. A later reader should know why the worklist total differs from the fixed reporting total.
Do not ask the model to choose between these totals based on which one looks more plausible. Inspect the selection rule and available records instead.
Small tests do not replace a workbook audit
Powell, Baker and Lawson’s 2008 paper, An auditing protocol for spreadsheet models, describes a layered audit that records workbook purpose, uses tools and reviews formulas. Its development experience also found input-change performance testing relatively ineffective in some operational spreadsheets where inputs and outputs were hard to distinguish.
This historical research does not measure AI-generated formula reliability or validate the proposed view-state exercise. It cautions against treating one convenient test method as a complete audit. The example here is deliberately small and explicit; a large inherited workbook may require substantially more investigation.
After checking the fixture, inspect the actual saved formula, referenced records and important dependent report cells. Recalculate in the application used by the workflow and record its version. Review any unrelated changes the assistant made before accepting the file.
If the formula passes the four states but refers to the wrong month’s data, it still fails the reporting task. View handling is one part of acceptance, alongside source completeness, date boundaries, units and the approved business rule.
Preserve the meaning when sharing the result
A copied number needs its population attached. Label an interactive selection as such, rather than presenting it as the fixed monthly total. Keep a reproducible record of the criteria or selected IDs when the result will inform a decision.
Inspect the actual shared artifact: the workbook, exported report or copied summary. Check that the surrounding title still describes the population the number represents. A caption that says “North accepted units” cannot be repaired by privately remembering that the displayed figure came from a different filter.
Start with one recurring total. State whether its population depends on the view, make the manual-hiding rule explicit and run the four-state check in the real spreadsheet application. Accept the result only within the scope actually verified, and retain the fixture for the next AI-generated revision.