Questions tagged [conditional-formatting]

For questions about conditional formatting in spreadsheets.

Filter by
Sorted by
Tagged with
74
votes
9answers
138k 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 ...
31
votes
5answers
52k views

Conditionally format by day of the week

How can I conditionally format cells in Google spreadsheets containing dates to have a different background according to the day of the week. For example, if a date falls on Saturday or Sunday, set ...
21
votes
3answers
35k 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 ...
18
votes
6answers
86k views

How to create a checkbox column, that if selected will create a strike-through across the entire row in Google Sheets?

I have a Google Sheets. I want to set it up so that the first column is checkboxes, and if the checkbox is selected, the entire row gets a strike-through. How can I accomplish this? I followed this ...
16
votes
1answer
100k 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 ...
16
votes
1answer
8k 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 ...
15
votes
6answers
81k 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 ...
15
votes
2answers
49k 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 ...
13
votes
2answers
10k 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 ...
12
votes
5answers
59k views

Conditionally formatting a cell based on another cell's value in Google Spreadsheet

I did some searching on this and came across this post. However, this solution uses an onEdit() trigger. I would like to see a solution that uses a formula approach, so in the OP's example from that ...
10
votes
2answers
9k 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 ...
9
votes
1answer
27k views

How do I make a cell highlighted if the date in the cell is today? [duplicate]

Possible Duplicate: Advanced date logic in Google Spreadsheets I have a spreadsheet with many consecutive days. So Cell1 = 7/30/2012, Cell2 = 7/31/2012 etc. I want Cell1 to be highlighted if ...
9
votes
4answers
44k 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?
8
votes
5answers
48k views

Conditional formatting color gradient

A few friends and myself have created a Google Spreadsheet where we are keeping track of virtual item sales. One column has costs, one column has income one has profit, and a fourth has a running ...
8
votes
3answers
67k 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 (...
8
votes
2answers
16k 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. ...
8
votes
2answers
5k 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 ...
8
votes
3answers
19k 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.
7
votes
2answers
50k views

How do I make the “Text contains” evaluate multiple values in Google Spreadsheet?

I want the cell to be formatted if the cell contains any of the following words: blue, orange, red, green, and white. I also want to not make 5 different commands for each (this is because I have a ...
7
votes
1answer
3k views

How to refer to a cell in the same column at given row?

I would like to get a cell in a particular row above the current cell. This is to be used in conditional formatting, so I can't specify a particular cell, e.g. A$4—the reference would be the same for ...
7
votes
3answers
14k 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....
7
votes
3answers
4k 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. ...
7
votes
1answer
4k 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 ...
6
votes
1answer
73 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 =...
5
votes
3answers
10k 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 ...
5
votes
2answers
8k views

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 ...
5
votes
1answer
5k 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 ...
5
votes
1answer
4k 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 ...
5
votes
3answers
28k 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.
5
votes
1answer
445 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: ...
5
votes
1answer
528 views

Use non-linear color scale for conditional formatting in Google Sheets

In Google Sheets, when you use conditional formatting on a column with numeric values, is there a way to place a colored scale that is non-linear, but exponential, for example? I know you could ...
5
votes
1answer
2k views

Color Shading with Google Spreadsheets

It was easy to do in Excel, but I am having trouble following some of the scripts written on here. Basically we have 3 colors we use, Green, Yellow and Red. 1 = Green, 2 = Yellow and 3 = Red. What I ...
5
votes
2answers
4k views

How to nest IMPORTRANGE into the Query function in New Google Sheets

We have applicants who come in and fill out a form via Google Forms. I have fields that auto-populate from the Response tab into a separate tab that is organized to fit our trackers. I am looking for ...
5
votes
5answers
9k 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". In other words, I want to highlight them so that they can be copied, formatted, etc. How ...
4
votes
2answers
4k views

Highlight minimum value in each row, ignoring zeros and skipping columns

I'm trying to highlight the smallest value in each row using conditional formatting (CF), while ignoring zeros and blanks. Also, I don't want the ranges to be continuous (i.e. skipping certain columns)...
4
votes
1answer
1k views

Using MAX in conditional formatting only evaluates numbers after the cell, regardless of range

I would like to highlight the maximum numerical value in a particular column. Take a look at this sheet: https://docs.google.com/spreadsheets/d/107c-9pET6SA1APwtGIJh1Qxi__SOXAHGtaGVGd5Nw6g/edit?usp=...
4
votes
1answer
2k views

Applying Conditional Formatting Color Scale to non-numerical cells

My column A is numerical, and I can apply conditional formatting color scales. However, I also want column B to be coloured according to the scale. The scale formatting does not accept formulas and ...
4
votes
1answer
421 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 ...
4
votes
1answer
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 ...
4
votes
2answers
904 views

Conditional format for column headers where all cells are numbers

I'm trying to color the header if all of the cells in that column are numbers. Blank or non-numbers should break this. I have: custom formula: =ISNUMBER(A3:A) But it styles the cells instead. Not ...
4
votes
1answer
1k 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 ...
4
votes
1answer
6k views

Changing row colour if value set?

I'm trying to use the following script to change the row colour if a value in column N is set: function onEdit() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName('...
4
votes
2answers
3k views

Google Spreadsheet: Use cell referencing in “Change Color Based on Rules”

In "Change Color Based on Rules", one can specify the text color to be any color as long as certain rules are followed: My question is, instead of hard-coding the values, can I use cell reference? In ...
4
votes
2answers
417 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 ...
4
votes
1answer
7k views

How do I check if a cell is blank for Conditional formatting?

In my exercise tracking spreadsheet, I want blank cells in columns B:O highlighted if column A is today's date, and the cell itself is blank. I have tried this: =AND(ISBLANK(B3),$A:A=INT(NOW())) ...
4
votes
1answer
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: ...
4
votes
2answers
968 views

Coloring cells according to the value to which its content is closest [closed]

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 ...
4
votes
2answers
782 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 ...
4
votes
2answers
748 views

Google Spreadsheets Conditional Formatting with color

I want to add a column of cells together only if the particular cell is colored green. Can I do this using conditional formatting on a google spreadsheet?
3
votes
3answers
86 views

Find or conditional format a column of phone numbers NOT in desired format

I have a column of phone numbers in that are in different formats. I want them all to be in this format 0000 000 000. What I do now is manually edit each wrong number into the correct format. How can ...

1
2 3 4 5
8