Let’s say you’re a teacher managing a classroom or a project manager tracking a team’s tasks. In Google Sheets, you have a list of student names or project tasks with cells having different colors to show their status.
For example, you can use green to indicate completed assignments or tasks, yellow for ongoing ones, and red for those that haven’t started.
Sorting data by cell color would help you quickly organize and view all similar status together. For a teacher, you could easily see who needs more help by bringing all the red-colored cells (incomplete assignments) to the top.
Now let’s take an example of the below data…
Steps to Sort by Color (Cell Color) in Google Sheets
In this data, you need to sort using the colors and put students with the incomplete status on the top, and In Progress on second.
- First thing you need to do is to click on the “Homework Status” column header to select it. Then, go to the “Data” menu and click “Create a filter.” It will add a filter icon to the column header.
- Now click the filter icon in the “Homework Status” column header. A dropdown menu will appear. Hover over “Sort by color” in the dropdown.
- Click on the “Sort by Color” option from here. You’ll find “Fill color” and “Text color” there. Click on the “Fill color”. From there, in the color list choose “Yellow” color to sort the column.
- At this point, yellow color cells are at the top, but we want to red color at the top and yellow color on second. So, you need to use the same option which we have done in the earlier step. Click on the Filter Icon > Sort by Color > Fill Color > Red. And now the moment you click it you will have the red color cell at the top and yellow color on second and green color at last.
And the moment you complete these steps you will have a data like the following in your sheet.
Important Point to Note while Sorting by Color
In Google Sheets, while sorting data by using multiple colors, you need to use a same step multiple time. In the above example, we have sorted Green > Yellow > Red.
So, in this we have sorted yellow color first, and then the red color. As we have three colors, so we had to sort two colors. And if you have 5 colors so you need to sort four colors using a sequence to sort Forth > Third > Second > First. That means you need to sort using opposite sequence.
Sort by Font Color in Google Sheets
As you have already seen that you also have the option to sort a column based on the font color. So, make sure to apply the filter on the column you want to sort using the font color.
Open the Filter from the column header, and then, hover over or find the “Sort by Color” option in the menu. Then, select “Sort by Font Color”. This opens a further menu with the colors used as font color within that column.
Click on the color by which you want to sort by. And all the cells with that font color will be at the top of the column. If you want to sort the columns using the multiple colors make sure to use the a opposite sequence as we have discussed above.
Sort by Color (Font – Cell) with a Google Script App
Below is a Google Apps Script that you can use to sort data in Google Sheets based on cell color.
function sortDataByColor() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Specify the range here
var range = sheet.getRange('A2:B21');
var values = range.getValues();
// Gets the background colors of the range
var backgrounds = range.getBackgrounds();
// Define your color sorting order here
var colorOrder = {
"#ea9999": 1, // Red
"#ffe599": 2, // Yellow
"#b7e1cd": 3 // Green
};
// Define the column number to sort by color (For Columns 1 Enter 0, for 2 enter 1)
var sortColumnIndex = 1; // Assuming you want to sort by the second column.
// Combine the data with colors
var combinedData = values.map(function(row, index) {
var colorValue = colorOrder[backgrounds[index][sortColumnIndex]] || 999; // Default for undefined colors
return { colorValue: colorValue, value: row };
});
// Log the combined data for debugging
console.log(combinedData);
// Sort the combined data array by the custom color order
combinedData.sort(function(a, b) {
return a.colorValue - b.colorValue;
});
// Extract the sorted values back into a simple array
var sortedValues = combinedData.map(function(item) {
return item.value;
});
// Set the sorted values back to the sheet
range.setValues(sortedValues);
} In this code, to work for you need to change the range, column number to sort by, and then the sequence of the colors. And then, go to the Extension > App Script > and then paste the code into the code window. Then, save it and then run the code to sort the data based on the cell colors.
Below is the part of the code which you need to edit to make it for your data.
And now, below we have a Google App Script code that sorts the data from the range A2:B21, from the second columns using the font colors apply to the values in the column.
function sortByFontColor() {
var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
// Specify the range here
var range = sheet.getRange('A2:B21');
var values = range.getValues();
// Gets the font colors of the range
var fontColors = range.getFontColors();
// Define your font color sorting order here
var colorOrder = {
"#ea4335": 1, // Example red font color
"#ffff00": 2, // Example yellow font color
"#6aa84f": 3 // Example green font color
};
// Define the column number to sort by font color (For Columns 1 Enter 0, for 2 enter 1)
var sortColumnIndex = 1; // Assuming you want to sort by the second column.
// Combine the data with font colors
var combinedData = values.map(function(row, index) {
var colorValue = colorOrder[fontColors[index][sortColumnIndex]] || 999; // Default for undefined colors
return { colorValue: colorValue, value: row };
});
// Log the combined data for debugging
console.log(combinedData);
// Sort the combined data array by the custom color order
combinedData.sort(function(a, b) {
return a.colorValue - b.colorValue;
});
// Extract the sorted values back into a simple array
var sortedValues = combinedData.map(function(item) {
return item.value;
});
// Set the sorted values back to the sheet
range.setValues(sortedValues);
}