[9 Fixes] For Filter Not Working In Excel Problem

Facing problem just because your filter not working in Excel? No need to suffer from this problem anymore….!

As today I have chosen this specific topic of “Excel filter not working” to answer. All in all this post will help you to explore the reasons behind why Filter Function Not Working In Excel and ways to fix Excel filter not working issue.

Why Is Your Filter Not Working In Excel And Ways To Fix It?

Reason 1# Excel Filter Not Working With Blank Rows

One very common problem with the Excel filter function is that it won’t work with the blank rows. Excel Filter doesn’t count the cells with the first blank spaces.

To fix this, you need to choose the range right before using the filter function. For a clearer idea of how to perform this task, check out the following example.

  • Before using the Excel filter function on the column C. either you can choose the column “C” completely or just select the data which you want to filter out.

Excel Filter Not Working With Blank Rows 1

  • From the “data” tab select the “Filter” option. Now all the items become visible in the filter checkbox list or filtered list.

Excel Filter Not Working With Blank Rows 2

  • Similarly, you have to choose the cell ranges or multiple columns before the application of the filter function.

Excel Filter Not Working With Blank Rows 3

To fix damaged Excel workbook, we recommend this tool:

This software will prevent Excel workbook data such as BI data, financial reports & other analytical information from corruption and data loss. With this software you can rebuild corrupt Excel files and restore every single visual representation & dataset to its original, intact state in 3 easy steps:

  1. Download Excel File Repair Tool rated Excellent by Softpedia, Softonic & CNET.
  2. Select the corrupt Excel file (XLS, XLSX) & click Repair to initiate the repair process.
  3. Preview the repaired files and click Save File to save the files at desired location.

Reason 2# Make Proper Selection Of The Data

If your spreadsheet contains empty rows or columns. Or if you only want to filter out only the specific range then choose the section which you want to filter prior to turning on the Filter.

If you don’t choose the area then Excel applies the filter on the complete one. This can also result in selection up to the first empty column or rows excluding the data present after the blanks.

So it will be better if you manually select the data in which you want to apply the Excel filter function.

  • In order to remove the blank rows from the selected filter area. First of all turn on the filter and then click on the drop-down arrow present in any columns to show the filter list.
  • Now remove the check sign across the ‘(Select All)’ after then shift right on the bottom of the filter list. Choose the ‘(Blanks)’ option and tap to the OK.

Excel-Filter-Blanks

  • After this only, the blank rows will clearly appear on your screen.
  • For easy identification, the row number of each blank row appears in blue color.
  • For deleting up these blank rows. You need to select these rows first and then make a right-click over the anyone of the blue color row number. Choose the Delete option from the listed option to delete the select blank rows.

Excel-Filter blanks 2

  • By turning off the filter you can see the rows that are now been removed.

Reason 3# Check The Column Headings

  • Check the data which has only one row of column headings.
  • In order to get multiple lines for the heading, into the cell you just need to type the first line. After that press “ALT + ENTER” option for typing in the new line of the cell.
  • In such cases, the Wrap Text function also works great in formatting the cells correctly.

Check your column headings

Reason 4# Check For Merged Cells

Another reason for your Excel filter not working is because of the merged cells.

So unmerge if you have any merged cells in the spreadsheet.

If the column headings are being merged, then the Excel filter becomes unable to choose the items present from the merged columns.

The same thing happens with the merged rows. Excel filter won’t count the merged rows data.

Check for merged cells

For unmerging the cells:

  • Go to the Home tab and to the Alignment group.

  • Now hit the down arrow present across Merge & Center and choose the Unmerge Cells option.

unmerge cells

Reason 5# Check For Errors

Check carefully that your data doesn’t include any errors.

Suppose, if you are filtering the ‘Top 10’, ‘Above Average’ or ‘Below Average’ values within your list then using the Number Filters gets compulsory. Otherwise, the error may hinder your Excel application to apply the filter.

  • For removing up the errors use the filters to fetch them. Usually, they get listed at the list’s bottom so scroll down.
  • Choose the error and tap to the OK option. After locating up the error, fix or delete it and then only clear up the filter.

Check for errors

Reason 6# Check For The Hidden Rows

On the Excel filter, list hidden rows won’t appear as the filter option.

  • For unhiding the rows firstly you need to choose the area having the hidden rows. This means you have to choose the rows to present below or above of the hidden data.
  • After that either you can make a right-click over the rows header area. Or you can choose the Unhide option. OR just go to the Home tab > cells group> FormatHide & Unhide> Unhide Rows option.

unhide excel cells

 

Reason 7# Check For The Other Filters

Check that your filter is not been left on any other column.

The best option for clearing up all the Excel filters is by tapping to the Clear button present on the ribbon (which is on the right section of the filter button).

clear excel filter

This will leave the filter turned on, but it will clear all the filter settings. So now you can make a fresh start with your complete data.

Reason 8# The Filter Button Is Greyed Out

If your Filter function button is appearing gray in color then it means that the filter not working in Excel. The reason behind this can be that your grouped worksheets.

So check out whether your worksheets are grouped or not.

  • You can easily identify it by watching out the title bar where generally the filename appears at the screen top.
  • If the ‘Your file name’ – Group is appearing then it means that your worksheet is grouped.

Group-Worksheets-in-Excel-1

  • In this case, ungrouping the worksheet can fix filter function not working in Excel issue.
  • So come down to your worksheet and make a right-click on the sheet tab. After that choose the Ungroup Sheets option.

Ungroup-Selected-Worksheet-2

  • You will see, after making these changes again the filter option starts appearing.

Reason 9# The ‘Equals’ Filter Isn’t Working

While using the Equals filter, Number Filter, or Date Filter, if your Excel is not showing the right data then check whether the format of your data is the same or not.

Suppose, if you are having 2 cells and in each cell, you have entered 1000 as data. One cell is formatted in “currency” format and another one is formatted in “number” format. So when you make use of the “Number FiltersEquals” option, then Excel will only fetch the matches in which you have typed the number format also.

Only typing the text 1000 will search the match for the cells only in the number format. Whereas typing the text $1,000.00 will look for the cells which are formatted as ‘Currency’.

The same rule is also applicable in the case of dates. 16-Jan-19 and 16/01/2019 both are different. Thus make sure that the complete data column is formatted in the same format to avoid filter function not working in Excel issue.

Wrap Up:

The very first thing that Excel filters always check is whether your data matches well with its basic layout standards or not. If you are following all the Excel filters rules then you won’t get Excel filter not working issue.

So, take a close check whether you are following all the above mentioned Excel Filter rules correctly or not.

If none of the above fixes works to resolve your Excel Filter Not Working issue then ask about your queries on our Facebook and Twitter page.



Priyanka is an entrepreneur & content marketing expert. She writes tech blogs and has expertise in MS Office, Excel, and other tech subjects. Her distinctive art of presenting tech information in the easy-to-understand language is very impressive. When not writing, she loves unplanned travels.