How to Sort by Color in Google Sheets

By

Sorting by color in Google Sheets is just a few mouse clicks or screen taps away.

Google added this capability to the Sheets app in March of 2020. You can use the color sorting to arrange cells by both fill (background) and text color.

Enabling the Sort by Color functionality

Google Sheets enables the color sorting feature by default, but you need to turn on filtering to use it.

The following steps enable sorting by color for both fill and text:

  1. Click or tap on one of the header row cells in the spreadsheet. Select any cell in the dataset
  2. Click or tap the “Filter” icon located on the far right of the menu bar (it will say “Create a Filter on mouseover). Click on the filter icon
  3. This will enable sorting and filtering for all columns with data in the spreadsheet. The filter icon will appear to the right of the text in the header. Click on the Filter option in the column header

When you click on the filter option in the column (which has the cells with the background color), it will show you options that you can use to sort by color in Google Sheets.

How to Sort by Fill Color

The following steps explain how to use Sort by Fill Color:

  1. Select the filter icon next to the header for the column you want to sort by color. Click on filter option in the column header
  2. From the drop menu, select “Sort by color,” then “Fill Color”, and choose the fill color you want to appear on top. The example shows selecting “light red 1”. Select light red color to sort the data
  3. The spreadsheet will now show “light red 1” colored cells first.Red color at the top

Performing the same sort on a spreadsheet that has multiple cells with repeated colors will group the cells that share the selected color.

  1. Use a spreadsheet with colors used multiple times:Data with multiple color
  2. Apply sort by color, the example uses “light red 1.” Sort by Light red 1 color
  3. All cells using the selected fill color will appear at the top of the spreadsheet.Sorted by red color

Note: Sort by color only applies to the selected color. You will need to repeat the sort for each color if you want all cells grouped by color.

Using Sort by Text Color

The following steps explain how to use Sort by Fill Color:

  1. Select the filter icon next to the header for the column you want to sort by color. Click on Filter option
  2. From the drop menu, select “Sort by color,” then “Text Color”, and choose the text color you want to appear on top. The example shows selecting “dark green 1”. Select Dark Green color to sort by
  3. All cells using the selected text color will appear at the top of the spreadsheet.Data sorted by text color

Note: Sort by color only applies to the selected color. In this case, the red cells were already grouped together so sorting the green cells together grouped all the colors together.

Use the Filter to Show Individual Color Groups

The “Filter by color” option makes it easy to display only cells that feature a specific color. The following steps explain how to filter by color.

  1. Select the filter icon from the header for the column you want to use. Click on Filter icon
  2. From the drop menu, select “Filter by color,” either “Fill Color” or “Text Color,” and the desired color you want to show. The example demonstrates using “light blue 1” as a fill color. Filter by light blue color
  3.  The spreadsheet will now only show rows with the chosen color.Filtered by color

Note: You can turn off the filter by selecting the filter icon, selecting “Filter by color” and choosing “None.”

Remove the already applied filter

Understanding Sorting Behavior

Sorting by color behaves differently from sorting by numerical sequence or in alphabetical order. Google Sheets does not apply a sorting value to colors in the same way it does with numbers and letters. Instead, Google Sheets opts for a manually controlled sorting method.

The main rule you need to know when sorting by color in Google Sheets is the app will always move all rows with the chosen color to the top of the spreadsheet. However, the sort will not alter the arrangement of cells that use any other color.

If you want to sort by color in a specific order, you will need to manually apply the sort command for each color. The order needs to start with the color you want on the bottom and work up through the arrangement so the last sorted color is on top.

If you have a spreadsheet with “red,” “blue,” and “green” fill color rows, you will need to sort multiple times to change the order. If you want to rearrange the rows into a “green,” “red,” to “blue” order, you start by sorting “blue,” the last color, first. Next, apply the “red” sort and then end with the “green” sort. This will arrange the rows in the desired “green-red-blue” order.

Because of the manual nature of ordering sorted colors, the more colors you use, the more time-consuming it gets. A spreadsheet with fewer colors will be easier to manage.

Advanced Uses

Google Sheets supports using both fill and text color sorting at the same time. However, it can be challenging to manage the order in a spreadsheet that uses both fill and text sorting. Instead, using fill color for sorting and text color for filtering opens up all sorts of new uses for these features.

For example, let’s take this dataset representing a family. The three brothers are using the fill color green, their spouses are blue, and their children are red. By using the text color white, we can represent another data point like “July Birthdays.”

July Bday

If we apply the filter to show only white color text, we can show only the family members with July birthdays while preserving the color sort order.

Filter to show only text with white color

Google Sheets color sorting is:

  • Ideal for grouping row content by fill and text color.
  • Easy to use for sorting rows with cells that share a fill or text color at the top.
  • Practical for filtering by fill or text color to show only one color group at a time.
  • Not a viable solution if you expect colors to arrange similarly to alphabetical or numerical organization automatically on a massive scale.

This how-to guide describes a native solution for using color sorting in Google Sheets. However, color sorting existed in Microsoft Excel long before it did in Google Sheets.

The Google Sheets community members developed multiple scripts and add-ons to manually create this formerly missing feature.

Sorting by color is an excellent addition to the Google Sheets feature set which further aligns it with Microsoft Excel capabilities.

Color sorting adds practical functionality from family budgeting to product management use. Sorting by color makes Google Sheets a more powerful, useful, and easy-to-use application.

So these are the ways you can sort by color in Google Sheets.

Popular Posts