Questions tagged [conditional-formatting]
For questions about conditional formatting in spreadsheets.
1
vote
1answer
17 views
Date fields that refuse to be parsed/formatted
I have some very mysterious date fields in a huge (600 000 records) CSV file which absolutely refuses to behave.
They come up aligned left, and conditional formatting shows that they cannot be seen ...
1
vote
1answer
13 views
Combine Custom Formula with Colour Scale in Conditional Formatting
In Conditional Formatting in Google Sheets, I'm wondering if it is possible to combine the Format Cells if - Custom Formula is with a Colour Scale?
It would appear not, but is there a clever ...
0
votes
2answers
14 views
How to apply conditional formatting only if cell has X value? [google sheet] [duplicate]
I have a sheet with statistical data to which I had applied color scale conditional formatting. Some rows I set to have green for highest and red for lowest number in row and other rows the other way ...
1
vote
2answers
21 views
Conditional formatting rule that compares against a range of values
I have a list of 2019 dates (col A) and holiday dates (col G). Wherever there's a date in col A that matches any date in the range G2:G20, I would like to highlight it in grey. See this sample ...
1
vote
1answer
14 views
How to color cells in column A if cells in other columns relating to those column A cells are filled in?
Column A contains student names. The other columns show work that is missing or incomplete.
I'd like to be able to have cells in column A colored in if work from various classes is missing, without ...
1
vote
2answers
21 views
What function can I use to conditionally format text depending on cursor position or active cell?
In Google Sheets, I have a 'Freeze' on Column A.
I want the text in Column A, Row X to Conditionally Format as BOLD RED, while my cursor is on any cell for Row X.
Is there a way to manage that?
1
vote
1answer
22 views
How do I conditionally highlight cells
Not sure if I'm sharing this right but below is a link to my sheet. Cell C3 is past today so it is red. What I'm trying to do is have it go back to white when I enter the next "Last change" date in ...
1
vote
1answer
12 views
Conditional Google Sheet Checkbox
I am creating a spreadsheet which can track all of the replacement items I am sending out for my business, however, I also want to be able to track my refunds on the same spreadsheet. I have got it so ...
1
vote
1answer
11 views
Conditional Format Copying in Google Sheets
I am looking to copy over a conditional format that changes the colour of an array of cells when checking a checkbox.
This works perfectly fine on the first checkbox. However, when I try to replace ...
0
votes
4answers
37 views
Show a Running Target Based on Today
I'm creating a running target from a quarterly Total divided by known trading days
Current formula in I4 is =IF(H3<>"",I3+O$30,"")
H3 = Yesterday's Sales Numbers
I3 = Yesterdays Running Target
...
0
votes
2answers
17 views
Conditional formatting by using the note in Google Sheets
How can I use conditional formatting by using the note on a specific or many cells in Google Sheets? For example, I would like to set up conditional formatting to a specific cell and when I use a note ...
1
vote
1answer
16 views
Conditional formating if cell is a formula
I'm trying to have cells which value has been entered manually and cells that are the result of a formula differently, is there a way to do that?
0
votes
1answer
12 views
Can I use conditional formatting to highlight a cell for dates less than 30 days away?
I have a large spreadsheet where I need to keep track of coming expiration dates and I would like dates which are within 30 days from today's date OR which have already passed to be highlighted in red....
1
vote
2answers
63 views
Conditional Formatting - Custom Formula - Text Contains
I have dates in column B that include weekdays in the following format: DDD, MMM DD, YYY (For example, Sat, Jan 19, 2019).
I want each row to auto format to a specific background colour based on the ...
0
votes
0answers
19 views
Conditional formatting weekly, fortnightly totals for my budget spreadsheet
I am making a budget spreadsheet.
I would like to enter certain expenses periods, i.e. weekly, fortnightly, monthly, quarterly and yearly. I would also like a column showing total monthly and yearly ...
0
votes
1answer
95 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
26 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
26 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
69 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
12 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
52 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
763 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
38 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
209 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
155 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
25 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
19 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
31 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
73 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
103 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
1k 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
24 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
2k 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
410 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
495 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
229 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
3k 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
39 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
128 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
418 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
160 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
799 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
157 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
565 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
940 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
34 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
70 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
25 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
40 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 ...
