Sort and Filter in Excel Without Mixing Up Your Rows

Sorting and filtering are among Excel's most useful everyday tools. They are also easy to misunderstand. A sort changes row order, while a filter temporarily hides records that do not match a condition. Both work best when your list has a clear structure.

Before you start: Use a new practice workbook. The names and figures below are fictional. Enter the headers in row 1 and the first record in row 2 unless a step says otherwise. Formulas use English function names and commas; your regional settings may require semicolons instead. Save a copy before experimenting.

Prepare a clean record list

Enter the following data in A1:D6. The OrderID identifies the record, so it gives you a simple way to check that the other fields stay attached to the correct order.

OrderID	Customer	City	Amount
101	Amina	Nairobi	1200
102	Brian	Mombasa	800
103	Amina	Nairobi	600
104	Chao	Nairobi	1500
105	Dalia	Kisumu	800

Convert the list to an Excel Table if you can. Keep each record in one row, use headers and leave no blank column splitting the list. Save a practice copy before trying the sort.

Sort the whole dataset by amount

Click inside the list and use the Data tab's sort controls. Choose Amount, cell values, largest to smallest. Make sure the sort range includes all four columns and that Excel recognises the header row.

The descending amounts should be 1500, 1200, 800, 800 and 600. Order 104 must still belong to Chao in Nairobi. Order 103 must still belong to Amina with 600. Check these relationships after sorting.

Sorting only the Amount cells while leaving the names in place can create false records. If Excel asks whether to expand the selection, review the highlighted range and include the entire dataset. Do not dismiss the prompt without checking what will move.

Add a second level for ties

Use a multi-level sort with Amount largest to smallest, then OrderID smallest to largest. The order IDs should be 104, 101, 102, 105 and 103. Orders 102 and 105 have the same amount, so the second rule decides their order.

A tie-breaker makes your intended presentation clear. It is especially useful when comparing screenshots or reports. A sort is not a calculation: it changes where the records appear, not the amount stored in each record.

Filter to a city

Open the City header's filter control and select only Nairobi. You should see orders 101, 103 and 104, possibly in the order left by your earlier sort. Filtering does not necessarily restore original order.

The other records still exist. They are hidden by the filter. Clear the City filter and all five orders should return. Do not delete hidden rows merely because they are out of sight, and check which rows are selected before making any bulk edits.

Combine a text filter with a number filter

Keep City filtered to Nairobi, then apply an Amount number filter of greater than or equal to 1000. Now only orders 101 and 104 should be visible. Filters on separate columns work together: a row must satisfy both active column filters.

If you get no rows, inspect every header for an active filter icon. A leftover condition on another column may be narrowing the result. Clearing just the most recent filter does not clear all other conditions automatically.

Check totals with filtered data

A plain SUM over the amount range still includes hidden filtered rows. On the unmodified dataset, =SUM(D2:D6) is 4900. If you want a visible-row total after the two filters above, =SUBTOTAL(109,D2:D6) returns 2700.

The 109 selects SUM while excluding filtered-out and manually hidden rows. Keep the summary outside the filtered data range. Label it Visible amount so readers know it can change when the filters change.

Common reasons a sort looks wrong

Numbers stored as text, inconsistent date values and extra spaces in labels can produce surprising results. Fix the input types before assuming that another sort will solve the problem. A visual format such as Currency does not guarantee that imported values are numeric.

Try it yourself

Clear all filters. Filter Amount to exactly 800, then sort OrderID ascending. Which records remain? What should the visible amount total be, and what happens when you clear the filter?

Check your answer

Orders 102 and 105 remain, in that order. They belong to Brian in Mombasa and Dalia in Kisumu. Their visible amount total is 1600.

Clearing the filter reveals all five records again. It does not undo the last sort or restore the original entry order. To recover a known order, sort by a stable field such as OrderID or keep an original-sequence column when that sequence matters.

Keep learning: Return to the Excel learning path to choose your next lesson.