Questions tagged [conditional-formatting]
For questions about conditional formatting in spreadsheets.
493
questions
1
vote
2
answers
50
views
Formula that looks for any matching substrings between two cells
In Google Sheets, I'm trying to compose a formula that compares the substrings in two cells.
Substrings in each cell are separated by a space.
The formula should return TRUE if any substring in one ...
0
votes
1
answer
18
views
Google Sheets complex formula or script is needed
I have trouble with creating a complex conditional formula in Google Sheets. Honestly, I feel there is a need for some script with a "for" cycle, but I'm not sure.
The task is the following. ...
- 103
0
votes
0
answers
37
views
How do I apply conditional formatting to a Data Table Chart in Google Sheets? [closed]
I have nearly the same issue as this question, but with Google Sheets instead of Excel.
I have a few rows of data in a Google Sheet that I want to include (live) in a Google Doc. In the past I've done ...
pacolley
0
votes
1
answer
23
views
Google Sheets: Change text color of multiple cells if checkbox is checked?
I have set up my conditional formatting to change text colors of a range of cells if a checkbox in that row has been checked.
However, this only changes text color of one of the cells.
Can anyone tell ...
- 5,438
0
votes
2
answers
30
views
Creating rule for two cells equaling same value
I'm trying to create a rule that will turn cells A through D red if cells C and D equal 0 or less.
I know how to make the rule with just one cell equaling 0, but would like to make it conditional on ...
0
votes
1
answer
34
views
How do I combine variables of a row and column?
I have dates set up in column A in yearmonthday format (ex. 20210101), types of product in column B (there are 8 that repeat throughout, so the same date is repeated in column A for 8 rows), and hours ...
- 9
1
vote
1
answer
24
views
Workplace scheduling spreadsheet help, find (partial) duplicate in range
I work at the front desk of a tutoring and test prep center whose master schedule is a simple Google Sheets file with the schedule for each of our M-Th and Sat. sessions sharing the same spreadsheet. ...
- 13
-1
votes
1
answer
43
views
Apply the same conditional formatting custom rule to multiple ranges on the same sheet
I have created a weekly tracker on Google Sheets, with the days organized in columns K through AI. Each day has three columns dedicated to tasks, assigned personnel, and notes. I have successfully ...
- 9
1
vote
2
answers
35
views
Is it possible to do a more concise IF x = 1 OR x = 2 OR x = 3 in Google Sheets?
The following is a part of a formula I'm using to filter for week day rows (for conditional formatting):
OR(
MOD(ROW(),7)=2,
MOD(ROW(),7)=3,
MOD(ROW(),7)=4,
MOD(ROW(...
- 416
-1
votes
2
answers
58
views
Conditional formating based on calculated cells [closed]
I have a sheet a sheet which records/calculates achieved Foreign Exchange rates.
There are 3 columns - € Rate; £ Used and € Got. The second two columns are given values according to the activity and ...
- 541
0
votes
0
answers
20
views
Google Sheets - Formula for If Cell A is exactly Option X (from a dropdown menu) then Link to Cell B
I'm trying to write a formula that will paste a link on Cell A (ex: G5) to Cell C (U5 on the same spreadsheet) if Cell B (H5) is exactly Option X (ex: "General Event") from a dropdown menu ...
0
votes
1
answer
53
views
How to copy/paste conditional formatting in Google Sheets (without including the original cells)
When I copy and "paste the conditional formatting only" option from one column to another (e.g. A1:A5 to B1:B5) the result is that for B1:B5 the conditional format range includes A1:A5.
How ...
1
vote
2
answers
346
views
Google Sheets Conditional format when cell contains any word from another cell (partial text match)
I am currently using =regexmatch(A1,"(?i)N02|N22|J25") in column A to highlight the cells that contain these terms.
But I don't want to hardcode it as the search terms keep changing.
The ...
- 343
0
votes
1
answer
53
views
Google Sheets conditional formatting gradient not applied for values >= 1 and < 2
I have a Google sheet where several columns use a gradient (using traffic light colours - from red to yellow to green) to indicate values from min to max.
This works fine for values less than one, and ...
-1
votes
1
answer
37
views
"HOW TO KEEP FORMATTING ON A SPECIFIC CELL/ROW (USING SORT FUNCTION) AFTER NEW DATA IS ENTERED"
I'd really appreciate some help with the document.
I'm using the auto sort (SORT function) on the second sheet and would like to keep the conditional formatting to a certain cell/row after a box is ...
- 1
0
votes
1
answer
125
views
Google Sheets, coloring a line depending on another column (conditional formatting for lines)
Imagine I have a table like the following.
Column A is the x-axis, e.g. time. Column B is some condition, e.g. 1 and 0 for yes and no or true and false. Column C has the y-values.
I'd like to create ...
1
vote
1
answer
43
views
Can I automatically conditionally format entire column with different background colors based on contents? [closed]
A common situation I encounter when working with a sheet is to have data such as this (in CSV format):
-, A, B
1, ColA, ColB
2, one, value1
3, two, value2
4, three, value1
5, four, value2
6, five, ...
1
vote
1
answer
33
views
Google Sheets conditional formatting value where 1 column value lower than other column
My data:
E F
25.00% 11.11%
30 20
10 20
40 40
0 10
20 0
I want to have conditional formatting where:
- if value in column F is lower than E, cell F should be colored ...
- 449
0
votes
1
answer
18
views
Can the formula of a cell be conditional based on the dropdown selection of another cell?
Is there a way to create a formula where D9 is included/excluded from the formula based on the answer to the dropdown selection of D13 (Financing Repairs? Yes/No)?
Spreadsheet
I've spent hours ...
0
votes
1
answer
21
views
Applying custom conditional formatting to additional ranges
I have a sheet where I am applying conditional formatting to certain cells in column B to highlight the highest and lowest values with red and green, respectively. It works for the column in which I ...
0
votes
1
answer
106
views
Highlight cell based on a specific days - DATE - Need formula
If the sold date is more than 180 days ago (compared to today) conditional format red
If the Sold Date is 90 days ago or less (compared to Today) then make green
If the sold date is less than 180 ago (...
- 15
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 ...
- 15
0
votes
1
answer
35
views
How do I check a set of numbers in different columns against numbers in different columns?
I am trying to make a Lotto checklist in Google sheets. I have my own numbers in 6 columns, A2:F5. And I have typed the winning numbers in H2:M2, 6 columns also, with 1000 rows.
Now I want to check if ...
- 57
3
votes
1
answer
107
views
How do I check a set of numbers in different columns against numbers in different columns
I am trying to make a Lotto checklist. I have my own numbers in 6 columns A2:F2, and I have typed the winning numbers in H2:M2, 6 columns also. Now I want to check if my numbers in A2:F2 have any one ...
- 57
0
votes
1
answer
28
views
Strikeout multiple lines and collumns with a checkbox
Is there some sort of conditional formating I could use to do this?
I would like the checkbox in col A to trigger strikeout in col B,C,D but stop before the next name in column B.
I am able to set the ...
2
votes
1
answer
157
views
Highlighting duplicate emails across multiple Google Sheet worksheets
I'm trying to extend user0's beneficial formula to more than 3 sheets, but Google Sheets reports an error in the Conditional Formatting custom formula field
From this:
=(COUNTIF(Q:Q,Q:Q)>1)+(...
- 23
0
votes
0
answers
128
views
How do you import cell fill color from another spreadsheet? [duplicate]
I am currently working on a spreadsheet with 100+ conditional formatting rules on columns, and the formatting rules significantly reduce initial page load speed. I already implemented IMPORTRANGE(), ...
0
votes
1
answer
48
views
Choose top 5 scores from multiple columns with conditions
I am trying to automate a bowling statistics table. Each bowler has multiple games, one column for each.
The problem I am having is that I am using =J1>=LARGE($J$3:$K$22, 4) in conditional ...
0
votes
0
answers
26
views
Conditional formatting for different groups of cells based on progress
I am in the delivery business. Daily, we keep track of the progress of certain routes at certain time intervals. I would like to highlight the cells if a certain % hasn't been reached. I was ...
- 13
0
votes
1
answer
9
views
Conditional Format on Cost deviations
I am trying to find a way for a column to highlight when it is greater than a value pulled from a v look up formula. This is a cost deviations report for my business, so i would like the quoted cost (...
2
votes
1
answer
553
views
Why does my conditional formatting not work with this formula?
Below is a formula I want to use in conditional formatting. Its purpose is to blur a range in a text concatenation table if a checkbox B15 is unchecked and if the left and right of the string matches ...
- 179
0
votes
1
answer
39
views
I want "Text starts with" conditional formatting but only if a checkbox is checked. How can I achieve this?
Stack Exchange! Today I wish to use Google Sheets' conditional formatting option "text starts with" to blur out a range of cells (by changing color of text and background). That is the easy ...
- 179
0
votes
0
answers
87
views
How can I make a bar chart based on conditional formatting of the input data?
[Context]
I have a spreadsheet for my spending so I can keep track of my budget each week. I've used conditional formatting and sometimes manual formatting to colour code each purchase by category. I ...
- 9
1
vote
1
answer
41
views
Why do conditional formatting rules based on date affect the wrong cells?
Here is a Google Sheet for our church rota - publicly viewable: https://docs.google.com/spreadsheets/d/1KODl9N_I3VeO0CKBrU_75GoQ5dBvUuSzx0FinNKf9zs/edit?usp=sharing
I have conditional formatting rules ...
- 508
1
vote
1
answer
22
views
What function would give a TRUE value if either of two conditions are met?
I want conditional formatting to change the cell color of a selected range when the text is exactly one of two possibilities. I have a function which concatenates three cells of text together, and ...
- 179
0
votes
0
answers
29
views
Google Form data not using my preset conditional formatting [duplicate]
I have just made a Google Form and linked it to a Google Sheets, I already went through and set conditional formatting on the sheet but, when someone fills out the Form the data goes to my Sheet but ...
- 1
1
vote
1
answer
81
views
Why is Google Sheets unable to apply conditional formatting to multiple cells of different rows and columns?
I realize there are similar questions to mine, but they have not the answer I seek. I want to use the COUNTIF( L5 , 0 ) function to change the color of a diagonal selection of cells. But Google Sheets ...
- 179
1
vote
1
answer
40
views
Google Sheets, apply conditional formatting based on parts of a cell
What I'm trying to achieve is this - In Google Sheets, I have a cell that contains numbers with a comma delimiter, for example: 0.5,2,50,200,0,4
I want to have 6 conditional formatting rules, working ...
- 13
0
votes
2
answers
814
views
Google sheet Conditional formatting if text is in a list of words
How do I format a sheet so that the cells change according to the value inside a cell of a specific column that matches certain words?
For example:
Apply to range: A:F
Custom formula is: =F:F=("...
- 103
0
votes
1
answer
39
views
Google Sheets - conditional formatting self-referencing
I'm building a calendar document, which has a grid on the left with calendar days (columns A:G), and then a list on the right hand side with details as to what's happening on those days (starting in I)...
- 3
1
vote
1
answer
137
views
Compare multiple columns that are separated, highlighting the Lowest value
I'm trying to set up conditional formatting on multiple columns to compare multiple prices and highlight the lowest value.
On the example sheet below. Row 2, the formula would compare the costs in B2, ...
- 11
1
vote
2
answers
81
views
Alternate colors with masked rows
In Google Sheets, it seems that masking an odd number of rows in a range somehow defeats the alternate color formatting (adjacent rows will occur with identical colors). Is there a workaround using, e....
- 41
1
vote
1
answer
185
views
Applying the same conditional formatting to multiple ranges (multi-range formatting rules)
I'm trying to use Conditional Formatting for a really simple task:
color cells if they have the same value as a specific another cell.
But I'm getting unexpected results when setting the range to ...
1
vote
1
answer
51
views
How to add text conditional on numerical ranges in Google Sheets?
I'm using Google Sheets and I want to display certain text based on the numerical value of another cell. I am a teacher trying to create an assignment where students have to find the best zombie ...
- 11
1
vote
1
answer
40
views
How can I conditionally format any empty cell from column C to L if the respective cell in column B not empty
I need your help with conditional formatting in Gsheet or Excel (I will choose one of them depending on the solution).
This format needs to be: Any empty cell in columns C to L must be highlighted if ...
- 11
1
vote
1
answer
14
views
Sytnax for conditional formatting in Google Sheets
I am trying to write a Google sheet to keep track of a certain financial situation.
I have four columns: one for money 'in', the next for profit, the next for loss, then a final outcome column.
In ...
- 11
1
vote
1
answer
146
views
Include vs. exclude specific cells in array in google sheets
I want to exclude specific rows in array commands in google sheets.
Here is some sample data:
Exercise type
Weight
Unit
Reps
Success?
OHP
40
kg
4
yes
Deadlift
240
kg
2
no
OHP
50
kg
10
yes
Squat
...
- 11
1
vote
1
answer
76
views
Google sheets - Conditional formating between 2 dates in function of real time
Is it possible to make a cell automatically highlighted during a time interval relative to the instant T, in real time.
The cell should only be highlighted when the time comes and it becomes ...
- 53
1
vote
1
answer
53
views
Google sheets - Notification IF a cell is highlighted by 2 conditional formattings
Is there a way to generate an alert or a notification WHEN a cell or some cells are highlighted by 2 or more different conditional formattings?
Making that the upper conditional formating overwrite ...
- 53
1
vote
1
answer
60
views
Google Sheets conditional formatting and sum functions not working for =IFERROR cells
Erik was able to help me out with an "aging" formula in the below post.
Show most recent date a theatre is visited Google Sheets
I've customized his formula to put out how many days old some ...
- 25