Google Sheets Data Sorting and Filtering Questions and Answers 1 — 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?
- Use the SORT function in a new sheet to organize by Assignee and then by Status.
- Apply a filter to the data, then set the 'Assignee' filter to 'Alex' and the 'Status' filter to 'In Progress'. (Correct answer)
- Manually hide rows that do not match the criteria.
- Create a Pivot Table with 'Assignee' and 'Status' as rows.
Correct 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.
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?
- A Filter View (Correct answer)
- Data > Sort range
- The SORT() function in an empty area of the sheet
- A standard Filter
Correct 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.
Question 3: Which of the following describes the key difference between using the 'Sort range' menu option and the `SORT()` function?
- The `SORT()` function can only sort by one column, while 'Sort range' can handle multiple columns.
- 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. (Correct answer)
- Only the 'Sort range' option can handle datasets with a header row.
- The `SORT()` function modifies the original data in place, while 'Sort range' creates a copy.
Correct 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.
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?
- =AND(B2>0, B2<10) (Correct answer)
- =OR(B2>0, B2<10)
- =FILTER(A2:B, B2:B > 0, B2:B < 10)
- =B2>0 AND <10
Correct 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.
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?
- Sort by Column A (A to Z), then click 'Add another sort column' and sort by Column C (A to Z).
- Use the `SORT` function as it's the only way to apply multiple sort criteria.
- 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). (Correct answer)
- Apply a filter and sort Column C first, then remove the filter and sort Column A.
Correct 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.
Question 6: What happens if you apply the `FILTER` function `=FILTER(A1:C50, D1:D50 > 100)` and no rows in the specified range meet the condition?
- The formula displays a blank result.
- The formula returns a #VALUE! error.
- The formula returns the #N/A error. (Correct answer)
- The formula returns the original unsorted range.
Correct 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.
Google Sheets Data Sorting and Filtering Questions and Answers 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?