Bird
Raised Fist0
Google Sheetsspreadsheet~15 mins

Sorting (single and multi-column) in Google Sheets - Real Business Scenario

Choose your learning style10 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Scenario Mode
👤 Your Role: You are a sales analyst at a retail company.
📋 Request: Your manager wants you to organize the sales data to easily see the best performing products by region and sales amount.
📊 Data: You have a table with columns: Region, Product, Sales Amount, and Date of Sale. The data includes multiple sales records from different regions and products.
🎯 Deliverable: Create a sorted table first by Region alphabetically, then by Sales Amount from highest to lowest within each region.
Progress0 / 5 steps
Sample Data
RegionProductSales AmountDate of Sale
EastShirt2502024-05-01
WestHat1502024-05-03
EastShoes3002024-05-02
NorthShirt2002024-05-01
SouthHat1802024-05-04
WestShoes4002024-05-05
NorthHat2202024-05-03
SouthShoes3502024-05-02
1
Step 1: Select the entire data table including headers.
No formula needed; just select cells A1:D9.
Expected Result
The full data table is selected.
2
Step 2: Sort the data by Region alphabetically (A to Z).
Use Data > Sort range > Sort by 'Region' A -> Z.
Expected Result
Rows are rearranged so that regions appear in alphabetical order: East, North, South, West.
3
Step 3: Add a second sorting level by Sales Amount from largest to smallest within each Region.
In the Sort range dialog, add 'Sales Amount' and sort Z -> A.
Expected Result
Within each Region group, rows are sorted so that the highest sales amount is first.
4
Step 4: Verify the sorted data to confirm Region is alphabetical and Sales Amount is descending within each Region.
No formula; visually check the sorted table.
Expected Result
Data shows East region rows first with sales sorted high to low, then North, South, and West similarly sorted.
5
Step 5: Alternatively, create a new sorted table using the SORT function with multiple columns.
=SORT(A2:D9, 1, TRUE, 3, FALSE)
Expected Result
A new table appears sorted by Region ascending and Sales Amount descending.
Final Result
Region  | Product | Sales Amount | Date of Sale
---------------------------------------------
East    | Shoes   | 300          | 2024-05-02
East    | Shirt   | 250          | 2024-05-01
North   | Hat     | 220          | 2024-05-03
North   | Shirt   | 200          | 2024-05-01
South   | Shoes   | 350          | 2024-05-02
South   | Hat     | 180          | 2024-05-04
West    | Shoes   | 400          | 2024-05-05
West    | Hat     | 150          | 2024-05-03
✓Data is organized by Region alphabetically: East, North, South, West.
✓Within each Region, sales records are sorted from highest to lowest sales amount.
✓This sorting helps quickly identify top-selling products per region.
Bonus Challenge

Create a dynamic sorted table that updates automatically when new sales data is added.

Show Hint
Use the SORT function combined with ARRAYFORMULA or dynamic ranges like A2:D to include new rows automatically.

Practice

(1/5)
1. What does the SORT function do in Google Sheets?
easy
A. It arranges data in a specified order based on one or more columns.
B. It deletes duplicate rows from the data.
C. It calculates the sum of selected cells.
D. It changes the font style of the selected cells.

Solution

  1. Step 1: Understand the purpose of SORT

    The SORT function is designed to reorder data based on column values.

  2. Step 2: Compare with other options

    Deleting duplicates, summing cells, or changing font are different functions, not sorting.

  3. Final Answer:

    It arranges data in a specified order based on one or more columns. -> Option A

  4. Quick Check:

    SORT = Arrange data order [OK]
Hint: SORT arranges data by columns, not formatting or calculations [OK]
Common Mistakes:
  • Confusing SORT with SUM or formatting functions
  • Thinking SORT deletes data
  • Assuming SORT changes cell styles
2. Which of the following is the correct syntax to sort data in range A2:C10 by the first column in ascending order?
easy
A. =SORT(A2:C10, 1, FALSE)
B. =SORT(A2:C10, TRUE, 1)
C. =SORT(A2:C10, 1, TRUE)
D. =SORT(A2:C10, 2, TRUE)

Solution

  1. Step 1: Recall SORT syntax

    The syntax is SORT(range, sort_column_index, is_ascending).

  2. Step 2: Match syntax to options

    =SORT(A2:C10, 1, TRUE) uses correct order: range A2:C10, column 1, ascending TRUE.

  3. Final Answer:

    =SORT(A2:C10, 1, TRUE) -> Option C

  4. Quick Check:

    SORT(range, column, TRUE) = ascending [OK]
Hint: Remember: SORT(range, column_number, TRUE/FALSE) [OK]
Common Mistakes:
  • Swapping the order of TRUE and column number
  • Using FALSE for ascending order
  • Selecting wrong column index
3. Given the data in A1:B4:
Name | Score
Alice | 85
Bob | 92
Carol | 78
What will be the result of =SORT(A2:B4, 2, FALSE)?
medium
A. Bob 92
Alice 85
Carol 78
B. Carol 78
Alice 85
Bob 92
C. Alice 85
Bob 92
Carol 78
D. Bob 92
Carol 78
Alice 85

Solution

  1. Step 1: Understand sorting by second column descending

    Sorting by column 2 (Score) with FALSE means highest to lowest.

  2. Step 2: Order the scores

    Scores: Bob 92, Alice 85, Carol 78. Sorted descending: 92, 85, 78.

  3. Final Answer:

    Bob 92
    Alice 85
    Carol 78     -> Option A

  4. Quick Check:

    SORT by Score descending = highest first [OK]
Hint: FALSE means descending order, highest values first [OK]
Common Mistakes:
  • Mixing TRUE and FALSE for ascending/descending
  • Sorting by wrong column index
  • Ignoring header row in sorting
4. You want to sort data in A2:C10 first by column 2 ascending, then by column 3 descending. Which formula fixes the error in this incorrect formula?
=SORT(A2:C10, 2, TRUE, 3)
medium
A. =SORT(A2:C10, 3, TRUE, 2, FALSE)
B. =SORT(A2:C10, 2, TRUE, 3, FALSE)
C. =SORT(A2:C10, 3, FALSE, 2, TRUE)
D. =SORT(A2:C10, 2, TRUE, 3, TRUE)

Solution

  1. Step 1: Identify correct multi-column SORT syntax

    Multi-column SORT needs pairs: column index and TRUE/FALSE for each.

  2. Step 2: Fix missing TRUE/FALSE for second column

    The original formula misses the order for column 3. =SORT(A2:C10, 2, TRUE, 3, FALSE) adds FALSE for descending.

  3. Final Answer:

    =SORT(A2:C10, 2, TRUE, 3, FALSE) -> Option B

  4. Quick Check:

    Multi-column SORT needs column and order pairs [OK]
Hint: Always pair column number with TRUE/FALSE in SORT [OK]
Common Mistakes:
  • Omitting TRUE/FALSE for each sort column
  • Swapping column order and sort order
  • Using wrong TRUE/FALSE for desired order
5. You have a list of employees with columns: Name (A), Department (B), and Salary (C). You want to sort the list first by Department ascending, then by Salary descending. Which formula correctly sorts this data in range A2:C100?
hard
A. =SORT(A2:C100, 1, TRUE, 3, FALSE)
B. =SORT(A2:C100, 3, FALSE, 2, TRUE)
C. =SORT(A2:C100, 2, FALSE, 3, TRUE)
D. =SORT(A2:C100, 2, TRUE, 3, FALSE)

Solution

  1. Step 1: Identify sorting order for each column

    Department (column 2) ascending = TRUE; Salary (column 3) descending = FALSE.

  2. Step 2: Match formula with correct column and order pairs

    =SORT(A2:C100, 2, TRUE, 3, FALSE) matches: column 2 TRUE, column 3 FALSE.

  3. Final Answer:

    =SORT(A2:C100, 2, TRUE, 3, FALSE) -> Option D

  4. Quick Check:

    Sort by Dept ascending, Salary descending = =SORT(A2:C100, 2, TRUE, 3, FALSE) [OK]
Hint: Sort by main column ascending, then secondary descending [OK]
Common Mistakes:
  • Reversing sort order for columns
  • Sorting by wrong column numbers
  • Using inconsistent TRUE/FALSE values
Recommended

Ready to prove it?

Pass the test and this pattern moves unknown strong while earning +1 Grip toward your certificate.

10 questions~5 mineasy hard