Questions tagged [conditional-formatting]
For questions about conditional formatting in spreadsheets.
313
questions
1
vote
1answer
18 views
How do I use conditional formatting on one column based on certain characters in another column?
When column D has a hyphen (-) in it, I want the fill color in column H to change.
What is the custom formula for that?
0
votes
1answer
13 views
Google Sheets apply conditionnal formatting for each column
I'd like to highlight the max value in each column. Example :
A B C
1 50 12 60%
2 20 84 50%
3 38 20 15%
Here A1, B2, C1 will be highlighted. So I know how to do that for 1 ...
3
votes
0answers
39 views
Conditional Formatting in Google Sheets
The shorter question to spare you from reading more than you need to
I have a few different formulas for use in conditional formatting with custom formula in Google sheets. They are all roughly 1100 ...
2
votes
1answer
25 views
What formula could I use for conditional formatting to highlight cells in Column B if the text in Column A has a link in it?
I want to easily see what text in Column A has a link in it by looking at Column B.
0
votes
1answer
16 views
highlight if value is duplicate and corresponding cell of all other duplicates is blank
I have a list of clients who contacted us. We need to get back to them.
I would like to format if:
They appear twice in the list (got that) and
We have not gotten back to them in any of their ...
-1
votes
1answer
27 views
Query matching two columns to three columns
I have 5 Columns divided in two groups.
The first column of the first contains the name of a fruit (Pear, apple or peach) and the second its price (1 -> 5). In the other group, I also have the name ...
0
votes
1answer
41 views
Conditional Formatting based on another sheet, tying a value to a colour, Google Sheets
Does anyone know how I'd be able to make a specific value always appear with the same formatting & colour in my sheet, based on another tab in the sheet?
The spreadsheet I'm trying to do this on ...
0
votes
1answer
21 views
Change cell colour based on another cell containing text
I am new to Google Sheets and trying to create a custom formula in conditional formatting. Looking to see if someone would be kind enough to help.
The picture below is a sample size of two list of ...
0
votes
2answers
27 views
Conditional formatting custom formula not working in google sheets
custom formula is: "=COUNTIF($B1:$B100,"month")>1."
I tried also:
"=COUNTIF($B1:$B100,"month")>1"
=COUNTIF($B1:$B100,"month")>1
="COUNTIF($B1:$B100,"month")>1"
I know it shouldn't work ...
0
votes
1answer
21 views
Is there a way to conditionally format border styles in Google Sheets?
The title says it all.
I have some conditional formatting rules, it seems I'm limited to some basic formatting options;
Is there any workaround to apply further formatting options (such as border)?
1
vote
1answer
12 views
Copy conditional formula to separate columns GoogleSheets
I've got a simple conditional formula to make cell green if there is more than X not empty fields. It works fine but I need a way to copy that formula to other columns. When I do it the simply click ...
0
votes
1answer
19 views
Conditional formatting row of data based off of a date range
I have a bunch of entries in a sheet which are all between a range of dates, the two columns with dates are start_date (B) and end_date (C).
I already highlighted past time periods using;
=DATEDIF($...
1
vote
1answer
22 views
Formatting based on multiple cells and duplicates
I will try my best to explain the situation :
I have a sheet where we enter test data and serial numbers.
Column A is serial number
Column C is if the unit passed or not.
I currently have ...
1
vote
0answers
37 views
Google Sheets Conditional Formatting only applying formatting to certain cells
I am currently working on making a spreadsheet that will figure out which class I should be in based on times. That all works and outputs if I should be in the class to a TRUE/FALSE cell. My ...
0
votes
0answers
18 views
Utilize SI units in Google Sheets
Seven years ago this was asked, are there any new developments with regard to formatting with SI units?
Normally, I will adjust the format to be all milli or micro simply by division, however my ...
2
votes
1answer
50 views
Apply conditional formatting to a blank cell if another cell in the row is not blank
I am trying to create an equation for conditional formatting that would highlight a cell that is blank if another cell in the same row is not blank (i.e. that row is being "used"). In the picture ...
1
vote
1answer
8 views
Conditionally format a range based on the value of a single cell outside of it [duplicate]
I am using conditional formatting with a custom formula. I want to highlight rows in the range A2:B11 if the values in $B2 (column B starting at row 2) is greater than the value in the cell D5.
I can ...
0
votes
1answer
73 views
Google Sheets - Conditional Formatting question
So I have it set that if a cell has a value of 0 to change the background to yellow. I want to have it so that if there are two 0's (zeros) next to each other in a row (edit: in adjoining cells not ...
0
votes
1answer
22 views
Formatting a cell based off of the rest of the row
I have a spreadsheet keeping track of the various documents that students of mine have been turning in. The first column contains names and each column after tracks a particular document.
Is there a ...
0
votes
1answer
24 views
Comparing cell value 450-500 to another cell of value 475 in Google Sheets
I wanted to compare the price range column which will have prices range like 450-500 to another cell value 703.
In this image you can see I need to find column D value (which is the price range) to ...
1
vote
1answer
23 views
Using a custom formula as part of a conditional formatting rule
Ive got a spreadsheet with 3 columns A, B, C, i want to add the 3 columns together in column E, if there is a value in column C, i want the summed number to be shown in red.
If there is a value in ...
3
votes
2answers
48 views
Conditionally formatting a column based on content of nearby column
I am trying to get cells in column C to turn green when the cell in the same row of column B has a value in it.
I think there is a bug because the custom formula below is not working.
1
vote
1answer
22 views
Google Sheets conditional formatting with AND for value range
I'm trying to set a colour range of conditional formatting based on another cells value. So if A1 is larger than A2 it will be Blue, if A1 is between 0 and A2 it will be Yellow and if A1 is less than ...
0
votes
1answer
21 views
Highlight row if partial text matches in either of two columns
I have a sheet that has a list of job names, and then the last failure and success of each job. The dates are listed with duration words instead of dates, ie 1 mo 12 days or 3 days 6 hr.
I want to ...
1
vote
1answer
23 views
How do I make the “Text contains” evaluate any word written in the cell?
I just want the cell in Google Sheets to change its fill color when any text is inputted in the cell? But it has to be text excluding numbers. Please help!
1
vote
1answer
224 views
Finding merged cells in Google Sheets
I'm often trying to freeze columns in a Google Sheets and I get the error as below
The way I get around this is to manually hunt for the merged cells and unfreeze them, but this can be difficult on ...
-1
votes
2answers
33 views
Mark all line if checkbox is true
I will give an example:
I have checkboxes in B3:G3.
This is a checklist, I want to change the background color to green for A3:H3 if B3:G3 boxes are checked.
Can someone please tell me how to do ...
2
votes
1answer
28 views
Conditional Formating for Google Sheet comparing to list of text (highlight duplicates)
I am trying to create a custom formula for conditional formatting. I want to highlight the cell if the text appears in a list of names. This list will contain 210 unique names so I can't type each one ...
0
votes
1answer
32 views
Conditional Formatting in Google Sheets based on the same rows conditional format
I have a spreadsheet where I have tasks, and I prioritize those tasks based on high, medium, and low levels. I currently use conditional formatting for this, so if I select from a drop-down "HIGH", ...
1
vote
1answer
18 views
How to highlight cell if it's value is a multiple of a given number
I would like to apply conditional formatting to any cell within a range containing a value that is a multiple of itself.
IE: Apply bold font to any cell of column A that is a multiple of 4 (4,8,12,16,...
2
votes
1answer
83 views
How can I select all cells that contain specific text in Google Sheets?
I have a spreadsheet that contains 20,000 cells.
I want to select all cells that contain "foobar".
How can I do this?
1
vote
0answers
31 views
Conditionally format a column if it contains all the values in a set
Each of the columns in my Google Sheet represent a farm. Each farm is a list of animals. I want to highlight the entire column if the farm has a duck, a horse, and a cow. So the column may look like ...
1
vote
0answers
48 views
Conditional Formatting IF/AND Google Sheets
EDIT 2:
Test document. See comment of what I'm trying to accomplish in cell A1.
https://docs.google.com/spreadsheets/d/1lqrPbVqSrO257PqOYKLuCTxG1iiLaL6KYawp1hu4n5c/edit#gid=0
EDIT:
I've tried this ...
0
votes
0answers
8 views
How can I create conditional formatting based on a reference table?
I'm trying to format rows in my spreadsheet based on a single column (S), which is fine BUT S contains both the category and the status of the item in each row. I want to color the row based on the ...
0
votes
1answer
39 views
If cell y contains any word from range x:x, mark cell y green
In Google Sheets, I have three columns (with different lengths!)
Column1 contains firm names (50)
Column2 containts firm names (100)
I would like to mark a cell in Column1 green (or with a number in ...
1
vote
1answer
119 views
Conditional Formatting in Google Sheets not working correctly
I recently tried to edit the conditional formatting for a column that was working correctly before and then change it back to the same exact formula it had before and now it isn't working correctly. ...
0
votes
1answer
83 views
How to detect duplicates across multiple sheets?
I have found a couple of formulas/scripts for what I am looking for, but they are all locked for one sheet only, thus I will try to explain what exactly I need. I am unable to do it my self, because ...
1
vote
1answer
14 views
Highlight data that match with data from other sheet
I have two tabs in a Spreadsheet. Both are having a list of data. I want to highlight cells in one tab that match with cells from another tab.
For example:
Tab 1
Column 1
ABC
XYZ
PQR
DEF
Tab 2
...
0
votes
0answers
41 views
Create conditional formatting for a table in google docs
So maybe someone can help me out with this. I have a Google sheet that I merge using AutoCrat to a google doc. The doc has 3 tables and one is priority - red, yellow, green. I am trying to put ...
0
votes
1answer
12 views
Conditional formatting for passed dates
I need the formula to highlight a cell if the date in that cell has passed.
Cell U3 had 6/29/19 today is 7/1/19 - I want to have the cell turn red after 6/29 to alert me to follow up.
2
votes
1answer
20 views
How to make a cell highlight red if its less than 6 digits and NOT effect blank cells?
I use this function
=LEN(P3:P)<6
in order to highlight all the entries with less than 6 digits (6 is minimum)
However, all the blank cells also highlight red. What function do I have to do in ...
1
vote
1answer
28 views
AND / OR Conditional Formatting for Google Sheets with multiple criteria
I am trying to write a custom formula to conditional format column E by referring to the different tenure level targets in column D to indicate if they are hitting their targets base off of their ...
0
votes
1answer
16 views
Conditional formatting skipping colors after the same large value
Looks like the conditional formatting skipping large formula because of the same large values.
Help me please how can I get the order what is on the picture in the column "What I would like".
0
votes
0answers
59 views
Conditional Formatting formula for alternating checkerboard with Merged Cells?
I wrote a formula that attempts to create a checkerboard pattern via conditional formatting, that should even work with Merged Cells. However, it seems to fail in an almost undebuggable manner with ...
2
votes
1answer
30 views
Google Sheets date conditional formatting
I have dates in column D in Google Sheets. I want the cells to highlight red if 30 days have passed and there is not a comment date in column F.
Example:
Column D: Submittal Date 6/29/2019
...
0
votes
2answers
303 views
Format Cells if greater than “value of the cell”
Values of K11–K13 are variables. On the Conditional formatting, I want to use the output from those cells in the "Greater than" function. Typing =k11 doesn't work. I'm not really sure on how to do it. ...
0
votes
1answer
27 views
Conditional formatting with multiple names in list
Col A2:C15 contain a list of names. I need to enter those names in various combinations into one of 15 other cells on formatted like:
H3 = John, Steve, Bob, Dan
Is there a way to use conditional ...
0
votes
1answer
19 views
Conditional formatting on whether something is a formula in Google Sheets?
Is there a way, in Google Sheets, to have conditional formatting of some sort, to change the background color of a cell based on whether the cell contains a formula or a plain value?
1
vote
1answer
24 views
Conditonal Formatting if cell A is within a range of numbers to make cell B a different color
I am looking to create a conditional format that if a cell "A" is within a range of two numbers that cell "B" will then change color in Google Sheets. Please help.
1
vote
1answer
30 views
Entire Column green if all cells in it are empty
I would like all columns that only contain empty cells to light up green.
In the below picture these should be: C3:C8, D3:D8, E3:E8, L3:L8
What would be the correct custom formula for this?
Bonus ...
