Questions tagged [conditional-formatting]

For questions about conditional formatting in spreadsheets.

Filter by
Sorted by
Tagged with
74 votes
8 answers
141k 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 ...
Robert Koritnik's user avatar
7 votes
3 answers
17k views

Colorize a cell in Google Sheets based on cell data

I'd like to colorize a cell based on its contents. 0 = red, 100 = green, and linearly interpolated between. function LinInt(x){ var ss = SpreadsheetApp.getActiveSheet(); var cell = ss....
John Moffitt's user avatar
4 votes
2 answers
13k views

Conditionally format row borders

I'd like to have a medium black top border across each row whose A column cell contains text in a Google Spreadsheet. I'd also like to have the same top border across each row whose whole row is ...
gills's user avatar
  • 192
13 votes
3 answers
19k 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 ...
Ryan Kohn's user avatar
  • 428
17 votes
6 answers
93k views

Advanced date logic in Google Spreadsheets

I have the following 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 ...
Andrew Davis's user avatar
12 votes
3 answers
29k views

Automatically add borders around nonempty cells

I'm looking to have cell borders automatically applied when a cell is not empty. I thought I could use conditional formatting for this, but it doesn't seem to be an option.
user avatar
2 votes
1 answer
2k views

Conditional formatting across sheets

I have two sheets, SheetA for values, SheetB for calculations. I want a cell in SheetA to be colored, if a value in SheetB satisfies a condition. Normally, referencing a field from another sheet is ...
stefan's user avatar
  • 123
1 vote
1 answer
673 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 ...
Geoff's user avatar
  • 81
20 votes
3 answers
31k 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. ...
Elizabeth's user avatar
  • 203
16 votes
2 answers
51k 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 ...
Douglas Gaskell's user avatar
7 votes
1 answer
6k 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 ...
Joshua Dance's user avatar
  • 1,062
5 votes
1 answer
6k views

Conditionally formatting the minimum value in a row

My goal is to apply conditional formatting to the minimum value in a row of a Google Spreadsheets. Under Conditional Formatting I've tried using "Custom Formula Is" =MIN(D3:L3). The result was ...
rainbowsorbet's user avatar
5 votes
1 answer
6k views

How to apply a relative reference in conditional formatting formula?

I have a whole bunch of cells (28 so far) that need conditional formatting. Here is what the data looks like: I want the colours in the top "Last Period" metric cells to vary depending on the values ...
Drewdavid's user avatar
  • 668
5 votes
1 answer
5k views

Using setBackgroundRGB() over setBackgroundColors()

I have a GSheet that is using IMPORTRANGE() to import data from some other sheet. On this destination sheet I want to color each row based on the value of column A. Because the source dataset may ...
hoss's user avatar
  • 163
4 votes
1 answer
2k views

Date Formatting for Deadlines

I have set up a Google spreadsheet for managing projects and due dates, and I need to apply the following formatting to a column of dates, but I'm not sure how: Dates more than 7 days from today: ...
hausaj's user avatar
  • 41
4 votes
1 answer
2k 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 ...
DeadEli's user avatar
  • 273
2 votes
1 answer
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 ...
Baylee's user avatar
  • 21
2 votes
1 answer
440 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 ...
Linkyu's user avatar
  • 271
1 vote
2 answers
4k views

Relative copy conditional formatting that references other worksheet

I have a group of worksheets, one of which has 'default' values while the others should only have values if they differs from the default worksheet. To facilitate this, I want to use conditional ...
Seanonymous's user avatar
1 vote
1 answer
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 ...
Jordan's user avatar
  • 11
0 votes
1 answer
6k 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
user214401's user avatar
27 votes
3 answers
51k 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 ...
Thomson256's user avatar
11 votes
4 answers
65k views

Conditional formatting based on number of characters

In Google Spreadsheets, is there a way to conditionally format a cell if it has more than 30 characters?
David Ben Ari's user avatar
10 votes
2 answers
10k 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 ...
David's user avatar
  • 109
8 votes
3 answers
6k 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. ...
Jodes's user avatar
  • 439
8 votes
3 answers
69k views

How do I apply formatting to a cell based on comparison with another cell?

I'm working with Google's spreadsheets to keep a record of how many dragons of each breed/gender I have in a game I play. I have five columns: A (Breed) | B (Male) | C (Male Goal) | D (Female) | E (...
user avatar
6 votes
2 answers
9k 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)...
AivanF.'s user avatar
  • 165
4 votes
2 answers
3k views

How to Apply Conditional Formatting Based on Adjacent Cell Value for Entire Column?

I am looking to apply conditional formatting to all cells in a column (excluding the header) where a colour will be applied depending on if its value is greater, equal, or less than the adjacent cell. ...
gpresland's user avatar
  • 143
3 votes
2 answers
2k 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 ...
Kula's user avatar
  • 31
3 votes
2 answers
63 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.
Lauren Pettiette's user avatar
3 votes
1 answer
250 views

How can we retain authorship when we copy/paste a comment?

We have comments in a Google Spreadsheet that were not originally written by us. The cells the comments are written in already have "Conditional format rules" applied to them (e.g., cell H2). We ...
marikamitsos's user avatar
  • 12.8k
3 votes
1 answer
3k 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") ...
skube's user avatar
  • 279
3 votes
2 answers
7k 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 ...
Adam Reed's user avatar
2 votes
1 answer
19k views

Copying Cell Color from Conditional Formatting

I have a spreadsheet that currently conditionally formats some cells, changing the background color. I am trying to copy/paste these cells and preserve the color of the background without having to ...
genevieve's user avatar
2 votes
1 answer
1k 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 ...
Krista K's user avatar
  • 203
2 votes
2 answers
7k views

Conditional formatting for cells that have date/time within the last 24 hours

I have a Google sheet that has a column for "last updated" and each row represents a separate file. People manually paste in the time and date in that column, like in the screenshot. If the date and ...
Candice Overcash's user avatar
2 votes
1 answer
4k 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 ...
Jon Hadley's user avatar
2 votes
1 answer
320 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 | ...
Jon Hadley's user avatar
2 votes
2 answers
6k views

Google sheets conditional formatting relative to another cell in the same column

I have column B with different values on different rows and I want to set a rule that , for example cell b3 turns red if the value is smaller than that of B2 and green if it's bigger. I know I need to ...
WonderfulWanderings's user avatar
1 vote
1 answer
45 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?
Lizzie C's user avatar
1 vote
1 answer
132 views

How do I conduct conditional data validation multiple times so that each input prompts a different conditional validation outcome

I am a member of a project that does electricity monitoring, and I am trying to organize our contact sheet to indicate the status of our electricity monitoring across locations. I want to make the ...
Alexander's user avatar
1 vote
2 answers
451 views

Transferring an Excel document to G Sheets, how do I fix the slightly broken auto-borders implementation?

I'd like to transfer this Excel spreadsheet to Google Sheets, maintaining the automatic background formatting functionality, particularly in the case of the borders. Here is the same spreadsheet ...
gills's user avatar
  • 192
1 vote
1 answer
20 views

Highlight specific word "nottingham" in a cell range - Need formula

I need help highlighting a specific word from G7 to G31. The conditional formatting needs to create a yellow highlight when the First word in the subdivision of rows 7-31 matches the first word of the ...
Aeri's user avatar
  • 15
1 vote
1 answer
146 views

How to automatically fill, sort and conditionally format 1st day of the week according to given year and month

How can I fill-in column A, make it start with the day's name according to the year in A2 and month in A1 and conditionally format them? https://docs.google.com/spreadsheets/d/...
Hassan Almaateeq's user avatar
1 vote
1 answer
4k views

Conditional Formatting based on another sheet's content

I am trying to set a conditional format in Google spreadsheets and I am having some difficulty. I am not sure if I should use a cell reference or if formula. In my first Sheet called "Roster" there ...
Elli Reyes's user avatar
1 vote
1 answer
2k 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?
Psalm's user avatar
  • 11
1 vote
1 answer
5k views

Format cells if Date within last 30 days?

Currently hardcoding the date to apply conditional formatting. Is there a way to do this based on a variable range of the last 30 days?
orschiro's user avatar
  • 5,488
1 vote
2 answers
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 ...
Fabio Arruda's user avatar
1 vote
1 answer
831 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 ...
Owen Covill's user avatar
1 vote
2 answers
2k 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 ...
Sharkfin's user avatar