Questions tagged [conditional-formatting]

For questions about conditional formatting in spreadsheets.

Filter by
Sorted by
Tagged with
0 votes
1 answer
42 views

What is wrong with my Conditional Formatting Formula?

I want to change the text color to Red for cells in range A3:A25 by comparing the corresponding values in the worksheet named 'Stock Master'. i.e., if the corresponding value (reference present in ...
Seshadev Panda's user avatar
1 vote
1 answer
23 views

Conditional format google sheets cells based on preceding cells

I made a google sheet to track some daily goals of mine, such as doing Duolingo or having good posture. The first column is the task, the following are daily grades with some notes, so it looks sort ...
Conditonal's user avatar
0 votes
0 answers
23 views

Google Sheet scripts to 'only allow Paste as Value' or 'lock Conditional Formatting rules'

Few of us share a Google Sheet file and work on it concurrently. The cells in the Google Sheet worksheet have built-in conditional formatting logic to help keep our workflows efficient. However, often,...
ŠüR0's user avatar
  • 23
0 votes
1 answer
22 views

Conditional formatting on individual rows in range

I want conditional formatting with color scale to easily highlight values from lowest to highest within a row. However, I have many rows. If I go for range A1:X50, the formatting will only be based on ...
Morphy's user avatar
  • 3
0 votes
0 answers
31 views

Can I use conditional formatting in google sheets to force cells to merge given a specific value [duplicate]

I am working on formatting a cash log for work (work in a restaurant and right now we record this by hand) the problem because each days entry isn’t uniform as the number of people working changes so ...
Rcyr1010's user avatar
1 vote
0 answers
85 views

Google Sheets - conditional formatting between cells for a whole column? [duplicate]

I want to highlight cells in column B that are different from cell on the same row in column A A B ABC ABC DEF GHI GHI GHI GHI in column B, in the DEF-row should be highlighted. The table ...
d-b's user avatar
  • 1
-1 votes
1 answer
29 views

Highlight cells between two numbers using conditional formatting

How can I apply conditional formatting in Google Sheets that will highlight cells with values between 50 and 100? The built-in options aren't quite cutting it. Is there a custom formula that I can use?...
user avatar
1 vote
1 answer
84 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 ...
Andrew Wade's user avatar
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 ...
Julien's user avatar
  • 11
0 votes
1 answer
143 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 ...
Genny's user avatar
  • 345
1 vote
2 answers
76 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 ...
Patrick Hennessey's user avatar
0 votes
1 answer
25 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. ...
Alex Savin's user avatar
0 votes
1 answer
105 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 ...
orschiro's user avatar
  • 5,488
0 votes
2 answers
37 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 ...
Kory Russell's user avatar
0 votes
1 answer
41 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 ...
Alice's user avatar
  • 9
1 vote
1 answer
26 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. ...
Kyle O'Brien's user avatar
-1 votes
1 answer
74 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 ...
Tanisha's user avatar
1 vote
2 answers
54 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(...
Matt's user avatar
  • 416
-1 votes
2 answers
124 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 ...
Web App User's user avatar
0 votes
0 answers
23 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 ...
Bruno Ferreira's user avatar
0 votes
1 answer
161 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 ...
Siebe Albers's user avatar
1 vote
2 answers
970 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 ...
Genny's user avatar
  • 345
0 votes
1 answer
149 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 ...
dansumption's user avatar
-1 votes
1 answer
140 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 ...
Miha's user avatar
  • 1
0 votes
1 answer
386 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 ...
Michael Fischer's user avatar
1 vote
1 answer
70 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, ...
Bob Whitelock's user avatar
1 vote
1 answer
81 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 ...
Adam's user avatar
  • 449
0 votes
1 answer
24 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 ...
Jason Patton's user avatar
0 votes
1 answer
25 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 ...
Rachel Mikel's user avatar
0 votes
1 answer
222 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 (...
Aeri's user avatar
  • 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 ...
Aeri's user avatar
  • 15
0 votes
1 answer
58 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 ...
Jun Palmejar's user avatar
3 votes
1 answer
110 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 ...
Jun Palmejar's user avatar
0 votes
1 answer
33 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 ...
Sheetster's user avatar
2 votes
1 answer
222 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)+(...
ŠüR0's user avatar
  • 23
0 votes
1 answer
60 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 ...
Hugo Where's user avatar
0 votes
0 answers
32 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 ...
Justin's user avatar
  • 13
0 votes
1 answer
12 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 (...
user306724's user avatar
2 votes
1 answer
821 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 ...
Ylahris's user avatar
  • 181
0 votes
1 answer
96 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 ...
Ylahris's user avatar
  • 181
0 votes
0 answers
158 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 ...
Jason's user avatar
  • 9
1 vote
1 answer
77 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 ...
Mr. Boy's user avatar
  • 538
1 vote
1 answer
28 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 ...
Ylahris's user avatar
  • 181
0 votes
0 answers
111 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 ...
kieran's user avatar
  • 1
1 vote
1 answer
25 views

How can I get conditional formatting to work across a row? (Lottery Spreadsheet)

Image here of what I'm trying to do, for a lottery spreadsheet: If a number in the "Numbers Played" row shows up in the "Winning Numbers" section on the right, in the same row, I ...
Savwire's user avatar
  • 11
1 vote
1 answer
189 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 ...
Ylahris's user avatar
  • 181
1 vote
1 answer
49 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 ...
Yoav Mor's user avatar
0 votes
2 answers
2k 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=("...
Viet's user avatar
  • 103
0 votes
1 answer
61 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)...
Will's user avatar
  • 3
1 vote
1 answer
430 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, ...
James's user avatar
  • 11

1
2 3 4 5
11