Excel Functions Tutorials
- Excel Tools
- Excel Ribbons and Tabs
- Quick Access Toolbar in Excel (QAT)
- "Save As" Shortcut in Excel
- Accounting Number Format in Excel
- Add-Ins in excel
- Add Filter in Excel
- Advanced Filter in Excel
- Auto Filter In Excel
- Auto Format Excel
- AutoFill in Excel
- Analysis ToolPak in Excel
- ANOVA in Excel
- Border in Excel
- Checkbox in Excel
- Check Mark in Excel (? Tick Symbol)
- Combo Box in Excel and VBA
- Conditional Formatting in Excel
- Conditional Formatting with Formulas
- Conditional Formatting for Blank Cells
- Conditional Formatting Based on Another Cell Value
- Conditional Formatting in Pivot Table
- Consolidate Data in Excel
- Comma Style in Excel
- CSV vs Excel
- Data Bars in Excel
- Data Table in Excel
- Data Validation Excel
- Data Model in Excel
- Developer Tab in Excel
- Descriptive Statistics in Excel
- Dynamic Named Range in Excel
- Drawing in Excel
- Excel Fill Handle
- Excel Fill Down
- Error Bars in Excel
- Excel Forms for Data Entry
- Excel Tables
- Excel Power View
- Exponential Smoothing in Excel
- Filters in Excel
- Flash Fill in Excel
- Freeze Panes in Excel
- Freeze Columns in Excel
- Freeze Cells in Excel
- Format Painter in Excel
- Shortcut for Format Painter in Excel
- F-Test in Excel
- Goal Seek in Excel
- Gridlines in Excel
- Heat Map in Excel
- 3D Maps in Excel
- Header and Footer in Excel
- Insert Button in Excel
- Insert / Draw Line in Excel
- Insert Function in Excel
- List Box in Excel VBA
- Lock Cells in Excel
- Macros in Excel
- Enable Macros in Excel
- Merge and Center in Excel
- Merge Cells in Excel
- Merge Tables in Excel
- Name Box in Excel
- Name Range in Excel
- Null in Excel
- One Variable Data Table in Excel
- OneDrive Excel
- Protect Workbook in Excel
- Pivot Table in Excel
- Pivot Table Examples
- Pivot Table Filter
- Pivot Table Slicer
- Paste Special in Excel (With Top 10 Shortcuts)
- Quick Analysis Tools in Excel
- Radio Button in Excel
- Recording Macros in Excel
- Regression Analysis in Excel
- Scenario Manager in Excel
- Scroll Bars in Excel
- Scroll Lock in Excel
- Slicers in Excel
- Solver in Excel
- Sort by Color in Excel
- Sort by Number in Excel
- Sort Data in Excel
- Sparklines in Excel
- Spell Check in Excel
- Split Panes in Excel (Horizontally, Vertically, Cross Split)
- Status Bar in Excel
- Text to Columns in Excel
- Timeline in Excel
- Toolbar on Excel
- Track Changes in Excel
- Trend Line in Excel
- Two-Variable Data Table in Excel
- Watch Window in Excel
- Wrap Text in Excel
- XML in Excel
- Financial Functions in Excel (17+)
- Logical Functions in Excel (15+)
- TEXT Functions in Excel (29+)
- Lookup Reference in Excel (44+)
- Maths Functions in Excel (52+)
- Date and Time Function in Excel (22+)
- Statistical Function in Excel (50+)
- Information Functions in Excel (5+)
- Excel Charts (48+)
- Excel Tips (178+)
- VBA (162+)
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.
Sort by Color in Excel (Table of Contents)
How to Sort by Color in Excel
In this article, I will show you how to sort the data based on the color of the cell. Here we discuss the step by step approach to sorting colors in excel with the help of examples.
Column Sort by Color in Excel – Example #1
Recently I received a database from the Human Resource department. The includes 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 the 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 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.
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 2: Now go to Data Tab (ribbon) and select Sort. (You can press ALT + D + S)
Step 3: Once you click on Sort it will open up the below dialogue box.
Step 3.1: Once small check we need to do here i.e. make sure My data has headers checkbox is ticked.
Step 4: Under Sort by select the column that we need to sort. In this example, we need to sort the second column i.e. Designation.
Step 5: After this, we need to go to Sort On option. Under this select cell color.
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.
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 remains unchanged.
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.
Sort by Font Color in Excel – Example #2
We can sort the data based on the cell color. Similarly, we can sort the data based on the Font Color too.
Look at the below data I have Product list region-wise. Each region name of the font is colored differently. I need to sort the data based on the font color.
Step 1: Select the data from A1 to B18.
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.
Step 4: Under Sort by option select the option Font Color.
Step 5: You can choose the priority of your color under Order section. I have selected Green to come on top.
Step 6: Press the OK button. All the green colored fonts will come on top, but all the other font colors remain the same.
Things to Remember about Sort by Color in Excel:
- 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 the cell icons as well.
- We can the particular to 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.
This has been a guide to Sort by Color in Excel. Here we discuss how to Sort by Color in Excel along with excel example and downloadable excel templates. You may also look at these useful functions in excel –
- Sum by Color in Excel
- Excel Database
- Apply Conditional Formatting in Pivot Table
- How to Use Conditional Formatting Based on Another Cell Value?
- Column Sort in Excel
- Sort by Number in Excel
- How to Highlight Every Other Row in Excel?
- How to Sort Data in Excel
- Format in Excel
- Conditional Formatting Tips