Are you one of those people who love using colors to easily visualize data in your spreadsheet? Well, we have great news! Since March 2020, Google Sheets has added the ability to sort data based on cell or text color. In this article, we will guide you step by step on how to use this feature.
Sorting by Cell Color/Fill
-
Highlight the data range you want to sort by color.
Here, we want the highlighted persons on the list, represented by green, to appear at the top of the data.
-
Click on the filter icon at the top right of the toolbar.
-
Now, click on the filter button in the column headers.
This is where you will find your sorting functions.
-
Select “Sort by color” -> “Fill color” and choose the fill color option to sort by.
Selecting the color to organize data in Google Sheets.
-
Your data range is now sorted according to the specified format.
Sorting by Text Color
Let’s assume now that your data range uses text colors rather than fill colors. Google Sheets also allows you to sort them, you just need to follow this simple guide:
-
Highlight your data range and enable the filter view icon.
-
Click on the filter button that appears in the column header.
Remember to click on the column where your formatting is applied. In our example, use column B as column A doesn’t have text color formatting.
-
Click on “Sort by color” -> “Text color” and choose the text color to filter by.
Selecting the text color to organize.
-
The color you selected will now appear at the top of your list.
Data sorted by text color in Google Sheets.
Clicking “blue” will bring all the blue texts to the top while keeping all other text colors (dark green and red) mixed at the bottom. This happens because sorting by color only applies to the selected format it is being used with. To group all your colors together, follow the instructions below.
How to Sort by Multiple Colors
-
Follow the instructions to sort by cell fill or text color for your first color.
Let’s take the previous example where we already have our blue data at the top.
-
Click the funnel icon and go to “Sort by color” -> “Text/Fill color”, then select another group of colors.
For our example, let’s choose dark green or our range of notes in column B.
Selecting the second color to group.
-
After sorting your second color, continue organizing your other colors if you have more than three.
As we only have three colors in our example and blue has already been sorted, it follows that red will automatically be sorted at the bottom. If you have more colors, simply repeat this procedure for each of them.
Result of Google Sheets with multiple text colors.
Additional information: If we want our final result to be based on notes that are A (A+/-) then decreasing to the lowest notes (C+/-), our previous procedure will not be enough. To get the right order, rearrange your data again by sorting by the blue color to bring the A notes to the top.
Reorganizing your fill colors.
Notes color-coded in Google Sheets.
Another way to do this in Google Sheets from the start would have been to sort by green before sorting by blue.
Combined Sorting in Google Sheets
It is important to note that Google Sheets will not rearrange your data sorted alphabetically or in ascending order when using sort by text color/fill. In the result of our first example, you will see that the names are sorted by fill but still in the order they appear in the initial list. Follow the guide below to organize them in alphabetical order.
How to Create an Alphabetically Sorted List by Color
-
Highlight your data and click on the filter icon on the column you want to sort alphabetically.
-
Select Sort A -> Z.
Alphabetical sorting of data.
-
Follow the procedure to sort by fill color.
Alphabetically sorted list by fill in Google Sheets.
This gives us our guest list sorted in alphabetical order. You can also sort using both text color and cell fill. To do this, first sort by the secondary format (if you want your final arrangement to be based on cell fill but sub-sorted by text format: cell fill is your primary format and text color is your secondary format) and finally, your primary format. Different orders of using format sorting may result in a different arrangement, so we need to be careful before filtering our data.
Use our invoice reminder software to easily set up customized reminders from your spreadsheet in just a few clicks.
If you enjoyed this article, you might also like our article on how to set up the sort by value function in Google Sheets or our article on how to sort numbers in Google Sheets.
If you want to learn how to do custom sorting in Google Sheets or how to import a CSV file from an email into Google Sheets, we also suggest checking out our detailed guide.
Crawlan.com is a great resource for more Google Sheets tips and tricks. Check it out!
