Questions tagged [conditional-formatting]
For questions about conditional formatting in spreadsheets.
497
questions
74
votes
8
answers
140k
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
...
- 2,205
37
votes
5
answers
66k
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 ...
- 481
27
votes
3
answers
49k
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 ...
- 371
22
votes
6
answers
127k
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 ...
- 633
21
votes
1
answer
153k
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 ...
- 273
19
votes
1
answer
11k
views
Conditional format rule "Text Contains" with a "+" misinterpreted as a formula
I'm trying to conditionally format a bunch of cells containing strings. If the string contains either a - or a +, the cell should be highlighted in red or green, respectively. However, when just ...
- 1,209
18
votes
3
answers
29k
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.
...
- 183
17
votes
6
answers
92k
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 ...
- 323
15
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 ...
- 1,209
14
votes
2
answers
10k
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 ...
- 143
13
votes
3
answers
18k
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 ...
- 428
12
votes
2
answers
82k
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 ...
- 221
12
votes
3
answers
28k
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.
TZPike05
12
votes
5
answers
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 ...
- 305
11
votes
4
answers
62k
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?
- 111
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
...
- 109
9
votes
1
answer
38k
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 ...
- 837
9
votes
1
answer
4k
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 ...
- 353
8
votes
5
answers
53k
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 ...
- 235
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 (...
Chani
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.
...
- 439
7
votes
2
answers
12k
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 ...
- 192
7
votes
3
answers
16k
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....
- 173
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 ...
- 1,052
6
votes
3
answers
16k
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 ...
- 191
6
votes
2
answers
8k
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)...
- 165
6
votes
1
answer
78
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 =...
- 381
6
votes
1
answer
20k
views
Is there a way to conditionally format border styles in Google Sheets? [duplicate]
The title says it all.
I have some conditional formatting rules, it seems I'm limited to some basic formatting options;
Is there any workaround to apply further formatting options (such as border)?
- 218
6
votes
4
answers
31k
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.
- 416
6
votes
5
answers
33k
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 ...
- 343
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 ...
- 153
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 ...
- 163
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 ...
- 648
5
votes
1
answer
8k
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())) ...
- 99
5
votes
1
answer
525
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
1
answer
1k
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 ...
- 151
5
votes
1
answer
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 ...
- 51
5
votes
2
answers
5k
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 ...
- 71
4
votes
2
answers
12k
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 ...
- 87
4
votes
2
answers
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)...
- 279
4
votes
1
answer
2k
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=...
- 163
4
votes
1
answer
3k
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 ...
- 197
4
votes
1
answer
460
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
2
answers
1k
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 ...
- 197
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 ...
- 263
4
votes
1
answer
7k
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('...
- 381
4
votes
2
answers
4k
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 ...
- 1,415
4
votes
1
answer
6k
views
Google Sheet formula on finding smallest value larger than 0 does not work inside conditional formatting
I wanted to find and highlight the smallest value larger than 0, but the formula I am using does not work in the conditional formatting.
=MIN(FILTER(L4:L100, (L4:L100)>0))
Namely, if I use it ...
- 616
4
votes
2
answers
1k
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 ...
- 183
4
votes
2
answers
427
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 ...
- 41