Excel Workout 2 - Data Validation & Conditional Formatting

Welcome to Excel Workout #2!
Difficulty Level: 2 out of 5

This week’s challenge is designed to test your knowledge on Data Validation and Conditional Formatting.

Data validation is a feature in Microsoft Excel that allows you to control the type and format of data that users can enter into specific cells. It helps to ensure the accuracy and consistency of data by limiting the range of values that can be entered into a cell.

Conditional formatting is a feature in Microsoft Excel that allows you to format cells based on specific conditions or criteria. This means that you can apply formatting such as color, font, or borders to cells automatically based on the contents of the cell.

Goals

• Create a drop-down list of country names located in the L8:L61 range in column M5.

• After selecting any country name in cell M5, the row that contains that particular country name within the range of L8:P61 is highlighted in blue.

Submission

Period

This workout will be released on Monday March 30, 2023, and the author’s solution will be posted on Sunday April 05, 2023.

Good luck!

Ilgar Zarbaliyev

Challenge #2.xlsx (8.3 MB)

2 Likes

The data validation part seems to have taken care of by the author. So I focused on the conditional formatting part which is also an easy one.

Conditional formatting Formula
=\$L8=\$M\$5

Applied to =\$L\$8:\$P\$61

1 Like

The data validation was already there. Therefore, I worked out only for Conditional Formatting.

Challenge #2.xlsx (8.3 MB)

2 Likes
1 Like

@IlgarZarbaliyev,
Here is my workout.

Summary

1 Like

No Intellisense, luckily the formula was easy. Thank you!
Challenge# 2 Franziola Chiu.xlsx (8.3 MB)

1 Like

Hi there,

Thanks for the refresher. I haven’t done this for a while

Thanks
Keith

1 Like

Hi, Here is my solution:

Formula

Challenge #2_AA.xlsx (8.3 MB)

1 Like

Hi, my submission:

Challenge #2.xlsx (8.3 MB)

1 Like

Thank you for participating in the Excel Challenge related to Data Validation & Conditional Formatting! I hope you found this challenge to be a fun and engaging way to improve your Excel skills and learn more about how to perform conditional summation in Excel.

Here is my solution to Workout #2:

Data Validation:

• Select Cell M5.

• Click on Data Tab, select Data Validation from Data Validation.

• Select L8:P61 Range.

Conditional Formatting

• Go to Styles Group from Home Tab and click on Conditional Formatting.

• Click on New Rule.

• Select Use a Formula to detect which cells to format

• Enter the following formula in the textbox of Format values where this formula is true: \$L8=\$M\$5

• lick on Format. Select Fill Tab. Select Blue color.

• Click OK.

Hint: If you plan to highlight all the row based on the criteria from drop-down list, your formula that you are going to use in Conditional Formatting must be referenced.
It our case, L column has been referenced with \$ sign.

Challenge #2 with Solution.xlsx (8.3 MB)

Once again, thank you for participating in this Excel Challenge, and I look forward to seeing you in future challenges and learning opportunities!

Challenge #2.xlsx (8.3 MB)

Summary

Use this Formula in conditional formating after apply data validation => \$L8=\$M\$5 apply to \$L\$8:\$P\$61

1 Like

Have relocated the table after removing redundant information from the sheet

Data Validation List Source (GEO Labels from the table):

`=\$A\$8:\$A\$61`

Data Validation Rule applied to the table

`=\$A8=\$B\$5`

Solved Workbook:
C2_ConditionalFormatting_DataValidation_Homesh.xlsx (21.2 KB)

1 Like

Data Validation:

Conditional Formatting:

Final Output:

Here’s my excel workout file: Challenge #2.xlsx (8.3 MB)

1 Like

Perfecto.

1 Like

Attached below is the excel file with my solution
Challenge #2.xlsx (8.3 MB)

1 Like