Google Sheets Conditional Formatting Rules Questions and Answers 1 — Questions and Answers
Question 1: A project manager has a sheet with project statuses in column C ('Not Started', 'In Progress', 'Complete'). They want to highlight the entire project row (A:E) in green as soon as the status in column C is changed to 'Complete'. Which custom formula should be applied to the range A2:E100 to achieve this?
- =$C2="Complete" (Correct answer)
- =C2="Complete"
- =$C$2="Complete"
- =C:C="Complete"
Correct answer: =$C2="Complete"
To format an entire row based on the value of a cell in that row, you must use a custom formula with a mixed reference. The dollar sign '$' before the column letter ('$C') locks the column, ensuring that every cell in the row (from A to E) checks the value in column C. The row number ('2') is left relative so that as the rule is applied down the range, it checks C3, C4, C5, and so on for each respective row.
Question 2: A user applies two conditional formatting rules to the same range of cells (A2:A50). The first rule in the list makes cells with values greater than 100 red. The second rule in the list makes cells with values greater than 150 blue. If cell A10 contains the value 175, what will its background color be?
- A mix of red and blue.
- Blue, because its condition is more specific.
- Red, because it is the first rule in the list that evaluates to true. (Correct answer)
- The formatting will be invalid due to the conflict and the cell will have no background color.
Correct answer: Red, because it is the first rule in the list that evaluates to true.
In Google Sheets, conditional formatting rules are evaluated in the order they appear in the list. Once a rule's condition is met for a given cell, that rule's formatting is applied, and subsequent rules are not evaluated for that cell. Since 175 is greater than 100, the first rule is met, and the cell is formatted as red.
Question 3: What is the primary purpose of the 'Color scale' conditional formatting rule in Google Sheets?
- To apply a specific color only to the minimum and maximum values in a range.
- To apply a two or three-color gradient across a range of cells, visualizing the relative value of each cell within the range. (Correct answer)
- To highlight cells that match a specific text value with a corresponding color.
- To create a data validation dropdown list with colored items.
Correct answer: To apply a two or three-color gradient across a range of cells, visualizing the relative value of each cell within the range.
The 'Color scale' rule type is designed to apply a color gradient to a range of data. It helps in visualizing data distribution, similar to a heat map, where colors represent the value's position between the specified minimum, midpoint, and maximum points.
Question 4: You are tracking task due dates in column D. You want to highlight any task that is overdue (the due date is before today) or is due today. Which custom formula will correctly accomplish this for a range starting at D2?
- =D2>TODAY()
- =D2=TODAY()-1
- =AND(ISBLANK(D2)=FALSE, D2<=TODAY()) (Correct answer)
- =D2<NOW()
Correct answer: =AND(ISBLANK(D2)=FALSE, D2<=TODAY())
The formula `=AND(ISBLANK(D2)=FALSE, D2<=TODAY())` is the most robust option. `D2<=TODAY()` correctly identifies dates that are in the past or are the current date. The `AND(ISBLANK(D2)=FALSE, ...)` part is crucial to prevent the rule from incorrectly formatting empty cells, which Google Sheets can evaluate as being less than today's date.
Question 5: Which of the following is NOT a standard, built-in condition found in the 'Format cells if...' dropdown menu for conditional formatting?
- Text contains
- Is between
- Is a valid URL (Correct answer)
- Date is after
Correct answer: Is a valid URL
While Google Sheets has many built-in conditions for text, numbers, and dates (such as 'Text contains', 'Is between', and 'Date is after'), 'Is a valid URL' is a condition found within Data Validation, not as a preset rule for Conditional Formatting.
Question 6: A user wants to highlight all cells in the range B2:E20 that contain duplicate values within that same range. Which custom formula should they apply?
- =COUNTIF(B:E, B2)>1
- =COUNTIF($B$2:$E$20, B2)>1 (Correct answer)
- =DUPLICATE(B2:E20)
- =IF(B2=B1, TRUE, FALSE)
Correct answer: =COUNTIF($B$2:$E$20, B2)>1
The formula `=COUNTIF($B$2:$E$20, B2)>1` correctly identifies duplicates. The `COUNTIF` function counts how many times the value of the current cell (B2, which changes for each cell in the range) appears within the entire fixed range (`$B$2:$E$20`). If the count is greater than 1, it means the value is a duplicate, and the formula returns TRUE, triggering the format.
Google Sheets Conditional Formatting Rules Questions and Answers 1
A project manager has a sheet with project statuses in column C ('Not Started', 'In Progress', 'Complete').
They want to highlight the entire project row (A:E) in green as soon as the status in column C is changed to 'Complete'.
Which custom formula should be applied to the range A2:E100 to achieve this?