I'm running into an issue that I do not know how to fix. I have a spreadsheet with an excel table. The automatic filter at the top of one column is no longer allowing users to select (blanks) as an option. All other columns with blank entries are still working as they are supposed to.

When I sort the automatic filter by select all, it chooses not to select those rows where there is no data entered. I have tried making sure no spaces are in those cells but it has not fixed the issue. Does anyone know how to resolve this? It is only showing up in one column of the table.

EDIT: I don't know if this was part of the original problem (another user reported it to me) but now the automatic filter on one column not only does not show blanks but will filter out several rows near the bottom that do have values in their cells. It filters these rows even when I tell it to filter by "Select All"

  • What kind of data do you have in that column? What is the CTRL+1 data type setting for the column?
    – Hannu
    Nov 28 '16 at 18:28
  • The column should be Text data. When I ctrl+1 it states it is general with no specific number format. Oddly, when I filter by Select All, it is also filtering out a couple of the newer entries which have text in their columns. After your comment, I selected all and set the format to text but the issue still occurs. Nov 28 '16 at 18:37
  • There seems to be bunch of issues listed here ccm.net/forum/affich-5838-excel-autofilter-stops-filtering - I'm not sure how relevant they are though.
    – Hannu
    Nov 28 '16 at 18:41
  • e.g. do you have any "Hidden" row or column?
    – Hannu
    Nov 28 '16 at 18:43
  • There are several columns that are hidden at various times. Unfortunately, the worksheet is used by several different users who need/work with different portions of the worksheet. We tend to hide some columns we don't need when using it. Nov 28 '16 at 18:58

I would convert the table back to Range, use Clean for every column and copying the result and pasting to the original column as Values only. Then convert to Table and see if the problem persist.

  • That's what I ended up doing and that seems to have fixed the problem. I also think I found what may have caused it. I believe the table was already filtering by another filter which made it ignore blanks in that column..maybe. Clearing out all filters seems to fix it. Dec 6 '16 at 15:35

Filter will only show "selection options" for the first 10 000 records. Thus if you have your first blank at record 10 0001 you will not have the Blanks option in the filter list. The only way I found to circumvent this is to create a Blank in the first 10 000 records, by deleting a value. This will then give you the selection option to filter for blanks. Just update the value afterwards.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.