How to Sort by Color in Excel (4 Criteria)

Get FREE Advanced Excel Exercises with Solutions!

In Microsoft Excel, there are numerous ways available to sort table data under different criteria. If the data table contains colors in cells, fonts, or conditional formatting icons, you can also sort by these colors so easily. In this article, you’ll get to learn the steps to sort data by color with proper illustrations.


How to Sort by Color in Excel (4 Suitable Examples)


1. Sort by Single Color in Excel

In the following picture, a dataset is presented with donor names, donations, transaction mediums, and donation dates in four separate columns. In Column B, a few donor names are highlighted with yellow color. We’ll sort these names and put the perspective rows on top of the table.

sort by single color in excel

📌 Step 1:

➤ Select the entire table first.

➤ Under the Data ribbon, select the Sort option from the Sort & Filter drop-down.

A dialogue box will open up.

sort by single color in excel

📌 Step 2:

➤ Select Donor from the Sort by list.

➤ Select Cell Color from the Sort On list.

➤ Under the Order bar, choose the only color available (yellow) in the list that will be on top of the selected table.

➤ Press OK.

sort by single color in excel

Like in the picture below, the rows with the highlighted donor names will appear on top.

sort by single color in excel

Read More: How to Add Sort Button in Excel


2. Sort by Cell Color in Excel (Multiple Layers)

Now the rows are highlighted with different colors based on three mediums of donating money.

What we want to do here is sort the table by the cell colors used in Column D.

sort by cell color in excel

📌 Step 1:

➤ Select the entire table again.

➤ Open the Sort dialogue box like stated in the previous section.

➤ Select Medium from the Sort by list.

➤ Select Cell Color from the Sort On list.

➤ Under the Order bar, choose an order of color that will be on top of the selected table.

➤ Press OK.

sort by cell color in excel

So, in the picture below, you’re now seeing the table data sorted by blue rows on top. But the green and orange rows are yet to sort. To do this, we have to add another level in the Sort dialogue box.

sort by cell color in excel

📌 Step 2:

➤ Open the Sort dialogue box once again after selecting the entire table.

➤ Click on Add Level button and you’ll find another row with Sort options.

➤ Select Medium from the Then by list and Cell Color from the Sort on list.

➤ From the Order list, now choose green or orange that you want to see under the blue rows in the table.

➤ Press OK and you’re done.

sort by cell color in excel

You’ll find an organized dataset with the rows sorted by selected colors on top.

sort by cell color in excel

Read More: How to Sort Alphanumeric Data in Excel


3. Sort by Font Color in Excel

Now let’s have a look at Column C where the donation amounts are in different font colors. We’ll sort the table data by these font colors now.

sort by font color in excel

📌 Steps:

➤ Like the previous method, select the entire table and open the Sort dialogue box from the Sort & Filter drop-down under the Data tab.

➤ Under the Column bar, select Donation from the Sort by list.

➤ Select Font Color from the Sort On list.

➤ From the Order list, choose a color that you want to see on top.

➤ Press OK and you’re done with the necessary steps.

sort by font color in excel

By adding another level for sorting more color fonts, you’ll finally find a new data table sorted by the color fonts present in a single column.

sort by font color in excel

Read More: How to Remove Sort by Color in Excel


4. Sort by Icon Color in Excel

In Column E, the donation dates are present where conditional formatting has been applied too. The green flags are indicating the newer dates whereas the yellow and red ones are for older dates. We’ll sort the data table by these icon colors now.

sort by icon color in excel

📌 Steps:

➤ Select the entire table and open the Sort dialogue box.

➤ Select Date from the Sort by list.

➤ From the Sort On list, select the Conditional Formatting Icon.

➤ Choose a flag color from the Order list which you want to see on top.

➤ Press OK.

sort by icon color in excel

After rearranging the order of all colors available in the table, you should get a modified data table as shown in the following picture.

sort by icon color in excel

Read More: Excel Sort and Ignore Blanks


Summarization

  • You can sort a data table by its cell color, font color, or icon color as your wish.
  • Sorting data by color doesn’t require any formula or function, you have to activate the Sort option only from the Data tab.
  • To sort data by multiple colors present in the dataset, you have to add levels for all colors to sort in the Sort dialogue box
  • Sorting and Filtering are not similar operations in Excel. By sorting, you can keep all data in the table and sort them by order. By filtering, a range of data or rows will be filtered out in the table and you’ll get the partial and specific data only that you want to see.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


Concluding Words

I hope all these criteria mentioned above to sort by colors will now provoke you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


Related Articles


<< Go Back to Sort in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Nehad Ulfat
Nehad Ulfat

NEHAD ULFAT is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET but switched gears, working as a content developer.  In this role, he creates techy content all about Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo