Questions tagged [conditional-formatting]
For questions about conditional formatting in spreadsheets.
0
votes
1answer
22 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)...
0
votes
1answer
20 views
Google Sheets Conditional Formatting Multiple Cells with Different Colors
I am studying for a big exam and want to set priorities using conditional formatting. For example if C90, C91, C92 are blank than the color would be white; if C90, C91 are blank but C92 is not blank ...
0
votes
0answers
21 views
How to use Sheet, Range protection and Conditional formatting simultaneously in Google Sheets?
I have created a google sheet for my sales team having two members. Gave access to them for two columns (Followup Date and Status) to fill their daily report. I have created another column '...
1
vote
1answer
26 views
Conditional Formatting with Multiple Conditions Weekday and Time
I'm working with Google Sheets and want to highlight cells in long date format (e.g. 11/13/2018 21:00:00) that are Tuesday and 21:00:00. Here is my attempt:
=AND((weekday(C4:C124)=3),(EQ(RIGHT(C4:...
0
votes
0answers
10 views
How to highlight a cell in column A if the text in column B matches text in a list on another tab?
I have a Google Sheet set up with place names in column B as below.
I have another tab on the same spreadsheet with a list that can change from time to time depending on which stores needs items ...
0
votes
2answers
45 views
Conditional formatting if entry is final entry of the day
I have a Google Sheet in which I keep track of transactions, kinda like this:
| Date | Funds | Running total |
|---------|---------|---------------|
| 5/23/18 | 10.00 | 10.00 |
| 5/23/...
0
votes
1answer
177 views
Google Sheets conditional formatting for multiple columns
I'd like to set up conditional formatting in columns B:D such that blank cells in B:D are highlighted but only when the same row in A is NOT empty.
I think the custom formula should be something ...
1
vote
1answer
31 views
How do I conditionally format text color to match across sheets in Google Sheets?
Edit: I couldn't figure out how to post pics without editing, so I added them to the OP. The first photo is the process form. On the form, as the boxes are checked the color changes from black to red ...
1
vote
1answer
56 views
Conditional Formatting across a range of cells with a formula in Google Sheets
I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018".
I have a separate sheet with a data set that has rows that classes end such as "9/24/2018".
I want to ...
1
vote
2answers
33 views
Conditional Formatting based on other column
I have a timesheet log with columns
DATE | HOURS | DESCRIPTION OF WORK | PROJECT
An employee may enter several rows for the same date to log the different amount of time with respect to the ...
0
votes
1answer
22 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
18 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
27 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
45 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
40 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
1
vote
2answers
482 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
23 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
932 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
268 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
294 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
25 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
67 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
1k 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
375 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
133 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
423 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
112 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
268 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
588 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
33 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
53 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
22 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
23 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
2k 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
55 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
531 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
285 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
1k 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
27 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
1k 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
15 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
129 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
813 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
707 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
3answers
873 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
99 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
35 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
0answers
88 views
Conditional formatting in Google Sheets
I have one table contain Minimum Level, Safety level and Closing.
If the Closing stock is equal or less than Minimum level it's color become pink color and if the Closing go to the Safety level the ...
0
votes
2answers
1k 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 ...
0
votes
0answers
38 views
Conditional formatting between two sheets, only if cells match
I have created a Google Sheet file and I want to color all names that match from two different columns in two different sheets.
I think the formula should be something like:
If a name from the ...
