Sorting and Filtering Data in Excel — The Right Way

What You Will Learn

• How to sort data correctly (single column and multiple columns)
• How to filter data without losing information
• The "filter view" vs "delete rows" distinction
• The most common beginner mistake that destroys datasets

Why This Topic Matters

Sorting and filtering are the two operations you will do in almost every spreadsheet session. They look trivial — and they are — but done wrong they silently break your data. A bad sort detaches names from amounts. A bad filter permanently deletes rows you needed. This lesson teaches the safe, repeatable way.

The Sample Dataset

We will use this small orders table throughout. You can re-create it in any spreadsheet:

Sorting in Excel — The Right Way

Single-column sort

1. Click anywhere inside the data (e.g., cell C2).
2. Go to Data → Sort (or use the A→Z / Z→A buttons on the toolbar).
3. Choose Sort by = City, order = A to Z.
4. Check "My data has headers" so the header row stays put.
5. Click OK.

Excel will sort the entire table by city, keeping each row intact. Mumbai rows will group together, then Chennai, then Kolkata.

Sort by multiple columns

Suppose you want to sort by city (A→Z) and then, within each city, by amount (largest first). Use Data → Sort and add two levels:

1. Level 1: Sort by city, A to Z.
2. Level 2: Then by amount, Largest to Smallest.

Result: Anita's two Mumbai orders will appear together, with the larger one (₹500) above the smaller one (₹300).

Sort by custom order

Sometimes alphabetical is not what you want. For status, you might want: paid → pending → refunded. Use Order: Custom List in the Sort dialog and type the order you want. This is useful for any categorical column with a meaningful non-alphabetical order.

Filtering in Excel — The Right Way

Apply the filter

1. Click anywhere inside the data.
2. Go to Data → Filter (or Ctrl+Shift+L).
3. Dropdown arrows appear on each header cell.

Use the filter

Click the dropdown on city. You will see a list of all cities with checkboxes. Uncheck "Select All", then check only "Mumbai". Click OK. Only Mumbai rows are now visible; the others are hidden, not deleted.

Filter by amount

Click the dropdown on amount → Number Filters → Greater Than. Enter 700. Now only rows where amount > 700 are visible: Ravi's ₹1200, Mira's ₹750, Sam's ₹900.

Combine filters

Filters stack. If you filter city = Mumbai AND amount > 700, you get only Mumbai rows with amount > 700. With our dataset, that gives zero rows — Anita's Mumbai orders are ₹500 and ₹300. This zero-row result is itself useful: it tells you Mumbai has no large orders in this dataset.

The Most Common Beginner Mistake

Sorting a single column without selecting the whole table.

If you click on column D (amount) and press the A→Z sort button, Excel will ask: "Expand the selection?" If you click "Sort only the current column", Excel will sort only the amount column — leaving customer, city, and status in their original rows. Now Anita's ₹500 is next to a different customer's name. The data is corrupted.

Always choose "Expand the selection". A row is one record; sorting must move the whole row together.

Filter View vs Delete Rows

Beginners sometimes delete rows they want to exclude from analysis. This destroys data. The right approach:

1. Apply a filter to hide unwanted rows.
2. Copy the visible rows to a new sheet if you need a separate view.
3. Keep the original sheet intact.

This way you can always undo or change the filter. Deleted data cannot be recovered.

Google Sheets Differences

The same operations work in Google Sheets with minor naming differences:

• Data → Create a filter instead of Data → Filter.
• Sort range instead of Sort dialog.
• Sheets has a "Filter view" option that lets multiple users filter the same sheet independently without affecting each other's view. Use it when collaborating.

Common Mistakes

1. Forgetting "My data has headers". The header row gets sorted into the data.
2. Sorting one column only. Rows become internally inconsistent.
3. Deleting instead of filtering. Data is lost permanently.
4. Filtering on formatted display value. If a number is stored as text, number filters will not work. Fix the type first.
5. Trusting the filter visually. Always check the row numbers to confirm what is hidden.

Practical Exercise (10 minutes)

1. Create the sample dataset above in a fresh spreadsheet.
2. Sort by amount, largest to smallest. Confirm Anita's ₹500 row stays together with her name and city.
3. Filter to show only "paid" orders. Confirm there are 3 visible rows.
4. Add a second filter: paid orders with amount > 600. Confirm only Mira (₹750) and Sam (₹900) remain.
5. Clear the filters. Confirm all 5 rows return.

Mini Challenge

Download any open CSV — for example, a small dataset from Kaggle's public datasets. Sort it by two columns (e.g., category then price). Filter it to show only one category with price above the median. Save the result as a new sheet. You have just done your first real data-cleaning operation.

Key Takeaways