Questions tagged [conditional-formatting]
For questions about conditional formatting in spreadsheets.
0
votes
2answers
33 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
20 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
28 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 ...
0
votes
0answers
24 views
Conditional Formatting Custom Formula IFBLANK
Help with conditional formatting. Id like to get the custom formula to change the color for one row (3 columns A, B, and C) if C is blank?
1
vote
1answer
22 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 ...
0
votes
0answers
15 views
Custom formatting applied after spliting the text and comparing each value with a list
I have a column called productType which has several product types separated by "|". It looks like this :
ProductType
Top|Dress|Skirt|Cardigan|Blazer|Pant|Outerwear
Dress|Top|Cardigan|Pant|Outerwear|...
0
votes
0answers
9 views
How do I conditionally format date ranges in a dynamic calendar with multiple criteria?
I would like to differentiate with conditional formatting the different types of Leave employees can take (VAC, TIL, STAT, LOA etc as well as the status of their request "Approved", "Pending", "Denied"...
1
vote
2answers
18 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
18 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
16 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
21 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
36 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
22 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
188 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
21 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
307 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 ...
1
vote
1answer
118 views
Colors in conditional formatting based on cell hex values
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
113 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
23 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
24 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
334 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
291 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
100 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
240 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
75 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
85 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
380 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
28 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
41 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
18 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
18 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
1k 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
52 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
420 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
213 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
696 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
23 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
85 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
693 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
618 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
2answers
624 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
55 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
18 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
85 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
37 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 ...
0
votes
0answers
22 views
Conditional If Formatting Based on Two Columns
I'm looking for help writing a custom format to highlight a cells in Column O if
a) the cell in Column O is blank
AND b) the value in Column I is blank.
I know it's possible and I'm just not doing ...
-1
votes
1answer
59 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 ...
