Have you ever had a situation where you applied an auto-filter and found that the auto-filter was only working on the 20th row, even though data was included up to the 100th row? In this article, we will introduce what you should check when you encounter such a situation.
How to narrow down the auto filter
As examples, we have prepared Tables 1, 2, and 3 like this one.
Table 1 has data in all cells, while Table 2 and Table 3 have blank cells with missing data. Blank cells are grayed out for clarity.
Let's narrow this table down to just the rows for name "A".
To filter by name, click the "▼" mark in the name column, uncheck "Select All" and then check only A, then click the "OK" button.
We can now narrow down the list to only "A"'s rows.
This can be done even for Table 2, where data is toothless, and can be narrowed down in the same way.
How to check if auto-filtering is applied to the last line
An example of a case where the filter is not applied to the last line is this.
As in Table 1 and Table 2, I narrowed down to only the rows for "A" in the name column in Table 3.
Both "G" and "H" are displayed even though they have been narrowed down in the same way as in Tables 1 and 2.
If you look closely at the line numbers, you will see that some lines have "blue" line numbers and others have "gray" line numbers.
This is because the row numbers of filtered rows are displayed in "blue" and all other unfiltered rows are displayed in "gray".
In other words, when you look at the last row of the table with the filter applied, if the row number is "blue", all rows are filtered, and if the row number is "gray", the filter is broken somewhere in the middle.
Causes the auto filter to cut out in the middle of the process.
If you look at the differences in the tables, you will see that Table 1 has no missing teeth in the data.
Table 2 has missing data, but there are no rows with no values in all columns.
On the other hand, Table 3 differs from Table 2 in that the missing cells in row 8 are aligned horizontally, i.e., there is no cell in row 8 with data entered.
In this case, the auto-filter recognized that the data was only entered up to row 7 and that rows 8 and beyond were a separate table. Therefore, when the filter is applied in this way, it does not hide rows 8 and beyond, as we tried above.
How to make the table so that the auto-filter does not cut off in the middle
The way to prevent the auto-filter from cutting off in the middle of a row is to have a column that always contains a value in every row, such as the "№" column.
This allows you to filter down to the last line.
This is recommended because it is very convenient and easy to restore the original sort order (ascending order by №) even if, for example, the data is sorted in ascending order by numerical value.
Conclusion
These are the causes and countermeasures for the auto-filter cutting out in the middle of the process.
However, inserting columns may result in misalignment of column numbers, etc., and other points that need to be fixed may increase.
In that case, there is an emergency way to connect the auto-filter to the last row by putting some value in a blank cell somewhere in the column, either a space or anything else.
For example, let's enter a space in column "B" of row 8, which is the cause of the broken filter in this case.
Then the filter could be applied to the last row as in Table 1 and Table 2.
This is quicker and easier as a stopgap measure, but it is not a fundamental solution, and there is no telling when the filter will break again in the same way.
Please try whichever is more convenient for your situation.
English 
日本語
Comment