Excel Challenge 9: Making Use of Advanced Sorting Techniques in Excel

Ready to tackle this month’s Excel challenge?

The main task seems innocent enough - sorting data. But how much do you really know about sorting in Excel?

Here’s what to do:

• First, watch the challenge video and read the instructions below the video.
• Review the previously published video(s) and article(s) suggested.
• Download the Excel worksheet you will use to complete the challenge tasks.
• Put yourself to the test!

Want to chat about your approach and process with other Excel heads? Join our Slack channel to share your insights and questions with like-minded learners.

The challenge

The school board has a list of all the schools in the district - from elementary to post-secondary, showing their city or town and population. They got an intern to put all this data together, but he sorted the dataset by the name of the school, which isn’t statistically useful to the Board.

Tasks

1. What they really want is to sort schools alphabetically by town. Each town should have its schools displayed in the following order:

• Elementary > Middle > High > Community College > University.

Towns that have more than one of the same school type should list the one with the larger population first.

2. In the cells to the right of the data, we want to determine the occupancy percentage (population vs. capacity) of each type of school. Occupancy levels should be shown from lowest to highest, left to right.

Both tasks sound simple, but our intern is stumped. Doing them manually would be too time-consuming. There must be a simpler way! What is it?

Watch our Sorting and SUMIF videos for clues, then solve the challenge: 

Download the file and let us know how you solved it!

The solution

We hope you'll enjoy taking part in this challenge!

Stay tuned to the GoSkills Excel Resource hub for more Excel challenges, and check out our range of expert-led Excel courses for all skill levels to further sharpen your skills.

If you enjoyed this challenge, try the Basic and Advanced Excel course to help you learn more essential formulas, functions, and practical, real-world Excel skills.