Md. Abdur Rahim Rasel
While dealing with a large Microsoft Excel dataset, sometimes we need to sort by cell color. We will notice that sorting by cell color does not work in Excel. It occurs for several reasons. Conditional Formatting is one of them. Today, in this article, we’ll learn three quick and suitable ways how to fix the error of the sort by cell color not working in Excel effectively with appropriate illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Reasons Behind Sort by Cell Color Not Working in Excel
There are several reasons behind the sort by color does not work in Excel. One of the main reasons is the Conditional Formatting Feature. If your cells are pre-formatted by the conditional formatting feature then you might face this problem. Take this for an example given below in the picture. Due to Conditional Formatting, the sort y cell color not working error occurs which has been given in the below screenshot.
3 Suitable Ways to Fix Sort by Cell Color Not Working in Excel
Let’s say, we have a dataset that contains information about several sales representatives of the Armani group. The Name of the sales representatives, their Identification Number, type of products, and the revenue earned by the sales representatives are given in columns B, C, D, and E respectively. From our dataset, we will solve the sort by cell color not working error. To fix the error, we will apply the Sort Command, Conditional Formatting, and Fill Color Command. Here’s an overview of the dataset for today’s task.
1. Apply Sort Command with Proper Steps to Solve the Issue in Excel
In this method, we will apply the Sort Command to fix the sort by cell color not working error in Excel. This is the easiest and the most time-saving way. From our dataset, we will sort the revenue earned by the sales representatives with the cell light golden color. Let’s follow the instructions below to fix this problem!
Step 1:
- First of all, select the cells array E5 to E14. After that, from your Data tab, go to,
Data → Sort & Filter → Sort
- Hence, the Sort Warning dialog box will appear in front of you. From the Sort Warning dialog box, firstly, select Expand the selection from the What do you want to do? Secondly, press the Sort option.
Step 2:
- Further, a new dialog box named Sort pops up. From the Sort dialog box, firstly, select Rev Earned from the Sort by drop-down box. Secondly, select Cell Color from the Sort on the drop-down box. Thirdly, select light golden from the Order drop-down box. After that, select On Top. At last press OK.
- After completing the above process, you will be able to solve the error in Excel which has been given in the below screenshot.
Read More: How to Use Sort Function in Excel VBA (8 Suitable Examples)
2. Use Fill Color Command to Fix the Problem
Now, we will fix the sort by cell color not working error in Excel by using the Fill Color command. To do that, firstly, fill some cells with a Light Blue color. After that follow the instructions below to solve this problem!
Steps:
- First of all, select the cells array B4 to E14. Hence, press Ctrl + T on your keyboard simultaneously. After that, a dialog box named Create Table will appear in front of you. At last, press OK.
- After that, a table will create. Now, click on the drop-down box of Product. A new window will pop up. From that window, firstly, select the Sort by Color. Secondly, select the Light Blue color.
- As a result, you will be able to fix the sort by cell color not working error in Excel which has been given in the below screenshot.
Read More: How to Remove Sort by Color in Excel (With Easy Steps)
Similar Readings
- How to Sort Birthdays by Month and Day in Excel (5 Ways)
- How to Put Numbers in Numerical Order in Excel (6 Methods)
- [Fix] Excel Sort by Date Not Working (2 Causes with Solutions)
- Excel Sort and Ignore Blanks (4 Ways)
- How to Sort Drop Down List in Excel (5 Easy Methods)
3. Remove Conditional Formatting
You can also fix the sort by cell color not working error by removing the conditional formatting from your entire worksheet. Let’s follow the steps below to solve this problem!
Steps:
- First of all, select cells E5 to E14.
- After that, from your Home tab, go to,
Home → Styles → Conditional Formatting → Clear Rules → Clear Rules from Entire Sheet
- After completing the above process, you will be able to solve the sort by cell color not working error in Excel which has been given in the below screenshot.
Read More: [Solved!] Excel Sort Not Working (2 Solutions)
Things to Remember
👉 Press Ctrl + T simultaneously on your keyboard to pop up the Create Table dialog box
Conclusion
I hope all of the suitable methods mentioned above to fix the sort by cell color do not work error will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
