Questions tagged [conditional-formatting]
For questions about conditional formatting in spreadsheets.
497
questions
-1
votes
0
answers
13
views
Automatically Format & Combine Cells [duplicate]
I have a pretty specific need and I’ve had a lot of trouble figuring how even to get started.
I have several columns of text. My goal is to combine them into one cell (per row), such that:
The first ...
- 1
0
votes
0
answers
11
views
Data validation with 2 conditions [migrated]
I am looking for a conditional formatting formula that validates two conditions from another sheet.
I have Sheet1 and Sheet2: Sheet2 has daily records of workers such as time in and off, Sheet2 ...
1
vote
1
answer
42
views
Conditional formatting based on frequency
I have a range (A1:L200) that has only numerical values (0-37).
I have a formula that shows the frequency that each value appears:
=FREQUENCY(A1:L200, N1:N38)
How can I use this formula or something ...
- 11
1
vote
0
answers
12
views
How to condition a cell on the response of multiple dropdown cells [closed]
I have multiple drop down cells relating to space available : cell 1 : length, cell 2 : width and a third one related to flateness.
I would like the options to make a cell green or red depending on ...
- 11
0
votes
1
answer
44
views
Google Sheets Conditional Format to highlight specific pieces of text within a cell
Is it possible to condition format so that sheets will format specific pieces of text within a cell instead of applying formatting to the entire cell. Is this an option in sheets yet?
e.g If cell ...
- 345
1
vote
2
answers
57
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
21
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
1
answer
51
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,468
0
votes
2
answers
35
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
38
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
47
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
44
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
86
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 ...
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
99
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
625
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 ...
- 345
0
votes
1
answer
91
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
79
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
222
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
57
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
53
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
19
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
22
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
151
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
43
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
32
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
182
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
178
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
56
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
28
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
10
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
658
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 ...
- 181
0
votes
1
answer
66
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 ...
- 181
0
votes
0
answers
129
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
57
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 ...
- 528
1
vote
1
answer
24
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 ...
- 181
0
votes
0
answers
59
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
123
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 ...
- 181
1
vote
1
answer
43
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
1k
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
45
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
263
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
99
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
504
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
113
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
43
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
16
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