SORT and SORTN functions in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are a sales analyst at a retail company.

📋 Request: Your manager wants a report showing the top 5 best-selling products sorted by total sales in descending order.

📊 Data: You have a sales data table with columns: Product Name, Units Sold, and Unit Price.

🎯 Deliverable: Create a sorted list of the top 5 products by total sales amount (Units Sold * Unit Price) using SORT and SORTN functions.

Sample Data

Product Name	Units Sold	Unit Price
Notebook	120	2.50
Pen	300	1.20
Marker	150	1.80
Stapler	80	5.00
Folder	200	3.00
Paper Pack	90	4.00
Highlighter	110	1.50
Glue	60	2.00

1

Step 1: Add a new column named 'Total Sales' to calculate total sales for each product.

=B2*C2

Expected Result

For Notebook: 120 * 2.50 = 300

2

Step 2: Copy the 'Total Sales' formula down for all products.

Drag the formula from D2 down to D9

Expected Result

Pen total sales: 300 * 1.20 = 360, Folder total sales: 200 * 3.00 = 600, etc.

3

Step 3: Use SORTN to get the top 5 products by total sales in descending order.

=SORTN(A2:D9, 5, 0, 4, FALSE)

Expected Result

Returns top 5 rows sorted by Total Sales column (column 4) descending.

4

Step 4: Use SORT to sort all products by total sales descending (optional to see full sorted list).

=SORT(A2:D9, 4, FALSE)

Expected Result

All products sorted from highest to lowest total sales.

Final Result

Top 5 Best-Selling Products by Total Sales
------------------------------------------
| Product Name | Units Sold | Unit Price | Total Sales |
|--------------|------------|------------|-------------|
| Folder       | 200        | 3.00       | 600         |
| Stapler      | 80         | 5.00       | 400         |
| Pen          | 300        | 1.20       | 360         |
| Paper Pack   | 90         | 4.00       | 360         |
| Notebook     | 120        | 2.50       | 300         |

✓Folder is the top-selling product with total sales of 600.

✓Pen and Paper Pack both have high total sales, but Pen sold more units.

✓Using SORTN helps quickly find the top 5 products without sorting the entire list.

Bonus Challenge

Create a dynamic top N list where the user can input the number N and the list updates automatically.

Show Hint

Use a cell to input N and replace the fixed number in SORTN with that cell reference.