Why Spreadsheet Rows Disappear When Filters Are Active

Updated 22/10/2026 · Reviewed content

An unexpectedly short spreadsheet list does not immediately establish that records were deleted. If filters are active, first identify the range and every selection rule, then account for which records should be visible. Recreating apparently missing entries too early can introduce duplicates.

The worked example below is invented: a community repair session in Seremban has six booking records. The records, arithmetic and diagnostic method are our own editorial illustration, not a test of a live workbook. Microsoft and Google documentation support the limited product behaviour attributed to them.

Start with the question the list should answer

Imagine that Lina is preparing the morning tool list for a repair session. The organiser asks for bicycle bookings in the morning, while another volunteer wants all bicycle bookings for the day. Those are different questions even though both volunteers use the same register.

Before investigating a missing record, write the intended question in plain language. For Lina it is, “Which bookings have Item equal to Bicycle and Session equal to Morning?” The word “and” matters: a booking has to meet both requirements to belong in that particular result.

A useful problem note also names the workbook and sheet. “There should be more rows” is difficult to investigate when two people are looking at different tabs, ranges or views. First agree on the actual data area and the intended question.

Work through a complete small register

Here is the whole fictional register for this example. Each row represents one booking, and each booking has a stable ID. There are no private customer details in this illustration.

Start with the item condition alone. Bicycle matches R01, R03 and R04, so the example produces three records. Add the morning requirement and R03 no longer qualifies; the answer becomes R01 and R04, or two records.

Now suppose a volunteer changes only the item selection to Lamp. If the morning condition remains part of the question, R02 qualifies and R05 does not. One visible lamp booking is consistent with those two conditions; it does not mean that the afternoon lamp booking has vanished from the register.

Reading down this table gives subsets of three, two, one and four bookings.

These counts are calculated from the six invented rows above. They are not attendance statistics or a prediction about a real event. The purpose is to make every inclusion and exclusion traceable to a stated rule.

Read the whole filter state

Microsoft's AutoFilter documentation says that rows are hidden when values fail the filtering criteria in one or more columns. This supports checking all active conditions rather than treating the most recently opened column menu as the whole query.

In Lina's example, changing Item does not express a decision about Session. Her diagnostic note therefore records both selections before she changes either one. If someone intended all lamp bookings, the remaining morning requirement is the specific discrepancy to resolve.

Our suggested reading habit is to translate the visible controls into one sentence. Include selected values, exclusions and any other active rule that affects the intended range. If you cannot explain a control, ask the sheet owner before editing shared settings.

Do not use gaps in row numbers as a complete diagnosis. They are a clue to investigate visibility, but they do not identify which operation produced the current display. Establish the active settings and compare known record IDs instead of deciding from appearance alone.

Establish who shares the view

Google's desktop Sheets help distinguishes a normal filter, visible to people with access, from a filter view that applies to your own view. It also describes temporary, unsaved filter views for viewers and named views for different simultaneous analyses. Check your access and current view type before changing settings.

For our scenario, Lina wants a morning bicycle list while her colleague checks afternoon lamps. A suitable separate view lets them pursue different questions. The handover should name the question and view, rather than say only “open the spreadsheet”.

A subset view is not a sharing brief. In this example, the organiser separately decides which information a recipient is authorised to receive. If the recipient should receive only a small extract, prepare and inspect that deliverable instead of relying on how a larger workbook happened to look on Lina's screen.

Diagnose one missing ID deliberately

Use a record you can identify reliably. In the fictional register, R03 is useful because its Bicycle value matches one condition while its Afternoon value fails the other. That contrast makes it possible to explain its absence without guessing.

1. Confirm the workbook, sheet and actual filter range being investigated.
2. Record every active condition and the intended question before making changes.
3. Compare the missing ID with a reliable full register or authorised reference.
4. Check its values against each condition and record the first mismatch.
5. In an appropriate separate view or with the owner's agreement, widen the relevant condition and look for that ID again.
6. Restore the intended working view and report what was established, including any unresolved discrepancy.

For R03, removing the morning restriction from the example question makes it eligible. That resolves this particular discrepancy in the invented data. It does not establish that every real workbook with missing rows has the same cause.

If the record still cannot be found, stop calling the issue a filter problem. Verify whether the reference covers the same sheet and range, whether the record was entered, and whether another visibility setting or a later edit needs investigation. Preserve the evidence rather than insert a replacement simply to make the count look right.

Report the subset with its boundary

A count should travel with its definition. “Two bookings” is incomplete for Lina's handover; “two morning bicycle bookings in the six-record example” states what was counted. Include the relevant date or snapshot when the real register can change.

Google's Sheets help documents a displayed-row count relative to a table's total after filtering. Treat any such indicator as information about its defined table, not automatic proof of the size of an entire workbook. A separate list elsewhere on the sheet requires its own boundary check.

Our recommendation is to pair a headline count with known IDs and the rule used. When the result matters, reconcile the complete intended subset with an authorised reference. A spot check can help explain one discrepancy, but should be reported as a spot check.

FAQ

Does a shorter filtered list establish deletion?

No. Determine the current filtering and range first, then compare the relevant IDs with a reliable reference. If the evidence does establish deletion or another edit, investigate that separately rather than force a filter explanation.

Why did choosing a different item still leave only one row?

Another condition may still limit the result. In our example, Lamp combined with Morning selects only R02. Read every active condition before deciding that a booking is missing.

Should I clear everyone's filters to investigate?

First establish whether the settings affect collaborators. Use an appropriate separate view where available, or coordinate the change with the owner. Keep the original question and settings so you can restore the intended work.

Can a filtered screenshot stand in for a complete register?

Only if that is the explicitly intended evidence and its limits are stated. A screenshot of Lina's morning bicycle subset shows two qualifying records; it does not describe every booking or prove the absence of afternoon work.

Related reading

If the problem concerns records acquiring the wrong quantities after reordering, use our spreadsheet sorting guide. For delivering a workbook to another person, the shared-file audience and role checklist addresses who should receive access. These are different checks from explaining a filtered subset.