Sorting & Filtering

Sorting puts rows in a useful order: highest salary first, oldest invoice first, names A to Z. Filtering hides the rows you do not need right now, so you can see only Jaipur staff or only unpaid bills. Together they answer most everyday questions about a list in seconds.

In this lesson you will sort by one and several columns, sort in a custom order, filter by text, numbers and dates, and total only the visible rows. You will also see how the menu commands compare with the SORT and FILTER functions.

The sample sheet

A travel agency with offices in three cities keeps this staff list:

The data follows two rules that make sorting and filtering safe: one heading row, and no completely empty rows or columns inside the list.

Sorting by one column

Click any single cell in the Salary column, then choose Data > Sort Largest to Smallest (the Z-A button). Excel detects the whole list, keeps the heading row in place, and moves complete rows, so each salary stays with its employee. Meera's row comes first.

For text columns the buttons read A to Z and Z to A; for dates they sort oldest to newest or newest to oldest.

Sorting by several columns

To list staff by department, with the highest salary first inside each department, use Data > Sort (the large Sort button):

1. Sort by Department, Order A to Z.
2. Click Add Level. Then by Salary (₹), Order Largest to Smallest.
3. Make sure My data has headers is ticked, and click OK.

Excel applies the first level, then uses the second level only to order rows that tie on the first.

Sorting in a custom order

Alphabetical order is not always right. Months should go January to December, and a priority column should go High, Medium, Low. In the Sort dialog, open the Order list and choose Custom List. Excel already knows days and months; to add your own, type the items in order (for example Sales, Accounts, Support) and click Add.

Filtering rows

Click inside the list and press Ctrl+Shift+L (or Data > Filter). A drop-down arrow appears on every heading. Click the arrow on City, untick Select All, tick Jaipur and click OK. Only Rohan, Isha and Priya remain visible; the row numbers turn blue and the status bar shows "3 of 7 records found". The other rows are hidden, not deleted.

Each drop-down offers filters that match the column's data type:

Filters on different columns combine with AND: City is Jaipur and Department is Sales leaves only Isha. Inside one column's custom filter dialog you can choose AND or OR between two conditions. To remove all filters, use Data > Clear; to turn the arrows off, press Ctrl+Shift+L again.

Totals of visible rows only

SUM adds every row, hidden or not. While filtering, you usually want the total of what you can see. SUBTOTAL does that. Its first argument picks the calculation:

=SUBTOTAL(109, F2:F8)

108500

With the Jaipur filter on, this adds only 45500 + 32000 + 31000. Clear the filter and the same formula shows 262500. Put such totals above the list or in another column, so a filter never hides the total itself.

The numbers 1 to 11 do the same jobs but also include rows you hid by hand (right-click > Hide); 101 to 111 ignore them. Both ignore rows hidden by a filter. =SUBTOTAL(103, B2:B8) counts the visible names: 3 for Jaipur.

Menu commands or functions?

The Sort and Filter commands change the view of the original data. The SORT and FILTER functions from Dynamic Arrays leave the data alone and build a sorted or filtered copy that updates automatically:

=FILTER(B2:F8, (F2:F8>=35000) * ((D2:D8="Pune") + (D2:D8="Kochi")))

This lists staff earning ₹35,000 or more in Pune or Kochi: Ananya and Meera. Use the menu for quick, one-off questions; use the functions for reports that other people will open again and again.

Removing duplicates

Data > Remove Duplicates deletes repeated rows. Tick the columns that define a duplicate, for example only Emp ID. It permanently removes rows, so work on a copy, or use UNIQUE to list distinct values without deleting anything.

Common mistakes

• Sorting one column alone. Rows get scrambled. Always expand the selection, or click one cell and let Excel detect the list.
• Empty rows inside the data. Excel stops detecting the list at the gap, so only part of it is sorted or filtered.
• Totals that disappear. A total row inside the filtered range can be hidden by the filter. Keep totals outside.
• Forgetting a filter is on. Hidden rows still exist, and copied formulas or SUM include them. Check for blue row numbers.
• Sorting a list with merged cells. Excel refuses. Unmerge first.

Key points

• Click one cell in the list and use Data > Sort so Excel moves whole rows together.
• Add levels in the Sort dialog to sort by several columns; use Custom List for orders like months or priorities.
• Ctrl+Shift+L turns filters on and off; filters on different columns combine with AND.
• SUBTOTAL(109, range) totals only visible rows; plain SUM totals everything.
• The SORT and FILTER functions give live, separate results; the menu commands change the view of the data itself.

=SUBTOTAL(101, F2:F8)

33875