Spreadsheet Filter

A filter narrows what a sheet displays to the rows meeting a condition, and leaves everything else in place. Google’s documentation is explicit that filters conceal rather than remove, and that the sheet reports “the number of rows being displayed out of the total rows in the table”. That count is the practical difference between a filter and a deletion, and it is worth reading before trusting anything computed on screen.

Filtering differs from sorting in what it touches. Sorting changes the order of rows permanently; filtering changes only visibility and can be switched off to restore the full view. Because nothing is written, a filter is the safest way to explore an unfamiliar dataset — and the natural first step before any destructive operation, since it shows what that operation would affect.

For cleaning it answers questions sorting cannot. Show only blank cells to see where values are missing, only rows above a threshold to inspect suspicious amounts, only one category to check whether its spelling is consistent. Each of those isolates a cleaning task from the rest of the data, which is also how you confirm a proposed deletion before running it.

The hazard is that a filtered view looks like the whole dataset, while the numbers on the sheet may not describe what you see. SUM adds every cell in its range, including the rows the filter has hidden, so a total can exceed the sum of the visible rows. SUBTOTAL with function number 9 leaves out the rows hidden by a filter, so it matches what is on screen. Which one a cell holds decides whether the total describes the visible subset or the whole table.

The same gap appears when you share work. What a copy contains depends on the app and on how the cells are selected: Microsoft documents that Excel by default can copy hidden or filtered cells along with the visible ones, and that copying only the visible cells takes a separate selection step (and, for a plain range in Excel for the web, is not available). So a recipient may receive either the displayed subset or hidden rows too, and may draw conclusions from a part of the data or from rows the sender never saw. Check the pasted rows against the filter state, compare the displayed-row count with the total before reporting a figure, and clear the filter, or say which condition is applied, before sharing. Where several people use one sheet, a personal filter view avoids changing what everyone else sees.

References:

• Sort and filter your data
• Filter data in a range or table