For questions about conditional formatting in Google Spreadsheets.

learn more… | top users | synonyms

1
vote
1answer
16 views

Google Sheets - Conditional Formatting Based on Date Formula

I'm trying to highlight the contents of a Date cell if the following conditions are met: Value of another cell = "No"; and Value of target cell + 7 days > Today The formula that I have right now ...
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
1answer
22 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
19 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 ...
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 ...
1
vote
2answers
44 views

Apply conditional formatting based on the formula that a cell contains

I am having trouble figuring out how to get conditional formatting to work in my Google spreadsheet. I use the second sheet to import prices of projectors, but a few don't update. I want the row of ...
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 ...
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 ...
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 ...
1
vote
1answer
2k views

Conditional formatting with if formula

On Google Spreadsheet, I want cell D88 to change background color depending on the value in E88. In my head this should work like this (imagine it's a conditional formatting window): ...
2
votes
1answer
43 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 ...
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 ...
2
votes
1answer
396 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). ...
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 ...
1
vote
1answer
91 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 ...
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 ...
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 ...
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") ...
4
votes
1answer
245 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 ...
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 ...
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 ...
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 ...
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 | ...
0
votes
1answer
22 views

Conditional formatting over wide area of cells, referencing other cells

As data validation does not seem to be able to reference cells for numerical conditions (instead only being able to hardcode numbers); I have fallen back on conditional formatting. However, it does ...
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 ...
0
votes
1answer
48 views

Conditional formatting of an entire row based on multiple cells

I'm trying to make a conditional formatting rule in Google Spreadsheet that would highlight a row green based on text in multiple cells. If cells L2, M2, and N2 = APPROVED; and cell O2 = X; then row ...
1
vote
1answer
29 views

Highlight a cell if the date in one column is the latest date

I'm trying to figure out the proper conditional formatting formula to hilight a cell based on the date in that cell (and/or an adjascent one) representing the most recent date in the column. Given ...
3
votes
1answer
55 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: ...
1
vote
1answer
33 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?
3
votes
1answer
53 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 ...
1
vote
2answers
159 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 ...
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 ...
3
votes
1answer
110 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 ...
2
votes
1answer
88 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
189 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 ...
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 ...
0
votes
0answers
23 views

Set color if cell value is element of another sheets row [duplicate]

I am trying to figure this out since a while now. I have got it working if everything is in the same sheet! It goes like this: Select row K -> conditional formatting -> Custom formula is ...
1
vote
1answer
455 views

Conditional Formatting based on another sheet's content

I am trying to set a conditional format in Google spreadsheets and I am having some difficulty. I am not sure if I should use a cell reference or if formula. In my first Sheet called "Roster" there ...
2
votes
1answer
49 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 ...
1
vote
2answers
1k views

Relative copy conditional formatting that references other worksheet

I have a group of worksheets, one of which has 'default' values while the others should only have values if they differs from the default worksheet. To facilitate this, I want to use conditional ...
1
vote
1answer
23 views

Formatting cells based on upcoming date in Google sheets

I am trying to format a cell to be highlighted red if the following conditions are true: the future date associated with that row (which is in a separate cell) is less than 21 days from today, and ...
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?
2
votes
1answer
61 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 ...
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: ...
3
votes
1answer
87 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 ...
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. ...
1
vote
1answer
164 views

Cell formatting in Google Spreadsheets does not get preserved

My Google Spreadsheet does not preserve some formatting. Every time I set, for example font size to 12, for the whole sheet, newly copied data uses a smaller font size. This only started being an ...
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 ...
1
vote
1answer
320 views

Google spreadsheets Conditional Formating Formula

I am having trouble highlighting all cells in a column that do not contain the same text that is found in the first cell at the top of the column. I would like to accomplish this with a conditional ...
5
votes
1answer
549 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 ...