For questions about conditional formatting in spreadsheets.

learn more… | top users | synonyms

1
vote
1answer
27 views

Conditional number format (whole number, percentage) based on the value in another cell

I have a custom function provides results based on user input - once of these variables accepts either "Numerical" or "Percentage", which provides the data as standard number, or a percentage of the ...
2
votes
2answers
26 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 ...
0
votes
1answer
35 views

How do I change a cell to the color of the hexadecimal value of a cell in Google Spreadsheets?

Based on my data, I calculated values of hexadecimal color codes. I want some cell (I don't care whether it's an empty cell at the end of the row or the same cell) to be the color based on the code. ...
3
votes
0answers
19 views

Is it possible to set up conditional formatted using Excel online?

As per the title really, is it possible to set up conditional formatted using Excel online? The cell's context menu doesn't have the option, and I can't find it in the ribbon either. Am I missing ...
2
votes
1answer
45 views

Google Spreadsheet Conditional Formatting depending on up to 7 Fields

I'm trying to color a column depending on the row. My table looks like this: the only thing i managed to do was =NOT(ISBLANK(C101:C104)) and set it to orange using the conditional formatting. I ...
1
vote
1answer
36 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 ...
3
votes
1answer
43 views

Alternate Background Colours, Conditional Formatting Based on Date?

I have a date column, A7:A, I wanted rows to be coloured every other week, A7:A may have duplicate dates but I want all the dates that lie on the same week to be one colour and alternate so it's ...
1
vote
1answer
47 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 ...
2
votes
1answer
31 views

Conditional Formatting Based on Date Formula

I'm trying to highlight the contents of a Date cell if the following conditions are met: Value of another cell = "No"; and Value of target cell + 7 days > Today The formula that I have right now ...
3
votes
2answers
63 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 ex. if has values "Guardian"(C2:C100) and "Dragoness"(I2:I100) in its row ...
3
votes
1answer
35 views

Google Sheets Conditional Formating “IsEqualTo” value in referenced cell

What do I add to the text field directly below the "is equal to" selection if I want the test to be the value of a specific cell? I want the condition to pass and format a cell "IF" it is equal to ...
2
votes
1answer
30 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 ...
3
votes
1answer
41 views

How to conditional format in Google sheets based on cell directly above it?

Tried to setup a conditional format. Overall idea, I want a number to go down, and be marked red if it is going up. I tried this custom formula =if(F2>F3, true) trying to say, if the cell value ...
1
vote
2answers
97 views

Apply conditional formatting based on the formula that a cell contains

I am having trouble figuring out how to get conditional formatting to work in my Google spreadsheet. I use the second sheet to import prices of projectors, but a few don't update. I want the row of ...
4
votes
1answer
56 views

Conditional formatting based of current cell AND another cell

Setting up a conditional formatting based on current cell or another cell is easy. But how do you mix them together? Let's say I have the following table: | id | red ...
2
votes
1answer
94 views

Conditionally substitute cell contents with Unicode characters

Is it possible to conditionally substitute a cells contents, with a Unicode character i.e. rather than colouring all '1' cells green, swap the '1' for another character instead? Specifically, I want ...
6
votes
1answer
124 views

How to refer to 'this' cell in a conditional formatting formula?

For a field I have conditional formatting with custom formula: =today()>C8+14 and =today()>C8+30 with different styling, basically I want to have a visual styling to highlight older and oldest ...
4
votes
1answer
27 views

Highlighting the maximal value of a column with conditional formatting

I have a simple column of numbers here (A2 to A10) in which I want to highlight the maximum value in that column. Instinctively I went to conditional formatting and learnt that the formula for it was ...
2
votes
1answer
32 views

Why can't I refer to another cell with the value comparator field?

This is more a curiosity question than a fix to a problem, because I found a few ways to fix it already. From what I could gather, if I want to apply a conditional formatting based on the value of ...
3
votes
1answer
54 views

Conditional formatting a range based on the value of a single cell inside of it

I'm trying to format a range based on the value of a single cell inside. So if I have a range like in this image: I am coloring based on the teams. If I was to rearrange the teams, how can I use ...
2
votes
1answer
39 views

Conditional formatting based on portion of text

Is there a better way to specify conditional formatting based on another cell? For example, I have specified a Custom formula as the following (which works): =or($A:$A="bar foo", $A:$A="baz foo") ...
5
votes
1answer
809 views

Conditional formatting with multiple criteria for a range of cells

I'm trying to use conditional formatting to highlight a cell if it's value is not empty, AND if it's value is not equal to -------. I've tried every normal formula I use to check if a cell is empty ...
1
vote
1answer
39 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 ...
2
votes
1answer
51 views

Conditional format formula to select a previous cells content (current cell -1)

Thanks to "Can Google Spreadsheets conditionally format based on a previous cells content?", I understand how to apply conditional formatting to a preceding cell, if I'm using a simple comparison, but ...
2
votes
1answer
52 views

Can Google Spreadsheets conditionally format based on a previous cells content?

Is it possible to conditionally format the background colour of a cell, based on the value in the column preceding it? For example, in the following table (first row/column are titles): | A | B | ...
1
vote
1answer
26 views

Conditional formatting over wide area of cells, referencing other cells

As data validation does not seem to be able to reference cells for numerical conditions (instead only being able to hardcode numbers); I have fallen back on conditional formatting. However, it does ...
1
vote
1answer
175 views

Apply conditional formatting if other cell is not empty

2 Columns. C2 | D2 C3 | D3 C4 | D4 C5 | D5 I want it to apply conditional formatting to D2 if C2 has information in it, and D2 is blank.( will be a date). If C2 is empty, then D2 should have no ...
2
votes
1answer
631 views

Conditional format rule “Text Contains” with a “+” misinterpreted as a formula

I'm trying to conditioan format a bunch of cells that are strings by having cells with a - red and cells with a + green. However when just putting a + o - in the conditional formatting rules it says ...
0
votes
1answer
99 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
32 views

Highlight a cell if the date in one column is the latest date

I'm trying to figure out the proper conditional formatting formula to hilight a cell based on the date in that cell (and/or an adjascent one) representing the most recent date in the column. Given ...
3
votes
1answer
86 views

See if a cell contains a string not from a list of strings in another column

In Google Sheets I have two columns, A and B, and want to know when a cell in column A contains a word that is not in column B. Column A contains cells with multiple words per cell, comma separated: ...
2
votes
1answer
150 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 ...
1
vote
1answer
35 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?
3
votes
1answer
74 views

Format a cell in bold if its row has other nonempty cells

I'm looking to format a Google Spreadsheet with something beyond the "basic" Conditional Formatting they offer. From what I understand, this must be done using a Google Apps Script. I've found one ...
1
vote
2answers
309 views

Google Sheets - Conditional Formatting with IF and THEN

I would like to create a daily dashboard with cells that show the progress of my work regarding to a date. For example: A1 is a text saying "Open". C1 is a cell that contains a date. This date ...
3
votes
1answer
179 views

Specify a range of text for Google Sheets Conditional Formatting

I would like Google Sheets to look at a column and highlight a cell red if it contains any of the letters A through M. My current solution is using a Custom Formula that looks like this ...
2
votes
1answer
126 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 ...
2
votes
1answer
314 views

Google Sheets: Asterisk character (no wildcard) on conditional formatting

I have a sheet with following values on column A: [ A ] [1] Dead line [2] 15 days remaining*** [3] Dead [4] 131 days remaining* [5] 80 days remaining** I would like to use ...
3
votes
1answer
236 views

Color every other row in filter view

In Google Spreadsheets, I can use conditional formatting to color every other row in a table a certain color to make looking at wide rows easier. My problem is that I also want to use filter views so ...
0
votes
0answers
23 views

Set color if cell value is element of another sheets row [duplicate]

I am trying to figure this out since a while now. I have got it working if everything is in the same sheet! It goes like this: Select row K -> conditional formatting -> Custom formula is ...
2
votes
1answer
51 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 ...
1
vote
1answer
31 views

Formatting cells based on upcoming date in Google sheets

I am trying to format a cell to be highlighted red if the following conditions are true: the future date associated with that row (which is in a separate cell) is less than 21 days from today, and ...
1
vote
1answer
46 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?
2
votes
1answer
183 views

Highlight all cells in a range that contain a formula in Google Sheets

I am looking to apply conditional formatting to all the cells in my sheet and highlight cells that contain a formula. I know that I can format cells based on a custom formula, and I know that I can ...
5
votes
1answer
109 views

Conditional formatting of a row header based on the values in the column

I have a table I'm using to list recipes and their ingredients. Ingredients are rows, recipes are columns, with a column next to the ingredients denoting if that ingredient is in stock. Like so: ...
2
votes
1answer
595 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 it's number, and it's compared to the total calories for the day (J3). ...
2
votes
1answer
210 views

Google Sheets: Format number as Mhz/kHz, or MB, kB etc

I want to make a spreadsheet with Google Sheets to compare microprocessor's specifications. One column will show processor speed, another will show program space, another for RAM space and so on. ...
1
vote
1answer
222 views

Cell formatting in Google Spreadsheets does not get preserved

My Google Spreadsheet does not preserve some formatting. Every time I set, for example font size to 12, for the whole sheet, newly copied data uses a smaller font size. This only started being an ...
4
votes
1answer
89 views

Multi-column Conditional Formatting and Row Export in Google Sheets

I need to (a) indicate and (b) export several columns of data into a separate sheet, but only the rows that satisfy a certain condition: three columns (which are not exported) must be after today's ...
3
votes
1answer
108 views

Conditional formatting based on another cell

I use Google Sheets to schedule office appointments. I have set conditional formatting so that if the appointment time slot cells are empty, they are highlighted yellow. (This makes it easier to find ...