How to Sort data by colors in excel

Microsoft Excel has many options for sorting data. Sorting is based on the colour of the cell is one of them.

In this blog post, we will learn about the different scenarios where we can sort the data by colour.

Sort based on a Single Colour:

Below are the steps to sort dataset having a single colour:

1. Select the entire dataset. 

2. Click the Data tab

3. Click on the ‘Sort’ option. This will open the Sort dialogue box.

4. In the Sort dialogue box, check ‘My Data has headers’. If the data does not have headers, you can keep this unchecked.

5. Click the ‘Sort By’ drop-down and select the column based on which you want the data to be sorted.

6. In the ‘Sort On’ dropdown, click on Cell Color.

7. In the ‘Order’ drop-down, select the colour based on which you want to sort the data. If there is only one colour in our dataset, by default it only shows that colour only.

8. In the second drop-down in Order, you can either select On-top or At-Bottom. This is the place where you specify whether you want all the coloured cells at the top of the dataset or at the bottom.

9. Click OK.

 

Keyboard Shortcut to Open the Sort Dialog box

 

ALT+ A S S (hold the ALT key then press A S S keys one by one)

Sort Based on Multiple Colors

In the above example, we only had cells with one colour that needed to be sorted. And you can use the same methodology to sort when you have cells with multiple colours.

Below are the steps to sort by multiple colours in Excel:

1. Select the entire dataset. 

2. Click the Data tab.

3. Click on the Sort option. This will open the Sort dialogue box.

4. Click the ‘Sort By’ drop-down and select the column based on which we want the data to be sorted.

5. In the ‘Sort On’ dropdown, click on Cell Color.

6. The ‘Order’ drop-down will show all the colours that are there in the dataset. Select the colour based on which you want to sort the data.

7. In the second drop-down in Order, select On-top.

8. Click on Add Level button to add another condition of sorting (to sort based on two colours). Clicking this option will display another set of sorting options.

9. Click the ‘Then by’ drop-down and select the other option based on which you want to sort the data.

10. In the ‘Sort On’ dropdown, click on Cell Color.

11. The ‘Order’ drop-down will show all the colours that are there in the dataset. Select the second colour based on which you want to sort the data. 

12. In the second drop-down in Order, select On-Bottom.

 

13. Click OK

Sorting by colour works with numbers as well as text data.

Sort Based on Font Color

Below are the steps to sort by font colour in Excel:

1. Select the entire dataset. 

2. Click the Data tab. 

3. Click on the Sort option to open the Sort dialogue box.

4. Check the ‘My Data has headers’ if your dataset has headers else keep it unchecked.

5. Click the Sort By drop-down and select the column based on which we want the data to be sorted.

6. In the ‘Sort On’ dropdown, click on Font Color.

7. In the ‘Order’ drop-down, select the colour based on which you want to sort the data. If there is only one colour in the dataset, this option will show only that colour.

8. In the second drop-down in Order, specify whether you want all the coloured cells at the top of the dataset or at the bottom.

9. Click OK.

 

The above steps would sort the data with all the cells with the font in the same colour at the top.

 

To learn more about sorting data in Excel, Elearno has an elaborated tutorial on how to sort in Excel that covers a lot of topics such as sorting by text/dates/numbers, sorting from left-to-right and much more.

 

Check out all our courses on Microsoft Excel.



NOT ALL STUDENTS HAVE SAME LEARNING NEEDS
AND EVERYONE HAVE THEIR FAIR SHARE OF DOUTS.
ELEARNO PROVIDES HASSLE-FREE DEMO AT YOUR
COMFORT ZONE

TO KNOW MORE SCHEDULE A FREE DEMO
THROUGH VIRTUAL CLASSROOM



Book Demo