Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in


How to filter rows based on cell color in Google sheet?

Normally, we can filter rows by a certain color in Excel quickly with the Filter feature, but, in Google sheet, the Filter function does not support this operation. This article, I will talk about how to filter rows by color in Google sheet.

Filter rows based on color in Google sheet

Filter rows based on color in Google sheet

Please apply the following steps to filter data based on color.

1. Click Tools > Script editor, see screenshot:

2. In the opened project window, please copy and paste the below script code to replace the original code in the code module, see screenshot:

function getHex(input) {
  return SpreadsheetApp.getActiveSpreadsheet().getRange(input).getBackgrounds();

3. And then save the code window, and a prompt box is popped out to remind you to give the project a name, see screenshot:

4. After saving the script code, go back the sheet, and enter this formula: =getHex(“A2:A16”) into a blank cell next to your data range, and then press Enter key, the color index of each row has been displayed in the helper column, see screenshot:

5. Then you just need to filter rows based on this helper column, please click the helper column header, and then click Data > Filter, see screenshot:

6. And then click the drop-down button in the helper column, in the expanded pane, choose and check the color index that you want to filter from the list box, see screenshot:

7. Click the OK button, the rows which filled with the specific cell color have been filtered as following screenshot shown:

Recommended Productivity Tools

Office Tab

gold star1 Bring handy tabs to Excel and other Office software, just like Chrome, Firefox and new Internet Explorer.

Kutools for Excel

gold star1 Amazing! Increase your productivity in 5 minutes. Don't need any special skills, save two hours every day!

gold star1 200 New Features for Excel, Make Excel Much Easy and Powerful:

  • Merge Cell/Rows/Columns without Losing Data.
  • Combine and Consolidate Multiple Sheets and Workbooks.
  • Compare Ranges, Copy Multiple Ranges, Convert Text to Date, Unit and Currency Conversion.
  • Count by Colors, Paging Subtotals, Advanced Sort and Super Filter,
  • More Select/Insert/Delete/Text/Format/Link/Comment/Workbooks/Worksheets Tools...

Screen shot of Kutools for Excel

btn read more      btn download     btn purchase

Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.