People who code: we want your input. Take the Survey

Questions tagged [conditional-formatting]

For questions about conditional formatting in spreadsheets.

Filter by
Sorted by
Tagged with
74
votes
9answers
138k 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 ...
7
votes
3answers
14k 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....
14
votes
6answers
79k views

Advanced date logic in Google Spreadsheets

I have the following 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 ...
12
votes
2answers
10k 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
6k views

Conditionally format row borders

I'd like to have a medium black top border across each row whose A column cell contains text in a Google Spreadsheet. I'd also like to have the same top border across each row whose whole row is ...
1
vote
1answer
577 views

Highlight Rows with Unique Values As Compared to Rows with Same ID# on a Different Sheet

I have two sheets: The first is the Master Sheet of responses to a survey. The second will be an Update Sheet auto-generated from the new responses. New responses can be either entirely new rows ...
15
votes
2answers
49k 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 ...
7
votes
1answer
4k 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
6k views

How to apply a relative reference in conditional formatting formula?

I have a whole bunch of cells (28 so far) that need conditional formatting. Here is what the data looks like: I want the colours in the top "Last Period" metric cells to vary depending on the values ...
2
votes
1answer
1k 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 ...
1
vote
1answer
2k 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
2answers
4k 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 ...
8
votes
3answers
19k 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.
5
votes
1answer
4k 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 ...
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
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: ...
2
votes
1answer
425 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
1k 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 ...
7
votes
2answers
15k views

How do I change a cell to the color of the hexadecimal value of a cell in Google Spreadsheets?

Based on my data, I calculated values of hexadecimal color codes. I want some cell (I don't care whether it's an empty cell at the end of the row or the same cell) to be the color based on the code. ...
9
votes
4answers
43k 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?
10
votes
2answers
9k 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 ...
5
votes
1answer
5k 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 ...
3
votes
1answer
3k 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") ...
3
votes
2answers
57 views

Conditionally formatting a column based on content of nearby column

I am trying to get cells in column C to turn green when the cell in the same row of column B has a value in it. I think there is a bug because the custom formula below is not working.
2
votes
2answers
2k views

Conditional formatting for cells that have date/time within the last 24 hours

I have a Google sheet that has a column for "last updated" and each row represents a separate file. People manually paste in the time and date in that column, like in the screenshot. If the date and ...
2
votes
1answer
2k views

How can I get a cell to change its value based on another cell's color?

I have three colors in column F: green, grey and red. What I want is for column G to display a number (1, 2, 3) depending on the color value of F. Is there a way to do this?
7
votes
3answers
66k 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
3k views

Conditional formatting with color scale for many rows independently once

I create reports with different cohort tables. Each row of a table must have conditional formatting with color scale (depending only on current row values, i.e. regardless of other rows min/max values)...
3
votes
1answer
126 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 ...
2
votes
2answers
6k views

Google sheets conditional formatting relative to another cell in the same column

I have column B with different values on different rows and I want to set a rule that , for example cell b3 turns red if the value is smaller than that of B2 and green if it's bigger. I know I need to ...
2
votes
1answer
278 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
3k 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 ...
1
vote
2answers
2k views

Conditional Formatting based on other column

I have a timesheet log with columns DATE | HOURS | DESCRIPTION OF WORK | PROJECT An employee may enter several rows for the same date to log the different amount of time with respect to the ...
1
vote
2answers
507 views

Date fields that refuse to be parsed/formatted

I have some very mysterious date fields in a huge (600 000 records) CSV file which absolutely refuses to behave. They come up aligned left, and conditional formatting shows that they cannot be seen ...
1
vote
1answer
41 views

How do I use conditional formatting on one column based on certain characters in another column?

When column D has a hyphen (-) in it, I want the fill color in column H to change. What is the custom formula for that?
1
vote
1answer
1k 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?
1
vote
1answer
23 views

How to automatically fill, sort and conditionally format 1st day of the week according to given year and month

How can I fill-in column A, make it start with the day's name according to the year in A2 and month in A1 and conditionally format them? https://docs.google.com/spreadsheets/d/...
1
vote
1answer
4k 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 ...
1
vote
1answer
152 views

Is it possible to use the contents of a cell in a custom conditional formatting rule in Google Sheets?

For example, given the sheet below: I'd like the background colours of columns B-D to change automatically when I update the values in column A Is that possible? I'm looking for a solution that will ...
1
vote
1answer
438 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
6k views

How can I highlight duplicate cells?

I'd like a formula that highlights all duplicate cells, even if they are in different columns. In my example spreadsheet A1 is equal to D15, the formula should highlight both, and so on with all ...
0
votes
0answers
32 views

Conditional formatting with multiple conditions (lost between IF or AND, and not empty cells !)

I am trying to combine formulas here, to put color on specific cells (out of a specific range) when they are not empty, and when a checkbox in another cell is checked. Here is what I tried : =AND($D4=...
0
votes
1answer
561 views

How do I make the color of a cell change if the value has been modified in Google Sheet?

Basically, if the person enters "12" in his/her column, how can I make the cell change to a different color when the same person changes the value again? This would let other people know if there ...
0
votes
0answers
23 views

Google Sheets formatting

I have a workbook in Google Sheets and it is for a schedule for my company so I have a few sheets in my workbook that have the days of certain weeks and then one more tab that has inventory ...
0
votes
1answer
83 views

Conditional formatting on whether something is a formula in Google Sheets? [duplicate]

Is there a way, in Google Sheets, to have conditional formatting of some sort, to change the background color of a cell based on whether the cell contains a formula or a plain value?