Skip to content

GS Challenge 4 – Conditional Formatting

Welcome to your 4th Google Sheets challenge!

In this article, I will give you four different questions within Google Sheets to test your spreadsheet knowledge about conditional formatting.

How many questions can you solve? Click the link below to download the practice file, test yourself, and find out!

Google Sheets Challenge File 4

Question 1

For question 1, your task is to use conditional formatting to highlight in yellow any region that includes the text value ‘West.’

Question 1 – Solution

Begin by selecting all of the cells containing a region. With these cells selected, go to Format Conditional Formatting.

In the panel on the right, select Text Contains under ‘format cells if.’

In the formula box, enter West.

Finally, change the fill color to Yellow, and click on Done.

Question 2

For question 2, you must re-create the heat map by adding conditional formatting to the range C6:N13.

Question 2 – Solution

Start by selecting the range C6:N13. Then go to Format Conditional Formatting.

In the panel on the right, select the Color Scale option.

Under ‘preview,’ select the White to Green option. You can then click on Done.

Question 3

For question 3, your goal is to highlight (in green) each actual sales number that exceeds the corresponding target sales number.

Question 3 – Solution

Begin by selecting all of the actual sales numbers. Then go to Format Conditional Formatting.

In the panel on the right, select Greater than under ‘format cells if.’

Enter the following formula into the formula box.

=C4

A relative reference is used in the formula because you want that reference to change for each sales number. In other words, you want the first actual sales number to reference the first target sales number, the second actual sales number to reference the second target sales number, etc.

Also, it is important to note that the equals sign signals that the formula contains a cell reference.

From here, you can click on Done to complete question 3.

Question 4

For question 4, your task is to highlight (in green) the entire row for the sales agent with the highest total sales.

Question 4 – Solution

Start by selecting the range B4:D11, which contains all of the data. Then go to Format Conditional Formatting.

Under ‘format cells if,’ select the custom formula is option.

In the formula box, enter the following formula.

=$D4=MAX($D$4:$D$11)

This formula is a little complicated, so let’s break it down.

The MAX function calculates the maximum value among all of the total sales numbers. An absolute reference is used here because you want this reference to stay the same for each cell this conditional formatting rule applies to.

The $D4 part of the formula references the total sales number for each row. The dollar sign in front of the ‘D’ keeps this part of the reference from changing; however, the ‘4’ does not have a dollar sign in front of it. Therefore, the ‘4’ will change for each row of the selected range where the conditional formatting is applied.

For example, the cells in row 8 will depend on the value in cell D8. The cells in row 6 will depend on the value in cell D6. Etc.

This formula then compares the referenced Total Sales value and compares that value to the calculated maximum value. If the two values match, a TRUE value is returned, and the conditional formatting is activated.

And once the formula is entered, you can click on Done.

How Did You Do?

How did it go? Were you able to solve all of the questions, or were there a few that stumped you? Let me know in the comments below.

I hope you had a blast working through this challenge, and if you are hungry for more, you can check out these other challenges below.

Watch the Video

This Post Has 0 Comments

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top