Filter Not Working?

Is your filter not working properly?

The filter function in Excel can come in handy if you are dealing with a large spreadsheet and you need to sort the data according to a set of conditions. Sometimes when using the filter function, it might not work properly. The most common filter issue is blank cells within the dataset. Deleting the blank cells is a quick fix!

But sometimes blank cells are not the issue or maybe not the only issue and you have to dig a little deeper. Here is a list of some potential reasons why your filter function is not working:

Blank or Hidden Rows within your dataset
Vertical Merged Cells
Data Value Error
Selection of more than one spreadsheet
Your worksheet is protected

In this week's blog "How to fix Blank or Hidden Rows within your dataset" will be covered.

In this example the filter is applied to the first row with the column headings.

When you drop down on the "Order Date", the Date Filter only includes January and February. Where are March, April, and May?
Excel worksheet with sample data. Click on the drop down arrow for Order Date field.

Excel worksheet with sample data. Click on the drop down arrow for Order Date field.

To fix this problem, highlight the entire worksheet by pressing CTRL+A. Note: If the worksheet contains data, and the active cell is above or to the right of the data, pressing CTRL+A selects the current region. Pressing CTRL+A a second time selects the entire worksheet.

Once the entire spreadsheet is highlighted, right click on the mouse, and choose "Unhide".
Excel worksheet with sample data. Click on unhide to unhide the hidden rows.

Excel worksheet with sample data. Click on unhide to unhide the hidden rows.

This will reveal the blank rows that were hidden and were keeping months March, April, and May from showing up in the filter.
Excel worksheet with sample data showing the blank rows that were hidden.

Excel worksheet with sample data showing the blank rows that were hidden.

Press F5 to bring up the "Go To" box. Click on the "Special" button.
Excel worksheet with sample data showing the Go to box and the special button.

Choose "Blanks" and click OK.

The blank rows are highlighted.

Click on "Delete" and then "Delete Sheet Rows".

Now that all the blank rows are deleted, click on the drop down arrow for "Order Date". Voila! March, April, and May now show up in the Date Filter!

Stay tuned as more fixes for excel filter issues will be covered in future blogs. You can visit our website here to read more Tip Tuesday blogs.

Tip-Tuesday

Filter Not Working?

Get Started