For questions about conditional formatting in Google Spreadsheets.

learn more… | top users | synonyms

59
votes
6answers
104k views

Google Spreadsheets conditional formatting based on another cell's content

How can I set conditional formatting on a particular cell that depends on another cell's value? A | B 1 | x 2 | 3 | 1 | x 1 | x 4 | 8 | // X can be any value and is here merely to // mark the ...
12
votes
5answers
55k views

Conditionally formatting a cell based on another cell's value in Google Spreadsheet

I did some searching on this and came across this post. However, this solution uses an onEdit() trigger. I would like to see a solution that uses a formula approach, so in the OP's example from that ...
6
votes
5answers
24k views

Advanced date logic in Google Spreadsheets

I have the folowing situation: Today is 4/9/12. I have a cell with a date in the sheet that is 4/13/12. This cell should turn red since it is within 7 days from the current date. What is a ...
6
votes
1answer
83 views

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

For a field I have conditional formatting with custom formula: =today()>C8+14 and =today()>C8+30 with different styling, basically I want to have a visual styling to highlight older and oldest ...
5
votes
1answer
93 views

Conditional formatting of a row header based on the values in the column

I have a table I'm using to list recipes and their ingredients. Ingredients are rows, recipes are columns, with a column next to the ingredients denoting if that ingredient is in stock. Like so: ...
5
votes
1answer
184 views

Color Shading with Google Spreadsheets

It was easy to do in Excel, but I am having trouble following some of the scripts written on here. Basically we have 3 colors we use, Green, Yellow and Red. 1 = Green, 2 = Yellow and 3 = Red. What I ...
5
votes
1answer
553 views

How to nest ImportRange into the query function in the new Google Sheets

We have applicants who come in and fill out a form via Google Forms. I have fields that auto-populate from the Response tab into a separate tab that is organized to fit our trackers. I am looking for ...
4
votes
1answer
72 views

Multi-column Conditional Formatting and Row Export in Google Sheets

I need to (a) indicate and (b) export several columns of data into a separate sheet, but only the rows that satisfy a certain condition: three columns (which are not exported) must be after today's ...
4
votes
1answer
27 views

Highlighting the maximal value of a column with conditional formatting

I have a simple column of numbers here (A2 to A10) in which I want to highlight the maximum value in that column. Instinctively I went to conditional formatting and learnt that the formula for it was ...
4
votes
1answer
260 views

Conditional formatting with multiple criteria for a range of cells

I'm trying to use conditional formatting to highlight a cell if it's value is not empty, AND if it's value is not equal to -------. I've tried every normal formula I use to check if a cell is empty ...
4
votes
1answer
46 views

Conditional formatting based of current cell AND another cell

Setting up a conditional formatting based on current cell or another cell is easy. But how do you mix them together? Let's say I have the following table: | id | red ...
4
votes
2answers
556 views

Google Spreadsheets Conditional Formatting with color

I want to add a column of cells together only if the particular cell is colored green. Can I do this using conditional formatting on a google spreadsheet?
3
votes
2answers
8k views

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

I'm working with Google's spreadsheets to keep a record of how many dragons of each breed/gender I have in a game I play. I have five columns: A (Breed) | B (Male) | C (Male Goal) | D (Female) | E ...
3
votes
1answer
34 views

How to conditional format in Google sheets based on cell directly above it?

Tried to setup a conditional format. Overall idea, I want a number to go down, and be marked red if it is going up. I tried this custom formula =if(F2>F3, true) trying to say, if the cell value ...
3
votes
1answer
42 views

Conditional formatting a range based on the value of a single cell inside of it

I'm trying to format a range based on the value of a single cell inside. So if I have a range like in this image: I am coloring based on the teams. If I was to rearrange the teams, how can I use ...
3
votes
1answer
54 views

Format a cell in bold if its row has other nonempty cells

I'm looking to format a Google Spreadsheet with something beyond the "basic" Conditional Formatting they offer. From what I understand, this must be done using a Google Apps Script. I've found one ...
3
votes
1answer
166 views

Color every other row in filter view

In Google Spreadsheets, I can use conditional formatting to color every other row in a table a certain color to make looking at wide rows easier. My problem is that I also want to use filter views so ...
3
votes
1answer
88 views

Conditional formatting based on another cell

I use Google Sheets to schedule office appointments. I have set conditional formatting so that if the appointment time slot cells are empty, they are highlighted yellow. (This makes it easier to find ...
3
votes
1answer
3k views

Google Spreadsheet - changing row colour if value set?

I'm trying to use the following script to change the row colour if a value in column N is set: function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ...
3
votes
1answer
56 views

See if a cell contains a string not from a list of strings in another column

In Google Sheets I have two columns, A and B, and want to know when a cell in column A contains a word that is not in column B. Column A contains cells with multiple words per cell, comma separated: ...
3
votes
1answer
112 views

Specify a range of text for Google Sheets Conditional Formatting

I would like Google Sheets to look at a column and highlight a cell red if it contains any of the letters A through M. My current solution is using a Custom Formula that looks like this ...
3
votes
2answers
3k views

Conditional formatting based on cell values in same column

In Google Spreadsheet, how do I change a cell's background color to red if the date in the cell matches the date in another cell in the same column? Column E in my spreadsheet is a list of due dates ...
3
votes
2answers
41 views

Coloring cells according to the value to which its content is closest

I am trying to make a guide for my dragons on a game I play but I'm not quite sure how to code what I am trying to do. Link ex. if has values "Guardian"(C2:C100) and "Dragoness"(I2:I100) in its row ...
2
votes
5answers
9k views

Conditional formatting color gradient

A few friends and myself have created a Google Spreadsheet where we are keeping track of virtual item sales. One column has costs, one column has income one has profit, and a fourth has a running ...
2
votes
4answers
4k views

Google SpreadSheet conditional formatting based on number of characters

In Google Spreadsheet, is there a way to conditionally format a cell if it has more than 30 characters?
2
votes
1answer
23 views

Google Sheets Conditional Formating “IsEqualTo” value in referenced cell

What do I add to the text field directly below the "is equal to" selection if I want the test to be the value of a specific cell? I want the condition to pass and format a cell "IF" it is equal to ...
2
votes
1answer
41 views

Can Google Spreadsheets conditionally format based on a previous cells content?

Is it possible to conditionally format the background colour of a cell, based on the value in the column preceding it? For example, in the following table (first row/column are titles): | A | B | ...
2
votes
1answer
191 views

Google Sheets: Asterisk character (no wildcard) on conditional formatting

I have a sheet with following values on column A: [ A ] [1] Dead line [2] 15 days remaining*** [3] Dead [4] 131 days remaining* [5] 80 days remaining** I would like to use ...
2
votes
1answer
63 views

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

I am looking to apply conditional formatting to all the cells in my sheet and highlight cells that contain a formula. I know that I can format cells based on a custom formula, and I know that I can ...
2
votes
1answer
44 views

Conditionally substitute cell contents with Unicode characters

Is it possible to conditionally substitute a cells contents, with a Unicode character i.e. rather than colouring all '1' cells green, swap the '1' for another character instead? Specifically, I want ...
2
votes
1answer
27 views

Why can't I refer to another cell with the value comparator field?

This is more a curiosity question than a fix to a problem, because I found a few ways to fix it already. From what I could gather, if I want to apply a conditional formatting based on the value of ...
2
votes
1answer
44 views

Conditional format formula to select a previous cells content (current cell -1)

Thanks to "Can Google Spreadsheets conditionally format based on a previous cells content?", I understand how to apply conditional formatting to a preceding cell, if I'm using a simple comparison, but ...
2
votes
1answer
152 views

Google Sheets: Format number as Mhz/kHz, or MB, kB etc

I want to make a spreadsheet with Google Sheets to compare microprocessor's specifications. One column will show processor speed, another will show program space, another for RAM space and so on. ...
2
votes
2answers
824 views

Date Formatting in Google Spreadsheet

I use a spreadsheet in my medical office for referrals. It includes places to fill in these three areas: date faxed, date of the appointment, date the appointment office notes were received. The ...
2
votes
2answers
9k views

Conditional formatting using other cells in Google Docs

I could use some help with conditional formatting. I would like to format the first 6 cells in a row in a Google Docs file based on the following condition: If column A is not empty and column C is, ...
2
votes
1answer
20 views

How would I apply formatting to another cell based on whether another cell's date has passed or not?

I'm working on making a spreadsheet in Google Sheets, and I'm attempting to have a column of names and then a column of dates adjacent to it. I'd like to set up some sort of conditional formatting ...
2
votes
1answer
29 views

Conditional formatting based on portion of text

Is there a better way to specify conditional formatting based on another cell? For example, I have specified a Custom formula as the following (which works): =or($A:$A="bar foo", $A:$A="baz foo") ...
2
votes
1answer
127 views

Can I use conditional formatting to color a column's cells' text based on the conditionally-formatted color of text in another column's cells?

I have been working on a spreadsheet in Google Sheets that serves as a database of the hundreds of characters who appear in a novel I am reading (to help me keep track of them all). Column A contains ...
2
votes
1answer
90 views

Google Sheet conditional formatting, highlight just 1 cell in a range based on another cell [duplicate]

How can I highlight a cell in a range, if its value is equal to a different cell? Example: Random numbers in range A5:A20. I want to highlight a cell in this range if it is equal to B4. [ A ...
2
votes
1answer
50 views

Changing colour of row if a certain aspect is met

I have a list of clients that send in certain information to me and I have setup columns to enter a tick using (=char(10004)) on R,S,T,U,V,W,X & Y columns and I need to colour them to go green ...
2
votes
1answer
401 views

Copy-pasting cells with conditional formatting

I'm trying to use conditional formatting in the goals area. Currently the calorie goal (J10) turns green as long as I stay under it's number, and it's compared to the total calories for the day (J3). ...
2
votes
1answer
2k views

Conditional formatting in Google Sheets: How do I check if a cell is blank?

In my exercise tracking spreadsheet, I want blank cells in columns B:O highlighted if column A is today's date, and the cell itself is blank. I have tried this: =AND(ISBLANK(B3),$A:A=INT(NOW())) ...
2
votes
1answer
575 views

Date Formatting for Deadlines

I have set up a Google spreadsheet for managing projects and due dates, and I need to apply the following formatting to a column of dates, but I'm not sure how: Dates more than 7 days from today: ...
2
votes
2answers
1k views

How can I set conditional formatting on particular cells that depend on another cell's value in Google Spreadsheets?

I would like to colour the background of a row in Google Spreadsheets: For example: If cell Dx = title 'To do' or 'title 'Done' then cells Fx to Nx are filled colour black. If cell Dx = title ...
2
votes
1answer
1k views

Is it possible to add conditional formatting when number changes?

This is probably not possible but... I'm looking to update cells in a Google spreadsheet using the conditional formatting tool.(Changes to SERPs rankings) So when the number in a cell is changed, ...
1
vote
1answer
92 views

Apply conditional formatting if other cell is not empty

2 Columns. C2 | D2 C3 | D3 C4 | D4 C5 | D5 I want it to apply conditional formatting to D2 if C2 has information in it, and D2 is blank.( will be a date). If C2 is empty, then D2 should have no ...
1
vote
1answer
34 views

Conditional formatting based on multiple cells

I'm trying to figure out how to write a formula for Google Spreadsheet where: If F3 Contains Data and G3 is more than 0 then cell J3 is Highlighted in Green Can anyone help me out with this?
1
vote
1answer
35 views

Conditional formatting based on comparison with today's date and also with a future date

I'd like to conditionally format cells that will go red if it's past its expiration, go yellow if it's within 3 months of expiring, to remain green if date is clear of expiry. For example: today's ...
1
vote
2answers
161 views

Google Sheets - Conditional Formatting with IF and THEN

I would like to create a daily dashboard with cells that show the progress of my work regarding to a date. For example: A1 is a text saying "Open". C1 is a cell that contains a date. This date ...
1
vote
1answer
45 views

Conditional Formatting: no color when cell is empty

I am using this custom formula for my sheet: =if(B2<((B$7/4)*$A2), 1, 0). However, this also highlights the rest of the cells that are empty. How do I exclude empty cells from this formula?