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.
📌 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.
📌 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.
Like in the picture below, the rows with the highlighted donor names will appear on top.
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.
📌 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.
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.
📌 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.
You’ll find an organized dataset with the rows sorted by selected colors on top.
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.
📌 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.
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.
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.
📌 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.
After rearranging the order of all colors available in the table, you should get a modified data table as shown in the following picture.
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.
