Skip to content

Email Newsletter Challenge 7

This week’s challenge is all about conditional formatting. You have a seemingly simple task that is deceivingly difficult. Think you can do it?

You can download the spreadsheet challenge using the links below.

The Challenge

Your challenge this week is to add conditional formatting to color the entire row green if the task is “Done”, or yellow if the task is “In Work”.

The Solution – Google Sheets

The solution for this challenge is a little different for Excel and Google Sheets. First is the solution for Google Sheets.

Start by selecting the range of data below the headers (B5:D11).

Then go to Format > Conditional Formatting.

In the Conditional Format Rules pane on the right, select Custom Formula Is.

Enter the following formula into the formula box.

=$D5="Done"

This formula checks to see if the value in D5 is equivalent to “Done”. If this is true, the conditional formatting is applied.

You include the dollar sign before the column letter because you want the conditional formatting formula for every cell of the selected range to reference the corresponding status value in column D. However, there is no dollar sign in front of the row number because you want the cells in each row to look at the status value for that particular row. In other words, you don’t want to reference row 5 ever single time.

To complete the conditional formatting, change the fill color to green.

Now before you click on Done, you want to create another rule that is similar to this one. So click on Add Another Rule.

This will create a duplicate of the rule you just created which will save you time.

From here, just change “Done” to “In Work”.

=$D5="In Work"

Then change the fill color to yellow. And now you can click on Done.

You have now completed the 7th spreadsheet challenge for Google Sheets.

The Solution – Excel

Here is the solution for Excel.

Start by selecting the range of data below the headers (B5:D11).

Then go to Home > Conditional Formatting > New Rule.

In the box that appears, select Use a formula to determine which cells to format.

Enter the following formula into the formula box.

=$D5="Done"

This formula checks to see if the value in D5 is equivalent to “Done”. If this is true, the conditional formatting is applied.

You include the dollar sign before the column letter because you want the conditional formatting formula for every cell of the selected range to reference the corresponding status value in column D. However, there is no dollar sign in front of the row number because you want the cells in each row to look at the status value for that particular row. In other words, you don’t want to reference row 5 ever single time.

To complete the conditional formatting, click the Format button.

Then under the Fill tab, select the color green and click on OK. Then click OK.

For the next conditional formatting rule, go to Home > Conditional Formatting > Manage Rules.

Select the conditional formatting rule you just created, and click Duplicate Rule. Then select Edit Rule.

In the formula box, change “Done” to “In Work”.

=$D5="In Work"

Then click the Format button, and under the Fill tab, select the color yellow and then click OK. And click OK.

Click OK one more time, and you are now finished with your 7th spreadsheet challenge.

I hope you felt challenged but also had fun and learned something new.

And if you’re not signed up for my weekly email newsletter, you can sign up right here!

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