Why your spreadsheet total changes when rows disappear

Decide what the report is meant to include

A total on a filtered worksheet may describe only the records currently visible. Before turning that figure into an automated weekly update, write down the inclusion rule: for example, approved orders for this week, excluding cancelled orders. A colleague hiding a row to make the sheet easier to read should not silently decide which business activity your report counts.

Know the difference between filtering and hiding

Microsoft documents that Excel's SUBTOTAL function excludes rows removed by a filter. For manually hidden rows, its function numbers behave differently: 9 adds them, while 109 leaves them out. These examples apply to vertical ranges. Hiding a column in a horizontal range does not provide the same exclusion. Check the actual formula and range, rather than assuming that everything you cannot see has been removed from the calculation.

Documents filtered and manually hidden row handling and vertical-range limits; the worked example applies these rules. Microsoft: SUBTOTAL function

A small example you can reproduce

In a test copy, put three amounts in B2:B4: 100, 200 and 300. With no rows hidden or filtered, SUBTOTAL(9,B2:B4) and SUBTOTAL(109,B2:B4) each return 600. Manually hide the row containing 200: the first remains 600, while the second becomes 400. Restore it, then filter out that row: both return 400. This hypothetical exercise shows why two apparently similar reports can disagree without any transaction changing.

Use a business rule instead of a visual shortcut

For a proposed weekly orders report, use explicit fields such as order ID, approval status and reporting date. Agree how cancellations, missing dates and late updates should be treated. Save the selected IDs with the report. If a record has no status, put it in an exception list instead of letting an assistant infer whether it belongs. Keep the original worksheet unchanged so someone can trace a disputed number back to its source.

Check what the integration actually reads

Do not assume an export or connector reproduces the screen's current view. Test the exact workbook and integration with a visible row, a manually hidden row and a filtered-out row. Inspect the records it returns and apply the approved inclusion rule explicitly. If the tool cannot expose the fields needed to make that decision, revise the input or use a reviewed export. AI can help explain a discrepancy, but should not invent a missing approval or quietly change the report's scope.

Reconcile the selection before distributing the result

Have the report owner compare the selected order IDs, record count and total against an independently reviewed sample. Keep the reporting period and inclusion rule beside the result. Test an empty period, a cancelled order and an order approved after the first run. Decide whether a late change creates a revised report and who receives it. Matching one total is insufficient when the wrong records could happen to add to the same amount.

Start with the smallest useful improvement

A clear report label and a corrected formula may solve a one-off problem without a custom system. For a recurring report, Exovara can assess a workflow that prepares a selection list, explains exceptions and produces a draft for staff approval. Measure the time currently spent reconciling different versions, then subtract the time still needed to review exceptions. Compare that recovered capacity with setup, software and maintenance costs; it is not automatically a reduction in payroll spending.

Discuss an implementation

This guide applies across Canada. We provide remote AI consulting for businesses in Burnaby; it does not describe a local client or a staffed office.

AI consulting in Burnaby · Explore the service · Compare setup options