Sorting data (single and multi-level) in Excel - Real Business Scenario

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 top-selling products by region and then by sales amount.

📊 Data: You have a table with columns: Region, Product, Sales Amount, and Date of Sale. The data includes multiple regions and products with different sales amounts.

🎯 Deliverable: Create a sorted table first by Region alphabetically, then by Sales Amount from highest to lowest within each region.

Sample Data

Region	Product	Sales Amount	Date of Sale
East	Apples	500	2024-01-05
West	Bananas	300	2024-01-07
East	Oranges	700	2024-01-10
North	Apples	200	2024-01-12
South	Bananas	450	2024-01-15
West	Oranges	600	2024-01-18
North	Bananas	350	2024-01-20
South	Apples	400	2024-01-22

1

Step 1: Select the entire data table including headers.

Click and drag from cell A1 to D9 to select all data.

Expected Result

All rows and columns of the data table are selected.

2

Step 2: Open the Sort dialog box.

Go to the Data tab on the ribbon and click the Sort button.

Expected Result

The Sort dialog box appears.

3

Step 3: Set the first level of sorting by Region in ascending order (A to Z).

In Sort dialog, under 'Column', select 'Region'. Under 'Sort On', select 'Values'. Under 'Order', select 'A to Z'.

Expected Result

First sorting level is set to sort Region alphabetically from A to Z.

4

Step 4: Add a second level of sorting by Sales Amount in descending order (largest to smallest).

Click 'Add Level'. Under 'Column', select 'Sales Amount'. Under 'Sort On', select 'Values'. Under 'Order', select 'Largest to Smallest'.

Expected Result

Second sorting level is set to sort Sales Amount from highest to lowest within each Region.

5

Step 5: Apply the sorting to the data.

Click OK in the Sort dialog box.

Expected Result

Data is sorted first by Region A-Z, then by Sales Amount descending within each Region.

Final Result

Region  | Product | Sales Amount | Date of Sale
---------------------------------------------
East    | Oranges | 700          | 2024-01-10
East    | Apples  | 500          | 2024-01-05
North   | Bananas | 350          | 2024-01-20
North   | Apples  | 200          | 2024-01-12
South   | Bananas | 450          | 2024-01-15
South   | Apples  | 400          | 2024-01-22
West    | Oranges | 600          | 2024-01-18
West    | Bananas | 300          | 2024-01-07

✓East region has the highest single sale with Oranges at 700.

✓Within each region, products are ordered from highest to lowest sales.

✓Sorting helps quickly identify top-selling products by region.

Bonus Challenge

Create a multi-level sort that first sorts by Region ascending, then by Product ascending, and finally by Sales Amount descending.

Show Hint

In the Sort dialog, add three levels: Region (A to Z), Product (A to Z), Sales Amount (Largest to Smallest).