Stack Exchange Network

Stack Exchange network consists of 175 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange

Questions tagged [conditional-formatting]

For questions about conditional formatting in spreadsheets.

0
votes
1answer
11 views

Simple Scorecard Conditional Formatting

I simply want to create a scoresheet comparing scores to par. If less than par then a red number, if more than par then highlight red, if equal to par then highlight green. Here is what I'm trying ...
1
vote
1answer
369 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
2answers
13 views

Format cell if all the cells in a row are not empty

I tried all kind of formula but they all seem to work the other way around for me. I have a very large sheet with lots of columns. I'd like to have the first cell in the row to be a certain color if ...
2
votes
1answer
10 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 ...
1
vote
1answer
13 views

Disable Conditional Formatting without deleting the rules

I have multiple conditional formatting rules in one of my Google Sheets, I would like to disable a few of those rules for some time and would like to enable them again. But I don't see any such ...
0
votes
1answer
10 views

How can I conditionally format cells to denote past/future/current dates?

I have a church rota Google Sheet. Column A shows the date of each service. To make it more obvious at a glance, I would like cells in column A to apply formatting so that dates in the past are ...
1
vote
1answer
7 views

Automatic cell coloring via conditional formatting

I am working on a sheet for a game I play and having some trouble with cell coloring. I have one sheet, named "PlayerList" which maintains a list of player names, class and role. Players are ...
0
votes
1answer
34 views

Google Sheets Conditional Formatting Isn't Working

I am making a spreadsheet in Google Sheets and my conditional formatting works in some cells and not in others. I have gone so far as to delete all of the formattings for the entire workbook and ...
1
vote
2answers
446 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 ...
5
votes
2answers
839 views

Coloring cells according to the value to which its content is closest

I am trying to make a guide for my dragons on a game I play but I'm not quite sure how to code what I am trying to do. Link Example: if has values "Guardian"(C2:C100) and "Dragoness"(I2:I100) in its ...
-1
votes
1answer
272 views

Transposing with header row

In Google Sheets I'm trying to transpose a list of items in rows to display as columns. I am looking for a way to transpose the Category field to act as a header row that extends over all the items of ...
1
vote
2answers
14 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 ...
0
votes
2answers
9 views

Conditionally format cells that not match with range from Sheet2

I have two sheets in the same file where "sheet1" has cleaned up information and "sheet2" has the dump from my jira. Now I am trying to compare Column F from sheet1 and sheet2 to confirm if they are ...
0
votes
1answer
11 views

Google Sheets Conditional Formatting for expiration dates

I need help! I need to create conditional formatting for expiration dates: Cells should stay green Cells should turn yellow when 1 month of exp. date Cells should turn red once expired
1
vote
1answer
10 views

Conditional Formatting Duplicate Invoices in Google Sheets

My workplace uses Google Sheets to track invoices across all locations. The two most important columns are next to each other and titled Invoice number and Vendor. I wanted to highlight the Invoice ...
0
votes
1answer
16 views

Max value conditional formatting rule not working in Google Sheets

I want the max value in Column B to show up. I put in this formula but it's not working. What am I doing wrong? Here is the table
1
vote
1answer
24 views

Conditional Formatting using Google Sheets

So I'm trying to build out a simple campaign tracker and I've been running into a few issues with the conditional formatting on google sheets. I need the cells on column C to change color and format ...
1
vote
2answers
9 views

How do I detect duplicates in two worksheets?

I have 1 Google Sheet with 2 worksheets: Worksheet "1" has 1 column A with 5 lines of data (AAA, AAB, AAC, AAD, AAE) Worksheet "2" has 1 column A with 5 lines of data (AAA, BBB, CCC, DDD, EEE) I ...
0
votes
1answer
10 views

Quick way to make an automatically color-coded Google sheet for every day of the year?

So if the day is January 1st, the cell will be one color. If it's January 2nd, it'll be another color, and so on for every day of the year. Is this possible?
4
votes
3answers
9k views

Is it possible to set up conditional formats using Excel Online?

Is it possible to set up conditional formats using Excel Online? The cell's context menu doesn't have the option, and I can't find it in the ribbon either.
1
vote
1answer
35 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
18 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 ...
1
vote
1answer
18 views

Join multiple ranges when using conditional formatting

I have a simple conditional formatting custom formula which highlights duplicate values in a column: =countif(A:A,A1)>1 I have another column on another sheet (same workbook) which I would like ...
10
votes
6answers
48k views

Advanced date logic in Google Spreadsheets

I have the folowing situation: Today is 4/9/12. I have a cell with a date in the sheet that is 4/13/12. This cell should turn red since it is within 7 days from the current date. What is a ...
1
vote
2answers
30 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
34 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. ...
0
votes
1answer
35 views

Cell with multiple color formatting results only to first color being applied

If a Google Sheet cell has 2 Conditional formattings, one which changes only font color, one which changes only the background, only the first conditional formatting affects the cell. The reason ...
1
vote
1answer
12 views

check if cell is empty OR has a value, AND cell in row isn't empty then assess as true or false

I'm trying to format a column so that if the column A cell is NOT empty, AND the column H cell is either empty OR "no" =AND(NOT(ISBLANK($A$1:$A)), (OR(ISBLANK($H$1:$H)), (LOOKUP("no", $H$1:$H))))
0
votes
1answer
5 views

Flagging empty cells if date in another cell has passed

Good afternoon! I've created a test sheet to help visualize this - https://docs.google.com/spreadsheets/d/1-qHH4GfeIZz-7OBHFpp4trrFNg7l-gBHDT2MHg9tFmE/edit?usp=sharing I'm aiming to have cells ...
1
vote
1answer
22 views

Conditionally format rows with partial matches

I have a Google Sheets table that looks like this: (col a) (col b) (col c) (col d) Company Cohort Session Count Group -------------------------------------------- Acme ...
1
vote
1answer
16 views

Adapting Conditional Formatting Formula for Cross Sheet Reference

I currently have a sheet setup for work that compares 3 columns of data with conditional formatting. The goal is to identify whether the same string of characters can be found 1 2 or 3 times across ...
0
votes
2answers
39 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
19 views

Conditional Formatting Text with more then one value

I am looking to have a row of 20 cells change font colour based on ABC to Z being white. Then 0 to 10 being blue. Then all the symbols being red. Is there a way to do this? I found I can do this but ...
0
votes
1answer
168 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
3k views

Blinking cell in Google Sheets depending on cell content

I am trying to get a cell to blink (alternate colours white-red) when it contains the word "Red". I currently have the following code that I have found, but don't know how to modify it to particularly ...
1
vote
2answers
28 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 ...
0
votes
2answers
22 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 ...
68
votes
9answers
130k views

How can I set conditional formatting on a particular cell that depends on another cell's value?

In my example I would like to conditionally format column B cells. Those marked with x should be formatted according to value in column A (in the example the value is 1): A | B 1 | x 2 | 3 | 1 | x ...
0
votes
1answer
113 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
1answer
16 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
1answer
23 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
13 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 ...
1
vote
1answer
18 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 ...
0
votes
4answers
44 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 ...
10
votes
2answers
4k views

Script to change individual text color within a cell

I have a Google spreadsheet with hundreds of employee names for a schedule. Each cell has multiple names within that cell that look like this: First Last First Last First Last First Last First Last ...
0
votes
1answer
44 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 ...
1
vote
1answer
76 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
23 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?
2
votes
2answers
2k 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 ...
1
vote
2answers
328 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 ...