Microsoft Excel is a great software for organizing numerical data. It has tons of features that greatly simplify people's lives. The slicer is one of those features that people love to have in Excel. It is very useful for classifying and extracting specific data from documents.
However, sometimes the filter in Microsoft Excel doesn't work as it should and it becomes a real headache to figure out what's wrong. If you are having trouble filtering data in MS Excel, we have found simple solutions for you that will solve your problem. This problem is common to many of our users and occurs for several reasons, so please read all the methods first and then decide which one is best for you. If unfortunately one doesn't work, you can always try the other.
Method 1- Apply the filter to the entire column
STEP 1- Select the column you want to filter by clicking the column letter, when you select the column correctly, Excel will highlight the column as following screenshot shown.
STEP 2 - Click Data tab in the main menu and then click filter button.
STEP 3 - You will find a file inverted triangle to the right of the first number in the selected column, click the left mouse button. The filter options pop-up will open. In the same pop-up window, you can see that all numbers in your column are selected by default. Scroll to the last number e deselect blank spaces to disable the blank cell filter.
STEP 4 - Apply the filters you want to apply to the column and then click ok to save your settings.
Method 2: remove blank cells from table
Blank cells make the filtering process difficult in MS Excel, so before you filter a column, make sure you don't have any blank cells in your data, which may cause problems when filtering the data. To remove unwanted cells from the column, you will need to remove the rows where the blank cell is present.
To delete blank cell rows, left click on the row number which has a blank cell, it will highlight the whole row. Right-click on the same line number and select the file To remove option. If you have multiple blank cells, first select all by holding Ctrl and left clicking the row number and then delete all rows at once.
NOTE: Double check the row before deleting anything because if you delete something important it will be difficult to restore it.
Method 3: separate the sheets
Filter options are disabled when you have grouped sheets in your document, all data tab options would be grayed out if the document has grouped sheets. This is an annoying problem because you can't figure out what's wrong with Excel and why the data card is disabled. As annoying as this problem is, the solution is very simple.
Select all sheets by holding the Ctrl key and left clicking on the sheets, right clicking on one of the sheets and selecting separate the sheets option. You will see that your Data tab is now enabled.
Method 4: remove the protection
Password-protected spreadsheets are limited for editing and therefore cannot be filtered. Therefore, to filter the data in the protected document, you must first unprotect the file. Follow these steps to extract your file. You will also need the password for the file
PHASE 1 - Go to review Excel main menu tab.
STEP 2 - Click Unprotected sheet, a password popup will open.
STEP 3 - Write the file Costa degli Etruschi is the password sheet and press Enter. Your spreadsheet is now unlocked.
Method 5: separate cells
If you have merged cells in your document, the filter option will not work, so you will need to separate the cells. Follow this procedure to separate cells in the document.
STEP 1 - Press Ctrl + F to open the Find and Replace menu, click options >> to enlarge the pop-up window.
STEP 2 - Click Format , another window will open.
PHASE 3 - Go to Alignment tab and select combine cells. Click ok to save the settings.
STEP 4 - Click Find everything in the find and replace window. It will list the merged cells in the document.
STEP 5 - Click the inverted triangle button next to the merge and center button on the Home tab, select the separate one(without closing the search and replace window). It will automatically separate all cells in the document.
Method 6: reapply the filter
In some cases, due to some bugs, the filter doesn't work, but reapplying the filter after clearing all filters works fine. Also, in many cases, it occurs due to multiple column filtration, so cleaning the filter column helps.
STEP 1 - Select the column by clicking on the column letter
STEP 2 - Click Clean filter in the Data tab (top right of the filter button).
STEP 3 - Once again select the column and click the filter button. Select the filter option you want and your data will be filtered correctly now.
This will solve your filtering problems in Microsoft Excel, please comment if you have any problems following the steps.
