Tutorials›Excel›Sorting & Filtering Data
🟢 Free Demo
Excel TutorialTopic 29 of 31

Sorting & Filtering Data

Sort data by one or multiple columns and filter datasets to find exactly what you need instantly.

✅ What You Will Learn

›How to sort by one column and by multiple columns simultaneously
›How to apply AutoFilter to show rows matching specific criteria
›How to use Number Filters and Text Filters for advanced conditions
›How to use Advanced Filter to filter to a separate location
›How to sort by colour, icon, or custom list order

Sorting and filtering are the most basic but most frequently used data operations in Excel. Every analyst applies them dozens of times a day — sorting a sales report by revenue descending to see top performers, filtering to a specific region or date range, or showing only the rows where a condition is met.

AutoFilter (Ctrl+Shift+L) adds dropdown arrows to every column header. Clicking a dropdown lets you filter by specific values, by condition (greater than, between, contains), or by colour. Multiple column filters apply simultaneously — the data shows only rows that match ALL active filters.

For more complex filtering — extracting filtered results to a separate location, or applying complex criteria not available in AutoFilter — use Advanced Filter (Data → Advanced).

Examples

Example 1Sort by multiple columns — primary and secondary sort
Steps:
1. Click anywhere in your data
2. Data tab → Sort
3. Sort by: Region (A to Z)   ← primary sort
4. Add Level → Sort by: Amount (Largest to Smallest)  ← within each region

Result: Data sorted alphabetically by Region, then by Amount (high to low) within each region.

Quick single-column sort:
  Click the column header dropdown → Sort A to Z / Sort Z to A
  Or select any cell in the column → Data → A→Z or Z→A buttons
💡

Always use the multi-level Sort dialog (not the quick sort buttons) when sorting by more than one column — quick sort can disrupt the second sort level.

Example 2AutoFilter with conditions — filter for high-value North orders
Steps:
1. Ctrl+Shift+L to toggle AutoFilter on
2. Click Region dropdown → uncheck Select All → check "North" → OK
3. Click Amount dropdown → Number Filters → Greater Than → 20000 → OK

Result: Only North region orders above ₹20,000 are visible.
Status bar at bottom shows: "X of Y records found"

To clear filters:
  Data tab → Clear (removes all active filters but keeps AutoFilter on)
  Ctrl+Shift+L again → turns AutoFilter off completely
💡

Active filters show a funnel icon on the column dropdown. The row numbers of filtered rows turn blue. Hidden rows are not deleted — they are still there and will reappear when filters are cleared.

📌 Key Points to Remember

  • ✓Ctrl+Shift+L toggles AutoFilter on/off. Data → Clear removes active filters without turning AutoFilter off.
  • ✓Filter by colour: click a column dropdown → Filter by Color (works when Conditional Formatting or manual cell colours are applied)
  • ✓Status bar shows count of visible rows when filtered — right-click status bar to add Sum, Average of visible values
  • ✓Advanced Filter can extract filtered results to a different sheet location — useful for creating automated extract reports
  • ✓Sorting is permanent until you sort again — if order matters, add a row number column before sorting so you can return to original order

🏢 Real-World Application

Customer service teams filter support ticket datasets to show only high-priority, unresolved tickets assigned to their team. Sales managers sort the CRM by deal value descending to focus calls on the largest opportunities first. Finance teams filter the GL to a specific cost centre and date range for monthly variance review. Sorting and filtering are used in every analytical workflow, every day.

⚠️ Common Mistakes to Avoid

WRONGSorting only the selected column — destroys row relationships
FIXNever select a single column before sorting. Click one cell anywhere in the data (not the whole column). Excel then sorts the entire row together, keeping all columns aligned.
WRONGForgetting active filters are on — reporting wrong totals
FIXCheck for the funnel icon on column headers and blue row numbers. Always clear all filters (Data → Clear) before building summary reports or saving files for sharing.
WRONGUsing Sort for deduplication — sorted data still has duplicates
FIXSorting places duplicates adjacent for manual review, but does not remove them. Use Remove Duplicates (Data tab) or UNIQUE() function to actually eliminate duplicate rows.
✏️Test Yourself

You apply AutoFilter to show only "Electronics" in Category and amounts > 10000. How many conditions are actively filtering the data?

❓ Frequently Asked Questions

How do I filter by a partial text match (contains)?

Click the column dropdown → Text Filters → Contains → type the search text. For example, "Contains: Laptop" shows all rows where the column contains the word "Laptop" anywhere.

Can I filter data based on values in another column?

AutoFilter filters each column independently. For cross-column conditions (show rows where A > B), use FILTER() function or Advanced Filter with a criteria range that contains the cross-column formula.

How do I copy only the visible (filtered) rows?

After filtering, select the visible range and press Alt+; (select visible cells only). Then Ctrl+C to copy. Pasting will include only the visible rows, not the hidden ones.

✏️ Practice Exercise

Using a 100-row order dataset, perform: (1) Multi-level sort by Region (A→Z) then by Amount (high→low), (2) Filter to show only Delivered orders above ₹15,000 in the North or South region, (3) Copy only the visible rows to a new sheet, (4) Clear all filters, (5) Use Advanced Filter to extract all Cancelled orders to a separate location starting at cell H1. Count how many cancelled orders exist using the status bar.

← PreviousExcel Tables (Format as Table)Next →Protect Sheet & Workbook
🎓 Level Up Faster

Learn Excel with Live Trainer Guidance

These tutorials give you the foundations. Our live Excel course at EVIKA Academy, Noida teaches you to build real dashboards on actual business data — with a trainer who uses Excel professionally every day.