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
28 views

Dependant Multi Conditional Formatting

So I am building a spreadsheet and everything was working fine but then I kept tinkering with it and making it more intuitive (and less work on the user) and ran into a snag. What I am trying to do ...
1
vote
1answer
21 views

Using conditional formatting to highlight entire area instead of just one cell

Here's a screenshot of the situation: When I hover over the rule to the right which I defined, the wanted area is highlighted entirely, so I would presume I entered everything correctly, but only one ...
1
vote
1answer
37 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/...
2
votes
1answer
232 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 ...
1
vote
1answer
176 views

Conditional formatting of Google SpreadSheet's cell based on proximity to upcoming date not working as expected

A picture is worth thousand words So, why isn't the first date cell yellow? It's in the selected range
2
votes
2answers
2k views

Is there a way to lock or protect conditional formatting in Google Sheets?

I have several columns that indicate the current state of a project task. When I complete some task (for example), I copy the "complete" text from some other cell and paste it in the new cell to avoid ...
2
votes
1answer
29 views

Adding condition to existing formula to not trigger formula if another cell is blank

I'm new to adding formulas etc. in Google Sheets, and I'm currently trying to set up a library borrowing/returns spreadsheet. I have three columns formatted to dates: 'G' (date borrowed), 'H' (date ...
1
vote
1answer
3k views

Use checkbox to change the background color of a range of cells in Google Sheets

I'm trying to have a checkbox change the background color of a range of cells using conditional formatting custom formula =$N123=True. With the range defined in conditional formatting only the first ...
2
votes
1answer
584 views

Colors in conditional formatting based on cell hex values [duplicate]

I am looking for a solution to change background and font color of a cell based on hex-values in another cell. I am managing a google spreadsheet for an online basketball league. I have several ...
1
vote
1answer
738 views

Conditional Formatting COUNTIFS (Google Sheets)

I'd like column P to be highlighted when: A contains "x" E contains "approved" O < today (date has passed) P and Q are blank My formula doesn't work: =COUNTIFS(A6:A, "x", E6:E, "approved", ...
1
vote
1answer
26 views

Conditional Formatting in GSheets

I have a sheet tracking activity scores for players across multiple accounts in a game I play. I am trying to format the scores so that the monthly cells (Columns I through T) will be red if the ...
0
votes
2answers
362 views

Is it possible at all to name conditional formatting rules in a Google Spreadsheet document?

I have any conditional formatting rules for my Google Spreadsheet documents. These rules span and overlap many cells. Most of these rules use custom formulas, so they aren't simple like the "Text is ...
1
vote
2answers
4k 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 ...
0
votes
1answer
44 views

Is Google Sheets capable of making a gantt-like chart like this? If so, how?

I have two columns in a spreadsheet: A1:A100 has start times, and B1:B100 has duration. I am trying to create a chart that looks something like this, where each row gets a vertical line placed at the ...
0
votes
1answer
169 views

Google Sheets Conditional Formula

Google Sheets formula for outputting PASS or FAIL in one cell based on the color (green or red) of another cell?
1
vote
1answer
450 views

Conditional format custom formula in Google Sheets

Have a question on how to do a custom formula in conditional format for the following: A1 = DCC220 B1 = 7.50 C1 = 7.00 D1 = 7.60 I want to compare the result in B1 (7.50) to a range, C1 lower, D1 ...
0
votes
1answer
185 views

Conditional formatting based on dependent variable cells

Apologies if the title is unclear -- if anyone can suggest better wording I will change it. I wasn't sure how to condense this question into a single sentence. I have a spreadsheet with three columns ...
1
vote
1answer
1k 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 ...
0
votes
1answer
247 views

Highlight Rows with Unique Values As Compared to Rows with Same ID# on a Different Sheet

I have two sheets: The first is the Master Sheet of responses to a survey. The second will be an Update Sheet auto-generated from the new responses. New responses can be either entirely new rows ...
0
votes
1answer
983 views

Conditional formatting in Google Sheets with 2 conditions [duplicate]

I have a sheet where column E lists statuses of jobs and column N has the due date for the job. What I'd like to do is colour code the date so that the date appears red if the due date is before today'...
1
vote
1answer
1k 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 ...
0
votes
2answers
38 views

Apply conditional formatting to one column

Is it possible to apply conditional formatting to one column such that when the cell has the same value (either text or number) as other cell in the column, the current cell will be formatted. For ...
1
vote
1answer
76 views

Conditional formatting with multiple criteria for one cell

I have 5 cells (A1:E1) that are filled with either 'yes', 'no', or 'maybe'. Can I have conditional formatting on F1, where if 'yes' is green, and if 'no' is red? To clarify: =countif({a1:e1};"yes") ...
1
vote
1answer
26 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 (...
0
votes
1answer
48 views

Conditional format a cell with a graduated colour when a range of other cells are completed with text

I'm trying to conditionally format a cell with a graduated colour (traffic light: green/ amber/ red), when a range of other cells are completed with text within google sheets. In other words, 4 cells ...
0
votes
1answer
3k 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
64 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
713 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
374 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
30 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
3k 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 ...
1
vote
2answers
331 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
983 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
936 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
2k 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
175 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
178 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
83 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
2k 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
55 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
35 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
171 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
2k 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 ...
7
votes
4answers
16k 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
3k 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 ...