Excel boasts numerous tools to sort huge datasets. It allows users to sort datasets alphabetically, numerically, and also by color. Everyone loves color-coded data which is easy to scan and quickly addresses the most important cells of the table.
Note that you can sort by either cell background or font color.
In this article, we will explore how to sort by color in Excel using cell or font colors. We will cover various methods and tips to make your sorting tasks easier, so read the article to the end.
Here’s a quick list of the topics covered in the following sections. Click on the links to jump to the particular section of your interest.
- Sort dataset based on cell background color
- Sort dataset based on multiple cell background colors
- Sort dataset based on font color
- Combined sorting
Download the Example Excel Spreadsheet
Make sure you practice what we are about to discuss further in this article. If you have your own color-coded Excel Sheet ready, good job, and jump to the next section.
Otherwise, we suggest downloading this example Excel spreadsheet containing the tables and datasets used in the upcoming sections.
Note that practice is the key to success. Let’s begin.
How to Sort Data by Cell Background Color in Excel
Sorting data based on cell background color in Excel is pretty straightforward. It helps you organize and analyze your data efficiently.
The following method is useful when you have highlighted a specific row or cell, such as high-priority tasks, overdue items, dates with maximum sales, etc.
Consider the example below, which has employee names in the first column and their departments in the second column.
The Green color highlights employees from the IT department. Our task is to bring all IT department employees to the top of the list.
Steps:
- Open the desired Microsoft Excel
- Click on any cell from the table
- Press “CTRL + A” to select the entire table range. In our case, the table range is A1:B19
- Now, go to the “Data” tab of the main menu
- Choose the “Sort” option from the list
- A new dialog box will appear on your screen. Refer to the following image
- Next, make sure you tick the empty box before the “My data has headers” option
- Click on the “Sort By” dropdown and select the column you want to sort
(Here, we will select “Department,” as we want that particular column to be sorted) - In the “Sort On” dropdown, select “Cell Color”
- In the “Order” dropdown, select the color based on which you want to sort the data. Since there is only one color in our dataset, it will be displayed as shown in the following image
- In the second dropdown in Order, select “On Top” to specify where you want this color to appear
- Click on “OK” to see the result
Excel will instantly move the IT department employees to the top of the list. For complete steps, refer to the above GIF.
SIDENOTE: Sorting by color simply groups all the cells with the same color together while the other cells stay in their original order.
How to Sort Data by Multiple Cell Colors in Excel
The process of sorting the dataset by multiple colors remains the same as in the previous section.
All you need to do is add a new level, as shown in the following image.
Now, let us see the detailed steps. We will consider the same example where we had employee data, including their names and departments.
As you can see in the above image, employees from the IT and HR (Human Resources) departments are highlighted in Green and Yellow.
Our task is to sort the employees working in the IT department to the top of the list and the employees working in Human Resources at the bottom of the list.
Here are the steps,
- Open the desired Microsoft Excel
- Select the entire table range as “A1:B19” by clicking the “CTRL + A” on your keyboard
- Hover to the “Data” tab from the main menu
- Choose the “Sort” option from the list
- You will see the following popup on your screen
- If your dataset has headers, make sure you tick the empty box before the option “My data has headers”
- Next, you need to click on the “Sort By” dropdown and select the column you want to sort. Here, we will choose “Department,” as we wish that particular column to be sorted
- From the “Sort On” dropdown, select “Cell Color”
- Similarly, from the “Order” dropdown, select the first color based on which you want to sort the data. Let us choose “Green” color
- In the second dropdown of the Order section, select “On Top” to specify where you want this color to appear
- To sort the second colored data, you need to add another condition of sorting. For that, click on “Add Level”
- A new set of options will appear, as shown below
- Click on the “Then by” dropdown and select the option named “Department”
- In the “Sort On” dropdown, select the “Cell color” option from the list
- In the “Order” dropdown, select the second color, which is “Yellow”
- In the second dropdown of the “Order” section, select “On Bottom”
- Click “OK” to see the results
As you can see in the above GIF, the employees from the IT department are moved to the top, and the Human Resources department is transferred to the bottom.
You can add any number of levels to sort the dataset by multiple colors. To sort the dataset in numerous color sequences, make sure you add levels correctly and ensure ordering.
How to Sort Data Based on Font Color in Excel
As mentioned earlier, Excel has a fantastic set of sorting features.
It allows you to sort the datasets by the font color. The process is simple and involves just a few steps.
Let’s consider the following dataset, which has employee names in the first column and their departments in the second column.
Task: Sort the IT department employees, marked in Red, to the top.
Here are the steps,
- Open the desired Microsoft Excel
- Select the entire table range “A1:B19”
- Navigate to the “Data” tab of the main menu
- Select the “Sort” option from the list
- A new popup will appear on the screen, as shown below,
- Make sure the box before the option “My Data has headers” is ticked
- Click the Sort By dropdown and select “Department”
- To sort by font color, you need to click on the “Sort On” dropdown and choose the “Font Color” option
- Next, from the “Order” dropdown, choose the “Red” color
- In the second dropdown of the “Order” section, select “On top”
- Click the “OK” button to close the popup
It will instantly sort all the entries marked with Red at the top of the list. You can follow the similar steps to sort by multiple font colors. All you need to do is click on the “Add Level” button of the popup.
Combined sorting in Excel
In the previous sections, we learned to sort the datasets by multiple cell and font colors in the same column.
But what if you wish to sort a particular column in alphabetical order and the other column by color-coded cells?
Do not worry because Excel has got you covered.
Let’s understand this with an example. We are considering the dataset below, which includes employee names and departments in the first and second columns.
Task: Our task is to put the IT department employees at the top of the list and ensure they are sorted in ascending order.
For the sake of convenience, I have divided the entire process into three steps.
STEP #1 – Create Filters for your table
Instead of using the SORT tool from the “Data” tab, we will use the Sort and Filter option available in the “Home” tab of the main menu.
Here are the steps,
- Click on any cell in the table
- Go to the “Home” tab
- Click on the “Sort & Filter” icon from the list
- It will add a dropdown arrow to the header of each column. Refer to the following image.
STEP #2 – Sort By Name
Finalizing the sorting sequence is crucial to obtain the desired results.
For our current dataset, we have two sorting conditions,
- Sort the first column by names in ascending order
- Sort the second column by Green cell background color to get the cells containing the term “IT” at the top
Let us first sort the table by the names in the first column using the following steps,
- Click on the dropdown arrow in the header of the first column named “Full Name”
- Choose the first option, which says, “Sort A to Z”
Note that if you wish to sort the names in descending order, you need to click on the second option named “Sort Z to A.”
STEP #3 – Sort by Cell Background Color
This is the final step, where we will sort the second column by the Green cell background color.
Let’s begin,
- Click on the dropdown icon from the “Department” header
- Select the third option, named “Sort by Color” from the list
- Choose the specific color you want to sort by. In our case, it is green
The entire process is shown in the following GIF. It ensures that the data is first organized alphabetically by name and then grouped by the specified cell background color.
To Summarize: Sort By Color In Excel
Sorting by color is easy. The steps are the same. You can sort the dataset by both the cell background color and the font color.
Excel is a powerful spreadsheet program. It allows you to sort a table using multiple sorting criteria.
If you are stuck somewhere or still have any doubts, feel free to comment below.
Also, do not forget to check our blog section for more tips and tricks on using Microsoft Excel and Google Sheets like a pro.