Google Sheets Conditional Formatting Rules 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?

Accepted Answer

=$C2="Complete"

Answer

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?

Accepted Answer

Red, because it is the first rule in the list that evaluates to true.

Answer

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?

Accepted Answer

To apply a two or three-color gradient across a range of cells, visualizing the relative value of each cell within the range.

Answer

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?

Accepted Answer

=AND(ISBLANK(D2)=FALSE, D2<=TODAY())

Answer

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?

Accepted Answer

Is a valid URL

Answer

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.

Google Sheets Test Practice Test

Google Sheets Test Practice Test

Google Sheets Conditional Formatting Rules Questions and Answers