Do you color code your data and need to sort values by color? This post is going to show you how you can sort your dataset based on the fill color in any column.

It is a common practice to use the fill color of a cell to signify status. For example, if a cell is green it might indicate the item has been approved. But this can make it harder to utilize the data for further analysis involving the color code.

Thankfully, Google Sheets offers users the ability to sort data by color.

Get your copy of the example workbook used in this post and follow along to find out how to sort by color!

Sort Colors from the Filter Toggles

Google Sheets offers a built-in method for sorting your data by colors and this can be found within the Filter toggles.

You will need to apply a filter to your dataset.

1. Select a cell inside your dataset.
2. Go to the Data menu.
3. Select the Create a filter option.

This will apply the Filter Toggles to the headings in your dataset and you will see the icon on the right side of each column heading.

💡 Tip: The Filter toggles can also be applied from the toolbar. Click the Filter icon to apply or remove filters from your data.

4. Click on the Filter Toggle in the column which contains the colors to sort by.
5. Select Sort by color from the menu.
6. Select Fill Color from the submenu.
7. Choose the color to sort by.

Your dataset is now sorted and any rows with the selected color will appear at the top.

📝 Note: The remaining data will appear in the original order and any other colors won’t be sorted.

If you want to sort all your colors, you will need to perform the sort operation for each color.

You will also need to perform these multiple sort operations in reverse order to the order you want the colors sorted.

Sort Colors with the SORT Function

Using the Filter toggles is the only built-in way to sort based on color.

But it is possible to extract the hex color code with a custom function and sort the results based on this hex code.

=SORT(Data!A2:D11,GETCOLOR(Data!A2:A11),true)

The above formula will sort the data in A2:D11 based on the fill color in column A.

The GETCOLOR(Data!A2:A11) part of the formula creates a new column of hex code values which is then used by the SORT function to sort the data.

=GETCOLOR(A2:A11)

You can try this formula to see the resulting hex codes from your data. Each color has a unique hex code, so sorting by hex code has the same effect as sorting by color!

The SORT function will produce a copy of your data that is sorted by color, but the color won’t be included in the results.

📝 Note: This SORT formula solution will sort all the colors in your data, but won’t give you control over which colors appear first. They will appear in alphabetical order of their hex codes.

Sort Colors with a Helper Column

Another way to achieve the task of sorting your data by color is to use a helper column with the custom GETCOLOR apps script function.

=GETCOLOR(A2)

Add the above formula in an adjacent column to your data.

You can then apply a filter to your data so it includes the new column.

1. Select a cell inside the data.
2. Go to the Data menu.
3. Select the Create a filter option.

4. Click on the Filter toggle in the helper column.
5. Select Sort A to Z from the options.

This will sort the hex codes in the helper column in alphabetical order which has the same effect as sorting the data based on color.

This will result in all the colors getting sorted and you will be able to see the results as the fill colors will sort along with the data!

Conclusions

Color coding data is not a recommended practice as it can be hard to perform further analysis based on color.

But if you already have a color-coded data set, it is possible to sort your data based on color.

Google Sheets offers a Sort by color feature which can be found in the Filter options.

It is also possible to sort data by color in a dynamic way with the SORT function and the help of a custom apps script function to extract hexadecimal color codes from your data.

Another option is to use a helper column added in the original data to sort the hex code in alphabetical order.

Do you color code your data in Google Sheets? Have you ever used these sorting methods? Let me know in the comments below!