Are you one of those people who loves using colors to easily visualize your spreadsheet data? Well we have great news! Since March 2020, Google Sheets has added the ability to sort data based cell or text color. In this article, we will guide you through using this step by step.
Sort by Cell / Fill Color
1. Highlight the data set you want to sort by color.
Here we want those who attended in the list, which are highlighted in green, to be on the top of the data.
2. Click the filter icon on the top right corner of the toolbar
3. Now, click the filter button on your column headers.
4. Select “Sort by Color” -> “Fill Color” and select the choice of the fill to sort by with
5. You will now have your dataset sorted using your specified format.
Sort by Text Color
Now suppose your dataset uses colored text instead of a fill. Google Sheets also allows you to sort them, just follow this simple guide:
1. Highlight your dataset and enable the filter view icon
2. Click the filter button that appears in the column header.
Remember to click in the column where your formatting is applied to. In our example, use column B since column A does not have text color formatting.
3. Click “Sort by Color” -> “Text Color” and choose the text color you want to filter by.
4. The color you selected will now be on top of your list.
Pressing “blue” will bring all blue text to the top but will keep all the other text colors (dark green and red) mixed at the bottom. This happens because Sort By Color only applies to only the selected format it is used with. To be able to group all your colors together, follow the next set of instructions.
How to Sort by Multiple Colors
1. Follow instructions for Sorting by Cell Fill or Text Color for your first color
Let’s use the previous example where we already have our blue data on top.
2. Press the funnel icon and go to Sort by color > Text/Fill color and then select another color group
For our example let’s choose dark green or our B grade range.
3. After sorting your second color, continue to arrange your other colors if you have more than three.
Since we only have three colors in our sample and blue has already been sorted, it will follow that red automatically sorts itself at the bottom. If you have more colors, simply repeat this procedure for each of the others.
Additional information: If we want our final output to be based on the grades that are A (either A+/-) then decreasing to the lowest grades (C+/-), our previous procedure will not be enough. To accomplish the right order, arrange your data again by the color blue to bring the A grades to the top.
An alternative way to do this in Google Sheets from the very beginning would’ve been to arrange by green before arranging by blue.
Combined sorting in Google Sheets
It is important to note that Google Sheets will not rearrange your sorted data alphabetically or in increasing order when using sort by text/fill color. In the output of our first example, you will see that the names are arranged by fill but still in the order of which they appear in the initial list. Follow the guide below to arrange this alphabetically.
How to Create an Alphabetical List That is Sorted by Color
1. Highlight your data and click the filter icon on the column you want to alphabetize.
2. Select Sort A -> Z.
3. Follow the procedure for sorting by fill color.
This gives us our attendees arranged alphabetically. You can also arrange using both our text color and cell fill. Do this by sorting by the secondary format first (Say you want your final arrangement to be by cell fill but sub-arranged by text format: cell fill is your primary format and text color is your secondary format) and then your primary format last. Different orders of using sort by format may bring up a different arrangement which is why we have to be mindful before filtering our data.
Recommended articles
More resources
