Microsoft Excel is great software for organizing numeric data. It has a variety of features that make people's lives much easier. Filtering data is one of the features that people love in Excel. It is very helpful in sorting and extracting specific data from the documents.
But sometimes the filtering in Microsoft Excel doesn't work as it should and it becomes really troublesome to solve the problem. If you are having trouble filtering data in MS Excel, we have found you simple solutions that will fix your problem. This problem is common among many of our users and occurs due to several reasons. So read all the methods first and then decide which one is best for you. If one doesn't work, you can always try the other.
Method 1 : Apply filter to entire column
STEP 1-Select the column you want to filter by clicking on the column letter. After successfully selecting the column, Excel will highlight the column as shown in the image below.
STEP 2- From the main menu, click the Data tab and then click the Filter button .
STEP 3-You will find an inverted triangle to the right of the first number of your selected column, left click on it. It opens the filter options pop-up window. In the same popup you will see that all the numbers from your column are selected by default. Scroll down to the last to last number and uncheck Spaces to disable blank cell filtering.
STEP 4-Apply the filters you want to apply to the column and then click OK to save your settings.
Method 2-Delete Blank Cells from Table
Empty cells hinder the filtering process in MS Excel. Therefore, before filtering a column, make sure that there are no blank cells between the data, which would cause problems when filtering data. In order to delete unwanted cells from the column, you need to delete the rows where the blank cell exists.
To delete empty rows of cells, left click on the row number that contains an empty cell. This highlights the entire row. Right-click the same line number and select the Delete option . If you have multiple blank cells, first select all by holding Ctrl key and left clicking on the row number, then delete all rows at once.
NOTE: Double check the line before deleting anything, because if you delete something important, it's hard to recover.
Method 3 : Ungroup tables
Filter options are disabled when you have grouped sheets in your document. All Data tab options are grayed out if the document contains grouped sheets. This is an annoying problem because you can't figure out what's wrong with Excel and why the Data tab is disabled. As annoying as this problem is, the solution is quite simple.
Select all sheets by holding ctrl and left clicking on sheets. Right-click one of the sheets and select Ungroup Sheets . You will notice that your data tab is now activated.
Method 4 : Remove protection
Password-protected spreadsheets are restricted to editing and therefore cannot be filtered. So, in order to filter data from the protected document, you must first remove protection from the file. Follow these steps to unprotect your file. You will also need the password of the file
STEP 1- Go to Review tab in Excel main menu .
STEP 2-Click Unprotect Sheet to open a password pop-up.
STEP 3-Enter the sheet's password and press Enter. Your table is now unlocked.
Method 5 : cancel cells
If you have merged cells in your document, the filter option will not work, so you will have to unmerge the cells again. Follow this process to ungroup the cells in your document.
STEP 1-Press Ctrl+F to open Find and Replace menu. Click the Options>> button to enlarge the popup.
STEP 2-Click Format button to open another window.
STEP 3-Go to Alignment tab and select Merge Cells . Click OK to save the settings.
STEP 4-In the Find and Replace window, click Find All button . It lists the merged cells in the document.
STEP 5-Click the inverted triangle button next to the Merge and Merge button on the Home tab and select Unmerge Cells (without closing the Find and Replace window). It will automatically ungroup all cells from the document.
Method 6 : Reapply the filter
In some cases the filtering doesn't work due to an error, but reapplying the filter after clearing all filters works fine. In many cases, this also happens due to the multiple filtering of the column, so clearing the filter's column helps.
STEP 1-Select the column by clicking on the column letter
STEP 2-Click the Clear Filter button in the Data tab (top right next to the Filter button).
STEP 3-Select the column again and click on the filter button. Choose your desired filter option and your data will now be properly filtered.
This solves your filtering problems in Microsoft Excel. Comment if you encounter any problems after the procedure.
แสดงความคิดเห็น