Stack Exchange Network

Stack Exchange network consists of 174 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.

1
vote
1answer
623 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 ...
0
votes
1answer
339 views

Google Sheets conditional format cell if any of the specified cells are blank

I am trying to format a cell to have white text & white fill if any of several other cells are blank. I excel, I would use =OR(K2="",P2="",Q2="",R2="",S2="",T2="",U2="",V2="",W2="",X2="",Y2="",Z2=...
1
vote
1answer
2k 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?
0
votes
1answer
29 views

Better management of long conditional formula in Google Sheets

I have a long and unwieldy formatting condition, currently it's doing 9 function calls, and I'd like to make it even larger. My trouble is that it's getting increasingly difficult to manage all the ...
1
vote
2answers
2k views

How to use AND in Google Sheets w multiple criteria

What is the proper syntax for using "AND", when creating a custom formula in Google Sheets? (The formula applies to a format I set in the Conditional Formatting interface.) H2:H2000 is greater than ...
0
votes
0answers
18 views

How do I change the cell color in the Google Sheet formula cell?

=IF((ISBLANK(H2)=TRUE)+(ISBLANK(I2)=TRUE)+(ISBLANK(J2)=TRUE)+(ISBLANK(K2)=TRUE),"",(B2+1+L2)) I created a pitch count log. The user enters the information and the cell gives out a date. I need ...
1
vote
2answers
229 views

Conditional formatting depending on the formula that the cell contains

I have a spreadsheet that has 3 rows. Total received, Share A, and Share B. Share A will have a formula that is either =A1*0.8 or =A1*0.9. Is there any way for me to set up a conditional format ...
3
votes
2answers
926 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 ...
1
vote
2answers
837 views

Use named ranges to dynamically add rows in google spreadsheets, while maintaining conditional formatting in newly added rows

I have a table that looks like in Google Spreadsheets: Some numbers in this table are calculated upon a range of cells (for instance, the total in the last row). If I add a new row to this table, I ...
1
vote
4answers
1k views

Apply formatting to the WHOLE row/column if ANY of the cells fulfils condition

Conditional formatting, even when applied to the whole row/column, treats each cell individually and applies formatting only to the cells that meet criteria. Example: An attempt to highlight whole ...
2
votes
1answer
170 views

Use non-linear color scale for conditional formatting in Google Sheets

In Google Sheets, when you use conditional formatting on a column with numeric values, is there a way to place a colored scale that is non-linear, but exponential, for example? I know you could ...
1
vote
1answer
79 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 ...
0
votes
2answers
2k views

Multiple criteria conditional formatting

I would like to do some conditional formatting with multiple points of criteria. I have the first part of the formula done but after that it's beyond me. =B2<=B22 is the first cell next to the ...
-1
votes
1answer
77 views

Apply the conditional formatting formulas across every column of the sheet

I've got a Google Sheet that I'm trying to write some formulas for - it's basically just a bunch of numbers in columns that I'm trying to do some conditional formatting on (per column). I want to be ...
0
votes
1answer
1k views

Using Conditional Formatting to highlight data in new table that matches data in separate table

Sheets 1 thru 6 all have an Array Formula pulling data from other sheets. The Array Formula runs multiple Query formulas that skip over rows with missing cell data and then goes back to those rows and ...
3
votes
1answer
47 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....
2
votes
1answer
32 views

Change text colour in one cell when putting text in another

I have a shopping list in column B and want to be able to use the =char(10004) in column C so then the item in the cell 'B' turns green. Can this be done?
2
votes
1answer
145 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 ...
1
vote
1answer
26 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
1k 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!
2
votes
1answer
2k 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 ...
-3
votes
1answer
3k 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 ...
6
votes
4answers
11k views

How to create a checkbox column, that if selected will create a strike-through across the entire row in Google Sheets?

I have a Google Sheets. I want to set it up so that the first column is checkboxes, and if the checkbox is selected, the entire row gets a strike-through. How can I accomplish this? I followed this ...
0
votes
1answer
2k views

Help with highlighting cells green/red if value in previous cell is more or less using conditional formatting

I am trying to use conditional formatting to highlight a cell either green or red based off a previous value entered in the cell above. I show an example as I am trying to format all my stats to ...
2
votes
1answer
467 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 ...
0
votes
1answer
259 views

Transposing with header row

In Google Sheets I'm trying to transpose a list of items in rows to display as columns. I am looking for a way to transpose the Category field to act as a header row that extends over all the items of ...
-1
votes
1answer
2k views

Conditional formatting with multiple criteria over a range of cells

There is another thread about this but all of the answers there aren't helping. In the range: D12:AY500 - I have a matrix of numbers such that: y=Date, x=Time: .. I would like it so the cells ...
0
votes
1answer
36 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 ...
-1
votes
1answer
195 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 ...
3
votes
2answers
10k 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 ...
1
vote
0answers
43 views

Conditionally Formatting Google Sheets [duplicate]

I'm trying to conditionally format a range of cells to turn colors based upon certain criteria. General notion: If E3 = "unpaid" and G1<=10, then turn A3-G3 red. If E3="unpaid" and G1<=30 and >...
0
votes
1answer
346 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&"...
-4
votes
1answer
545 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 ...
0
votes
1answer
630 views

Conditional formatting based on another cell value

I'd like to format cell A338 so that, if that cell is blank and if cell H338 is not blank, then conditional formatting would apply. =AND(NOT(ISBLANK($H338)),(ISBLANK($A338))) That didn't work. Where'...
1
vote
1answer
2k 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 ...
0
votes
1answer
850 views

Google Sheets Alternate Row Color by Unique Sorted Values

In Google Sheets there is now the Alternating rows formatting. It's almost the equivalent of of tables in Excel. Background What if I have a sheet that is sorted by Invoice #'s, my unique number ...
2
votes
1answer
527 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 ...
0
votes
0answers
37 views

Set independent/separate conditional formatting rules for background and colour?

I added a conditional format to change the background based on one criteria. Based on a second criteria I would also like to change the text. Is it possible to set these independently in the ...
3
votes
1answer
2k 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 ...
0
votes
1answer
22 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
1answer
1k 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 ...
0
votes
1answer
137 views

Conditional Formatting two sheets and based on drop down value

I have a work sheet called Current and within it a Column SportsTeam. The values for SportsTeam are populated as a dropdown list. The values for the dropdown list are in worksheet called Setup. In ...
0
votes
1answer
70 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. ...
2
votes
1answer
58 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 ...
0
votes
1answer
797 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 ...
2
votes
2answers
3k 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 ...
3
votes
1answer
1k 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 ...
4
votes
2answers
2k 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)...
0
votes
1answer
359 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 ...