Excel Challenges - Conditional Formatting

 

This article presents the solution for Excel Workout #7, focusing on the topic of Conditional Formatting Feature. You can access the practice file with its solution by using the provided link.

Conditional Formatting Workout

Conditional Formatting Feature:

Conditional Formatting in Excel is a functionality that permits users to format cells based on specific conditions or criteria. It serves as a robust tool for emphasizing significant data or detecting patterns within extensive datasets.

Through conditional formatting, users have the ability to apply various formatting styles to cells depending on the cell's value, content, or its comparison to other cells. For instance, users can assign a distinct color to cells with values exceeding a particular threshold or apply a different color to cells containing specific text.

Objectives:

Please adhere to the instructions below, including downloading the necessary Excel worksheet for completing the challenge tasks. Once you've completed the download, proceed to take the challenge and test your skills.

Task:

1. You can select state names from the Y4:AD4 range via the drop-down menu in cell AG5.
2. Let's choose "Arizona" in cell AG5.
3. The cities belonging to the state of Arizona will be listed in the drop-down menu in cell AH5.
4. Now, select the city of "Phoenix."
5. Next, select the state of "Colorado" from cell AG5.
6. Surprisingly, the city of "Phoenix" is still visible in cell AH5, even though it belongs to the state of Arizona.
7. When you click the drop-down button in cell AH5, only the cities of Colorado state are visible.

The question is:

How can we ensure that the value in cell AH5 remains blank when we change the state in cell AG5, until a new city is selected?

Here is my solution to Workout #7:

=COUNTIF(INDIRECT(AG5),AH5)=0

We use conditional formatting to conceal the value of the initial item in the list, applying either a red color or a custom format (";;;").

Thank you for reading our newsletter!
Stay tuned for more updates, news, and tips in the next edition.
If you have any feedback or suggestions for future topics, 
please feel free to reach out to us.
Have a great day!