Questions tagged [conditional-formatting]
For questions about conditional formatting in spreadsheets.
91
questions with no upvoted or accepted answers
3
votes
3
answers
524
views
Conditional formatting for columns, date & time specific?
I have uploaded 4.000+ Venmo donation data sets into Google Sheets for sorting, but the date and time is preset and looks like this:
2018-11-01T20:16:52
I have tried multiple approaches to sorting ...
- 31
2
votes
2
answers
2k
views
Conditionally format if date is same/greater or smaller than current date
I'm trying to get my Google sheet to automatically colour a cell red if the current date is the same or greater than the date in the cell.
Example: today is 21/02/2020.
The date in the cell is 25/...
- 21
2
votes
1
answer
3k
views
google sheets checkbox conditional formatting
I have a schedule where I use inspector name, job number and then checkboxes to book the inspector on the visit date
What I'm looking to do is if an inspector is booked in a job on lets say jan 3rd ...
2
votes
2
answers
690
views
Conditional Formatting in Google Sheets
The shorter question to spare you from reading more than you need to
I have a few different formulas for use in conditional formatting with custom formula in Google sheets. They are all roughly 1100 ...
- 129
2
votes
1
answer
365
views
Conditional Formatting based on another sheet, tying a value to a colour, Google Sheets
Does anyone know how I'd be able to make a specific value always appear with the same formatting & colour in my sheet, based on another tab in the sheet?
The spreadsheet I'm trying to do this on ...
- 31
2
votes
1
answer
1k
views
Conditional Formatting across a range of cells with a formula in Google Sheets
I have a calendar put into Google Sheets with the data put in as dates such as "9/24/2018".
I have a separate sheet with a data set that has rows that classes end such as "9/24/2018".
I want to ...
1
vote
1
answer
58
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
2
answers
100
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
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
1
vote
1
answer
257
views
Compare Columns, Highlight Missing Values
I am looking to compare two columns, a "Roster" column and a "Present" column and highlight any missing values.
The "Roster" column is a fixed value, but the "...
- 11
1
vote
1
answer
50
views
Google Sheets Line Height issue after form submission
I need my row height in google sheets to stay at 40.
The issue I am having is every time a user submits a form and a new entry is created, the line height goes back to the original formatting.
I ...
- 11
1
vote
1
answer
366
views
Google Sheets: Highlight First Instances within Range of Cells that are Specific Amounts Greater Than a Cell
I have a vehicle data sheet with 3 different columns (L7:N56) to track the odometer reading during services or at the beginning/end of a trip.
I want to be able to check all three columns (L7:N56) ...
alisa
1
vote
1
answer
117
views
Highight cell based on current time
Working on an hourly To-Do list. I have a column (H) with the list hour (on the Hr) starting with 5am all the way until 11pm.
For Example:
5:00 AM
6:00 AM
7:00 AM
8:00 AM
and so on....
I would like ...
- 11
1
vote
1
answer
121
views
How do I conduct conditional data validation multiple times so that each input prompts a different conditional validation outcome
I am a member of a project that does electricity monitoring, and I am trying to organize our contact sheet to indicate the status of our electricity monitoring across locations. I want to make the ...
- 11
1
vote
1
answer
22
views
Conditional Formatting Help
I must be having an off day as I'm usually good with formulas - but I'm trying to finalize something for work and my brain is not getting me there....
I need a formula to color cell G75 Orange if cell ...
- 11
1
vote
1
answer
164
views
Conditional Formatting Not Working On My Machine, But Does On Other Machines!
I hope someone can help me with my current issue. I have an issue that my Google Sheets are not working on my Windows computer, but do work when I use the Sheets with other computers!
I make ...
- 111
1
vote
1
answer
79
views
Find or search in the custom formula used by conditional format
Is there any method for searching formula in conditional formatting? 'Find and replace' menu only can search formula in cells.
I want to remove a range of cells but i don't want to break a conditional ...
- 315
1
vote
1
answer
290
views
Conditional formatting with dates in Google Sheets
I'm trying to add a little functionality in my register to help me catch incorrectly entered dates. I've got a column, A, for dates where no date entered should be earlier than the previously entered ...
1
vote
1
answer
879
views
Why is a conditional formatting range mis-applying some cells?
I have a google sheet with one conditional formatting rule defined:
This is the only formatting rule in the sheet:
As you can see, Q3 is not obeying the rule. And when I test, neither is Q2. At some ...
- 528
1
vote
0
answers
30
views
Google Sheets conditional formatting adapt to value
I'm currently working on a Google Sheet which tracks hourly performance, the layout is basic which is hours against units completed. The conditional format I already have in place works for a time ...
- 11
1
vote
1
answer
52
views
Highlight duplicate based on two columns
I have a spreadsheet of patient data (several thousand records) where I need a way to indicate whether there's a duplicate patient. We know there's a duplicate if the last name and the date of birth ...
1
vote
1
answer
177
views
How can I highlight a column of cells (H) if the date in the cell is more than a week old, and the cell next to it (I) is blank?
I'm trying to make a spread sheet to track open purchase orders. One PO per row. One column has the order date in it, and the next column has the receive date for the order. I'd like the cell with the ...
- 11
1
vote
0
answers
14
views
Is it possible to format row fill colour by a cell in an expandable way? if new job number, new row colour
I keep a to do list.
Each row has a job number.
There are several entries for each job number.
I'd like to ask google sheets to look at the job number in column. Give the row a colour.
Every time a ...
- 11
1
vote
0
answers
169
views
Google Sheets conditional formatting multiple columns based on value relative to 1 column
I have a reference column of values (column A & B) and then all subsequent pairs of columns that get added to sheet, I want to highlight their value in relation to those two. So column C values, ...
- 11
1
vote
0
answers
19
views
Conditional format using the IF function
I'm trying to use a conditional format using the IF function to determine if a value needs a dropdown menu.
Example: if A1=book, then provides a list of authors to choose from; the same if A1=car, ...
1
vote
1
answer
24
views
Google Sheets - conditionally format a cell based on whether the content of the cell appears in a column from a different sheet?
I have a sheet called Master that contains a column of names in cells A1-A60 (e.g. A1: Ann,A2: Bob,A3: Chris, A4: Donna, etc.).
I have another sheet called Subsheet that contains names that may or ...
- 111
1
vote
1
answer
94
views
highlight if value is duplicate and corresponding cell of all other duplicates is blank
I have a list of clients who contacted us. We need to get back to them.
I would like to format if:
They appear twice in the list (got that) and
We have not gotten back to them in any of their ...
1
vote
1
answer
376
views
Conditional Formatting in Google Sheets based on the same rows conditional format
I have a spreadsheet where I have tasks, and I prioritize those tasks based on high, medium, and low levels. I currently use conditional formatting for this, so if I select from a drop-down "HIGH", ...
1
vote
1
answer
188
views
Conditionally format a column if it contains all the values in a set
Each of the columns in my Google Sheet represent a farm. Each farm is a list of animals. I want to highlight the entire column if the farm has a duck, a horse, and a cow. So the column may look like ...
- 111
1
vote
1
answer
79
views
AND / OR Conditional Formatting for Google Sheets with multiple criteria
I am trying to write a custom formula to conditional format column E by referring to the different tenure level targets in column D to indicate if they are hitting their targets base off of their ...
- 31
1
vote
2
answers
499
views
What function can I use to conditionally format text depending on cursor position or active cell?
In Google Sheets, I have a 'Freeze' on Column A.
I want the text in Column A, Row X to Conditionally Format as BOLD RED, while my cursor is on any cell for Row X.
Is there a way to manage that?
- 11
1
vote
1
answer
3k
views
Can I use conditional formatting to highlight a cell for dates less than 30 days away?
I have a large spreadsheet where I need to keep track of coming expiration dates and I would like dates which are within 30 days from today's date OR which have already passed to be highlighted in red....
- 11
1
vote
1
answer
427
views
Google Sheets Conditional Formatting For Dates and a Blank Row
In Google Sheets I am trying to use conditional formatting to change the format of a column of cells if the date in that column it more that 14 days before today, AND if a separate column is blank.
...
1
vote
1
answer
2k
views
How do I conditionally format text color to match across sheets in Google Sheets?
Edit: I couldn't figure out how to post pics without editing, so I added them to the OP. The first photo is the process form. On the form, as the boxes are checked the color changes from black to red ...
- 11
1
vote
1
answer
609
views
Conditional format custom formula in Google Sheets
Have a question on how to do a custom formula in conditional format for the following:
A1 = DCC220
B1 = 7.50
C1 = 7.00
D1 = 7.60
I want to compare the result in B1 (7.50) to a range, C1 lower, D1 ...
- 11
1
vote
1
answer
24
views
Create list by cell color
I have a master list of trading cards. I'd love to be able to use a color coding system. For example, if Green is cards I've already collected, when I highlight the background of the cell green, I ...
- 11
1
vote
2
answers
2k
views
Conditionally format cells that not match with range from Sheet2
I have two sheets in the same file where "sheet1" has cleaned up information and "sheet2" has the dump from my Jira. Now I am trying to compare Column F from sheet1 and sheet2 to ...
- 119
0
votes
1
answer
39
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
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
104
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 ...
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
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
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 (...
0
votes
0
answers
132
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
0
votes
1
answer
37
views
How to edit/append a cell value based on date range?
I have a list of dates (including previous, current, and future weeks) and there is a number associated with each date. Example:
I want to add 'x' in front of all the values belonging to dates from ...
- 1
0
votes
1
answer
345
views
How to us Conditional Formatting with color scale based on data in another cell
need some help with conditional formatting. Take a look at the sheet below. I would like to apply conditional formatting to D20 based on the vales of C20. For example, I want it to be on a color scale ...
0
votes
1
answer
28
views
How to highlight duplicate values when clicking on a specific cell
I'm new to spreadsheets, but trying to put a functional one together for my team.
I have a list of member names, and several lists of tasks they are assigned on different days of the week. The goal is ...
