If you work with large volumes of data, you need to use Excel filters.
When you add a filter in Excel, analyzing data becomes much easier because you’re able to focus on a more targeted data set… but what if your Excel filter isn’t working?
Here are 10 things to check if your Excel filter isn’t working – along with the associated solutions.
Problem #1: Blank Rows
Excel data lists should not contain blank rows. One or more blank rows between the column headings and the first record in your list or anywhere else – can cause big problems!
Solution: Delete the Blank Rows in Your List
To eliminate blank rows, drag to select your entire data list before turning a filter on.
After enabling the filter, click the down arrow next to any column heading, clear the check box next to Select All, click Blanks, then click OK.
Once you filter your list to display blank rows, drag across the row selectors for the blank records, right-click anywhere in the selected area, then click Delete Row from the shortcut menu.
Problem #2: Column Headings
Column headings identify the fields in your data list. They should be formatted differently than the records in your list and they need to be in the first row.
Solution: Enter Headings Correctly
I make my headings bold. You can also center them if you want to. Just make sure they’re formatted differently than the other rows in your data list.
Headings should be the first row in your data list. In the screenshot below, the heading “Annual Salary” has been entered in two cells. This can cause problems when filtering your list.
If you want Annual Salary to appear on two lines, type Annual then press Alt + Enter, and type Salary.
Problem #3: Errors in Your Data List
Errors can cause problems in any spreadsheet. In a data list, you may notice your Excel filter not working… or incorrect filter results being displayed.
If this happens to you, just look for the green triangles!
Solution: Fix the Errors!
In the spreadsheet below, cell C4 has a green triangle displayed in the upper-left corner of the cell indicating a number stored as text error, which could cause your Excel filter to display invalid results.
In this case, the fix is fairly straightforward. Select cell C4, click the Error button, then select Convert to Number from the drop-down menu.
The bottom line is you need to fix any errors in your spreadsheet before you filter in Excel. Correct any cell references or formulas that are causing #DIV/0, #REF, or #VALUE errors!
Problem #4: Merged Cells
Merged cells can cause problems when you add an Excel filter.
You have to remember that each column represents a field in your data list. If two cells are merged, it means that one record will have fewer fields that the rest of them, leading to unpredictable results when you filter data.
Solution: Unmerge Cells as Required
If merged cells are causing problems when you filter in Excel, you have to unmerge cells within your data list.
Simply select the merged cell and click the Merge & Center button in the Home > Alignment group on the ribbon in Excel.
Problem #5: Hidden Rows or Columns
Hidden rows or columns can produce unexpected results, if you don’t know they exist.
An Excel filter essentially hides rows based on criteria you provide. If you’re not aware that certain rows or columns are already hidden, you may make assumptions about your data that are incorrect.
Bottom line? Make sure there aren’t any hidden rows or columns in your spreadsheet before you use a filter in Excel.
Solution: Unhide All Rows and Columns
To unhide all rows and columns, click the Select All button (above the row 1 heading and to the left of the column A heading).
Right-click on any row heading and select Unhide then right-click on any column heading and select Unhide.
Once you do these steps all hidden rows and columns are revealed – and this is a much better starting point for filtering data.
Problem #6: The List is Already Filtered
If you attempt to filter a data set that already has filters applied, you might be puzzled by the results.
In the spreadsheet below, there are a couple of visual indicators that the list has filters. A funnel icon appears on the button next to Dept heading in cell B1 and the row numbers are no longer in sequence.
Solution: Clear the Criteria from Your List
If you think an Excel filter has already been applied to your data, clear the criteria from your list. Select any cell in the data list then click the Clear button in the Data > Sort & Filter group.
Problem #7: Incorrect Format Used for Criteria
Specifying criteria in a different format than your data list can cause problems – especially when it comes to dates.
If the dates in your list is formatted using the default format for the USA (m/d/yyyy) and you specify criteria that uses yy/m/d, the filter isn’t going to work.
Solution: Use the Correct Format When Specifying Criteria
Examine the data in your list and be sure to use the same format when specifying criteria. This is most important when analyzing records containing dates.
Problem #8: Worksheets are Grouped
When Excel worksheets are grouped, you can enter headings and apply formatting in a very consistent manor. Grouping sheets is an efficient way to build a workbook but you have to ungroup sheets before you can apply filters.
The filter button is greyed out while your worksheets are grouped. In the image below, you can see that each of the worksheet tabs has been selected and appears in white.
Solution: Ungroup Sheets Before Filtering Data
To ungroup your worksheets, right-click any tab and click Ungroup Sheets. The Filter button in the Data > Sort & Filter becomes available and is no longer be grayed out.
Problem #9: Protect Sheet is Enabled
If the Filter button is greyed out along with many of the other commands in Excel’s ribbon, it could mean that worksheet protection has been enabled.
If you see the Unprotect Sheet button in the Review > Protect group, it means that worksheet protection has been enabled, it keeps you from using the filter in Excel.
Solution: Turn Worksheet Protection Off
To disable worksheet protection, click the Unprotect Sheet button in the Review > Protect group. Enter the password when prompted to restore access to filters in Microsoft Excel.
Problem #10: Filter by Color, Filter Blanks Not Available
The ability to filter by color or filter blanks will only be available if you format records in your data list using color – or if blank cells exist.
Using color can be an effective way to identify records with certain attributes in common. In the screenshot below, yellow might be used to indicate that Apple Lyn is a new hire.
You can easily identify employees that haven’t been assigned to a department by filtering for Blanks in the Dept column.
Solution: Utilize Color and Blank Cells as Appropriate
In order to filter for specific colors and blank cells in your list, you need to incorporate these elements into your data.
What’s Next?
I hope you found these tips for troubleshooting Excel filters useful.
If you need a refresher on the basics of filtering, check out how to add a filter in Excel to display relevant data.
If you want to become more proficient using the filter feature in Excel, there is an article on how to filter Excel data faster with these 15 keyboard shortcuts.
See you next time!
