Sort by color in Excel (Table of Contents)
Sort by color in Excel
In Excel, there are two ways to sort any data by Color. Firstly, we can sort the data by color through filters. For this, apply the filter selecting option from the Data menu tab and then from drop-down option, select the Sort by cell color or font color. And other ways is sorting the data using Sort option available in Data menu tab. Once we select this, under Sort On drop-down we can select the sorting by cell color or by font color.
How to Use Sorting In Excel?
In excel we can find SORT under the DATA menu as shown in the below screenshot.
We can sort the data by color in excel in a simple way where we have custom sort option in excel, here we can sort the data by multiple options like by Cell Color or by Value (Numbers) or by Font Color or by Cell Icon or by ascending or descending order. Once we click on the sort option we will get the below dialogue box with multiple sorting as shown below.
Examples of Sort by color in Excel
In this example, we are going to see how to sort the data by color.
Example # 1: Sort By Cell Color
let’s consider the sales data which has several categories like product name, Brand, MRP, Selling Price, Local, Zonal, National.
In sales data, we have categorized brands in specific colors so that it can be displayed easily as shown in the below screenshot.
To apply sort by color in the sales data follow the below steps.
- Select the C column named as Brand.
- Go to Data Menu and click Sort
- Once we click the sort, sorting feature will either expand the selection or ask the option, so select Expand the selection and click Sort command button so that we will get multiple sorting options as shown below.
- Here we can see that sort by has various options which come from the sales data caption header like Brand, MRP, SP, Local, Zonal, National, Year.
- Now let’s select sort by “Brand”, Select Sort on by Cell Color, so that we can sort the Brand by color.
- Now select the order by color where the sorting option will show various colors that we have already mentioned for all brands. Hence we can select any color, so I have selected a purple color.
- Here the color purple represents Brand name “Kare In” so brand will be sorted color wise as shown below and select the option on top so that sorting will get the order by chosen color on top.
Example #2: Sort by Font Color
In this example, we are going to see how to do sorting by font color by using the same sales data where MRP has been highlighted as specific color which denotes highest to lowest MRP.
In the above sales data, we can see that MRP has been highlighted by font color and we will do the sorting by font color as follows.
- Select the cell name MRP.
- Go to Data menu and click Sort.
- Once we click Sort, sorting feature will either expand the selection or ask the option as shown below. Choose ‘ Expand the selection ‘ and click the Sort command button.
- We will get the sorting dialogue box as shown.
- Here select the Sort By option as MRP and Select the Sort On as Font Color
- Now select the order by Color which shows several colors that we have mentioned for highest MRP
- Click on the font color for MRP and select the color Maroon and choose On Top option to get the chosen color so that sorting will be arranged by font color as shown below.
- Hence the MRP has been sorted by “Font color “
Example #3:
In this example, we will see how to sort font color Year wise where YEAR has been highlighted with several font colors as shown in the below screenshot.
- Select the cell name I which has caption name as YEAR.
- Go to Data menu and click Sort.
- Once we click Sort, the sorting feature will either expand the selection or ask the option. Choose expand the selection and click the sort command button.
- We will get the sorting dialogue box as follows, now choose the Sort by option as Year and choose Sort On with Font Color.
- We will get the option to choose font color as follows. Now choose the order by Font color, choose the On Top option to get the chosen color on top and we will get the following result.
- We can see the above sales data that “year” has been arranged with Font color as specified from Top.
Example #4:
In the above examples, we have seen that sorting has been done with cell color and font color by selecting ON TOP.
Let’s consider the same sales data and now we will see how the sales data appears if we select the order by On Bottom option as follows
- Select the Brand column and apply Sort option, sorting will ask to expand the selection or keep the current selection. So we will select the first option to Expand the selection.
- Now we will get the below-sorting dialogue box as follows. As you can see we have selected the sorting by “Brand” and Sort on “ Cell Color” and select as “Green Color”.
- Now we will get the results as shown below in the screenshot.
- We can see the sales data has been arranged Brand wise with cell color “Green” by default On Top selection has been chosen.
- Now select the On Bottom option to see how the sales data can be arranged from the bottom.
- We have chosen the On Bottom option so the result will be shown from the bottom.
- In the below screenshot we can see the difference that sales data has been arranged Brand wise with cell color “Green” and the order by one BOTTOM.
Things to Remember about Sort by color in Excel
- While applying Sorting by color make sure that all colors are been specified with different colors.
- Make sure that “My data has header” option has been check marked.
- Sort by color can be applied for only one color.
- We can sort the color with On TOP or On Bottom option.
Recommended Articles
This has been a guide to Sort by color in Excel. Here we discuss the Sort by color in Excel and how to use the Sort by color in Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –
