Google Sheets Data Sorting and Filtering Questions and Answers

Question 1

A project manager has a sheet with columns for 'Task Name', 'Assignee', 'Due Date', and 'Status'. They want to see all tasks assigned to 'Alex' that are also marked as 'In Progress'. Which of the following methods is the most efficient way to display only this specific data without creating a new dataset?

Accepted Answer

Apply a filter to the data, then set the 'Assignee' filter to 'Alex' and the 'Status' filter to 'In Progress'.

Answer

Applying a filter is the most direct and efficient method. It allows you to set conditions on multiple columns ('Assignee' and 'Status') to dynamically hide rows that don't match, showing only the desired data without altering the original data structure or creating a new table.

Question 2

You are collaborating on a Google Sheet with several team members. You need to sort the data by 'Region' (A-Z) and then by 'Sales' (Largest to Smallest) for your own analysis, but you do not want to alter the default view for everyone else. What should you use?

Accepted Answer

A Filter View

Answer

A Filter View allows you to create, name, and save custom sorting and filtering rules that only you can see, without affecting how the data is displayed for other collaborators. A standard filter would change the view for everyone.

Question 3

Which of the following describes the key difference between using the 'Sort range' menu option and the `SORT()` function?

Accepted Answer

The 'Sort range' menu option permanently reorders the original data, while the `SORT()` function creates a new, sorted array of data in a different location.

Answer

The 'Sort range' command directly reorganizes the cells you have selected. In contrast, the `SORT()` function is dynamic; it takes a source range as input and outputs a new, sorted version of that data in the cells where the formula is placed, leaving the original data untouched.

Question 4

A user wants to filter a list of products in column A to show only items with a stock quantity (in column B) greater than 0 but less than 10. Which 'Filter by condition' custom formula would achieve this?

Accepted Answer

=AND(B2>0, B2<10)

Answer

When using a custom formula in the 'Filter by condition' menu, you write a formula that evaluates to TRUE or FALSE for each row, starting with the first row of data (e.g., row 2). The formula `=AND(B2>0, B2<10)` correctly checks if the value in column B is both greater than 0 AND less than 10 for each row.

Question 5

To sort a range of data first by 'Department' (Column C) in ascending order and then by 'Employee Last Name' (Column A) in ascending order, what is the correct procedure using the advanced sorting options?

Accepted Answer

Select the range, go to Data > Sort range, set the first sort column to 'Department' (A to Z), then click 'Add another sort column' and set the next column to 'Employee Last Name' (A to Z).

Answer

The advanced range sorting dialog box (Data > Sort range > Advanced range sorting options) is designed for multi-level sorting. You must add the primary sort key ('Department') first, and then add the secondary sort key ('Employee Last Name') using the 'Add another sort column' button.

Google Sheets Test Practice Test

Google Sheets Test Practice Test

Google Sheets Data Sorting and Filtering Questions and Answers