Questions tagged [conditional-formatting]
For questions about conditional formatting in spreadsheets.
58
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
...
7
votes
3
answers
17k
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....
4
votes
2
answers
13k
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 ...
13
votes
3
answers
19k
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 ...
17
votes
6
answers
93k
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
3
answers
29k
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.
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 ...
1
vote
1
answer
673
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 ...
20
votes
3
answers
31k
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.
...
16
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 ...
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 ...
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 ...
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 ...
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 ...
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: ...
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 ...
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 ...
2
votes
1
answer
440
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 ...
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 ...
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 ...
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
27
votes
3
answers
51k
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 ...
11
votes
4
answers
65k
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
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
...
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.
...
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 (...
6
votes
2
answers
9k
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)...
4
votes
2
answers
3k
views
How to Apply Conditional Formatting Based on Adjacent Cell Value for Entire Column?
I am looking to apply conditional formatting to all cells in a column (excluding the header) where a colour will be applied depending on if its value is greater, equal, or less than the adjacent cell.
...
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 ...
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
250
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
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")
...
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 ...
2
votes
1
answer
19k
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 ...
2
votes
1
answer
1k
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 ...
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
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 ...
2
votes
1
answer
320
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
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 ...
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?
1
vote
1
answer
132
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 ...
1
vote
2
answers
451
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 ...
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 ...
1
vote
1
answer
146
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
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 ...
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?
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?
1
vote
2
answers
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 ...
1
vote
1
answer
831
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
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 ...