For questions about conditional formatting in spreadsheets.

learn more… | top users | synonyms

0
votes
1answer
8 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
8 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
26 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 ...
-1
votes
0answers
20 views

Highlight the cells used in a formula in Google Sheets

I want to highlight the cells that're used in the formula of a given cell. Let's say I want to apply conditional formatting on B12 which has value =A12+C3. Now I want A12 and C3 to be highlighted.
3
votes
2answers
32 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 ...
0
votes
0answers
14 views

Conditional format — copy format of another cell (Google Sheets)

I'm using Google Sheets spreadsheet, hope it's ok to ask about it here (in superuser). Chances are the methods to accomplish this might be different from Excel or LibreOffice, if it can be done at all....
3
votes
1answer
43 views

Import only those cells from a spreedsheet 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 a conditional formatting which highlights the ...
4
votes
1answer
31 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 ...
1
vote
1answer
38 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
34 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
39 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. ...
3
votes
0answers
27 views

Is it possible to set up conditional formatted using Excel online?

As per the title really, is it possible to set up conditional formatted using Excel online? The cell's context menu doesn't have the option, and I can't find it in the ribbon either. Am I missing ...
2
votes
1answer
52 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
39 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 ...
3
votes
1answer
46 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
50 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 ...
2
votes
1answer
41 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 ...
3
votes
2answers
70 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 ex. if has values "Guardian"(C2:C100) and "Dragoness"(I2:I100) in its row ...
3
votes
1answer
37 views

Google Sheets Conditional Formating “IsEqualTo” 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
42 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 ...
3
votes
1answer
44 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
125 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
57 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 ...
2
votes
1answer
150 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 ...
6
votes
1answer
203 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 ...
4
votes
1answer
28 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
34 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
65 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 ...
2
votes
1answer
45 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") ...
5
votes
1answer
2k 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
42 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 ...
2
votes
1answer
59 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
56 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 | ...
1
vote
1answer
27 views

Conditional formatting over wide area of cells, referencing other cells

As data validation does not seem to be able to reference cells for numerical conditions (instead only being able to hardcode numbers); I have fallen back on conditional formatting. However, it does ...
1
vote
1answer
395 views

Apply conditional formatting if other cell is not empty

2 Columns. C2 | D2 C3 | D3 C4 | D4 C5 | D5 I want it to apply conditional formatting to D2 if C2 has information in it, and D2 is blank.( will be a date). If C2 is empty, then D2 should have no ...
3
votes
1answer
844 views

Conditional format rule “Text Contains” with a “+” misinterpreted as a formula

I'm trying to conditioan format a bunch of cells that are strings by having cells with a - red and cells with a + green. However when just putting a + o - in the conditional formatting rules it says ...
0
votes
1answer
149 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
1answer
32 views

Highlight a cell if the date in one column is the latest date

I'm trying to figure out the proper conditional formatting formula to hilight a cell based on the date in that cell (and/or an adjascent one) representing the most recent date in the column. Given ...
3
votes
1answer
96 views

See if a cell contains a string not from a list of strings in another column

In Google Sheets I have two columns, A and B, and want to know when a cell in column A contains a word that is not in column B. Column A contains cells with multiple words per cell, comma separated: ...
2
votes
1answer
169 views

Can I use conditional formatting to color a column's cells' text based on the conditionally-formatted color of text in another column's cells?

I have been working on a spreadsheet in Google Sheets that serves as a database of the hundreds of characters who appear in a novel I am reading (to help me keep track of them all). Column A contains ...
1
vote
1answer
36 views

Conditional formatting based on multiple cells

I'm trying to figure out how to write a formula for Google Spreadsheet where: If F3 Contains Data and G3 is more than 0 then cell J3 is Highlighted in Green Can anyone help me out with this?
3
votes
1answer
97 views

Format a cell in bold if its row has other nonempty cells

I'm looking to format a Google Spreadsheet with something beyond the "basic" Conditional Formatting they offer. From what I understand, this must be done using a Google Apps Script. I've found one ...
1
vote
2answers
484 views

Google Sheets - Conditional Formatting with IF and THEN

I would like to create a daily dashboard with cells that show the progress of my work regarding to a date. For example: A1 is a text saying "Open". C1 is a cell that contains a date. This date ...
3
votes
1answer
228 views

Specify a range of text for Google Sheets Conditional Formatting

I would like Google Sheets to look at a column and highlight a cell red if it contains any of the letters A through M. My current solution is using a Custom Formula that looks like this =or(D4="a",D4=...
2
votes
1answer
147 views

Google Sheet conditional formatting, highlight just 1 cell in a range based on another cell [duplicate]

How can I highlight a cell in a range, if its value is equal to a different cell? Example: Random numbers in range A5:A20. I want to highlight a cell in this range if it is equal to B4. [ A ][...
2
votes
1answer
419 views

Google Sheets: Asterisk character (no wildcard) on conditional formatting

I have a sheet with following values on column A: [ A ] [1] Dead line [2] 15 days remaining*** [3] Dead [4] 131 days remaining* [5] 80 days remaining** I would like to use ...
3
votes
1answer
298 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 ...
0
votes
0answers
23 views

Set color if cell value is element of another sheets row [duplicate]

I am trying to figure this out since a while now. I have got it working if everything is in the same sheet! It goes like this: Select row K -> conditional formatting -> Custom formula is =COUNTIF(B:B,...
2
votes
1answer
52 views

Changing colour of row if a certain aspect is met

I have a list of clients that send in certain information to me and I have setup columns to enter a tick using (=char(10004)) on R,S,T,U,V,W,X & Y columns and I need to colour them to go green ...
1
vote
1answer
32 views

Formatting cells based on upcoming date in Google sheets

I am trying to format a cell to be highlighted red if the following conditions are true: the future date associated with that row (which is in a separate cell) is less than 21 days from today, and ...