Skip to content

Excel Challenge 3 – Data Validation

Welcome to the third Microsoft Excel challenge. If you missed the first two, you can find them right here.

In this article, I will give you four different questions within Excel to test your spreadsheet knowledge.

How many tasks will you be able to perform correctly? Click the link below to download the practice file to test yourself and find out!

Excel Challenge File 3

Question 1

For question number 1, your goal is to add data validation to cell D9 so that the user may only enter positive whole numbers.

Question 1 – Solution

To begin, select cell D9 and then go to Data Data Validation.

Under Allow, choose Whole Number. Under Data, select Greater Than. For the Minimum, enter a 0.

Finally, click on Ok. Question 1 is now complete.

Question 2

For question 2, you must add data validation to the name fields to restrict the text length to 16 characters or less.

Question 2 – Solution

To pull this off, start by selecting both cells D7 and G7. You can use the CTRL key to select both cells.

With both cells selected, go to Data Data Validation.

Under Allow, choose Text Length. Under Data, select less than or equal to. For the Maximum, enter 16.

Finally, click on Ok. Question 2 is now complete.

Question 3

For question 3, your goal is to create a dropdown menu for the Make field that includes the four car makes that are listed.

Question 3 – Solution

Select cell D11, and then go to Data Data Validation.

Under Allow, select List. For the Source, select the range J5:J8.

Finally, click on Ok. You now have a dropdown list for the Make field.

Question 4

Question 4 is a tough one. For this question, you must create a dropdown list for the Model field that changes depending on the selected Make.

In other words, if the user selects Ford, then only Ford models should show up in the dropdown list. If the user selects Honda, then only Honda models should be visible in the dropdown.

Question 4 – Solution

To complete question 4, you must first create four range names.

Start by selecting the range L5:L8, enter the name of ‘Ford’ into the name box, and press the enter key.

Next, select M5:M8, enter the name of ‘Chevrolet’ into the name box, and press the enter key.

Select N5:N8, enter the name of ‘Honda’ into the name box, and press the enter key.

And then select O5:O8, enter the name of ‘Tesla’ into the name box, and press the enter key.

You now have the four range names that you need. The next step is to select cell G11 and go to Data Data Validation.

 

Under Allow, select List. For the Source, enter the following formula.

=INDIRECT($D$11)

This formula uses the INDIRECT function to take the selected Make and transform it into a valid range name reference.

You can then click on Ok.

The dropdown list for the car models will now change depending on the selected Make. Try it out, and see for yourself!

How Did You Do?

I hope you had a ton of fun working through Excel challenge number 3. Did you find the questions to be easy or rather hard? Let me know in the comments how you did!

Don’t forget to send this article to your friends and co-workers as well to see how they do!

Also, check out the Excel Total Course if you are interested in learning the skills to solve questions like this with ease!

This course teaches you everything you need to know about Excel. And I can promise you that what you learn, you will remember for a lifetime!

Thanks so much for joining me in this week’s Excel challenge. Be sure to check back in soon for Excel challenge number 4.

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