Do you need to organize an Excel sheet by its cell color?

You often use color in a table to indicate related data. A good example would be a table of consumers who each have a favorite vegetable.

You might want to color the cell to match the color of their vegetable. You could then sort the table by color so that all vegetables of the same color appear together!

This post uses will show you different ways to sort by color in Excel.

Sort by Color from the Data Tab

The general steps of any sort are to select the data you need sorted and then specify the sort details.

Follow these steps to sort by color using the Data tab.

1. Select the cells you need to be sorted.

You can select either a single cell or a range within your table. Excel will try to guess what cells you want to be sorted if you select anything less than the full table.

2. Go to the Data tab.
3. Select the Sort command.

Select Expand the selection and click Sort… to sort the entire table. You will only get this prompt if Excel thinks you selected a partial table range.

By default, Excel assumes your topmost table row is a header. This example table has a header so ensure My data has headers is checked. Excel will exclude that row from sorting.

4. Select your column in the Sort by dropdown.
5. Select the Cell Color in the Sort On dropdown.
6. Select green in the Order dropdown and ensure On Top is selected so that green cells will appear first.

7. Click Add Level and select your column again in the new Sort by dropdown.
8. Select Cell Color again in the new Sort On dropdown.
9. This time select yellow in the Order dropdown so that yellow cells appear after green cells.

10. Repeat the process to add any other colors you want to sort.
11. Click OK when done.

💡 Tip: The order the rules appear in the Sort menu will be the order in which the colors are sorted. You can use the up and down arrow at the top of the menu to re-order the colors as needed.

Observe how your entire table gets sorted by green, yellow, and red in that order!

Sort by Color from Filter Toggles

You may already be using filter toggles in your table for reasons other than sorting. There is a sorting option on these toggles as well.

Follow these steps to apply the sort by color option from the filter toggles.

Ensure filter toggles are on by first selecting your table header.

Under the Data tab ensure the Filter option is toggled on.

Select the filter toggle of your colored cells column header.

Select the Sort by Color option and then choose the color to sort by. The selected color will appear at the top of the sorted data.

The Custom Sort selection is available if you want to sort by more than one color.

See how your whole table is now sorted with green cells on top! Notice too this basic sort leaves your other colors unsorted.

Sort by Color from the Right Click Menu

The right-click menu offers a quick way to access common Excel features like sorting.

These steps will allow you to sort by color with the right click menu.

1. Right-click on a cell whose color you want to sort by.

2. Select the Sort option.

3. Select the Put Selected Cell Color On Top option from the submenu.

Note a more complex sort is available with the Custom Sort option.

Your table now gets re-sorted so that all your green cells appear at the top! Be aware that this type of sort leaves your cells of other colors unsorted.

Sort by Color with VBA

VBA is available in the Desktop versions of Excel and can be used to automate tasks like sorting. You can write VBA code to sort by color for any currently selected range!

Follow these steps to get started with VBA.

Select the Developer tab and select the Visual Basic command or press Alt + F11. You might need to show the Developer tab if it’s hidden from the ribbon.

Select the Insert menu followed by the Module option.

Sub SortByColor()

Dim ws As Worksheet
Dim tbl As Range
Dim col As Range

Set ws = ActiveSheet
Set tbl = ws.UsedRange
Set col = Intersect(Selection.EntireColumn, tbl)

With ws.Sort
    .SortFields.Clear
    
    .SortFields.Add(col, _
        xlSortOnCellColor).SortOnValue.Color = vbGreen
    .SortFields.Add(col, _
        xlSortOnCellColor).SortOnValue.Color = vbYellow
    .SortFields.Add(col, _
        xlSortOnCellColor).SortOnValue.Color = vbRed
    
    .SetRange tbl
    .Header = xlYes
    .Apply
End With

End Sub

Double-click the new module and paste the above code into that module.

The code takes all the content of your active sheet as your table. Your active cell indicates the column of colors to sort.

The code first clears any previous sorting. The top-to-bottom sort priority is specified as green, yellow, and red. The code tells Excel to treat the top table row as a header and then applies the sort.

To run a the code you can select a cell within your colored column.

Go to the View tab and select Macros.

This will open the Macro menu where you can see all your available macros. Select your SortByColor macro and click the Run button.

Your table is now sorted by color!

Sort by Color with Office Scripts

Office Scripts is an scripting language that used to be only available in the online version of Excel under Microsoft 365 Business Plans. It has recently expanded into the beta channel of Desktop Excel as well.

You can sort by color using Office Scripts by following these steps.

Open your Excel workbook of table data.

Go to the Automate tab and select New Script.

If you don’t see the Automate tab, then your version of Excel might not support Office Scripts or you might need to enable this feature from the admin panel of your Microsoft 365 account.

function main(workbook: ExcelScript.Workbook) {

let ws = workbook.getActiveWorksheet();
let tbl = ws.getUsedRange();

let green: ExcelScript.SortField = {ascending: true, color: "00ff00", key: 1, sortOn: ExcelScript.SortOn.cellColor};

let yellow: ExcelScript.SortField = {ascending: true, color: "ffff00", key: 1, sortOn: ExcelScript.SortOn.cellColor};

let red: ExcelScript.SortField = { ascending: true, color: "ff0000", key: 1, sortOn: ExcelScript.SortOn.cellColor};

let hasHeaders = true;

tbl.getSort().apply([green, yellow, red], false, hasHeaders);

}

Paste the above script into the Code Editor pane.

The script assumes your table is the only content on the active sheet.

The green, yellow, and red colors are assumed to be in the second column by specifying the key as 1.

The 00ff00, ffff00, and ff0000 values are the hex code representations of each color. Setting hasHeaders to true excludes your table header from sorting.

The line tbl.getSort().apply does the sort with green, yellow, and red sorted in that order.

Try your code by selecting anywhere in your sheet and clicking the Run button on the code editor.

You immediately see the results of your color sort!

Conclusions

This post described to you how to sort by color in several different ways.

The conventional Data tab method lets you do a comprehensive sort using the Sort menu options.

You can do a quick one-color sort by going through the filter toggle or right-click menu. Be aware a multi-color sort is only possible through the Custom Sort option.

You can always implement VBA code if you’ll be repeatedly re-sorting your data. This goes for Office Scripts as well with supported licensing. Office Scripts is an expanding presence in Excel and is a good route to take for automating your sorting!

Have you ever needed to sort by cell color in Excel? How did you get this done? Let me know in the comments below!