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

=C2="Complete"

Answer

=$C$2="Complete"

Answer

=C:C="Complete"

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

A mix of red and blue.

Answer

Blue, because its condition is more specific.

Answer

The formatting will be invalid due to the conflict and the cell will have no background color.

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

To apply a specific color only to the minimum and maximum values in a range.

Answer

To highlight cells that match a specific text value with a corresponding color.

Answer

To create a data validation dropdown list with colored items.

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

=D2>TODAY()

Answer

=D2=TODAY()-1

Answer

=D2<NOW()

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

Text contains

Answer

Is between

Answer

Date is after

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?

Accepted Answer

=COUNTIF($B$2:$E$20, B2)>1

Answer

=COUNTIF(B:E, B2)>1

Answer

=DUPLICATE(B2:E20)

Answer

=IF(B2=B1, TRUE, FALSE)