Google Sheets Data Sorting and Filtering Flashcards

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?

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

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.

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?

Answer: A Filter View

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.

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

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.

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.

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?

Answer: =AND(B2>0, B2<10)

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.

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?

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).

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.

What happens if you apply the `FILTER` function `=FILTER(A1:C50, D1:D50 > 100)` and no rows in the specified range meet the condition?

Answer: The formula returns the #N/A error.

If the `FILTER` function does not find any rows that match the specified condition(s), it will return the #N/A error, indicating that no results were found.