Last Updated on January 2, 2024 by Jake Sheridan
It’s common practice to color code spreadsheet data to assign different meanings to cells or rows.
For example, a completed task in a schedule spreadsheet could be color coded green, while an urgent customer order in a sales spreadsheet could be colored red or orange.
Color coding cells puts important information front and center and makes it easy to scan for relevant information.
But sometimes with large data sets scanning a spreadsheet isn’t enough and it’s necessary to sort data in order to consolidate relevant info.
While it might at first seem impossible to sort data based on colors since there is no column to sort by, it’s actually quite easy thanks to a useful feature in Google Sheets.
Read on to learn how:
6 Steps to Sort by Color in Google Sheets
Before we can sort by color, we need to create a filter.
Google Sheets Filters can not only hide certain data that the user doesn’t want to see, but can also sort data in various ways, including by color
To create a filter:
Select the range you want to sort by color, including headers, and open the Data menu, then click Create a Filter:
The filtering icon will appear in each cell of the header range. You can click on this to open a menu with options to apply filters to the filter range
That’s it. Once you have the filter set up, you can sort by color in just a few clicks.
Quick note before getting started: it’s not always easy to return to the original sort order after sorting using filter views.
So if you need to return to the original sort order, it’s a good idea to add an additional column with the original row numbers and make sure it’s part of the filter view.
Now you’ve set up your filter, here’s how to sort by color in Google Sheets:
Click on the Filter icon in the header of the color you want to use to sort colors. Note that in the example images all columns in a given row have the same color so any sort icon will work
In the menu that appears, hover over the Sort By Color menu, then hover over the Fill Color or Text Color menus depending on whether you want to sort by background or text color and select a color to sort to the top from the list
All rows with a cell of the color in the selected column will sort to the top of the list, while the remaining rows will remain in their original order
Repeat the process as desired with as many colors as you’d like. Each time only the selected color will be affected
To restore the original sort order, click on the filter icon in the Original Sort column and select Sort A – Z
Example Spreadsheet: Make a copy of the example spreadsheet
In this tutorial, I covered how to sort by color in Google Sheets. Want more? Check out all the Google Sheets Tutorials.
More ways to sort in Google Sheets:
- How To Sort
- Add Dates
- Format Dates
- Stop Google Sheets From Changing Dates
- Sort Alphabetically
- Auto Sort
- Arrange Numbers In Ascending Order
- Sort By Date
- Change Date Format
- Sort By Number
- Sort By Last Name
- Sort And Filter
- Sort Unique Values
- Sort In Google Sheets And Keep Rows Together
- SORTN Function
- Randomize A List