Filtering a worksheet changes what is visible, but the total beside the data may still answer a different question. A report showing one region can be confusing if its summary continues to represent every region.
Excel’s SUBTOTAL function provides a way to calculate over filtered lists. Its most important setting determines how manually hidden rows are treated—a distinction that can change the meaning of a report without changing any source values.
Separate filtering from manually hiding rows
Excel distinguishes rows excluded by a filter from rows hidden using a manual command. Microsoft documents that SUBTOTAL always excludes filtered-out rows. Function numbers 1–11 include manually hidden rows, while 101–111 exclude them.
For a sum, the relevant function numbers are 9 and 109:
=SUBTOTAL(9,D2:D100)=SUBTOTAL(109,D2:D100)Both respond to filtering. Their treatment of manually hidden rows differs.
Before choosing between them, describe the intended total in a sentence. “Total for the selected region” may require a different policy from “total of all currently visible entries.”
Test the difference with a small example
Suppose three amounts are 100, 200, and 300. With all rows visible, both formulas return 600.
If a filter excludes the row containing 200, both return 400. If instead that row is manually hidden, the formula using 9 still returns 600, while the one using 109 returns 400.
This example reveals a reporting decision. Was the row hidden only to simplify the display, or should hiding it remove its contribution from the calculation?
The formula cannot infer that intention. The workbook author must choose and communicate it.
Do not test only the unfiltered worksheet. The two formulas look equivalent until the visibility conditions change.
Give the result an accurate label
A heading such as “Total” leaves too much unstated. Consider labels such as “Filtered sales total” or “Visible-row total,” depending on the selected behavior.
For a workbook that colleagues will use interactively, add a brief note explaining whether manually hidden rows count. This prevents someone from using Hide Rows as an informal filtering method and unintentionally changing the report’s interpretation.
Also check that the referenced range includes all intended records. Correct handling of visibility does not compensate for a formula that stops before the newest entries.
Where a report contains several totals, keep their policies consistent or explain why they differ.
Validate the report before sharing it
Create a small review sequence: show all data, apply a filter, hide a qualifying row manually, and restore the full view. Compare each result with an independently calculated expectation.
Microsoft notes that SUBTOTAL is intended for vertical data ranges. Hiding a column in a horizontal calculation does not behave like hiding a row in a vertical one.
Avoid extending a familiar formula into a different layout without testing that distinction.
Finally, preserve the context when exporting or taking a screenshot. A number detached from its filter selection can become misleading even when the spreadsheet calculation is correct.
A trustworthy subtotal combines three things: a defined inclusion rule, a formula that implements it, and a label that tells the reader what the result represents.
Sources: Microsoft: SUBTOTAL function