WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Sort by Color in Excel

Sort by Color in Excel

How to Sort by Color in Excel? (with Examples)

When a column or a data range in excel is formatted with colors either by using the conditional formatting or manually, when we use filter on the data excel provides us with an option to sort the data by color, there is also an option for advanced sort where user can enter different levels of color for sorting.

You can download this Sort by Color Excel Template here – Sort by Color Excel Template

Example #1 – Column Sort

Recently I received a database from the Human Resources department. These include employee names and the designations in the company.

The problem here is they not mentioned designation against each employee; rather, they just highlight all the executives in one color, junior in one color, and so on. And they asked my help to sort all executives one side, juniors one side, seniors one side.

Those days I was not a prolific user of the sort by color option. However, thanks to my seniors for just letting me know about the sort by color in excel early on in my career, even though I did not use it to the full extent, I just knew there is an option called Sort by color in excel.

Below is the data I have received from the HR department.

Hr Department Data - Sort

Ok, just looking at the outset, I can understand light blue represents Senior, light green represents Junior, orange represents Executive, and dark blue represents Team Leaders.

Step 1: Select the entire data table from A1 to B22.

Step 1 - Select the entire data

Step 2: Now go to Data Tab (ribbon) and select Sort. (You can press ALT + D + S)

Data Tab Ribbon

Step 3: Once you click on Sort, it will open up the below dialogue box.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Add lEvel

Step 3.1: Once small check, we need to do here i.e., make sure My data has headers checkbox is ticked.

Select my data has a header

Step 4: Sort by select the column that we need to sort. In this example, we need to sort the second column i.e., Designation.

Column Sort by color in excel step 4

Step 5: After this, we need to go to Sort On option. Under this select cell color.

Column Sort by color in excel step 5

Step 6: As soon as you select Cell color, it will enable one more option called Order. This is simply in what order your colors should come on top. Select the color according to your wish. I have selected dark blue to come on top.

Column Sort by color in excel step 6

Step 7: Click the OK button. It will sort the data based on the color of the cell. One thing we need to notice here is it will only sort the required color; all the other colored cells remain unchanged.

Column Sort by color in excel step 7

Note: To sort the same colors together, we need to sort 4 times here. Each time we need to select each color one at a time.

Example #2 – Sort By Font Colour in Excel

We can sort the data based on cell color. Similarly, we can sort the data based on the Font Color too.

Look at the below data. I have the Product list region-wise. Each region’s name of the font is colored differently. I need to sort the data based on the font color.

font color data by in excel example

Step 1: Select the data from A1 to B18.

Data

Step 2: Open the Sort by pressing ALT + D + S.

Step 3: Select the column we want to sort. In my case, I have selected the Region column.

Region Column

Step 4: Under Sort by option, select the options Font Color.

font color data

Step 5: You can choose the priority of your color under the Order section. I have selected Green to come on top.

font color data by in excel step 4

Step 6: Press the OK button. All the green-colored fonts will come on top, but all the other font colors remain the same.

font color data by in excel step 5

Things to Remember

  • It will sort only the selected color, and all the other colored cells and fonts remain constant.
  • It is important to select the My data has headers option to avoid sorting headers of the data as well.
  • We can sort one color at a time.
  • We can sort based on cell icons as well.
  • We can use a particular color to come on either to top or bottom.
  • It is better to sort by alphabetic or numbers because it will sort all the group one side.

Recommended Articles

This has been a guide to Sort by Color in Excel. Here we discuss how to Sort by Color in Excel along with examples and a downloadable template. You may also look at these useful functions in excel –

  • Sum by Color in Excel
  • Excel Column Sort
  • Sort by Number in Excel
  • Sort Data in Excel
  • MMULT in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download Sort by Color Excel Template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More