Google Sheets Conditional Formatting Rules Flashcards

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?

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.

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?

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.

What is the primary purpose of the 'Color scale' conditional formatting rule in Google Sheets?

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.

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?

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.

Which of the following is NOT a standard, built-in condition found in the 'Format cells if...' dropdown menu for conditional formatting?

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.

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?

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.