What You’ll Learn in This Lesson
- ✓How sorting works and why it will not break your data
- ✓How to sort a single column and use multi-level sort in Excel
- ✓What Excel AutoFilter is and how to filter rows without deleting them
- ✓How to use Excel custom filter with two conditions at once
- ✓The difference between clearing a filter and removing one entirely
Sorting and filtering in Excel are probably the two skills that will save you the most time in your day-to-day work — and most beginners have no idea they exist until someone shows them.
Picture this. You are at your desk on a Monday morning. Your manager has just forwarded you a spreadsheet with 400 rows of customer orders and asked you to pull out everything from last quarter that was worth more than five thousand dollars. You scroll down, squinting at dates and numbers, trying to spot the ones that qualify. Twenty minutes later you have found maybe half of them and your eyes are starting to cross. Here’s the thing — with the right Excel skills, that same job takes about 45 seconds. Not an exaggeration.
By the time you reach the end of this lesson, you will know how to organize any dataset in any order you choose, and how to instantly hide everything that is not relevant to what you need right now. This is Lesson 7 of the XplorExcel beginner series. If you are brand new to Excel, start with Lesson 6 on AutoFill & Flash Fill before continuing here.
Part 1 — Sorting and Filtering in Excel: The Basics
What Does Sorting Actually Do?
Before you touch anything on the ribbon, it is worth understanding what is actually happening when you sort. Think of your spreadsheet like a stack of index cards. Each row is one card, and each card has several pieces of information written on it. When you sort, Excel shuffles those cards into a new order based on whichever column you choose. The key thing is this: the whole card moves together.
Sorting does not delete data. It does not change any of your values. It only changes the sequence of your rows. And if you sort and immediately regret it, press Ctrl + Z to undo and everything snaps back to where it was.
How to Sort a Column A to Z (or Z to A)
STEP BY STEP: Sort a Single Column
- Click on any single cell inside the column you want to sort by.
- Go to the Data tab on the ribbon.
- In the Sort & Filter group, click the A→Z button to sort ascending, or the Z→A button to sort descending.
- Excel detects your full data range automatically and sorts every row together.
💡 PRO TIP
Right-click any cell in the column you want to sort and choose Sort from the context menu. You get the same options without touching the ribbon — faster once you get used to it.
Understanding the Sort Warning Dialog Box
You’ve probably seen this one. You go to sort a column and a dialog box pops up asking you to choose between two options. It feels alarming when you do not know what it means.
Expand the selection — sorts the whole table so your rows stay together. This is almost always what you want.
Continue with current selection — sorts only the column you selected and leaves everything else in its original order. This separates your data and can corrupt your spreadsheet.
Trust me on this: always choose Expand the selection.
⚠️ COMMON MISTAKE
Clicking the column letter at the top to select the entire column before sorting. This triggers the warning dialog unnecessarily. Instead, just click a single cell anywhere inside your data and sort from there — Excel figures out the rest.
Sorting Numbers and Dates
The same three-click process works for numbers and dates. Excel reads the data type in your column and adjusts accordingly.
- For numbers: A→Z sorts smallest to largest. Z→A sorts largest to smallest.
- For dates: A→Z sorts oldest to newest. Z→A sorts newest to oldest.
- If your date sort looks off, check that cells are formatted as Date, not as Text.
⌨ KEYBOARD SHORTCUTS — SORTING
Alt + A + S + A → Sort A to Z
Alt + A + S + D → Sort Z to A
Alt + A + S + S → Open the Sort dialog box
Ctrl + Z → Undo the last sort instantly
Multi-Level Sort in Excel: Sort by More Than One Column
When Do You Need a Multi-Level Sort?
Imagine you manage a team of 80 employees across five departments. Your manager wants a roster sorted alphabetically by department, and within each department, sorted alphabetically by last name. A single-column sort cannot do this. Multi-level sort in Excel solves this — you tell Excel what to sort by first, and then what to sort by second, all in one operation.
How to Use the Sort Dialog Box
STEP BY STEP: Multi-Level Sort
- Click any cell inside your data range.
- Go to the Data tab and click the word Sort to open the full dialog box.
- In the Sort by dropdown, choose your first sort column (e.g. Department). Set the Order to A to Z.
- Click Add Level. A second row of dropdowns appears.
- In the Then by dropdown, choose your second sort column (e.g. Last Name). Set the Order to A to Z.
- Click OK. Excel sorts by Department first, then by Last Name within each department.
💡 PRO TIP
Inside the Sort dialog, the Order dropdown also lets you sort by Cell Color or Font Color. If you use color-coding to flag important rows, this lets you float all your red rows to the top. Most people never find this option.
⚠️ COMMON MISTAKE
Adding the same column twice across two sort levels. Each level needs a different column to do anything useful. If both levels say Department, the second level is redundant and Excel will effectively ignore it.
Excel AutoFilter: How to Filter Rows Without Deleting Them
What Is AutoFilter and How Does It Work?
Here is the single most important thing to know about filtering before you start: filtering hides rows — it does not delete them.
Think of it like a window blind. You pull it down and the view outside disappears from sight — but the world is still there. Pull the blind back up and everything is exactly as it was. When you apply a filter and rows vanish, they are only hidden. Clear the filter and they all come back instantly.
Excel AutoFilter adds small dropdown arrows to each of your column headers. You click those arrows to choose what you want to see. Everything else gets hidden until you say otherwise.
STEP BY STEP: Enable Excel AutoFilter
- Click any cell inside your data.
- Go to the Data tab.
- Click the Filter button (it looks like a funnel). Dropdown arrows appear on every column header.
- To turn AutoFilter off, click the Filter button again. All hidden rows return.
Filtering by Specific Values (Checkbox Method)
STEP BY STEP: Filter Excel Rows by Value
- Click the dropdown arrow on your column header.
- A list of all unique values in that column appears, each with a checkbox.
- Click Select All to uncheck everything at once.
- Check the box next to the value you want to see.
- Click OK. Row numbers will turn blue to indicate a filter is active.
Filtering by Text, Number, or Date Criteria
The dropdown arrows give you access to more powerful filter options depending on your column data type:
- Text Filters: Equals, Contains, Does Not Contain, Begins With, Ends With
- Number Filters: Greater Than, Less Than, Between, Above Average, Top 10
- Date Filters: Today, This Week, This Month, Last Quarter, Between
How to Clear a Filter vs. Remove a Filter
These two actions sound similar but they do very different things.
- Clearing a filter — makes all hidden rows visible again, but the dropdown arrows stay in place. Go to Data > Clear. Use this for everyday work.
- Removing AutoFilter — turns off the whole feature. The dropdown arrows disappear entirely. Click the Filter button again on the Data tab to toggle it off.
Excel Custom Filter: Get More Precise Results
What Is a Custom AutoFilter?
Sometimes the standard filter options are not precise enough. What if you need orders worth more than $1,000 but less than $5,000? Or customers whose names contain either “Smith” or “Jones”? The Excel custom filter lets you set up to two conditions on the same column and connect them with either AND (both must be true) or OR (either is enough).
STEP BY STEP: Set Up an Excel Custom Filter
- With AutoFilter active, click the dropdown arrow on the column to filter.
- Hover over Number Filters or Text Filters depending on your data type.
- Click Custom Filter at the bottom of the submenu.
- Set your first condition — e.g. is greater than → 1000.
- Select AND or OR using the radio buttons.
- Set your second condition — e.g. is less than → 5000.
- Click OK.
💡 PRO TIP
Use the OR option when you want to capture two separate groups. Filtering a text column for Contains “Smith” OR Contains “Jones” will show every row that has either name anywhere in the cell — very handy for grouped lookups.
⚠️ COMMON MISTAKE
Mixing up AND with OR. AND is exclusive — it keeps only rows that satisfy every condition. OR is inclusive — it keeps any row that satisfies at least one. If you use AND when you mean OR, you might get zero results and think something is broken. Just switch to OR and try again.
Useful Custom Filter Options to Know
- Equals / Does Not Equal — exact match or exclusion
- Contains / Does Not Contain — finds text anywhere inside a cell
- Begins With / Ends With — matches the start or end of a cell value
- Greater Than / Less Than — numeric or date boundaries
- Between — shortcut for ≥ AND ≤ in one step
- Above Average — Excel calculates the column average and shows only rows that beat it
Real-World Practice Scenarios
Scenario 1 — Sales Data: Filter by Quarter and Deal Size
You have a 500-row sales log. Your manager wants only the deals closed between January 1 and March 31 that were worth more than $10,000.
Step 1: Click the Date column dropdown → Date Filters → Between → enter 01/01/2024 and 31/03/2024 → OK.
Step 2: Click the Deal Value dropdown → Number Filters → Greater Than → enter 10000 → OK.
🎯 TRY IT YOURSELF
Create a simple table with 20 rows including a Date column and a Value column. Fill in varied dates across two or three months and a mix of values. Apply a date filter, then layer a number filter on top and see how they combine.
Scenario 2 — HR Roster: Multi-Level Sort by Department and Name
You have an 80-row employee list. Your manager wants it sorted by Department (A→Z) and within each department, by Last Name (A→Z). Open the Sort dialog, set Department as Level 1, click Add Level, set Last Name as Level 2, click OK. Ten seconds of work — perfectly organized roster.
Scenario 3 — Inventory List: Sort by Category, Then Price
A supplier sent you a 300-product inventory in random order. You need it grouped by Category and within each category sorted by Price from lowest to highest. Open the Sort dialog, add Category (A→Z) as Level 1, add Price (Smallest to Largest) as Level 2. Done — clean, scannable, and ready to share.
🎯 TRY IT YOURSELF
Build a quick ten-row table with a Category column (three or four different categories) and a Price column with varied numbers. Sort it through the Sort dialog. Once that works, add a third sort level just to get comfortable with how it stacks.
Quick Reference — Sorting & Filtering Cheat Sheet
What’s Next?
You now know how to sort, multi-level sort, AutoFilter, and custom filter in Excel. These are not just beginner skills — they are tools that experienced Excel users rely on every single day. You are going to reach for what you learned in this lesson constantly.
If you want to make sorting and filtering even more reliable, head back to Lesson 6 on AutoFill & Flash Fill and then convert your data range into a proper Excel Table — it gives you AutoFilter built in by default and makes your sorts more predictable every time.
Up next: Lesson 8 — Basic Charts in Excel, where you will learn to turn your organized, filtered data into clear visual charts that anyone can understand at a glance.
📚 Further Reading
Previous Lesson
← AutoFill & Flash Fill in Excel