For questions about conditional formatting in spreadsheets.

learn more… | top users | synonyms

61
votes
6answers
109k 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
56k 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 ...
7
votes
5answers
27k 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
203 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
2k 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 ...
5
votes
1answer
113 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
217 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
693 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
92 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
830 views

Conditional Formatting: Minimum Value in Row

The goal Apply conditional formatting to the minimum value in a row of a Google Spreadsheet. What I've tried Under Conditional Formatting, using "Custom Formula Is" =MIN(D3:L3). The result ...
4
votes
1answer
28 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
671 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: ...
4
votes
1answer
57 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
569 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?
4
votes
1answer
31 views

How can we retain authorship when we copy/paste a comment?

We have comments in a Google Spreadsheet that were not originally written by us. The cells the comments are written in already have "Conditional format rules" applied to them (e.g., cell H2). We ...
3
votes
2answers
12k 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
845 views

Conditional format rule “Text Contains” with a “+” misinterpreted as a formula

I'm trying to conditioan format a bunch of cells that are strings by having cells with a - red and cells with a + green. However when just putting a + o - in the conditional formatting rules it says ...
3
votes
2answers
1k views

Conditional Formatting - Range error

I am using the new Google Spreadsheet application. Here is my data... Cell B3 = 60 Cell B5 = 47 Cell B6 = 52 Cell B7 = 75.1 Cell B8 = 68.4 Cell B9 = 56 I setup this conditional formatting rule... ...
3
votes
2answers
32 views

Conditional format for column headers where all cells are numbers

I'm trying to color the header if all of the cells in that column are numbers. Blank or non-numbers should break this. I have: custom formula: =ISNUMBER(A3:A) But it styles the cells instead. Not ...
3
votes
1answer
37 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 ...
3
votes
1answer
44 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
65 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
97 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
298 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
43 views

Import only those cells from a spreedsheet that have certain conditional formatting

I have created a Google form which links to a spreadsheet. There is an option which asks for the person's name. For the answers in the sheet, I've created a conditional formatting which highlights the ...
3
votes
1answer
46 views

Alternate Background Colours, Conditional Formatting Based on Date?

I have a date column, A7:A, I wanted rows to be coloured every other week, A7:A may have duplicate dates but I want all the dates that lie on the same week to be one colour and alternate so it's ...
3
votes
1answer
115 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

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())) ...
3
votes
1answer
4k 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 = ss.getSheetByName('...
3
votes
1answer
26 views

Conditional formatting based on data “age”

I have a sheet of figures, numbers as values. I was wondering if there was a way to have a cell automatically change colors based upon either: Change colors based upon how long it has been since the ...
3
votes
1answer
96 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
229 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 =or(D4="a",D4=...
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
0answers
27 views

Is it possible to set up conditional formatted using Excel online?

As per the title really, is it possible to set up conditional formatted using Excel online? The cell's context menu doesn't have the option, and I can't find it in the ribbon either. Am I missing ...
3
votes
2answers
70 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
10k 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
6k 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
56 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
420 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
265 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
41 views

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 ...
2
votes
1answer
150 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
34 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
59 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
254 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
1answer
539 views

Conditional formatting across sheets

I have two sheets, SheetA for values, SheetB for calculations. I want a cell in SheetA to be colored, if a value in SheetB satisfies a condition. Normally, referencing a field from another sheet is ...
2
votes
2answers
869 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
2answers
34 views

Create a grade sheet where it will flag number grades in a certain range by turning that row another color

I'm trying to create a grade sheet where it will flag number grades in a certain range by turning that row another color. The problem I'm having has to do with grades of Zero. I have this formula ...
2
votes
1answer
52 views

Google Spreadsheet Conditional Formatting depending on up to 7 Fields

I'm trying to color a column depending on the row. My table looks like this: the only thing i managed to do was =NOT(ISBLANK(C101:C104)) and set it to orange using the conditional formatting. I ...