Meritshot

Chapter 9 of 15

Sorting & Filtering

Organise and slice your data — single and multi-level sort, AutoFilter, Advanced Filter, SORT and FILTER functions in Excel 365.

Meritshot9 min read
ExcelSortFilterAutoFilterAdvanced FilterSORTFILTER
All Excel Chapters

Why Sorting and Filtering?

Raw data in the order it was entered is rarely useful. Sorting and filtering let you:

  • Sort: Rank data by value, find top/bottom performers, organise names alphabetically
  • Filter: Show only the rows you care about — specific departments, date ranges, value thresholds

Sorting

Single-Column Sort

  1. Click any cell in the column you want to sort
  2. Data tab → Sort A to Z (ascending) or Sort Z to A (descending)
  3. Or: Home → Sort & Filter → Sort A to Z

Shortcut: Right-click a cell → Sort → Sort A to Z / Sort Z to A

Multi-Level Sort

Sort by multiple columns (e.g., sort by Department first, then by Salary within each department):

  1. Data → Sort (opens the Sort dialog)
  2. Add levels:
    • Level 1: Column B (Department) — A to Z
    • Level 2: Column C (Salary) — Largest to Smallest
  3. Click OK

The result: employees grouped by department, and within each department sorted highest salary first.

Sort Options

In the Sort dialog:

  • Order: Smallest to Largest, A to Z, or Custom List (e.g., Mon, Tue, Wed)
  • Sort On: Cell Values, Cell Color, Font Color, or Cell Icon (for conditional formatting)
  • My data has headers: Always check this if your first row is headers

Custom Sort Order

For sorting months (not alphabetically):

  1. Data → Sort → Order → Custom List
  2. Select "Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec"
  3. Months now sort in calendar order, not A-Z

SORT Function (Excel 365)

Dynamic sorting with a formula — output updates automatically when source data changes:

=SORT(array, [sort_index], [sort_order], [by_col])

=SORT(A2:C11)                  → sort by first column, ascending
=SORT(A2:C11, 3, -1)           → sort by column 3 (Salary), descending (-1)
=SORT(A2:C11, 2, 1)            → sort by column 2 (Department), ascending

-- Sort by multiple columns:
=SORT(A2:C11, {2,3}, {1,-1})   → sort by col 2 ascending, then col 3 descending

Results spill automatically into adjacent cells — no need to format a table.

SORTBY Function (Excel 365)

Sort one range by another range (not necessarily in the same table):

=SORTBY(array, by_array1, sort_order1, by_array2, sort_order2, ...)

=SORTBY(A2:C11, C2:C11, -1)
→ Sort the employee table by salary (column C), highest first
→ C column doesn't need to be in the sort range

=SORTBY(A2:A11, {C2:C11, D2:D11}, {-1, 1})
→ Return names sorted by salary desc, then by years asc

AutoFilter

AutoFilter adds dropdown arrows to every column header — click to filter rows:

Enabling AutoFilter

  1. Click any cell in your data
  2. Data → Filter (or Ctrl + Shift + L)
  3. Dropdown arrows appear on every header

Using Filters

Click the dropdown on any column:

  • Checkboxes — check/uncheck specific values to show/hide
  • Search box — type to find values quickly
  • Number Filters — Greater Than, Less Than, Between, Top 10, Above Average, etc.
  • Text Filters — Contains, Does Not Contain, Begins With, Ends With
  • Date Filters — This Week, Last Month, Q1, This Year, Before/After, Between

Multi-column filtering: Filter on column B (Department = Finance), then filter on column C (Salary > 75000) — both filters apply simultaneously (AND logic).

Clearing Filters

  • Remove filter from one column: Click dropdown → Clear Filter From "Column Name"
  • Remove all filters: Data → Clear
  • Remove AutoFilter entirely: Ctrl + Shift + L again

Keyboard Shortcut to Toggle AutoFilter

Ctrl + Shift + L — toggles AutoFilter on/off.

Filtering Shortcuts

After enabling AutoFilter:

  • Alt + Down Arrow on a header cell — opens the dropdown
  • Enter to apply filter
  • Ctrl + Shift + L twice — clear and reapply all filters

Advanced Filter

Advanced Filter offers more control — filter with complex criteria, extract to another location, or get unique values only.

Setup

  1. Create a criteria range above or to the side of your data:
Criteria range:
Department   Salary
Finance      >80000
  1. Data → Advanced (in the Sort & Filter group)
  2. Choose:
    • Filter the list, in-place — hides rows, like AutoFilter
    • Copy to another location — extracts results to a new range

OR vs AND in Criteria

-- AND (same row): Finance AND salary > 80000
Department   Salary
Finance      >80000

-- OR (different rows): Finance OR salary > 80000
Department   Salary
Finance      
             >80000

Unique Records Only

Enable Unique records only in the Advanced Filter dialog to extract a deduplicated list.

-- Extract unique department names to another column:
1. List action: Copy to another location
2. Criteria range: (leave blank to get all records)
3. Copy to: E1
4. Check: Unique records only
→ E column: Finance, Technology, Marketing, HR (no duplicates)

FILTER Function (Excel 365)

Dynamic filtering with a formula — updates automatically:

=FILTER(array, include, [if_empty])

=FILTER(A2:C11, B2:B11="Finance")
→ Returns all rows where department = Finance

=FILTER(A2:C11, C2:C11>80000, "No results")
→ Returns all rows where salary > 80000; shows "No results" if none match

-- Multiple conditions (AND):
=FILTER(A2:C11, (B2:B11="Finance") * (C2:C11>80000))
→ Rows where dept = Finance AND salary > 80000

-- Multiple conditions (OR):
=FILTER(A2:C11, (B2:B11="Finance") + (B2:B11="Technology"))
→ Rows where dept = Finance OR Technology

Combining FILTER with SORT

=SORT(FILTER(A2:C11, B2:B11="Finance"), 3, -1)
→ Filter Finance employees, then sort by salary descending

UNIQUE Function (Excel 365)

Extract distinct values from a range:

=UNIQUE(B2:B11)           → list of unique departments
=UNIQUE(A2:C11)           → unique rows (all columns)
=UNIQUE(A2:A11, FALSE, TRUE)  → TRUE = show values that appear exactly once

Filtering with Functions (Non-365)

Before Excel 365, use helper columns and COUNTIFS for filtered summaries:

-- Visible rows only (sum filtered data):
=SUBTOTAL(9, C2:C11)    → SUM of visible (filtered) rows only; 9 = SUM function code
=SUBTOTAL(1, C2:C11)    → AVERAGE of visible rows
=SUBTOTAL(3, A2:A11)    → COUNT of visible rows

-- SUBTOTAL function codes:
1=AVERAGE, 2=COUNT, 3=COUNTA, 4=MAX, 5=MIN, 9=SUM, 10=STDEV
→ Use 101-111 to also exclude manually hidden rows

Practical Examples

Example 1: Top 10 Performers List

Using SORT + FILTER together (Excel 365):
=SORT(FILTER(A2:D11, D2:D11=5), 3, -1)
→ All employees with rating 5, sorted by salary high to low

Without 365 — manual approach:
1. Filter column D = 5 using AutoFilter
2. Sort column C (Salary) descending

Example 2: Department Summary (Filtered)

Using SUBTOTAL to calculate on filtered data:

Set up a summary row above or below the data:

Total visible:    =SUBTOTAL(9, C2:C11)    → updates when filter changes
Average visible:  =SUBTOTAL(1, C2:C11)
Count visible:    =SUBTOTAL(3, A2:A11)

Use FILTER with SEARCH for a live search:

B15 = search term (e.g., "Pri")

=FILTER(A2:C11, ISNUMBER(SEARCH(B15, A2:A11)), "No match")
→ Shows all rows where name contains the text in B15
→ Updates instantly when B15 changes

Example 4: Extract Unique List for a Dropdown

=SORT(UNIQUE(B2:B11))   → sorted unique department list
→ Use this as the source for a Data Validation dropdown list (Chapter 12)

Sorting Tips for Large Datasets

  • Sort before filtering when your filter depends on position (e.g., top 5 by value)
  • Use Tables (Ctrl+T) — AutoFilter applies automatically, and the sort order is maintained when adding new rows
  • Avoid sorting raw data directly if you need to restore the original order — add an index column (1, 2, 3...) first so you can re-sort to restore

Common Mistakes

1. Sorting without selecting the full table

Select only column C → Sort → only C sorts; A and B stay put → data corruption!
Fix: Always click one cell (not a range), then sort → Excel auto-extends to the contiguous region

2. Leaving a filter on and not noticing

The row numbers of filtered data are not sequential (e.g., 1, 3, 7, 12). Always check the row numbers and the filter indicator in the column headers (the funnel icon).

3. SUBTOTAL counts hidden rows (with some codes)

=SUBTOTAL(3, A2:A11)     → excludes filtered rows but includes manually hidden rows
=SUBTOTAL(103, A2:A11)   → excludes BOTH filtered and manually hidden rows

4. FILTER returns #CALC! if result is empty

=FILTER(A2:C11, B2:B11="Nonexistent")   → #CALC! error
Fix: =FILTER(A2:C11, B2:B11="Nonexistent", "No results")

Practice Exercises

  1. Sort the employee data by department (A-Z), then by salary within each department (high to low).
  2. Use AutoFilter to show only employees in Finance or Technology with salary above ₹80,000.
  3. Use Advanced Filter to extract a unique list of department names to a separate column.
  4. Use SUBTOTAL to calculate the average salary of only the currently visible (filtered) rows.
  5. Write a FILTER formula (Excel 365) that returns all employees with a rating of 4 or 5, sorted by salary descending.

Summary

In this chapter you learned:

  • Single-column sort: Data → Sort A to Z / Z to A
  • Multi-level sort: Data → Sort dialog → add sort levels
  • Custom sort order — calendar months, custom lists
  • =SORT(array, col_index, order) — dynamic sort formula (Excel 365)
  • =SORTBY(array, by_range, order) — sort one range by another (Excel 365)
  • AutoFilter (Ctrl+Shift+L) — dropdown filters; Number/Text/Date filter sub-menus
  • Advanced Filter — filter with criteria range; copy results; extract unique records
  • =FILTER(array, condition, if_empty) — dynamic filter formula (Excel 365); use * for AND, + for OR
  • =UNIQUE(range) — extract distinct values (Excel 365)
  • =SUBTOTAL(code, range) — aggregate only visible rows (code 9=SUM, 1=AVERAGE, 3=COUNTA)
  • Always click a single cell before sorting to let Excel auto-extend the selection

Next up: Pivot Tables — summarise thousands of rows instantly with drag-and-drop analysis.

Previous

Statistical Functions

Next

Pivot Tables