Guides

Filter values by color with a formula in Google Sheets

Google Sheets can filter rows by color from the menu, but there is no formula that returns the cells of one color. Here are the three ways that work, and where each one falls short.

You colour-code a list — paid in green, overdue in red — and now you want just the green values somewhere else: in a summary, in another range, or as input for another formula. FILTER works on values and conditions; it can’t see a cell’s colour. You have three options.

Method 1 — Filter by color from the menu

Google Sheets can hide every row that doesn’t have the colour you pick:

  1. Select the column, then Data → Create a filter.
  2. Open the column’s filter icon → Filter by color, and choose Fill color or Text color.
  3. Pick the colour. Rows with any other colour disappear.
Limits: this is a view of the sheet, not a formula. It hides rows instead of returning values, so you can’t place the matching cells in a separate range or feed them into another formula. Fine for looking at one colour; not for building a summary on top of it.

Method 2 — A custom function you paste yourself

Free and it returns a real list — but you own the upkeep, and this snippet only reads the fill colour. Open Extensions → Apps Script, paste this, and save:

/** * Return the values of the cells whose fill colour matches a reference cell. * Pass both arguments as text: =FILTERBYFILL("B2:B100", "A2") */ function FILTERBYFILL(rangeA1, colorA1) { var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(); var target = sheet.getRange(colorA1).getBackground(); var range = sheet.getRange(rangeA1); var colors = range.getBackgrounds(); var values = range.getValues(); var out = []; for (var r = 0; r < colors.length; r++) { for (var c = 0; c < colors[r].length; c++) { if (colors[r][c] === target && values[r][c] !== "") out.push([values[r][c]]); } } return out.length ? out : [[""]]; }

Back in the sheet: =FILTERBYFILL("B2:B100", "A2").

Watch out for:
  • The range must be passed as a string ("B2:B100") — a normal range argument gives Apps Script only the values, never the formatting.
  • Changing only a cell’s colour does not recalculate the sheet, so the list goes stale until you edit a value or reopen the file.
  • Text colour, font style, or several ranges each mean another function or more code.
  • The script lives in this spreadsheet only. Every other file you want it in, you paste it again and re-authorise; every collaborator does the same in their copy.

Method 3 — The Custom Count and Sum add-on

Install once and the functions are in every spreadsheet on your account — now and later — with no code to paste or keep working. After installing Custom Count & Sum:

# values whose fill matches the colour of A2 =FILTERBGCOLOR("B2:B100", "A2") # or give a hex colour directly =FILTERBGCOLOR("B2:B100", "#d9ead3") # text colour instead of fill =FILTERFONTCOLOR("B2:B100", "A2") # sort or de-duplicate the result like any other array =SORT(FILTERBGCOLOR("B2:B100", "A2")) =UNIQUE(FILTERBGCOLOR("B2:B100", "#d9ead3"))

What the add-on version does for you:

The full list is on the function reference.

One limit that applies to every method: none can be used inside MAP, BYROW or other LAMBDA functions — Google runs those entirely in the calculation engine, which has no access to a cell’s formatting.

Which method should you use?

  Filter by color (menu) Own script Add-on
Setup None Paste & maintain code One-click install
Returns a list you can use in a formula No — hides rows Yes Yes
Updates after a colour change Re-apply by hand Only with a custom trigger Yes, via the Recalculate button
Text colour, style, several ranges One colour at a time A function each Built in
Available in your other spreadsheets — No — paste per file Yes — every sheet on your account
Upkeep None You own every fix & addition Updates itself
Cost Free Free The FILTER functions need a plan after a 14-day free trial

The menu filter is enough when you only need to look at one colour. A custom function works if you need the fill colour only and don’t mind the recalc caveat. Reach for the add-on when the list has to live in the sheet, feed other formulas, or follow text colour or font style as well.

Return the cells of one colour with a formula

Custom Count and Sum adds 38 formatting-aware functions to Google Sheets. COUNTSTYLE is free on any range; a 14-day trial unlocks the rest.

Install free from Google Workspace Marketplace