How to sort by Color in Google Sheets

When you’re working on Google Sheets, you might come across data that is distinguished by color. It can be useful to sort the data by color and make the sheet look neater. For instance, take the data of students highlighted in red and green below. Red means they’ve failed a test and green means they’ve passed it.

The same data when sorted by color looks much neater and cleaner. 

4 methods to sort by color in Google Sheets

  • Using Sort by color from the Filter icon in the toolbar
  • Using Sort by color from the Filter option in the menu bar
  • Using Google Apps Script
  • Using a third-party add-on

Method 1: Using Sort by color from the Filter icon in the toolbar

This method uses the Filter icon present in the toolbar.

Step 1: Select the column you want to sort.

Tap on the column name that you want to sort. You can see the range in the Name box at the top-left. Here, column A is selected. 

Step 2: Select Create new filter view.

Find the filter icon in the toolbar. Click the dropdown near it and select Create new filter view.

Step 3: Choose the color to sort by.

The first row of the column contains the filter icon at the extreme right. Click on it and select Sort by color. Now, choose Fill Color because we are sorting based on the cell’s background color. If you’re sorting based on text color, choose Text Color. Next, select the color you want first on the sorted list. 

In this instance, light green is selected. That will sort the students who’ve passed first, followed by those who failed. 

After choosing your color, you can see your data sorted by color. 


Method 2: Using Sort by color from the Filter option in the menu bar

This method is similar to the previous method. It uses the Data option from the main menu bar.

Step 1: Highlight the desired column.

Choose the column you want to sort and select the column name. This will highlight the entire column. In this instance, column A is selected.

Step 2: Select Create a filter.

From the main menu bar, click on Data. This will open a dropdown. Select Create a filter.

Step 3: Choose your desired color.

Create a filter would create a filter icon at the extreme right on the first cell of the selected column. Click on it and select Sort by color. Now, choose Fill Color because we are sorting based on the cell’s background color. If you’re sorting based on text color, choose Text Color. Now, select the color you want first on the sorted list. 

In this instance, light red is selected. That will sort the students who’ve failed first, followed by those who passed. 

Step 4: Check the results.

You can now see the sorted list. Ensure that these are your expected results.


Method 3: Using Google App Script

This method involves adding some code to your Google Sheet via the App Script.

Step 1: Select Apps Script.

From the main menu bar, click on Extensions. Now, select Apps Script. This will open a new window. 

Step 2: Copy-paste the code.

Delete any existing code in the code editor. Copy-paste the below code into the code editor. Alternatively, you can also copy-paste the same code from this GitHub link.

/**
 * Create custom menu
 */
function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Color Tool')
        .addItem('Sort by color...', 'sortByColorSetupUi')
        .addItem('Clear Ranges','clearProperties')
        .addToUi();
}

/**
 * Sort By Color Setup Program Flow
 * Check whether color cell and sort columnn have been selected
 * If both selected, move to sort the data by color
 */
function sortByColorSetupUi() {
  
  var colorProperties = PropertiesService.getDocumentProperties();
  var colorCellRange = colorProperties.getProperty('colorCellRange');
  var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');
  
  //if !colorCellRange
  if(!colorCellRange)  {
    title = 'Select Color Cell';
    msg = '<p>Please click on cell with the background color you want to sort on and then click OK</p>';
    msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(1); google.script.host.close();" />';
    dispStatus(title, msg);
  }
  
  //if colorCellRange and !sortColumnLetter
  if (colorCellRange && !sortColumnLetter) {
      
      title = 'Select Sort Column';
      msg = '<p>Please highlight the column you want to sort on, or click on a cell in that column. Click OK when you are ready.</p>';
      msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(2); google.script.host.close();" />';
      dispStatus(title, msg);
  }
  
  // both color cell and sort column selected
  if(colorCellRange && sortColumnLetter) {
    
    title= 'Displaying Color Cell and Sort Column Ranges';
    msg = '<p>Confirm ranges before sorting:</p>';
    msg += 'Color Cell Range: ' + colorCellRange + '<br />Sort Column: ' + sortColumnLetter + '<br />';
    msg += '<br /><input type="button" value="Sort By Color" onclick="google.script.run.sortData(); google.script.host.close();" />';
    msg += '<br /><br /><input type="button" value="Clear Choices and Exit" onclick="google.script.run.clearProperties(); google.script.host.close();" />';
    dispStatus(title,msg);
    
  }
}


/**
 * display the modeless dialog box
 */
function dispStatus(title,html) {
  
  var title = typeof(title) !== 'undefined' ? title : 'No Title Provided';
  var html = typeof(html) !== 'undefined' ? html : '<p>No html provided.</p>';
  var htmlOutput = HtmlService
     .createHtmlOutput(html)
     .setWidth(350)
     .setHeight(200);
 
  SpreadsheetApp.getUi().showModelessDialog(htmlOutput, title);

}


/**
 * helper function to switch between dialog box 1 (to select color cell) and 2 (to select sort column)
 */
function sortByColorHelper(mode) {
  
  var mode = (typeof(mode) !== 'undefined')? mode : 0;
  switch(mode)
  {
    case 1:
      setColorCell();
      sortByColorSetupUi();
      break;
    case 2:
      setSortColumn();
      sortByColorSetupUi();
      break;
    default:
      clearProperties();
  }
}

/** 
 * saves the color cell range to properties
 */
function setColorCell() {
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var colorCell = SpreadsheetApp.getActiveRange().getA1Notation();
  var colorProperties = PropertiesService.getDocumentProperties();
  colorProperties.setProperty('colorCellRange', colorCell);

}

/**
 * saves the sort column range in properties
 */
function setSortColumn() {
  
  var sheet = SpreadsheetApp.getActiveSheet();
  var sortColumn = SpreadsheetApp.getActiveRange().getA1Notation();
  var sortColumnLetter = sortColumn.split(':')[0].replace(/\d/g,'').toUpperCase(); // find the column letter
  var colorProperties = PropertiesService.getDocumentProperties();
  colorProperties.setProperty('sortColumnLetter', sortColumnLetter);
  
}

/** 
 * sort the data based on color cell and chosen column
 */
function sortData() {
  
  // get the properties
  var colorProperties = PropertiesService.getDocumentProperties();
  var colorCell = colorProperties.getProperty('colorCellRange');
  var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');
  
  // get the sheet
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastCol = sheet.getLastColumn();
  
  // get an array of background colors from the sort column
  var sortColBackgrounds = sheet.getRange(sortColumnLetter + 2 + ":" + sortColumnLetter + lastRow).getBackgrounds(); // assumes header in row 1
  
  // get the background color of the sort cell
  var sortColor = sheet.getRange(colorCell).getBackground();
  
  // map background colors to 1 if they match the sort cell color, 2 otherwise
  var sortCodes = sortColBackgrounds.map(function(val) {
    return (val[0] === sortColor) ? [1] : [2];
  });
  
  // add a column heading to the array of background colors
  sortCodes.unshift(['Sort Column']);
  
  // paste the background colors array as a helper column on right side of data
  sheet.getRange(1,lastCol+1,lastRow,1).setValues(sortCodes);
  sheet.getRange(1,lastCol+1,1,1).setHorizontalAlignment('center').setFontWeight('bold').setWrap(true);
  
  // sort the data
  var dataRange = sheet.getRange(2,1,lastRow,lastCol+1);  
  dataRange.sort(lastCol+1);
  
  // add new filter across whole data table
  sheet.getDataRange().createFilter();

  // clear out the properties so it's ready to run again
  clearProperties();
}

/**
 * clear the properties
 */
function clearProperties() {
  PropertiesService.getDocumentProperties().deleteAllProperties();
}

Once you’ve pasted the code into the code editor, click the Save icon to save the code.

Step 3: Select Color Tool from your Google Sheet.

Go back to your Google Sheet and find Color Tool added to the main menu bar. Click on it and select Sort by color…

Step 4: Select the column and color. 

After clicking the Sort by color option, the script will prompt you for the color. Highlight a cell with the desired background color. Click the OK button. 

Now, you will be prompted for the desired column. Select a cell from the column you want to filter. Click the OK button.

Step 5: Confirm your choices.

You will see a dialog box with your choices. You can confirm them by clicking the Sort By Color button. If your choices are incorrect, click the Clear Choices and Exit button.

You can now see the sorted data. The extra column is added as part of the code.


Method 4: Using a third-party add-on

This is another method to sort data by text color, fill color, and font size.

Step 1: Select Get add-ons.

Click Extensions from the main menu. This will open a dropdown. Select Add-ons and tap on Get add-ons.

Step 2: Install the add-on.

Clicking Get add-ons will open a dialog box. Search for sort by color. Select the Sort Range PLUS add-on.

Click the blue Install button. 

It will prompt you to allow access. Click Allow.

After successful installation, you can see a dialog box stating the same.

Step 3: Highlight the column to be sorted.

Tap on the column that you want to sort. You can also right-click and drag your cursor across the desired range of cells.

Step 4: Start the Sort Range Plus add-on.

Click Extensions from the main menu bar. Hover over the Sort Range Plus add-on. Select Sort Range…

Step 5: Confirm your choices.

If the range of cells selected has a header, ensure you’ve checked the checkbox. You also have other options to sort like text color and font size. Choose accordingly and confirm by clicking the blue Sort button.

You have successfully sorted your data by color.


Wrapping up

No more wondering how to sort data by color! You now know four different methods to sort data by color with ease.

1 thought on “How to sort by Color in Google Sheets”

  1. This is great, thank you for posting. I’m trying to use the exact Add-On you mention in Option 4 – Sort Range Plus, but I want to call it automatically whenever this sheet is edited. How can I do that? I can’t find the code anywhere!
    I’d be so grateful for any help.

    Thank you,
    Erin

    Reply

Leave a Comment