Skip to content

GS Challenge 2 – Sort and Filter Data

Welcome to the 2nd Google Sheets challenge article.

In this article, I will give you four different questions within Google Sheets 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!

Google Sheets Challenge File 2

Question 1

For question 1, filter the data so only Category B products are visible.

Question 1 – Solution

To filter the data, select the data and then go to Data Create a Filter.

Next, click the filter button next to Category, select Clear, and then select Category B.

Finally, click on Ok. The data is now filtered so that only Category B products are visible.

Question 2

For question 2, you must sort the data by Category, then by Qty, and then by Cost, all in ascending order.

Question 2 – Solution

Begin by selecting all of the data, including the headers. Then go to Data Sort range > Advanced range sorting options.

In the window that appears, check the box next to Data has header row.

For Sort by, select Category. Then click on Add another sort column.

For then by, select Qty. Click on Add another sort column.

For then by, select Cost. You also want everything sorted in ascending order, so leave all three sorting options set to A→Z.

Finally, click on Sort. Your data is now sorted correctly.

Question 3

For question 3, you must write a formula to return all products with a cost greater than 25.00, sorted by cost in descending order.

Question 3 – Solution

You will need to use two functions for this formula – the FILTER function and the SORT function. Putting these two functions together, your final formula should look like this.

=SORT( FILTER( H5:L16, L5:L16>25 ), 5, FALSE )

The FILTER function references the source data and filters that data by the Cost column values greater than 25.

The SORT function takes that result and sorts the data by the 5th column (the Cost column), in descending order, due to the last argument being set to FALSE.

Question 4

For question 4, your goal is to write a formula to return all products with an SKU beginning with the letter ‘B.’

Question 4 – Solution

To retrieve the products with an SKU starting with the letter ‘B,’ you will need the FILTER function and LEFT function.

Your final formula looks like this.

=FILTER( H5:L16, LEFT(H5:H16, 1 ) = “B” )

The LEFT function extracts the first letter for each of the SKUs. That result is used as the second argument for the FILTER function, which checks to see which letters are equivalent to ‘B.’

As a result, the FILTER function returns only the products with an SKU beginning with the letter ‘B.’

And that concludes your Google Sheets challenge for the week.

Leave a comment below to let me know how you did, and keep an eye out for next week’s Google Sheets challenge.

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