Nehad Ulfat
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.
📌 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.
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.
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.
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.
📌 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.
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 Sort Data in Excel Using Formula
- How to Sort Two Columns in Excel to Match (Both Exact and Partial Match)
- How to Sort Duplicates in Excel (Columns and Rows)
- How to Create Custom Sort in Excel (Both Creating and Using)
