Questions tagged [conditional-formatting]
For questions about conditional formatting in spreadsheets.
57
questions
74
votes
8
answers
141k
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
...
- 2,205
7
votes
3
answers
16k
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....
- 173
13
votes
3
answers
18k
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 ...
- 428
4
votes
2
answers
12k
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 ...
- 87
2
votes
1
answer
2k
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 ...
- 123
17
votes
6
answers
92k
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 ...
- 323
12
votes
3
answers
28k
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.
TZPike05
1
vote
1
answer
672
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 ...
- 81
18
votes
3
answers
29k
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.
...
- 183
15
votes
2
answers
51k
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 ...
- 1,209
7
votes
1
answer
6k
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,052
5
votes
1
answer
6k
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 ...
- 153
5
votes
1
answer
5k
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 ...
- 163
5
votes
1
answer
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 ...
- 648
4
votes
1
answer
2k
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 ...
- 263
4
votes
1
answer
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: ...
- 41
2
votes
1
answer
437
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 ...
- 271
2
votes
1
answer
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 ...
- 21
1
vote
1
answer
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 ...
- 11
1
vote
2
answers
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 ...
- 121
0
votes
1
answer
6k
views
Google Sheets Conditional Formatting for expiration dates
I need help!
I need to create conditional formatting for expiration dates:
Cells should stay green
Cells should turn yellow when 1 month of exp. date
Cells should turn red once expired
- 11
27
votes
3
answers
49k
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 ...
- 371
11
votes
4
answers
63k
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?
- 111
10
votes
2
answers
10k
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
...
- 109
8
votes
3
answers
69k
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 (...
Chani
8
votes
3
answers
6k
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.
...
- 439
6
votes
2
answers
8k
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)...
- 165
3
votes
2
answers
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 ...
- 31
3
votes
2
answers
7k
views
Conditional number format (whole number, percentage) based on the value in another cell
I have a custom function provides results based on user input - once of these variables accepts either "Numerical" or "Percentage", which provides the data as standard number, or a percentage of the ...
- 31
3
votes
1
answer
240
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 ...
- 12.6k
3
votes
2
answers
63
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.
3
votes
1
answer
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")
...
- 279
2
votes
2
answers
7k
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
1
answer
315
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 | ...
- 567
2
votes
1
answer
4k
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 ...
- 567
2
votes
1
answer
18k
views
Copying Cell Color from Conditional Formatting
I have a spreadsheet that currently conditionally formats some cells, changing the background color. I am trying to copy/paste these cells and preserve the color of the background without having to ...
- 21
2
votes
2
answers
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
1
answer
970
views
Utilize SI units in Google Sheets
Seven years ago this was asked, are there any new developments with regard to formatting with SI units?
Normally, I will adjust the format to be all milli or micro simply by division, however my ...
- 203
1
vote
1
answer
45
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?
- 65
1
vote
1
answer
138
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/...
- 461
1
vote
2
answers
2k
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 ...
- 11
1
vote
1
answer
4k
views
Adding date picker to Google Sheets cells while data validation is customized
I have a column with customized data validation (custom formula) that accepts only dates, provided that a few other conditions are met.
How can I add a date picker to the cells of this column? ...
- 123
1
vote
1
answer
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 ...
- 11
1
vote
1
answer
175
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 ...
- 416
1
vote
1
answer
20
views
Highlight specific word "nottingham" in a cell range - Need formula
I need help highlighting a specific word from G7 to G31. The conditional formatting needs to create a yellow highlight when the First word in the subdivision of rows 7-31 matches the first word of the ...
- 15
1
vote
1
answer
5k
views
Format cells if Date within last 30 days?
Currently hardcoding the date to apply conditional formatting. Is there a way to do this based on a variable range of the last 30 days?
- 5,468
1
vote
1
answer
783
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 ...
- 11
1
vote
2
answers
320
views
Transferring an Excel document to G Sheets, how do I fix the slightly broken auto-borders implementation?
I'd like to transfer this Excel spreadsheet to Google Sheets, maintaining the automatic background formatting functionality, particularly in the case of the borders.
Here is the same spreadsheet ...
- 87
1
vote
1
answer
2k
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?
- 11
1
vote
1
answer
121
views
How do I conduct conditional data validation multiple times so that each input prompts a different conditional validation outcome
I am a member of a project that does electricity monitoring, and I am trying to organize our contact sheet to indicate the status of our electricity monitoring across locations. I want to make the ...
- 11