Questions tagged [conditional-formatting]

For questions about conditional formatting in spreadsheets.

Filter by
Sorted by
Tagged with
3
votes
2answers
2k views

Set conditional formatting on a column that depends on another cell's text (not value)

I am making a shift calendar. I have it so my calendar dates are displayed with the day names and they are a formula so if I enter one date, the rest of the calendar changes to follow suit. I want ...
3
votes
2answers
19k views

Conditional Formatting, Negative Numbers

What I want from 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 -$A$...
3
votes
2answers
329 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
1answer
963 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 ...
3
votes
3answers
12k views

Conditional Formatting in Google Spreadsheet

I have a list of addresses in column C for work orders, I'd like them to be highlighted if there are duplicates so we don't forget a work order on each visit. These work orders/results are dynamic and ...
3
votes
2answers
2k views

Conditional Formatting - Range error

I am using the new Google Spreadsheet application. Here is my data... Cell B3 = 60 Cell B5 = 47 Cell B6 = 52 Cell B7 = 75.1 Cell B8 = 68.4 Cell B9 = 56 I setup this conditional formatting rule... ...
3
votes
3answers
10k views

Identifying a merged cell in Google Sheets without using a custom function

I am using Google Sheets and I am trying to find a way to identify merged cells without using a custom function. For example, I need to determine (using only the built-in functions) whether any ...
3
votes
1answer
4k 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 ...
3
votes
1answer
2k 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 ...
3
votes
1answer
69 views

Highlight cells whose content cannot be parsed as number

How can I highlight cells whose content is not parsable as number?
3
votes
1answer
544 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 ...
3
votes
1answer
751 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 ...
3
votes
1answer
867 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 ...
3
votes
1answer
8k views

Is there a way to conditionally format border styles in Google Sheets? [duplicate]

The title says it all. I have some conditional formatting rules, it seems I'm limited to some basic formatting options; Is there any workaround to apply further formatting options (such as border)?
3
votes
1answer
755 views

How to highlight entire row (conditional formatting) using COUNTIFS

For the range C2:Z100 we apply the following formula to find duplicate students' enrollments using conditional formatting: =COUNTIFS($C:$C,C2 , $D:$D,D2)>1 (where column C is the last name and D ...
3
votes
1answer
98 views

2-dimensional comparison for conditional formatting in Google Spreadsheets

So I have this spreadsheed with two areas (D7:AD16 and D18:AD27). Now I want to format the colors in D18:AD27 such that yellow color is added to every cell that is empty in D18:AD27 but not in D7:AD16....
3
votes
1answer
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 ...
3
votes
1answer
129 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
1answer
231 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 ...
3
votes
2answers
4k views

Conditional formatting based on cell values in same column

In Google Spreadsheet, how do I change a cell's background color to red if the date in the cell matches the date in another cell in the same column? Column E in my spreadsheet is a list of due dates ...
3
votes
2answers
57 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
1answer
3k 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)...
3
votes
1answer
12k views

Blinking cell in Google Sheets depending on cell content

I am trying to get a cell to blink (alternate colours white-red) when it contains the word "Red". I currently have the following code that I have found, but don't know how to modify it to particularly ...
3
votes
1answer
2k 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 ...
3
votes
1answer
169 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 ...
3
votes
1answer
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
1answer
793 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: ...
3
votes
1answer
3k 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=...
3
votes
1answer
1k views

Is it possible to add conditional formatting when number changes?

This is probably not possible but... I'm looking to update cells in a Google spreadsheet using the conditional formatting tool.(Changes to SERPs rankings) So when the number in a cell is changed, ...
3
votes
2answers
484 views

Conditional Formatting in Google Sheets

The shorter question to spare you from reading more than you need to I have a few different formulas for use in conditional formatting with custom formula in Google sheets. They are all roughly 1100 ...
3
votes
3answers
262 views

Conditional formatting for columns, date & time specific?

I have uploaded 4.000+ Venmo donation data sets into Google Sheets for sorting, but the date and time is preset and looks like this: 2018-11-01T20:16:52 I have tried multiple approaches to sorting ...
2
votes
2answers
45k views

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 ...
2
votes
2answers
3k 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
1answer
278 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
2answers
11k views

Conditional formatting using other cells in Google Docs

I could use some help with conditional formatting. I would like to format the first 6 cells in a row in a Google Docs file based on the following condition: If column A is not empty and column C is, ...
2
votes
1answer
904 views

Conditional Format if cell contains partial match for text

I want to conditional format the columns B to G, if one of them contains the text in say, cell H5. This is the formula I use for that, =$H$5=B1 at the moment. What I want is for the conditional format ...
2
votes
1answer
40 views

What formula could I use for conditional formatting to highlight cells in Column B if the text in Column A has a link in it?

I want to easily see what text in Column A has a link in it by looking at Column B.
2
votes
1answer
4k views

Cell with multiple color formatting results only to first color being applied

If a Google Sheet cell has 2 Conditional formattings, one which changes only font color, one which changes only the background, only the first conditional formatting affects the cell. The reason ...
2
votes
1answer
2k views

Google Sheet formula on finding smallest value larger than 0 does not work inside conditional formatting

I wanted to find and highlight the smallest value larger than 0, but the formula I am using does not work in the conditional formatting. =MIN(FILTER(L4:L100, (L4:L100)>0)) Namely, if I use it ...
2
votes
1answer
345 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 ...
2
votes
1answer
7k 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 ...
2
votes
1answer
78 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 ...
2
votes
2answers
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 ...
2
votes
1answer
175 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 ...
2
votes
1answer
131 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: ...
2
votes
1answer
3k 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
1answer
427 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 ...
2
votes
1answer
1k 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 ...
2
votes
2answers
1k views

Date Formatting in Google Spreadsheet

I use a spreadsheet in my medical office for referrals. It includes places to fill in these three areas: date faxed, date of the appointment, date the appointment office notes were received. The ...
2
votes
1answer
1k views

Apply conditional formatting to a blank cell if another cell in the row is not blank

I am trying to create an equation for conditional formatting that would highlight a cell that is blank if another cell in the same row is not blank (i.e. that row is being "used"). In the picture ...

1
2
3 4 5
8