Questions tagged [conditional-formatting]

For questions about conditional formatting in spreadsheets.

Filter by
Sorted by
Tagged with
1
vote
1answer
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") ...
1
vote
1answer
22 views

Using a custom formula on a sheet that is constantly expanding

A custom formula for 5 possible colors, by day is used to highlight entries by DayOfWeek Is it possible to use a format like A2:A instead of manually editing it as the sheet grows bigger? Formula ...
1
vote
1answer
24 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
1answer
67 views

How to combine these formulas in one conditional formatting rule

I am checking if column S is empty & Column Q="Consultation" then I need to set a rule in a cell to make conditional formatting in cell A1. So these are my steps: Step 1: in X column: =...
1
vote
1answer
47 views

How to conditionally format italics without changing other parameters?

I am using conditional formatting to color lines based on a priority value in column A, this works fine. I would like to add another rule that would italicize the lines that match a specific parameter,...
1
vote
1answer
202 views

Conditional formatting to highlight values that are not in valid date format

I have a Google Sheets spreadsheet with a date column. A contributor to this spreadsheet keeps entering dates in invalid format. I would like to conditionally format the cells in this column to have a ...
1
vote
1answer
30 views

How do I conditional format a table in Google Sheets based on two other cell values?

In the scenario below, the table contains random integer values. I want to use conditional formatting in order to highlight the values in the table between the specified min and max values. While I ...
1
vote
1answer
20 views

Color a cell if the text inside it is not the first time it has appeared on the sheet

In cell A1 I have the text "hello". In cell A2 the text "hello" appears again. In cell A3 I have the text "goodbye". In cells A4:A10 the text "goodbye" appears again. Is there are a way (with ...
1
vote
1answer
2k views

Google Sheets Conditional Formatting based on Text and another cell

I'd like to color code a sheet based on two criteria: If the cell in the range contains specific text "A1" and if another cell, $X$7, contains an X or is not blank. I can't seem to get AND or IF or ...
1
vote
1answer
18 views

How to check and replace cells

I need help trying to figure this out. I am extremely new to formulas and very confused. Here is my thought process: IF B1 contains a comma AND C1 is exactly "none" THEN I want C1 to be replaced ...
1
vote
1answer
50 views

Fill in red if cell is not today

I am new to Google Sheets, excuse me for a (most likely) dumb question. I have a column with dates looking like 03.03.2020. What formula do I use to fill the cells with values not equal to today date ...
1
vote
1answer
16 views

Copy conditional formula to separate columns GoogleSheets

I've got a simple conditional formula to make cell green if there is more than X not empty fields. It works fine but I need a way to copy that formula to other columns. When I do it the simply click ...
1
vote
1answer
12 views

Conditionally format a range based on the value of a single cell outside of it [duplicate]

I am using conditional formatting with a custom formula. I want to highlight rows in the range A2:B11 if the values in $B2 (column B starting at row 2) is greater than the value in the cell D5. I can ...
1
vote
1answer
35 views

Using a custom formula as part of a conditional formatting rule

Ive got a spreadsheet with 3 columns A, B, C, i want to add the 3 columns together in column E, if there is a value in column C, i want the summed number to be shown in red. If there is a value in ...
1
vote
1answer
39 views

Google Sheets conditional formatting with AND for value range

I'm trying to set a colour range of conditional formatting based on another cells value. So if A1 is larger than A2 it will be Blue, if A1 is between 0 and A2 it will be Yellow and if A1 is less than ...
1
vote
1answer
29 views

How do I make the “Text contains” evaluate any word written in the cell?

I just want the cell in Google Sheets to change its fill color when any text is inputted in the cell? But it has to be text excluding numbers. Please help!
1
vote
1answer
48 views

Conditional Formatting Duplicate Invoices in Google Sheets

My workplace uses Google Sheets to track invoices across all locations. The two most important columns are next to each other and titled Invoice number and Vendor. I wanted to highlight the Invoice ...
1
vote
1answer
33 views

Conditionally format rows with partial matches

I have a Google Sheets table that looks like this: (col a) (col b) (col c) (col d) Company Cohort Session Count Group -------------------------------------------- Acme ...
1
vote
1answer
19 views

How to color cells in column A if cells in other columns relating to those column A cells are filled in?

Column A contains student names. The other columns show work that is missing or incomplete. I'd like to be able to have cells in column A colored in if work from various classes is missing, without ...
1
vote
1answer
70 views

Google Spreadsheet: Conditional format used together with a range doesn't seem to format cells correctly

I'm trying to create a small calendar, which is built like this: A1: = DATE(2018, 1, 1); B4: = A1 C4: = A1 + 1 D4: = A1 + 2 … This works fine and the columns B2 … D4 looks ok: Mon, 01/01 | Tue, 02/...
1
vote
2answers
14k views

Google sheets function IF, AND conditional formatting

EDIT 2: link to test sheet: https://docs.google.com/spreadsheets/d/1IWQWDK9piw_xFVm_48XZ8YAS_LQqXi6sd2GITLIfoAQ/edit#gid=903221057 EDIT 1: added a column, so now J=K, K=L, L=M | updated formulas to ...
1
vote
1answer
2k views

Advanced cell highlighting based on date in different cell and empty

I have a column set with dates in the future for different campaign events and another column with empty cells, but those cells will need information (text string) added to them within two weeks of ...
1
vote
1answer
2k views

Conditional formatting with multiple criteria and different cells

I have cell J1, which contains a number, determined by a formula. I have cell J2, which contains a dropdown obtained by data validation: CES,CES1,CES2,CES3. If J2 = CES, and J1 > 20, I want J1 to be ...
1
vote
1answer
40 views

Conditional formatting cells close to minimum of the column

Currently I have a custom formula in Google Spreadsheet as following: =ArrayFormula(E1=MIN(if(ISERROR(FIND("NO",E$1)),E$2:E$100))) Which highlights the cells with the minimum value in columns (...
1
vote
1answer
13k views

Copying Cell Color from Conditional Formatting in Google Sheets

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 ...
1
vote
3answers
225 views

Conditional formatting to highlight Quiz Answers

I checked multiple similar threads but could not find exactly what I need (as far as I can tell). We prepare quiz shows using google spreadsheets, and we do have the occasional human error. This is ...
1
vote
1answer
1k views

Conditional Formatting in Google Sheets based on multiple comparisons

I am trying to change the color of a cell using conditional formatting in Google Sheets using the following formula: =IF(B4<=B1, (IF(C4<=C1, (IF(D4<=D1, (IF(E4<=E1, ))) )))) Pseudo code ...
1
vote
1answer
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
1answer
37 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 ...
1
vote
2answers
4k views

Apply conditional formatting to the top four values in a range

I have a range of cells B11:B18 and I want to conditionally format them to highlight ONLY the top four values. I have tried different formulas I found online but they rank ALL eight cells!
1
vote
1answer
96 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, ...
1
vote
2answers
4k 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" ...
1
vote
2answers
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&...
1
vote
1answer
32 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.
1
vote
2answers
1k 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 ...
1
vote
1answer
408 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: ...
1
vote
1answer
523 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 ...
1
vote
1answer
2k views

Cell formatting in Google Spreadsheets does not get preserved

My Google Spreadsheet does not preserve some formatting. Every time I set, for example font size to 12, for the whole sheet, newly copied data uses a smaller font size. This only started being an ...
1
vote
1answer
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
1answer
137 views

Change background color of a cell when another cell is filled in

Let's say I have a Google Sheet where A1 needs to be filled green when B1 till B7 has been filled in with no matter what; and A1 needs to be red when 1 or more cells from B1 till B7 are not filled in ...
1
vote
2answers
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
1answer
507 views

Google spreadsheets Conditional Formating Formula

I am having trouble highlighting all cells in a column that do not contain the same text that is found in the first cell at the top of the column. I would like to accomplish this with a conditional ...
1
vote
1answer
516 views

Conditional formatting for dates in Google sheets

I work at a language school and I have a column giving the students expected arrival date. I want to set conditional formatting to: Highlight in red any date that is older than 1 week Highlight in ...
1
vote
1answer
107 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 ...
1
vote
0answers
22 views

Google Sheets conditional formatting adapt to value

I'm currently working on a Google Sheet which tracks hourly performance, the layout is basic which is hours against units completed. The conditional format I already have in place works for a time ...
1
vote
1answer
33 views

Highlight duplicate based on two columns

I have a spreadsheet of patient data (several thousand records) where I need a way to indicate whether there's a duplicate patient. We know there's a duplicate if the last name and the date of birth ...
1
vote
0answers
11 views

Is it possible to format row fill colour by a cell in an expandable way? if new job number, new row colour

I keep a to do list. Each row has a job number. There are several entries for each job number. I'd like to ask google sheets to look at the job number in column. Give the row a colour. Every time a ...
1
vote
1answer
45 views

Isolating conditional formatting to each range in a column

I've set up conditional formatting to highlight the top 3 values (gold, silver, bronze) in a range of rows. However, I want to be able to copy/paste the conditional formatting below the initial rows ...
1
vote
0answers
73 views

Google Sheets conditional formatting multiple columns based on value relative to 1 column

I have a reference column of values (column A & B) and then all subsequent pairs of columns that get added to sheet, I want to highlight their value in relation to those two. So column C values, ...
1
vote
0answers
17 views

Conditional format using the IF function

I'm trying to use a conditional format using the IF function to determine if a value needs a dropdown menu. Example: if A1=book, then provides a list of authors to choose from; the same if A1=car, ...

1 2 3
4
5
9