Sort is such a powerful tool that can organize text, numbers, and dates. Besides, you can easily retrieve your data based on your requirements using the Filter tool. But you might have faced trouble while working with these tools. In this article, I’ll show 8 issues for not working with sort and filter in Excel along with their solution.
Let’s introduce today’s dataset (B4:F15 cell range) as shown in the following screenshot. Here, the Number of Visits for each website is provided along with the Name and Category of the Sites. Besides, the dates and mode of Platforms are also given.
1. Excel Sort Is Not Working When Any Blank Cell Exists
Assuming that F7 and F10 cells are blank in the dataset as shown in the following dataset.
What if you apply the Sort tool?
➤ Firstly, select the whole dataset and choose the Custom Sort from the drop-down list of the Sort & Filter option in the Home tab.
➤ Then, pick the Number of Visits as the Sort by and Order is Smallest to Largest.
After pressing OK, you’ll get an error and the blank cells are located at the lowermost cells after applying the Sort tool.
So, do the following things to solve the issue.
- Either delete the blank rows if these are not necessary.
- Or fill the blank cells with the specified cell values and apply the Sort tool.
Read More: [Solved!] Excel Sort Not Working
2. Presence of Leading Space Doesn’t Work When Excel Sort Applied
Furthermore, if there exists a leading space before the cell values. This might have occurred if you copy any dataset from any other sources.
For example, leading space is available in C8, C11, and C15 cells.
Next, apply the Custom Sort for the Category of the Sites along with the Order as A to Z.
Shortly, you’ll get the following output where the categories including leading space are ahead of all other categories in the C5:C15 cell range.
To get rid of the problem, go to the formula for any of the cells (e.g. C8 cell) and remove the space before the text.
After removing the space, you’ll get the expected output if you apply the Custom Sort again.
3. Mixed Data Type in Same Column Creates Trouble for Excel Sort
The Sort feature might not work when there are different types of data in the same column. In the F5:F15 cell range, F6, F8, F9, F11, and F13 cells hold the text values as numbers stored as text if you add leading zeros.
Then, if you turn on sorting, you’ll find that sorting is not working well.
How can you solve the issue?
➤ While turning the Sort, you’ll get the following dialog box namely Sort Warning. Now, check the circle before the Sort anything that looks like a number, as a number option.
Eventually, you’ll get the desired output.
Read More: Excel Not Sorting Numbers Correctly
4. Excel Sort by Date Not Working When It Is in Text Format
Again, you might face that the Sort feature is not working in the case of the date.
If you apply the Sort tool for the Date of Visiting, you’ll get the following output.
You’re finding this error as the date is in Text (go to the Number ribbon in the Home tab) format.
➤ If you want to get the solution for this problem, open the Format Cells option (just press CTRL + 1). And, choose any valid Date format.
Later, you’ll get the following output if you sort the Date of Visiting (Order: Oldest to Newest).
Moreover, you may apply the Text to Column feature to fix the date issue.
5. Excel Filter Is Not Working When Blank or Hidden Rows Exist
Assuming that rows 8 and 12 are blank as shown in the following screenshot.
Let’s apply the Filter tool.
➤ Initially, select the cell range where you want to filter.
➤ Then, choose the Filter option from the drop-down list of the Sort & Filter option from the Editing ribbon in the Home tab.
Therefore, you’ll get the following output including blank rows.
If you want to omit such type of error, you need to uncheck the Blank Cells option as depicted in the following image.
Finally, you’ll be able to remove the wrong output.
6. Presence of Merged Cells Doesn’t Work for Excel Filter
There remain some merged cells in the following dataset.
Most likely, you’ll get errors if you filter including the merged cells. So, you need to unmerge cells.
➤ To do that, you need to pick the Unmerge Cells from the Merge & Center option in the Home tab.
Read More: Excel Sort by Cell Color Not Working
7. Excel Filter Stops Working If Errors in Dataset Present
If you look closely at the following image, you’ll find some errors in the F5:F15 cell range. There is supposed to be the cell value in numerical value instead of the text “Number” and “Text”.
In such a situation, you’ll get that the filter is not working properly. That’s why you have to uncheck the boxes before the Number and Text options.
8. Confusing Column Heading Doesn’t Work with Excel Filter
In the column heading, ensure that you have a single row in the column. Otherwise, the Filter tool can extract the wrong output.
➤ Fortunately, you can fix the issue by inserting a new line (press ALT + ENTER to insert a line break) and using the Wrap Text option.
Sort and Filter are Greyed out in Excel
In some cases, you may find that the entire Sort & Filter option is greyed out and you cannot use any of the features.
This is for grouping the sheets. And you can activate the tool by ungrouping the sheets. Just go to the Sheet tab and choose the Ungroup Sheets by right-clicking.
Things to Remember
- If you want to sort any data from a column, make sure that you select the whole dataset. But you don’t need to select the whole dataset if you want to filter any data.
- Furthermore, try to unhide any rows or columns if you want to sort and filter any data.
Download Practice Workbook
Conclusion
This is all about the issues of not working with the Sort and Filter tool in Excel. More importantly, I discussed the ways of fixing the issues. I strongly believe this article would be highly beneficial for you. Anyway, if you have any queries or recommendations, please share them in the comments section.
Related Articles:
<< Go Back to Excel Sort Not Working | Sort in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
