Questions tagged [conditional-formatting]
For questions about conditional formatting in spreadsheets.
507
questions
10
votes
1
answer
40k
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 ...
- 17.6k
1
vote
1
answer
120
views
How to dash a whole row if a cell is OK?
I have a cell that can be OK, ERROR or WARNING. When it is changed to OK I want to modify the whole row in some way. I was thinking about to dash all cells.
Is it possible? Or is there something I ...
- 17.6k
0
votes
1
answer
2k
views
Conditional color scale not working with functions
I want to apply conditional color scale but it doesn't work. This is the original cell (A12)in a sheet called ProcessedData:
= votes:[2074
Then, I use in B4:
= REGEXEXTRACT(ProcessedData!A12&"...
CommunityBot
- 1
2
votes
1
answer
616
views
Google Sheets Alternate Row Color by Unique Sorted Names
Background
I have a sheet that is sorted by Country Names, all unique Country Names are associated with more than one City.
Question
How do I alternate the color of every other Country Name, such ...
- 17.6k
13
votes
2
answers
88k
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 ...
CommunityBot
- 1
1
vote
1
answer
46
views
Problem with conditional format of a calendar like table
I created a minimal sheet to demonstrate the problem: https://docs.google.com/spreadsheets/d/1cc8UdkOvIPf_wDGCHaDN0BqR3sX3Teb1qKlvdM1cL3g/edit?usp=sharing
Cell A1 specifies a startDate
Cell A2 ...
- 17.6k
0
votes
1
answer
4k
views
Evaluating each cell in a row based on the cell adjacent to it [duplicate]
I'm trying to set up a spreadsheet the will color the background of a cell based on the text within another cell. This would have to be set up in a row so that if (for example) B2: contained "done", ...
- 17.6k
-3
votes
1
answer
5k
views
Google sheets with conditional formatting not working
Update:
I want one specific cell to become green (filled with green color) if a range of other cells (which contain only dates) count up to 7 or more filled cells AND contain dates within a specific ...
- 3,352
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 ...
- 17.6k
2
votes
1
answer
2k
views
Color Scale via Conditional Formatting with Non-Numerical Values
I am trying to find a way to use conditional formatting to automatically assign the same colors to cells containing the same order numbers.
I cannot predict the order numbers so I'm trying to use ...
- 3,352
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 ...
- 17.6k
4
votes
2
answers
427
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 ...
- 17.6k
5
votes
1
answer
8k
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())) ...
- 17.6k
-1
votes
1
answer
454
views
Conditional formatting
I'd like the date / time that is in the past to turn red.
The table has dates and time in different formats.
Can I use one rule to do everything?
If not is it possible to do so?
I've tried =today()...
- 17.6k
2
votes
1
answer
2k
views
Copy-pasting cells with conditional formatting
I'm trying to use conditional formatting in the goals area. Currently the calorie goal (J10) turns green as long as I stay under its number, and it's compared to the total calories for the day (J3). ...
- 17.6k
-5
votes
1
answer
649
views
How to conditional format a row based on date range
I'd like to set up a conditional format to highlight a row where I'm currently at in my trip. Ie a date that is greater than or equal to today but not for following sections of my trip.
With this ...
- 17.6k
3
votes
1
answer
953
views
Conditional formatting "is equal to" 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 ...
- 17.6k
4
votes
1
answer
7k
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('...
- 17.6k
3
votes
1
answer
900
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 ...
- 17.6k
5
votes
2
answers
5k
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 ...
- 17.6k
3
votes
1
answer
70
views
Highlight cells whose content cannot be parsed as number
How can I highlight cells whose content is not parsable as number?
- 17.6k
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?
- 17.6k
3
votes
1
answer
1k
views
Compare value in all cells in a column in Google Sheets
I'm trying to use conditional formatting to make all the values in one column that are the same turn red. I used arrayformula, but I can only get it to do that for the first value in the column.
I ...
CommunityBot
- 1
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 ...
0
votes
1
answer
47
views
Highlight the admin responses rows
I have a comment form and sheet on my site:
• Column A: Timestamp
• Column B: Name
• Column C: Comment
I'd like to highlight every row that includes the admin's answer. In other words, if a cell in ...
CommunityBot
- 1
-1
votes
1
answer
440
views
Google Spreadsheets formula should I use for highlight more than an 1hour passed between cells?
I need help with a Google spreadsheet formula. This is what I'm trying to do:
Highlight the cells in “Conditional Formatting” where the time spent between 2 cells is more than 60min. In this case ...
- 17.6k
0
votes
1
answer
2k
views
Automatically format certain words in cells
I have cells containing some text:
This is a text in a cell. This cell contains two sentences.
This is also a cell. I have two cells in total.
I want to automatically make all the occurrences of ...
CommunityBot
- 1
0
votes
1
answer
145
views
Conditional Formatting on Google Sheets (based on multiple inputs)
I would like to highlight a cell (CC3) yellow based on if the numbers in BK3 and BN3 are greater than or equal to 80.
I can figure out how to make it work based on one argument, but not the second. ...
CommunityBot
- 1
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 (...
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 ...
CommunityBot
- 1
0
votes
1
answer
30
views
Choose "Type" of Input Number When Using Number Formatting
So I have the a formula that outputs a duration as seconds with three digits of milliseconds.
For example if the formula outputs 67.769, It should be formatted as 01:07.769
I tried using mm":"ss"."...
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 ...
- 17.2k
2
votes
1
answer
91
views
Conditional formatting one cell when all cells in a range have a particular value
I have a column A. I want to apply a custom background color to the cell A1 only when each cell in the range C1:T1 contains "Y" (literal letter uppercase Y).
This formatting will be repeated for each ...
1
vote
1
answer
2k
views
In Google Sheets how can I conditionally color a cell value if less than another cells value?
The Sheet I am working with has a multiple columns, each with a range of integer values. I would like to check to see if that integer value is less than another cells integer value, and if so ...
CommunityBot
- 1
1
vote
2
answers
4k
views
Copy conditional formatting with custom format
I want to copy conditional formatting that compares this cell with the cell adjacent to it. In a nutshell it's supposed to look like this:
If A1<B1, make it green, if A1>B1, make it red
If A2&...
CommunityBot
- 1
4
votes
1
answer
3k
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 ...
CommunityBot
- 1
4
votes
2
answers
5k
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)...
- 279
1
vote
1
answer
1k
views
How to specify a character range on Google Sheets conditional formatting
I'm trying to color cells that do not start with a letter; but I'm not sure how to specify a character range in this format.
Cells contents such as:
!this one <--- formatted
this is fine
@...
1
vote
1
answer
139
views
Google Sheets: Conditional Coloring Based on Slope Increase (Acceleration)
In Google Sheets, how do you conditionally color cells based on whether the cell's value is accelerating or decelerating?
For example, in the image below, I created four columns: the time, distance, ...
- 52.7k
4
votes
1
answer
2k
views
Using MAX in conditional formatting only evaluates numbers after the cell, regardless of range
I would like to highlight the maximum numerical value in a particular column.
Take a look at this sheet: https://docs.google.com/spreadsheets/d/107c-9pET6SA1APwtGIJh1Qxi__SOXAHGtaGVGd5Nw6g/edit?usp=...
- 17.6k
0
votes
1
answer
45
views
Advanced matching in duplicates
I need to compare some cells in my table but I need to select more columns so its little bit tricky. Here is my table:
https://docs.google.com/spreadsheets/d/...
0
votes
1
answer
2k
views
Conditional Formatting Custom Formula: If certain cells in a row says "false", then they should be highlighted in red
What I'm trying to do is to highlight rows L to P in red if all cells say "False", and here is the formula I used:
=(sum(arrayformula(n(regexmatch($L2:$P2, "False")))) = 5)
This does not work ...
CommunityBot
- 1
0
votes
3
answers
6k
views
Use conditional formatting to add text
Is it possible to use conditional formatting to edit/add text in a cell? E.g., if it matches the custom formula "=B9>A9", add "is greater than A9" as text at end of whatever is in B9. I didn't find ...
0
votes
1
answer
386
views
Custom formula conditional formatting highlights empty cells in red - how to fix this?
I use the ff. custom formula for conditional formatting in Google sheets:
=sum(arrayformula(n(regexmatch($J2:$W, "Unsure|Yes")))) = 0
However, it also colors empty cells.
I tried to incorporate ...
CommunityBot
- 1
0
votes
1
answer
90
views
How do I use conditional formatting to choose between columns for a datedif function?
I am trying to find the difference between two dates using this function =DATEDIF(G2,K2,"D"). I always want to find the difference between column K and either G or H depending on which one has a date. ...
- 17.6k
-1
votes
1
answer
193
views
Adding 24, 48, 72 hr highlights to cell for tracking purposes [duplicate]
I am trying to add conditional formatting that turns yellow after 48 hours from the date in the cell, turns orange after 72 hours from the date in the cell, and red after 96 hours after the date in ...
- 52.7k
0
votes
1
answer
80
views
Conditional Formatting Referencing another Cell (and using addition)
I am tracking high school credit acquisition. Each trimester a student must earn 2 credits to remain on track. If Cell A1 is "trimester 1 total credits", and cell B1 is "trimester 2 total credits", I ...
CommunityBot
- 1
1
vote
1
answer
433
views
Conditional formatting of duplicate values
In a full sheet range, I need a cell to be highlighted when a duplicate is typed, whether it is a number (phone number in this case) or a text string (name in my case). Both the new cell and the cell ...
- 52.7k
1
vote
1
answer
3k
views
Conditional formatting with if formula
On Google Spreadsheet, I want cell D88 to change background color depending on the value in E88. In my head this should work like this (imagine it's a conditional formatting window):
=if(E88="yes") ...
CommunityBot
- 1
1
vote
1
answer
141
views
Conditional Formatting if all cells fail to meet a certain criteria
This is the spreadsheet I'm working on. I am trying to figure out a formula for conditional formatting so that it only highlights the cells in red if none of the cells from columns N to Y of the ...
CommunityBot
- 1