[Fix:] Sort and Filter Not Working in Excel

Sort is such a powerful tool that can organize text, numbers, and dates. Besides, you can easily retrieve your data based on your requirement 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.


Download Practice Workbook


Issues and Solutions to Sort and Filter Not Working in Excel

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.

Dataset


Excel Sort Not Working


1. If Any Blank Cell Is Available

Assuming that F7 and F10 cells are blank in the dataset as shown in the following dataset.

If Any Cell is Blank

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.

If Any Cell is Blank

➤ Then, pick the Number of Visits as the Sort by and Order is Smallest to Largest.

If Any Cell is Blank

After pressing OK, you’ll get an error and the blank cells are located at the lowermost cells after applying the Sort tool.

If Any Cell is Blank

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: Excel Sort and Ignore Blanks (4 Ways)


2. Presence of Leading Space

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.

Sort and Filter in Excel Not Working Presence of Leading Space

Next, apply the Custom Sort for the Category of the Sites along with the Order as A to Z.

Sort and Filter in Excel Not Working Presence of Leading Space

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.

Sort and Filter in Excel Not Working Presence of Leading Space

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.

Sort and Filter in Excel Not Working Presence of Leading Space

After removing the space, you’ll get the expected output if you apply the Custom Sort again.

Presence of Leading Space


Similar Readings


3. Mixed Data Type in the Same Column

The Sort feature might not work when there prevail 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.

Sort and Filter in Excel Not Working Mixed Data Type in the Same Column

Then, if you turn on sorting, you’ll find that sort is not working well.

Mixed Data Type in the Same Column

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.

Mixed Data Type in the Same Column

Eventually, you’ll get the desired output.

Sort and Filter in Excel Not Working Mixed Data Type in the Same Column

Read More: How to Sort Data by Two Columns in Excel (5 Easy Ways)


4. Excel Sort by Date Not Working

Again, you might face that the Sort feature is not working in the case of the date.

Sort and Filter in Excel Not Working Excel Sort by Date Not Working

If you apply the Sort tool for the Date of Visiting, you’ll get the following output.

Sort and Filter in Excel Not Working Excel Sort by Date Not Working

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.

Sort and Filter in Excel Not Working Excel Sort by Date Not Working

Later, you’ll get the following output if you sort the Date of Visiting (Order: Oldest to Newest).

Sort and Filter in Excel Not Working Excel Sort by Date Not Working

Moreover, you may apply the Text to Column feature to fix the date issue.

Read More: [Fix] Excel Sort by Date Not Working (2 Causes with Solutions)


Excel Filter Not Working


1. If Blank or Hidden Rows Exist

Assuming that rows 8 and 12 are blank as shown in the following screenshot.

Sort and Filter in Excel Not Working If Blank or Hidden Rows Exist

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.

Sort and Filter in Excel Not Working If Blank or Hidden Rows Exist

Therefore, you’ll get the following output including blank rows.

Sort and Filter in Excel Not Working If Blank or Hidden Rows Exist

If you want to omit such type of error, you need to uncheck the Blank Cells option as depicted in the following image.

If Blank or Hidden Rows Exist

Finally, you’ll be able to remove the wrong output.

If Blank or Hidden Rows Exist

Read More: How to Sort Rows in Excel (2 Simple Methods)


2. Presence of Merged Cells

There remain some merged cells in the following dataset.

Presence of Merged Cells

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.

Presence of Merged Cells

Read More: How to Sort Merged Cells of Different Sizes in Excel (2 Ways)


Similar Readings


3. Errors in Dataset

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”.

Errors in Dataset

In such a situation, you’ll get that filter is not working properly. That’s why you have to uncheck the boxes before the Number and Text options.

Errors in Dataset


4. Confusing Column Heading

In the column heading, ensure that you have a single row in the column. Otherwise, the Filter tool can extract the wrong output.

Confusing Column Heading

➤ 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.

Confusing Column Heading

Read More: Excel Sort by Column without Header (5 Methods)


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.

Sort and Filter are Greyed out in Excel

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.

Sort and Filter are Greyed out in Excel

Read More: Difference Between Sort and Filter in Excel


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.

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

Md. Abdul Kader

Md. Abdul Kader

Hi! I am Abdul Kader and presently working as ‘Excel & VBA Content Developer’ at Exceldemy. I publish my articles related to Microsoft Excel here. In 2019, I completed my graduation in Urban and Regional Planning from Chittagong University of Engineering and Technology. Having extreme eagerness to learn, I want to develop my skills and capabilities higher.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo