31 votes
Accepted

Apply conditional formatting if other cell is not empty

The ISBLANK formula should serve your purpose well. Create a new rule in the conditional formatting menu. Set the range of your rule to D2:D so that the formatting is applied to column D. Then, in ...
user avatar
  • 1,658
30 votes
Accepted

Conditional format rule "Text Contains" with a "+" misinterpreted as a formula

Use ~ (tilde) to escape + (plus sign), = (equal sign) and also other special characters like * (asterisk) and ? (question mark) that are used as wildcards. References Apply conditional formatting ...
user avatar
  • 41.6k
28 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(...
user avatar
  • 296
19 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. "...
user avatar
18 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 ...
user avatar
17 votes

Conditionally format by day of the week

Easy (but a bit tedious!) in new Google Sheets. For Conditional Formatting a new rule is required for each colour, roughly. (One colour can usually be skipped from rules and instead applied with ...
user avatar
  • 16.6k
17 votes
Accepted

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

You can use a range as a parameter for the ISFORMULA() function. e.g. If you want to highlight all cells containing formulas in the range A1:J10, then you can use the formula =ISFORMULA(A1:J10) and ...
user avatar
  • 408
15 votes

Conditional formatting color gradient

Google Sheets now supports conditional color scales under Menu "Format > Conditional formatting..." then select the tab "Colour scale".
user avatar
15 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 ...
user avatar
  • 16.6k
12 votes

How do I apply formatting to a cell based on comparison with another cell?

Here is what you can do: RightClick B2 → select Conditional formatting → select Custom Formula is (from the List) Enter the following formula in the textbox: =b2>=c2 Check the ...
user avatar
  • 1,047
12 votes
Accepted

Conditional formatting with multiple criteria for a range of cells

Writing a formula that satisfies your criteria is a matter of breaking down what your criteria are and implementing corresponding Sheets functions. You have established that the formatting should be ...
user avatar
  • 1,658
11 votes

Conditional formatting based on number of characters

Yes! Highlight the cells you want to format. Go to Format > Conditional Formatting. Once you're in the conditional formatting dialog, choose "Custom Formula Is" from the select box (it's the last ...
user avatar
  • 211
9 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.
user avatar
9 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.
user avatar
8 votes

How to refer to a cell in the same column at given row?

Solved with: =INDIRECT( ADDRESS( 4, COLUMN() ) ) ADDRESS gives me a string like "$A$1" and INDIRECT makes it a cell reference.
user avatar
7 votes
Accepted

Conditionally formatting the minimum value in a row

Sooo close (I think!). Please try: =D3=min($D3:$L3) =min(D3:L3) returns a value but CF needs a TRUE to trigger. So we check "the current cell" (D3) to see whether its value matches that of the ...
user avatar
  • 16.6k
7 votes
Accepted

Asterisk character (no wildcard) on conditional formatting

You can achieve your desired results by putting a tilde, ~ in front of the asterisk as an escape character, if you put the conditional formatting rules in the order listed below. First, create the ...
user avatar
  • 186
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 ...
user avatar
  • 41.6k
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 ...
user avatar
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. ...
user avatar
5 votes

Conditional formatting color gradient

An old thread, but it's worth noting that Google just launched this functionality into Google Sheets. This likely negates the need for external solutions for most users. More info: Google Apps ...
user avatar
  • 51
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 ...
user avatar
  • 16.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 ...
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 ...
user avatar
  • 41.6k
4 votes
Accepted

Colorize a cell in Google Sheets based on cell data

I've just had a go at this, and here's a more flexible function. The previous example couldn't properly handle ranges of selected cells, this one can (but you could also add some better error checking ...
user avatar

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