Google Sheets has been getting new features on a regular basis.
And in March 2020, they released a couple of awesome features – including the ability to sort a dataset based on the color. This could either the cell background color or the text color.
So if you work with data that has color applied to it, you can easily sort based on the color so that all these colored cells/rows come together.
In this tutorial, I will show you the exact steps you need to follow to sort by color in Google Sheets.
Let’s get started!
Table of Contents
Sort by Cell Background Color
Suppose you have a dataset as shown below and you want to sort this data based on the color (so that all the cells with yellow color are together at the top).
Below are the steps to sort by cell color in Google Sheets:
- Select any cell in the data (or select the entire dataset)
- Click the Data option in the menu
- In the options that show up, click on Create a filter. This will apply a filter to the top row in the dataset
- Click on the filter icon in any of the column headers (where the column has the color based on which you want to sort)
- Hover the cursor over the Sort by Color option and then hover it on the Fill color option. This will show you all the colors that have been used in the dataset
- Click on the color based on which you want to sort. In this example, I clicked on the “light yellow 2” color
The above steps would instantly sort the dataset and give you the result as shown below (where all the yellow color cells are at the top).
So this is how you can sort by color in Google Sheets when you have one color (apart from the default white).
But what if you have more than one color in the dataset (something as shown below):
In this dataset, I want to sort by color where I have all the green cells at the top followed by the yellow cells and then all the white cells (i.e., the ones that have no color applied to the background).
Below are the steps to sort by multiple colors in Google Sheets:
- Select any cell in the data (or select the entire dataset)
- Click the Data option in the menu
- In the options that show up, click on Create a filter. This will apply a filter to the top row in the dataset
- Click on the filter icon in any of the column headers (where the column has the color based on which you want to sort)
- Hover the cursor over the Sort by Color option and then hover it on the Fill color option. This will show you all the colors that have been used in the dataset
- Click on the “light yellow 2” color option. This will sort the dataset where all the rows with yellow color are at the top.
- Now again, click on the filter icon in any header, hover the cursor on Sort by Color, then on ‘Fill color’, and then click on the “light green 2” color option.
The above steps would sort the data where the last chosen color option (green in our example) is at the top of the sorted dataset, followed by the yellow color.
Sort by Cell Text Color (Font Color)
Just like you can sort based on the cell background color, Google Sheets also allows you to sort based on the font color.
Suppose you have the dataset as shown below where some of the cells have the text in red color.
Below are the steps to sort this dataset by font color so that all the cells with red font color will come at the top of the dataset:
- Select any cell in the data
- Click the Data option in the menu
- Click on Create a filter. This will turn on the filters for the top row of the dataset
- Click on the filter icon in any of the column headers
- Hover the cursor over the Sort by Color option and then hover it on the Fill color option. This will show you all the colors that have been used in the dataset
- Choose the color for which you want all the cells at the top. In this example, I am choosing ‘red’
That’s it!
The above steps would sort the chosen colored cells at the top.
In case you want these cells at the bottom, you should choose the other color (black in this example), and it will sort and bring all the cells with the font in black color at the top. This also means that the ones with red color would automatically be at the bottom.
If you have multiple colored fonts, you can still sort these. Just sort it once by one color and then by the next color. And remember that the color you sort by last will always be at the top in the dataset (after sorting).
How to Unsort and Get the Original Data back
In case you may need the original sort order back sometime later, you can easily do that as well.
The trick here is to add a helper column (either to the left or right of the dataset) and numbers these with an increment of 1 (such as 1,2,3…) as shown below:
Now, if you sort the data and, for some reason, need to unsort it and get the original data back, you can sort this data based on the helper column.
Another way to make this possible is to simply create a copy of your document. That way, you will have the original data in a separate workbook. You can also use the version history feature in Google Sheets to go back and check how the original data looked before you sorted by color.
So these are the ways you can sort by color in Google Sheets (be it cell color of the text font color).
I hope you found this tutorial useful!
Other Google Sheets tutorials you may like:
1 thought on “How to Sort by Color in Google Sheets (Easy Step-by-Step)”
Nevermind. I hit filter rather than sort!!!! Sort works perfectly