Questions tagged [conditional-formatting]

For questions about conditional formatting in spreadsheets.

Filter by
Sorted by
Tagged with
2
votes
1answer
98 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 ...
2
votes
1answer
114 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 ...
2
votes
1answer
889 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. ...
2
votes
1answer
97 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 ...
2
votes
1answer
71 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 ...
2
votes
1answer
42 views

Date highlighting of a cell, separated by weeks, given only a starting date and a cell range

I am trying to build a "life calendar" similar to this - https://labs.coruscantconsulting.co.uk/life/weeks/ - in Google Sheets. I have the manually designed sheet finished, but want to add the ...
2
votes
1answer
109 views

Format cell, If word from list in cell

I'm trying to color tag cells according to keywords they may contain. I've searched just about everywhere, and have tried various applications of the Search and Find functions to no avail. Imagine I ...
2
votes
1answer
65 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 ...
2
votes
1answer
71 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
1k views

Color Scale via Conditional Formatting with Non-Numerical Values

I am trying to find a way to use conditional formatting to automatically assign the same colors to cells containing the same order numbers. I cannot predict the order numbers so I'm trying to use ...
2
votes
2answers
147 views

Create a grade sheet where it will flag number grades in a certain range by turning that row another color

I'm trying to create a grade sheet where it will flag number grades in a certain range by turning that row another color. The problem I'm having has to do with grades of Zero. I have this formula ...
2
votes
1answer
1k views

How would I apply formatting to another cell based on whether another cell's date has passed or not?

I'm working on making a spreadsheet in Google Sheets, and I'm attempting to have a column of names and then a column of dates adjacent to it. I'd like to set up some sort of conditional formatting ...
2
votes
1answer
790 views

Can I use conditional formatting to color a column's cells' text based on the conditionally-formatted color of text in another column's cells?

I have been working on a spreadsheet in Google Sheets that serves as a database of the hundreds of characters who appear in a novel I am reading (to help me keep track of them all). Column A contains ...
2
votes
1answer
86 views

Changing colour of row if a certain aspect is met

I have a list of clients that send in certain information to me and I have setup columns to enter a tick using (=char(10004)) on R,S,T,U,V,W,X & Y columns and I need to colour them to go green ...
2
votes
1answer
2k views

Copy-pasting cells with conditional formatting

I'm trying to use conditional formatting in the goals area. Currently the calorie goal (J10) turns green as long as I stay under its number, and it's compared to the total calories for the day (J3). ...
2
votes
2answers
1k views

How can I set conditional formatting on particular cells that depend on another cell's value in Google Spreadsheets?

I would like to colour the background of a row in Google Spreadsheets: For example: If cell Dx = title 'To do' or 'title 'Done' then cells Fx to Nx are filled colour black. If cell Dx = title '...
2
votes
1answer
29 views

How to elevate a conditional format rule to be the number one rule instantly

I would like to make a newly created rule for conditional formatting the top rule so it has precedent over all others but be able to do so without having to drag it to the top as this takes for ever ...
2
votes
1answer
197 views

IF AND not working in Google Sheets for Conditional Formatting

I am trying to use the three following functions... =IF(AND(M3="Funding on Hold",R3>=3,R3<=6),TRUE,FALSE) =IF(AND(M3="Funding on Hold",R3<=9,R3>=7),TRUE,FALSE) =IF(AND(M3="Funding on Hold"...
2
votes
1answer
305 views

Google Sheets color row if date is closest to today's date

If possible, I would like to have a color schema applied to the table. For instance, the cell that has the closest date to today will be green The cell that has the furthest date to today will be red....
2
votes
1answer
658 views

Google Sheets Conditional Formatting custom formula not working

I am trying to shade all rows that are associated with a specific value of column B as =$B2="BAMS", yet custom formula keeps saying the wrong formula. Any idea?
2
votes
1answer
2k views

Changing cell color in a range

i have 4 columns of values in each cell, the cells are different colors : Cell A1 = 12 (color - Green) Cell B1 = 10 (color - Pink) Cell C1 = 11 (color - Blue) Cell D1 = 13 (color - Red) Cell ...
2
votes
1answer
3k views

How can I get a cell to change its value based on another cell's color?

I have three colors in column F: green, grey and red. What I want is for column G to display a number (1, 2, 3) depending on the color value of F. Is there a way to do this?
1
vote
4answers
6k 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 ...
1
vote
2answers
6k views

How can I highlight duplicate cells?

I'd like a formula that highlights all duplicate cells, even if they are in different columns. In my example spreadsheet A1 is equal to D15, the formula should highlight both, and so on with all ...
1
vote
2answers
148 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 ...
1
vote
3answers
8k views

Change cell colour based on another cell containing text (day name)

I am new to Google Sheets and trying to create a custom formula in conditional formatting to make the fill colour of a cell change based on another cell containing a specific text. I want to change ...
1
vote
2answers
2k 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
1answer
154 views

Conditional formatting based on multiple cells

I'm trying to figure out how to write a formula for Google Spreadsheet where: If F3 Contains Data and G3 is more than 0 then cell J3 is Highlighted in Green Can anyone help me out with this?
1
vote
1answer
1k views

Conditional Formatting: no color when cell is empty

I am using this custom formula for my sheet: =if(B2<((B$7/4)*$A2), 1, 0). However, this also highlights the rest of the cells that are empty. How do I exclude empty cells from this formula?
1
vote
1answer
41 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?
1
vote
1answer
2k 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
vote
1answer
29 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,...
1
vote
1answer
63 views

How to change row colour depending on due date in a cell

I need a whole row to be red if the date in column C is older than today() I have tried this basic formula =C2:C<TODAY() but that makes every row that is still empty also red. So how to add ...
1
vote
1answer
1k views

Can conditional formatting reference other sheet?

I needed to check which indexes exist on another sheet. So for the A:A range, I made conditional formatting with the following formula: =COUNTIF('Other Sheet'!A:A;A1) That had a red error outline. ...
1
vote
2answers
403 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
317 views

Conditional formating if cell is a formula [duplicate]

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?
1
vote
1answer
5k 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
314 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
1answer
2k 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
589 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 ...
1
vote
2answers
396 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 ...
1
vote
1answer
957 views

How to specify a character range on Google Sheets conditional formatting

I'm trying to color cells that do not start with a letter; but I'm not sure how to specify a character range in this format. Cells contents such as: !this one <--- formatted this is fine @...
1
vote
1answer
422 views

Conditional formatting of duplicate values

In a full sheet range, I need a cell to be highlighted when a duplicate is typed, whether it is a number (phone number in this case) or a text string (name in my case). Both the new cell and the cell ...
1
vote
1answer
113 views

Conditional Formatting if all cells fail to meet a certain criteria

This is the spreadsheet I'm working on. I am trying to figure out a formula for conditional formatting so that it only highlights the cells in red if none of the cells from columns N to Y of the ...
1
vote
1answer
46 views

Conditionally format data in spreadsheet column, but only when it changes

Say I have this data in a Google Spreadsheet: cat cat mouse mouse mouse cat dog dog And I only want to apply conditional formatting to the ones in brackets [] (so I can hide them but still calculate ...
1
vote
1answer
156 views

Is it possible to use the contents of a cell in a custom conditional formatting rule in Google Sheets?

For example, given the sheet below: I'd like the background colours of columns B-D to change automatically when I update the values in column A Is that possible? I'm looking for a solution that will ...
1
vote
1answer
475 views

Conditional formatting based on comparison with today's date and also with a future date

I'd like to conditionally format cells that will go red if it's past its expiration, go yellow if it's within 3 months of expiring, to remain green if date is clear of expiry. For example: today's ...
1
vote
1answer
2k views

Conditional formatting of an entire row based on multiple cells

I'm trying to make a conditional formatting rule in Google Spreadsheet that would highlight a row green based on text in multiple cells. If cells L2, M2, and N2 = APPROVED; and cell O2 = X; then row ...
1
vote
1answer
847 views

Google Sheet conditional formatting, highlight just 1 cell in a range based on another cell [duplicate]

How can I highlight a cell in a range, if its value is equal to a different cell? Example: Random numbers in range A5:A20. I want to highlight a cell in this range if it is equal to B4. [ A ][...
1
vote
1answer
811 views

Formatting in Google Spreadsheets

I am trying to format in Google Spreadsheets and am unsure how to do the following: If column G is equal to “Yes” then it will format the entire row by changing to font to grey, italic and strike ...

1 2
3
4 5
8