In this lesson
Apply a two-level sort and check the ID-amount pairs against the original list.
Example
Group requests by team, largest first
Enter ID, Team and Amount in A1:C5: R01 Support 40, R02 Sales 80, R03 Support 60 and R04 Sales 20. Group teams alphabetically and put the largest amount first within each team. Each ID must keep its own amount.
How it works
Sorting moves records. If you sort just the Amount column, you can attach an amount to the wrong ID. Select the complete record range or work inside an Excel table.
The first sort key creates groups; the next key orders records within each group. Sorting by Team then Amount gives a different result from sorting by Amount then Team.
Steps
Enter the records and copy them to BeforeSort. Calculate Amount total 200 and keep the copy so you can compare individual rows after sorting.
Select A1:C5, or click inside a table covering that range. Open Data > Sort. Check that all three columns are included and My data has headers is selected.
Sort first by Team, A to Z. Add a level for Amount, largest to smallest. The amounts should be numbers for this numeric order.
Apply the sort. Read the rows: R02 Sales 80, R04 Sales 20, R03 Support 60 and R01 Support 40. Compare each pair with BeforeSort.
Check that the Amount total remains 200. Also inspect every ID-amount pair: the total would stay 200 even if amounts had moved independently.
On the original copy, add Sequence values 1 through 4. Sorting by this column can restore the starting order after later edits have made Undo unavailable.
Try it yourself
- Return to an unsorted copy and sort by Amount descending as the first key, then Team ascending as the second. Predict the ID order before applying it.
- Create a variant where an amount is stored as text and inspect its sort behavior. Correct its data type on the variant and rerun the sort without modifying the preserved original.
Show expected result
- The Team-first result is R02, R04, R03, R01, with all four ID-amount pairs intact and the amount total 200.
- The Amount-first result is R02, R03, R01, R04. The different order demonstrates that key precedence changes the question being answered.
If something goes wrong
- Amounts moved but IDs did not.
- Undo the sort on the working copy. Select the complete range or use a table, then sort again and compare all four ID-amount pairs.
- A larger amount appears below a smaller one.
- Check whether that amount is stored as text and whether Amount is the intended sort level. Convert text amounts on a copy and rerun the numeric sort.
Quick check
Try these questions after the exercise. Check the result in your app before marking the lesson complete.
Choose an answer for all 3 questions to check your understanding.
Optional: try it with Copilot
If you have Copilot, try this after the exercise. Keep the original file and compare its suggestions with your own result.
Predict the request ID order for Team A to Z, then Amount largest to smallest. Explain what to check besides the total of 200.
More from Microsoft
These Microsoft guides cover the tools used in this lesson.
Loading your progress…