Skip to content

Excel Challenge 1 – Test Your Knowledge

Welcome to the first Microsoft Excel challenge article.

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 1

Question 1

For question 1, your goal is to fill out the data for each employee following the example in the first row.

You can use any method you want to accomplish this task.

Question 1 – Solution

Of course, you could have typed all the data manually to finish the task, but there is actually a much faster way to do it.

You can complete this task using the flash fill feature in Excel.

Flash fill is a feature in Excel that allows you to quickly replicate patterns. In this case, you can use flash fill to finish the first names by selecting the cell under the first record and going to Fill Flash Fill.

Excel is smart enough to recognize that you want the first name based on the data in the first row.

You can repeat this for the other columns as well. Just select the cell under the first record in the next column, and go to Fill Flash Fill. You can also use the keyboard shortcut CTRL + E. This is the short key for flash fill in Excel.

Just use flash fill to complete one column at a time until all the data has been entered.

Question 2

For question 2, you must ensure that only a date between the specified start and end date may be entered for each employee.

In other words, your goal is to restrict the user from entering anything except a date value that falls between the start and end date.

Question 2 – Solution

For this question, you will need to use Data Validation on the specified range. Start by selecting the range of cells you want to restrict. Then go to Data Data Validation.

Under allow, select Date. Under data, select between.

For the Start Date, select the cell containing the start date. And for the End Date, select the cell containing the end date. Also, make sure that both of these are absolute references.

Finally, click on Ok. Now only a date value between the start and end date may be entered into the specified range.

Question 3

For question 3, you must highlight in red all the starting salaries that surpass the salary limit.

Question 3 – Solution

For this question, you will need to use conditional formatting. Begin by selecting the starting salaries, and then go to Home Conditional Formatting > Highlight Cell Rules > Greater Than…

Under Format cells that are GREATER THAN, enter the reference for the cell containing the salary limit. Make sure this is an absolute reference.

Make sure the formatting is set to Light Red Fill, and then click on Ok.

The salaries surpassing the salary limit are now highlighted in red.

Question 4

For your final question, your goal is to calculate the average of all the starting salaries below the salary limit.

Question 4 – Solution

For this question, you will need to use the AVERAGEIF function.

Start by selecting cell C14, and enter the formula:

=AVERAGEIF(C5:C13,"<"&E5)

Make sure the comparison operator is placed in quotes and attached to the reference with an ampersand.

This formula then calculates the average for all of the salaries that fall below the salary limit.

And that officially completes Excel Challenge #1!

I hope you had a blast working through these problems and learned a thing or two along the way.

Also, leave me a comment down below to let me know how you did! I’d love to hear your thoughts. And be sure to keep an eye out for the next Excel Challenge coming next week!

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