How to Sort Data by Color in Excel (4 Criteria)

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.


Download Practice Workbook

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


4 Suitable Examples of How to Sort by Color in Excel

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


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


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


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


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.

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.


You May Also Like to Explore

How to Sort Alphabetically in Excel with Multiple Columns (4 Methods)

How to Sort by Last Name in Excel (4 Methods)

Auto Sort When Data is Entered in Excel (3 Methods)

How to Use SORT Function in Excel (4 Examples)

How to Sort Two Columns in Excel to Match

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo