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.

0
votes
1answer
362 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 @...
0
votes
2answers
1k views

Conditional Formatting color if today minus cell date is more than 90 days

I want cells in Column M to get a color if date (in that cell) has reached 90 days and if Column A has the value "Onvoltooid" I selected the column range and clicked on the item: Conditional ...
3
votes
1answer
1k views

How can I change the background color of a cell based on the cell contents using a color lookup sheet?

Let's say in Sheet 2 I have a column (A) of words, and a column (B) of hex color codes. In Sheet 1, wherever a word from Sheet 2 appears alone in a cell, that cell should get the background color ...
1
vote
1answer
49 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, ...
2
votes
1answer
463 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=...
0
votes
1answer
36 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
1answer
695 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 ...
1
vote
1answer
1k views

Highlight second highest number in Google Sheets row

I am attempting to use conditional formatting to highlight the second highest number in a row in Google Spreadsheets. I've set it to "format cells if custom formula is", applied to range "CO27:DB27" ...
0
votes
1answer
707 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 ...
0
votes
3answers
2k 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
1answer
243 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 ...
1
vote
2answers
3k 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&...
0
votes
1answer
64 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. ...
-1
votes
1answer
59 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 ...
1
vote
1answer
301 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 ...
1
vote
1answer
82 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 ...
0
votes
1answer
65 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 ...
1
vote
1answer
25 views

Conditional formatting for a single cell based on a row being empty or containing any values

I want A1 to go green if any cells in range B1:J1 contain any values. A1 to remain blank if B1:J1 contain no values. In the sheet all values will be text based but will vary from cell to cell.
3
votes
1answer
106 views

Conditional formatting based on data “age”

I have a sheet of figures, numbers as values. I was wondering if there was a way to have a cell automatically change colors based upon either: Change colors based upon how long it has been since the ...
2
votes
2answers
10k views

Conditional Formatting, Negative Numbers

What I'm doing in Google Sheets I have cell A1 with a value 10. I have cell A2 with value -11 and cell A3 with value 11 . I want to conditionally format cells so that when their value is less than -$...
3
votes
2answers
367 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
227 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 ...
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 ...
2
votes
1answer
1k 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
2answers
100 views

Create a grade sheet where it will flag number grades in a certain range by turning that row another color

I'm trying to create a grade sheet where it will flag number grades in a certain range by turning that row another color. The problem I'm having has to do with grades of Zero. I have this formula ...
0
votes
1answer
3k 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. ...
4
votes
2answers
8k 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.
2
votes
1answer
125 views

Google Spreadsheet Conditional Formatting depending on up to 7 Fields

I'm trying to color a column depending on the row. My table looks like this: the only thing i managed to do was =NOT(ISBLANK(C101:C104)) and set it to orange using the conditional formatting. I ...
1
vote
1answer
45 views

Conditionally format data in spreadsheet column, but only when it changes

Say I have this data in a Google Spreadsheet: cat cat mouse mouse mouse cat dog dog And I only want to apply conditional formatting to the ones in brackets [] (so I can hide them but still calculate ...
3
votes
1answer
134 views

Alternate Background Colours, Conditional Formatting Based on Date?

I have a date column, A7:A, I wanted rows to be coloured every other week, A7:A may have duplicate dates but I want all the dates that lie on the same week to be one colour and alternate so it's ...
1
vote
1answer
93 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 ...
2
votes
1answer
118 views

Conditional Formatting Based on Date Formula

I'm trying to highlight the contents of a Date cell if the following conditions are met: Value of another cell = "No"; and Value of target cell + 7 days > Today The formula that I have right now is: ...
5
votes
2answers
816 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 ...
3
votes
1answer
257 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 ...
2
votes
1answer
579 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 ...
4
votes
1answer
543 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
vote
2answers
734 views

Apply conditional formatting based on the formula that a cell contains

I am having trouble figuring out how to get conditional formatting to work in my Google spreadsheet. I use the second sheet to import prices of projectors, but a few don't update. I want the row of ...
4
votes
1answer
193 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 ...
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 ...
2
votes
1answer
2k 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 ...
13
votes
3answers
13k 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 ...
6
votes
1answer
58 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 =...
2
votes
1answer
332 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 ...
3
votes
1answer
656 views

Conditional formatting a range based on the value of a single cell inside of it

I'm trying to format a range based on the value of a single cell inside. So if I have a range like in this image: I am coloring based on the teams. If I was to rearrange the teams, how can I use ...
1
vote
1answer
299 views

How do I conditionally format a cell based on a date within a string of text?

Suppose today is 13 October 2015. I want to apply conditional formatting rules to four cells in Google Sheets: Cell 1: 10/22 Remove cat from bookcase Cell 2: 10/23 Put books back Cell 3: ...
2
votes
1answer
1k 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") ...
13
votes
2answers
35k 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
vote
1answer
157 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 ...
3
votes
1answer
572 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 ...
2
votes
1answer
182 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 | ...