Skip to content

Excel Challenge 6 – Sort and Filter Data

Welcome to your sixth Excel challenge! In this article, I will give you four questions to test your ability to sort and filter data in Excel.

How many questions can you get right? Download the practice file below to test yourself and find out!

Excel Challenge Number 6

Question 1

For question number one, your task is to sort the data by last name and then by first name in alphabetical order.

Question 1 – Solution

To solve question one, select all the data, including the headers. Then, in the ribbon, go to Home Sort & Filter > Custom Sort.

In the dialog box that appears, click on the Add Level button, and check the checkbox next to my data has headers.

Next, set up the first level to sort by Last Name and the second level to sort by First Name.

Finally, click on Ok to sort the data. And that completes question number one.

Question 2

For question number two, your goal is to create a filter to view only those records with a last name starting with the letter ‘A.’

Question 2 – Solution

To solve question two, select all the data, including the headers. Then go to Home Sort & Filter > Filter.

From here:

  • Click on the filter button for the last name column
  • Click Select All to de-select all of the options
  • Click Adams to select it
  • Click on Ok.

You are now finished with question number two.

Question 3

For question three, you must write a formula to return the three members of the Adams family sorted from youngest to oldest.

Question 3 – Solution

To solve question three, begin by selecting cell F5. Then enter the following formula.

=SORT(FILTER(B5:D16, B5:B16="Adams"), 3)

The FILTER function filters all the data by checking which cells in the first column (B5:B16) contain values equivalent to ‘Adams.’

This data passes to the SORT function, which then sorts the data by the third column (age) in ascending order.

As a result, you get back the three members of the Adams family sorted from youngest to oldest.

Question 4

For question number four, your task is to write a formula that returns everyone who shares a last name with the selected individual (including the individual).

Question 4 – Solution

To solve question four, start by selecting cell F7. Then enter the following formula.

=FILTER(B5:D16, B5:B16=XLOOKUP(G4, C5:C16, B5:B16))

The FILTER function filters all the data by checking which cells in the range B5:B16 is equivalent to the result generated by the XLOOKUP function.

The XLOOKUP function takes the selected first name and returns the corresponding last name.

As a result, you get back everyone who shares a last name with the selected individual (including the individual).

And that officially completes question four and your sixth Spreadsheet Life Excel Challenge!

How Did It Go?

So how did you do?

Were you able to correctly answer all of the questions? Or were some a little more difficult than expected?

Leave me a comment below and let me know! I’d love to hear your thoughts.

Learn By Doing

If you enjoyed testing your knowledge like this and found that you learned a few things along the way, then I recommend that you check out my Excel Total course.

The Excel Total course teaches you by allowing you to apply what you learn. That way, you get plenty of hands-on practice!

After all, one of the best ways to learn is by doing. And that’s what the Excel Total course is all about!

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