If you are having a few hassles when filtering here are some reasons your Excel filter may not be working.
Excel has an expectation that you have prepared your data to meet some basic layout standards before you use filter.
Get these right and you will minimize filtering hassles.
Check you are following these rules when laying out and selecting your data, prior to filtering.
Check the range you have selected
If your data has empty rows and/or columns or if you are only wanting to filter a specific range, select the area you want to filter prior to turning Filter on.
Failing to select the area leaves Excel to set the filter area. This could lead to Excel selecting only up to the first empty row or column, excluding additional data past these points. It’s much better to manually select to be sure you have all of the data included.
Note: if you would like to remove blank rows from the filter area just turn on filter, click a drop-down arrow on any column to display the filter list. Remove the check mark from ‘(Select All)’ and then scroll right to the bottom of the filter list. Select ‘(Blanks)’ and click OK.
Now only blank rows will be displayed. You can easily identify the rows as the row number will now be coloured blue.
To delete the blank rows just select them and then right-click over the top of one of the row blue numbers. Select Delete to delete the rows. Turn filtering off and you will see that the rows have now been removed.
Check your column headings
Check your data has just one row of column headings.
If you need multiple lines for a heading, just type the first line into a cell and then press ALT + ENTER to type on a new line within the cell. Formatting the cell using Wrap Text also works.
Check for merged cells
Unmerge any merged column headings so that each column has an individual heading.
Check for errors
Check your data doesn’t include errors. For example, if you were trying to filter on the ‘Top 10’ values in your list (use Number Filters to do this), and error may stop Excel from applying the filter.
To remove errors first use Filter to find them. They are always listed at the bottom of the list so scroll right to the bottom. If you see one (or many) remove the check mark from ‘(Select All)’ at the top of the list first and then scroll back to the bottom of the list. Select the error and click OK.
Once you have located the error, fix it or delete it and then clear the filter.
Check for other filters
Check that a filter hasn’t been left on another column. The best way to clear all of the filters is to click the Clear button on the Ribbon (to the right of the Filter button). This then leaves Filter turned on, but removes all filter settings allowing you to start again with the full set of your data.
Extra bit of info…
If you are finding that your file is starting to become very slow at responding, i.e. the ‘whirly-wheel’ is displayed for more than a few seconds every time you update your file, check that you only selected the table area before you applied Filter, not entire rows, entire columns or the entire worksheet.
Also, if you have applied Filter to multiple tables within the file you might like to remove Filter from any tables you aren’t working with. This may also help eliminate ‘whirly-wheel’ moments.
If you found this post helpful please ‘Like’ us!
If you enjoyed this tutorial you might also enjoy:
- Filter data in Excel
- Hide errors in an Excel worksheet
- How do I sum a range of cells that include #N/A or #DIV/0! errors?
- Copy and paste excluding hidden columns or rows
Please take a moment to register your email so we can continue to share with you free tips, tutorials and the latest news in the industry!