Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [conditional-formatting]

For questions about conditional formatting in spreadsheets.

68
votes
9answers
130k views

How can I set conditional formatting on a particular cell that depends on another cell's value?

In my example I would like to conditionally format column B cells. Those marked with x should be formatted according to value in column A (in the example the value is 1): A | B 1 | x 2 | 3 | 1 | x ...
17
votes
4answers
27k views

Conditionally format by day of the week

How can I conditionally format cells in Google spreadsheets containing dates to have a different background according to the day of the week, e.g highlight the Saturdays and Sundays in a column ...
15
votes
3answers
14k 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 ...
14
votes
2answers
38k 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 ...
12
votes
5answers
58k 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 ...
10
votes
6answers
48k 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 ...
10
votes
2answers
4k views

Script to change individual text color within a cell

I have a Google spreadsheet with hundreds of employee names for a schedule. Each cell has multiple names within that cell that look like this: First Last First Last First Last First Last First Last ...
8
votes
1answer
4k 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 ...
8
votes
1answer
4k 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 ...
7
votes
3answers
10k views

Colorize a cell in Google Sheets based on cell data

I'd like to colorize a cell based on its contents. 0 = red, 100 = green, and linearly interpolated between. function LinInt(x){ var ss = SpreadsheetApp.getActiveSheet(); var cell = ss....
7
votes
4answers
16k views

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

I have a Google Sheets. I want to set it up so that the first column is checkboxes, and if the checkbox is selected, the entire row gets a strike-through. How can I accomplish this? I followed this ...
7
votes
2answers
11k views

Automatically add borders around nonempty cells

I'm looking to have cell borders automatically applied when a cell is not empty. I thought I could use conditional formatting for this, but it doesn't seem to be an option.
6
votes
1answer
14k views

How do I make a cell highlighted if the date in the cell is today? [duplicate]

Possible Duplicate: Advanced date logic in Google Spreadsheets I have a spreadsheet with many consecutive days. So Cell1 = 7/30/2012, Cell2 = 7/31/2012 etc. I want Cell1 to be highlighted if ...
6
votes
3answers
50k 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 (...
6
votes
4answers
22k views

Conditional formatting based on number of characters

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

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

I would like to get a cell in a particular row above the current cell. This is to be used in conditional formatting, so I can't specify a particular cell, e.g. A$4—the reference would be the same for ...
6
votes
1answer
60 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 =...
5
votes
5answers
31k 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 ...
5
votes
1answer
37k 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 ...
5
votes
1answer
4k views

Conditionally formatting the minimum value in a row

My goal is to apply conditional formatting to the minimum value in a row of a Google Spreadsheets. Under Conditional Formatting I've tried using "Custom Formula Is" =MIN(D3:L3). The result was ...
5
votes
1answer
1k views

Using setBackgroundRGB() over setBackgroundColors()

I have a GSheet that is using IMPORTRANGE() to import data from some other sheet. On this destination sheet I want to color each row based on the value of column A. Because the source dataset may ...
5
votes
1answer
312 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
968 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
2answers
3k views

How to nest IMPORTRANGE into the Query function in 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 ...
5
votes
2answers
839 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 Example: if has values "Guardian"(C2:C100) and "Dragoness"(I2:I100) in its ...
4
votes
2answers
2k views

Highlight minimum value in each row, ignoring zeros and skipping columns

I'm trying to highlight the smallest value in each row using conditional formatting (CF), while ignoring zeros and blanks. Also, I don't want the ranges to be continuous (i.e. skipping certain columns)...
4
votes
1answer
257 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
727 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 ...
4
votes
1answer
1k 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 ...
4
votes
2answers
1k 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. ...
4
votes
1answer
5k views

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('...
4
votes
2answers
3k views

Google Spreadsheet: Use cell referencing in “Change Color Based on Rules”

In "Change Color Based on Rules", one can specify the text color to be any color as long as certain rules are followed: My question is, instead of hard-coding the values, can I use cell reference? In ...
4
votes
3answers
9k views

Is it possible to set up conditional formats using Excel Online?

Is it possible to set up conditional formats using Excel Online? The cell's context menu doesn't have the option, and I can't find it in the ribbon either.
4
votes
2answers
376 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 ...
4
votes
1answer
5k views

How do I check if a cell is blank for Conditional formatting?

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())) ...
4
votes
1answer
2k 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
63 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 ...
4
votes
1answer
209 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
709 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
12k views

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

I want the cell to be formatted if the cell contains any of the following words: blue, orange, red, green, and white. I also want to not make 5 different commands for each (this is because I have a ...
3
votes
2answers
980 views

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

I am making a shift calendar. I have it so my calendar dates are displayed with the day names and they are a formula so if I enter one date, the rest of the calendar changes to follow suit. I want ...
3
votes
3answers
12k views

Conditional Formatting in Google Spreadsheet

I have a list of addresses in column C for work orders, I'd like them to be highlighted if there are duplicates so we don't forget a work order on each visit. These work orders/results are dynamic and ...
3
votes
2answers
2k 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
1answer
603 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 ...
3
votes
1answer
1k 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
5k views

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
68 views

Highlight cells whose content cannot be parsed as number

How can I highlight cells whose content is not parsable as number?
3
votes
1answer
1k views

Applying Conditional Formatting Color Scale to non-numerical cells

My column A is numerical, and I can apply conditional formatting color scales. However, I also want column B to be coloured according to the scale. The scale formatting does not accept formulas and ...
3
votes
2answers
425 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
275 views

Import only those cells from a spreadsheet 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 conditional formatting to highlight the ...