AI Spreadsheet Totals: Decide What Hidden Rows Mean

Four rows of numbers can produce three defensible totals. The arithmetic can be right while the report answers the wrong question. In an AI-assisted Excel workflow, an instruction to add the visible amounts needs a clear definition of visibility.

Consider a hypothetical freelancer reviewing proposals for one campaign. They apply a filter, then manually hide another row while tidying the display. Before asking AI for a formula, they need to decide whether that second hidden proposal still belongs in the report.

A filter changes the view

Microsoft’s filtering guidance describes filters as temporarily hiding rows that do not meet the selected criteria. Multiple column filters further narrow the displayed subset. Record the active criteria when explaining the resulting view.

The business question remains yours to define. Do you want every amount in the log, every amount belonging to Campaign A, or only the amounts currently displayed? Those can be useful questions for different purposes. Give each result a label that states which question it answers.

A proposal hidden for presentation might still belong in a campaign review. Alternatively, the current view might be exactly what a colleague has requested. Choose the inclusion rule before choosing the formula.

Match the function to the inclusion rule

Microsoft’s SUM reference describes addition across values and references, and directs readers to SUBTOTAL when they want a visible-cell sum. In the simple numeric range below, ordinary SUM includes all four amounts.

The SUBTOTAL reference distinguishes filtered rows from manually hidden ones. Its sum options 9 and 109 both exclude filtered-out rows; 9 includes manually hidden rows, while 109 excludes them. That argument is part of the calculation’s meaning.

Work through four fictional records

In a test sheet, put Campaign in A1 and Amount in B1. Enter these records in rows 2 to 5, with amounts stored as numbers:

• Row 2: Campaign A; B2 contains 100.
• Row 3: Campaign B; B3 contains 200.
• Row 4: Campaign A; B4 contains 300.
• Row 5: Campaign A; B5 contains 400.

Filter A1:B5 to show Campaign A, excluding row 3. Then manually hide row 4. Put the three checking formulas outside that filtered range, for example in B8, B9 and B10. The expected outputs are:

• =SUM(B2:B5): 1,000.
• =SUBTOTAL(9,B2:B5): 800.
• =SUBTOTAL(109,B2:B5): 500.

The included amounts explain the difference: all four add to 1,000; Campaign A’s 100, 300 and 400 add to 800; the displayed 100 and 400 add to 500. No amount has been changed to make the answers agree.

These are illustrative expected Excel results based on the documented rules. The row-set arithmetic has been checked independently; run the example in the Excel version you use before applying the approach to a real workbook.

Reset the example and test separate states

Prepare each state independently from the fully visible baseline. With no hiding or filtering, all three results should be 1,000. With only the Campaign A filter, they should be 1,000, 800 and 800. With only row 4 manually hidden, they should be 1,000, 1,000 and 700, respectively.

That comparison helps expose an assumption hidden in the word visible. A formula can pass the all-visible example and still follow the wrong rule for a filtered report. Keep the expected outcomes beside the test data so a colleague can repeat the comparison.

This example uses a vertical column. Microsoft notes that SUBTOTAL’s row-hiding behaviour does not become a hidden-column rule for a horizontal range. Keep that boundary in mind when adapting the layout.

Read the formula in a table’s total row

Microsoft’s Excel table-total guide shows a Sum choice producing =SUBTOTAL(109,[Midwest]). The column name in brackets belongs to that documented table example. Its Total Row defaults use SUBTOTAL, although other functions can also be entered.

Consequently, inspect the actual formula in a workbook you receive. A label or remembered menu choice is not enough to establish the inclusion rule. Ask the owner what the total is intended to communicate before replacing an existing formula.

For the freelancer’s log, a view-sensitive total could suit an interactive review. A formal summary might instead require an explicitly agreed set of proposal records. Name that requirement directly; do not rely on a colleague keeping the same rows hidden forever.

Give AI the cases it must distinguish

A useful briefing could state: “Excel: A2:A5 contains A, B, A, A; B2:B5 contains 100, 200, 300, 400. Compare all records, filter-selected records including manually hidden ones, and rows remaining visible. Test a clean baseline, Campaign A filtering, row 4 manually hidden, and both conditions together. Preserve source values and explain which total matches my report.”

This is a proposed briefing method, not a claim that a particular assistant has passed it. Compare the suggested formula and explanation with the documented behaviour. Then check the agreed example in the application where the result will be used.

Give the assistant your business definition as well. If the requirement is approved proposals, describe what approval means and where it is recorded. Presentation choices alone should not quietly stand in for that decision.

Other formula ambiguities deserve their own examples. The MaryChuks guide to cells that look empty but still count explores a related gap between appearance and contents.

Share the number with its conditions

When handing over a summary, include the source range, the selected campaign or other criteria, the intended treatment of manual hiding and the time of review. A colleague should be able to reconstruct why particular records contributed without guessing from a cropped image.

If a result is intended to describe the current display, label it accordingly. If it represents an agreed campaign, preserve the criteria and supporting records. Where both answers matter, show both with distinct labels rather than asking one figure to serve two purposes.

Retain a copy before experimenting with a working report, and keep source entries unchanged while investigating a discrepancy. Resolve the difference between the intended record set and the formula’s record set first. A clearer instruction and a small checked example give AI assistance something concrete to satisfy.

Sources

Microsoft documentation checked on 27 October 2026; these pages do not display publication dates. The proposal log is fictional, and the arithmetic checks are independent additions of its specified row sets.

• SUM function.
• SUBTOTAL function.
• Filtering a range or table.
• Totalling an Excel table.