36 votes

How do I change a cell to the color of the hexadecimal value of a cell in Google Spreadsheets?

=sparkline(1,{"charttype","bar";"color1",A1}) This will provide a solid colored cell using the Hex color code in A1. Also accepts some named colors as well, e.g. "...
Peter John's user avatar
31 votes
Accepted

Conditionally format by day of the week

I had been struggling with this for a long time, but finally cracked it: Use conditional formatting on the column with the dates and type the following as a custom formula: =or(WEEKDAY(A1)=1,WEEKDAY(...
aej's user avatar
  • 326
21 votes

How do I make the "Text contains" evaluate multiple values in Google Spreadsheet?

IMO better without "Text contains". To avoid the possibility of conflicting rules, clear all conditional formatting from ColumnA (assumed because OP does not say where the data is), select it and ...
pnuts's user avatar
  • 17.6k
20 votes
Accepted

How to create a checkbox column, that if selected will create a strike-through across the entire row in Google Sheets?

Conditional formatting: If checkbox ticked in cell A1, change color/strikethrough of cells from B1-F1. Insert a checkbox from "Insert" tab to A1. Select the cells (B1-F1) that needs to be of ...
sreejith r's user avatar
10 votes

How to refer to 'this' cell in a conditional formatting formula?

This is the shortest possible way I've found to reference the current cell in conditional formatting spanning a range: INDIRECT("RC",FALSE). Documentation is here.
Markus Amalthea Magnuson's user avatar
10 votes
Accepted

Combine Custom Formula with Colour Scale in Conditional Formatting

This is now possible by inserting a formula instead of a static number into the box for min/mid/max amount.
Dylan Vander Berg's user avatar
7 votes
Accepted

How do I change a cell to the color of the hexadecimal value of a cell in Google Spreadsheets?

Short answer Use Google Apps Script or a Google Sheets Add-on as there is no built-in feature to set cell background color by color codes. Explanation Google Apps Script is a tool that can be used to ...
Rubén - Volunteer Moderator -'s user avatar
6 votes

Conditionally format by day of the week

If the dates are in column A, select them, then do: Format > Conditional formatting... > Format cells if... > Custom formula and put =and(isblank(A:A)=false, or(weekday(A:A)=1, weekday(A:A)=7)) Some ...
Emerson's user avatar
  • 61
6 votes

Advanced date logic in Google Spreadsheets

In case you need more precise times than in the accepted answer, you can use DATEDIF to specify a certain difference between dates. Example: format cell A1 if date in A1 is older than 10 days. ...
alex sichwart's user avatar
5 votes

Automatically add borders around nonempty cells

I could do it just by using conditional formatting in MS Excel and then importing the book to Google sheets. It keeps the conditional formatting of the borders as long as I don't touch conditions for ...
user154134's user avatar
5 votes
Accepted

Using MAX in conditional formatting only evaluates numbers after the cell, regardless of range

Insert $ between A and 2 in the formula. As CF 'steps through' to test each cell in the range in turn the range is being automatically adjusted. So when for example testing A9 it is checking for the ...
pnuts's user avatar
  • 17.6k
5 votes
Accepted

Using setBackgroundRGB() over setBackgroundColors()

As you (maybe) know, the fact that a method is "deprecated" does not mean it doesn't work - just that it might be removed in the future. So you're probably not getting an error, but a warning, and ...
Vidar S. Ramdal's user avatar
5 votes
Accepted

Is there a way to lock or protect conditional formatting in Google Sheets?

I'm afraid that currently it's not possible to use cut & paste / drag & drop. Consider to send a feature request directly to Google through Google Feedback: Click on menu Help > Report a ...
Rubén - Volunteer Moderator -'s user avatar
5 votes
Accepted

Conditional formatting with color scale for many rows independently once

I know I'm a bit late to the party... but I had this same question myself and then ended up finding the answer on my own with a little tinkering. Basically, you just need to use "Number" for ...
BlackHatCS's user avatar
4 votes

Highlight all cells in a range that contain a formula in Google Sheets

Note that it is also possible to use conditional formatting as in (for your case): =isformula(a1) This kind of specification is more flexible than the whole range answer. For example, lets say in ...
cladelpino's user avatar
4 votes
Accepted

How to apply a relative reference in conditional formatting formula?

Try this: =INDIRECT(ADDRESS(ROW(),COLUMN(),4))/REGEXEXTRACT(INDIRECT(ADDRESS(ROW()+1,COLUMN(),4)),"\((.*)\)")*1>1 INDIRECT ADDRESS 1 ROW COLUMN 1 The third parameter in ADDRESS() is ...
ale's user avatar
  • 52.7k
4 votes
Accepted

Google Sheets Alternate Row Color by Unique Sorted Values

Scenario If you have 500 items, in column B, but only 20 of them are unique invoices due to them being broken up into purchase orders. It makes sense to keep them separate so you can more easily ...
Xzila's user avatar
  • 853
4 votes

How to create a checkbox column, that if selected will create a strike-through across the entire row in Google Sheets?

Assuming (1) your checkbox is character ☑ (Code 9745) and your entire row runs to ColumnZ, then please select ColumnsB:Z and apply a Custom formula is of: =$A1="☑" with formatting of Strike-through ...
pnuts's user avatar
  • 17.6k
4 votes

How to create a checkbox column, that if selected will create a strike-through across the entire row in Google Sheets?

Based on the answer from Rubén, I stumbled into a way to do it across the whole spreadsheet. It worked for me as follow: Insert a checkbox from "Insert" tab to H1 (in my case) From tab above, "Format"...
Stefan's user avatar
  • 41
4 votes
Accepted

Conditionally format row borders

The process that would generate a script that makes mentioned conditional formatting includes of course what you mentioned you are unfamiliar with: scripting. A) Learn the basics of google apps ...
Maarten Troost's user avatar
4 votes

Set conditional formatting on a column that depends on another cell's text (not value)

Since you use the date value, we have a nice weekday() function we can make use of. weekday(*yourDate*,2) will give you an integer from 1-7 counting from Monday. On the conditional formatting ...
WaterGenie's user avatar
4 votes

Is there a way to conditionally format border styles in Google Sheets?

One workaround is to use Google Apps Script instead of the built-in conditional formatting options but it doesn't performs so efficiently. References https://developers.google.com/apps-script/guides/...
Rubén - Volunteer Moderator -'s user avatar
4 votes
Accepted

Conditional formatting custom formula not working in google sheets

In the end =COUNTIF($B1:$B100,"month")>1 worked, no quotation marks. I thougth I tried it before
Manuel Ramirez's user avatar
4 votes
Accepted

Why does my conditional formatting not work with this formula?

Use indirect() when referring to another sheet in a conditional formatting custom formula rule, like this: =not($B$15) * ifna(match(left(C19), indirect("Code Sheet!X4:AD4"), 0)) * ifna(match(...
doubleunary's user avatar
  • 14.5k
3 votes
Accepted

Google Spreadsheet Conditional Formatting depending on up to 7 Fields

FORMULAS: RED: =if(And(COUNTA(B42:H42)>1,isblank(D42)),true,false) YELLOW =if(and(istext(D42),(Or(isblank(B42),isblank(C42),isblank(E42),isblank(F42),isblank(H42)))),true,false) GREEN =if(...
Aurielle Perlmann's user avatar
3 votes

Conditional number format (whole number, percentage) based on the value in another cell

I know this is an old question but it seems that Google has yet to implement number formatting for conditional formatting options. While user79865's answer achieves the desired result it involves ...
DanCue's user avatar
  • 131

Only top scored, non community-wiki answers of a minimum length are eligible