Excel Filter Not Working [Causes & Tested Solutions 2024]

Written By Farhan Max

To search for a needle in a bottle of hay is extremely difficult, as is sorting the data with a condition in a large Excel sheet.

Fortunately, there’s an option called Filter, which comes in handy for sorting spreadsheets. However, there are instances when the option doesn’t work correctly or appears grayed out.excel-filter-not-working

But there’s nothing to be concerned about as I’ll go over the reasons and provide absolute solutions to the Filter option.

So, let’s begin.

How to Fix Filter Not Working in Microsoft Excel

Users can fix the filter option in Microsoft Excel by checking for errors, selecting data properly, wrapping column headings, unhiding rows and columns, and unmerging cells. Also, you need to ungroup and unprotect sheets to resolve the grayed-out option in Filter.

The solutions are very effortless, and you can fix the problem within a minute by following the methods below.

Here are the ways to fix the Filter option in Microsoft Excel:

1. Check for Errors

Error is the first thing you need to check in the spreadsheet. It can appear due to incorrect equations, invalid cell values, etc.

You can get an error if you’ve copied a cell from another Excel sheet and pasted it with the formula. You must copy and paste the values without a formula to avoid errors.

2. Select Entire Data

When you apply the Filter option in a datasheet, it only evaluates the data up to the first blank rows or columns. If you have empty rows or columns in the spreadsheet, excel won’t select the data below or past the rows and columns.

Therefore, it’s ideal to select the data manually to include your desired rows and columns. But when there is a lot of data in the sheet, you can follow the process below to select quickly.

Here’s how you can select the entire data and use Filter in Excel:

  • Press Ctrl + A twice to select all the data.
  • Navigate to the Data tab from the top.
  • Click on Filter. select-entire-excel-data

After using the Filter option, Excel will return data from the entire datasheet.

3. Check the Column Heading

Multiple lines in a column heading confuse the Excel Filter. So, using a single line as the column heading is better.

But sometimes, you may need to use multiple lines as the heading. In that specific case, you can type your first line and press Alt + Enter to create a new line within the cell.

There’s an additional way to use multiple cells in a cell by using the Wrap Text option.

Follow the process below to use the Wrap Text option in an Excel cell:

  • Select the cell.
  • Go to the Home tab.
  • Press on Wrap Text. excel-wrap-text

It will automatically create multiple lines within the cell by keeping the actual cell width. If you’ve added total rows in Excel, it will help to identify and sort the values quickly.

4. Unhide Rows & Columns

Excel allows the user to hide a row or column in a spreadsheet. But Excel excludes the hidden data when you apply a filter to the sheet.

If you’ve hidden rows or columns, you must unhide them to get the proper result.

Read the following steps to learn the rows and columns unhiding process in Excel:

  • Click on the row or column header.
  • Go to the Home tab from the top.
  • Press on Format and then Hide & Unhide.
  • Click on Unhide Rows or Unhide Columns. excel-unhide-rows-columns

After revealing the hidden columns and rows, you can Filter the entire sheet and get an accurate outcome.

5. Unmerge Cells

Merged cells are a common reason behind the sort and filtering not working in Excel as intended. When you merge the cells, the Filter can’t collect data from their individual columns.

If you’ve merged the column heading in the spreadsheet, you must unmerge the cells in the Excel sheet to use the Filter option properly.

Follow the instructions below to unmerge cells in Excel:

  • Select the merged cell.
  • Navigate to the Home tab.
  • Click the drop-down menu beside Merge & Center.
  • Press on Unmerge Cells. excel-unmerge-cells

After unmerging the cells, apply the Filter option and get results from every individual cell.

6. Ungroup Sheets

If your Filter option appears grayed out in Excel, you may have grouped the Excel sheets.

The Filter option doesn’t work in grouped Excel sheets. Hence, you need to ungroup the sheet before using the Filter option in a particular sheet.

Read the steps below to ungroup the Excel sheet:

  • Right-click on the grouped sheet from the bottom.
  • Press on Ungroup Sheets. excel-ungroup-sheets

Once you’ve ungrouped the sheets, the Filter option will appear again and can be accessible.

7. Unprotect Sheet

Microsoft Excel has the option to protect your spreadsheet from unauthorized editing. You need to provide a password to make it secure from others.

But, if you’ve added a password in the Excel sheet, the Filter option will appear grayed out, and you’ll be unable to organize the data.

Read the instructions below to unprotect the sheet in Excel:

  • Go to the Review tab from the toolbar.
  • Click on Unprotect Sheet.
  • Enter your password and press OK. excel-unprotect-sheet

After unprotecting the Excel sheet with a password, the Filter option will start working again.

If you want to know the reason behind the Filter option problem, continue reading the passage below.

Why is the Filter Option Not Functioning in Microsoft Excel?

The Filter option isn’t functioning on your Microsoft Excel sheet because of errors in the spreadsheet, having multiple column heading lines, hiding rows or columns, and merging column heading cells. Additionally, you can get a grayed-out problem due to grouped and protected sheets.

To learn about the causes in detail, consider reading the following passage.

Here are the reasons behind the Filter option not functioning in Microsoft Excel:

Errors in Spreadsheet

Errors can appear in Excel sheets due to inserting or pasting incorrect formulas into a cell. If the selected data range has an error, it can cause issues while getting a result from the Filter option.

Multiple Column Heading Lines

When the column heading has multiple lines, Filter gets confused and fails to give an accurate outcome. Your Excel Filter isn’t working because of multiple lines in a column heading cell.

Hidden Rows & Columns

Excel doesn’t count the hidden rows and columns in the Filter option. If the spreadsheet has one or multiple hidden rows and columns, it will exclude them from the sorted result.

Merged Cells

Merged column heading acts as a single column in the Filter option. As a result, individual columns under the heading don’t appear in the filter result.

If your sheet has merged column headings, you will receive information from only some cells and face problems.

Grouped Excel Sheets

The Filter option works for an individual sheet. When you group the sheets, the Filter option becomes grayed and inaccessible. You are facing problems because of making grouped sheets intentionally or mistakenly.

Protected Excel Cells

The protected sheets option secures the spreadsheet from unauthorized editing and making any filters to the data.

If you’ve enabled password protection, you won’t be able to use the Filter option in Excel.

The above reasons will help you to detect and understand the problem quickly.

Frequently Asked Questions

How do I fix the filter option in Microsoft Excel?

You can fix the Filter option in Excel by checking errors, selecting data accurately, wrapping text, unhiding rows, and columns, unmerging cells, ungrouping, and using unprotected sheets.

How do I filter data in Excel?

To filter the data in Excel, select all data > Data > Filter > click on the column heading > choose filters > OK.

Why is the filter option not working in Excel after a certain row?

The Excel filter won’t work after a blank row if you don’t select the rows manually or highlight all the spreadsheets.

Why is the filter option grayed out in Excel?

The Filter option has appeared grayed out in Excel because of grouped sheets and for protecting the sheets with a password.


The filter is beneficial for sorting out the data in an Excel spreadsheet. But it’s pathetic and takes time when the Filter option doesn’t work.

In this article, I’ve provided reasons for the Filter option not working in Excel and their solutions.

If you find this article helpful, don’t forget to share it in the comment box below. Cheers!