Skip to content
Tutorials Center
My learning

Topics

Lesson 42 of 93 ยท Tables, Sorting and Filtering

Filtering Data

Learn to filter data in Excel: AutoFilter text, number and date filters, filter by color, the Advanced Filter criteria range, and the FILTER function.

Filtering answers the question "show me only the rows I care about". Instead of scrolling through thousands of records, you tell Excel the condition, and every row that fails it is hidden. The data is not changed or deleted, only hidden from view until you clear the filter. Excel gives you three tools for the job: the AutoFilter buttons for quick everyday work, the Advanced Filter for complex conditions and copying results elsewhere, and the FILTER function that builds a live result with a formula.

You should already know the shape of a good data list (a header row, one record per row, no blank rows), which is covered in Excel Tables and Sorting Data. The buttons and dialogs below are real Excel. The runnable examples are stand-ins in JavaScript and pandas that apply the same logic, so you can see which rows survive.

Three ways to filter

AutoFilter Drop-downs on the header row.
Advanced Filter A criteria range, and copy to another place.
FILTER function A formula that returns matching rows.

AutoFilter hides rows. FILTER builds a new list.

1 / 2

AutoFilter

Click any cell in your data and press Ctrl+Shift+L (or choose Data โ†’ Filter). Small drop-down arrows appear on every header. Click one and you get a list of the distinct values in that column with checkboxes, a search box, and a Text Filters, Number Filters or Date Filters submenu depending on the column's content.

Rows that do not match are hidden, the row numbers turn blue, and the status bar shows something like "12 of 200 records found". A column with an active filter shows a funnel icon on its button. Use Data โ†’ Clear to remove all filters but keep the buttons, or press Ctrl+Shift+L again to switch AutoFilter off.

How several filters combine

  • Across columns the filters combine with AND. A filter on Region and another on Score shows only rows that satisfy both.
  • Inside one column, ticked values combine with OR. Ticking East and West shows rows that are either.
  • Each column's drop-down lists values that still exist among the rows left by the other filters.

Text, number and date filters

The submenus build a condition without any formulas:

  • Text Filters: Equals, Does Not Equal, Begins With, Ends With, Contains, Does Not Contain, and Custom Filter for two conditions joined by And/Or. In the search box and in filter text you can use wildcards: ? matches one character, * matches any run of characters, and ~ escapes them.
  • Number Filters: Greater Than, Between, Top 10 (which can mean top or bottom, items or percent), Above Average and Below Average.
  • Date Filters: Today, This Week, Last Month, Next Quarter, Year to Date, Between, and All Dates in the Period for a chosen month or quarter. In recent versions the drop-down list groups dates into a tree of Year โ†’ Month โ†’ Day.
JS A stand-in for AutoFilter

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The last line returns nothing: G??? means a G plus exactly three characters, so none of the product names match.

Filter by color and copy filtered results

If cells have fill or font colors, or icons from conditional formatting, the drop-down also offers Filter by Color. You can filter by cell color, font color or icon, one column at a time. As with sorting, the color is a property of the cell at the moment you filter, so re-apply the filter after the colors change.

Filtered rows are hidden, not deleted. To work on just the visible results:

  • Select the visible range and copy it. Excel copies only the visible rows by default, so pasting elsewhere produces just the filtered list.
  • SUBTOTAL(109, C2:C200) sums only visible cells, and SUBTOTAL(103, A2:A200) counts visible non-empty cells. A plain SUM still adds the hidden rows too. The SUBTOTAL codes ignore rows hidden by a filter in either form (101-111 and 1-11), and the 100-series also ignores manually hidden rows.
  • Deleting visible rows while a filter is on deletes just those rows, which is a handy way to clean up, but check the row count first.

Advanced Filter

Data โ†’ Advanced uses a criteria range, a few cells that you write yourself, usually above or beside the data. The first row holds column headings copied exactly from the data, and the rows beneath hold the conditions.

  • Conditions on the same row are combined with AND.
  • Conditions on different rows are combined with OR.

For example, this criteria range keeps East rows with at least 10 units, or any West row:

TEXT
Region    Units
East      >=10
West

The dialog also lets you Copy to another location (so the original list stays visible and a fresh filtered copy lands elsewhere, with the headings you choose) and tick Unique records only, which removes duplicate rows from the result. A criterion can even be a formula, as long as the cell above it is blank or uses a heading that is not one of the data headings, and the formula refers to the first data row with a relative reference. Advanced Filter results do not update when the data changes. You run the command again.

The FILTER function

In Microsoft 365 and Excel 2021 or newer, FILTER returns the matching rows as a live result:

TEXT
=FILTER(array, include, [if_empty])

include is a column or row of TRUE/FALSE values with the same height as array. if_empty is the value to show when nothing matches. Without it, an empty result gives the error #CALC!.

TEXT
=FILTER(A2:D7, (B2:B7="East") * (C2:C7>=10), "No rows")      AND
=FILTER(A2:D7, (B2:B7="East") + (B2:B7="West"), "No rows")   OR
=FILTER(A2:D7, ISNUMBER(SEARCH("garden", A2:A7)), "None")     contains

The tricks from AND, OR, NOT and XOR apply: multiplying conditions is AND, adding them is OR. FILTER is case-insensitive, supports any formula as the test, and the result spills into neighbouring cells (see Dynamic Arrays and Spilling). Combine it with SORT as in FILTER, SORT, SORTBY and UNIQUE to get a sorted report that follows your data.

Use AutoFilter for quick exploration of one list, and FILTER when the result must be a separate, always-fresh block that other formulas can read.

JS A stand-in for FILTER and the criteria range

Press Run (or Ctrl + Enter) to see the console output here.

No output.

The same ideas in pandas

A pandas boolean mask is the equivalent of the include array. & is AND, | is OR, ~ is NOT, and each condition needs its own parentheses:

Python Boolean masks in pandas

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Search-box filters, custom sorts and deleting filtered rows

A few filter and sort tasks are not obvious from the buttons. Excel cannot run on this site, so these are read-only steps, with a small JavaScript stand-in for the search-box logic.

The search box in the filter drop-down

In the AutoFilter drop-down, the Search box filters the list of items as you type. Typing an shows every value that contains "an" (Banana, Mango, Anna). Wildcards work: ? is one character and * any number, so A*n finds values that start with A and end with n. After typing, leave Select All Search Results ticked and click OK to show every match. Tick Add current selection to filter to combine several searches. Searches in dates and numbers match their displayed text, so 2025 finds every date in 2025 and 5 finds any number containing 5, which is often more than you want. Use Number Filters > Equals for exact numbers.

A dynamic filter box driven by a cell is a different technique: type the search text in H1, and use a formula to show only matching rows without touching the filter:

TEXT
=FILTER(A2:D200, ISNUMBER(SEARCH(H1, B2:B200)), "No matches")

As soon as H1 changes, the list updates. To filter by several words, use (ISNUMBER(SEARCH(H1, B2:B200)) + ISNUMBER(SEARCH(H2, C2:C200)) > 0) for OR logic or multiply the tests for AND (FILTER, SORT, UNIQUE). In versions before Microsoft 365, use a helper column =ISNUMBER(SEARCH($H$1, B2)) and filter it for TRUE.

JS Search-box logic: contains, wildcards and a dynamic FILTER

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Custom sort lists and sorting by part of a value

Excel sorts alphabetically, but business lists often have their own order (High, Medium, Low; Mon to Sun). Use Data > Sort > Order > Custom List and either pick a built-in list (days, months) or choose NEW LIST and type the order, one item per line. The list is saved with Excel and is available for any sort. A formula alternative that travels with the file is a helper column with the position in a list: =MATCH(C2, {"High","Medium","Low"}, 0) and sort by that (Sorting Data).

Sort by last name when the cell holds "First Last": extract the last word into a helper column, then sort on it: =TEXTAFTER(A2, " ", -1) (Microsoft 365) or =TRIM(RIGHT(SUBSTITUTE(A2, " ", REPT(" ", 50)), 50)) in any version. Flash Fill (Ctrl+E) can also create the helper from an example. Names with middle words or suffixes ("Jr.") need a cleaner source column. Better still, keep first and last names in separate columns from the start (Joining and Splitting Text).

Delete only the visible (filtered) rows

When a filter is on, deleting the selected rows removes only the visible ones; hidden rows are untouched:

  1. Apply the filter that shows exactly the rows to delete (for example Status = "Cancelled").
  2. Select the data rows (not the header): click the first row number and Shift-click the last visible one, or select the visible cells.
  3. Home > Delete > Delete Sheet Rows (or right-click the row numbers > Delete). Excel deletes only the visible rows.
  4. Clear the filter (Data > Clear) to see the remaining rows.

Check the row count in the status bar ("12 of 340 records found") before and after. Deleting cells (not rows) in a filtered range, or clearing contents with Delete, can behave differently, so prefer Delete Sheet Rows. If the data is a Table, Delete Table Rows does the same. Always keep a copy before bulk deletes. For a repeatable method, Power Query's Remove Rows or a macro avoids manual errors (Power Query Basics).

Swapping columns and splitting cells

  • Swap columns: select a column, hold Shift, and drag its border to the new position until a vertical insertion line appears (Templates, Quick Analysis, Autofill and Small Time-Savers). Filtered data is easier to rearrange after you clear the filter.
  • Split a cell into several: select the column, Data > Text to Columns > Delimited, and choose the delimiter (space, comma, custom). Make sure the cells to the right are empty, or Excel overwrites them; set the destination to a different column to be safe. With Microsoft 365 TEXTSPLIT(A2, " ") does it with a formula. To undo a merged cell, select it and click Merge & Center again.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. You filter Region to "East" and Units to "Greater than 10". Which rows are shown?

    Filters on different columns combine with AND, so a row must pass every column's test.
  2. 2. In an Advanced Filter criteria range, how do you express "East OR West"?

    Conditions on the same row are ANDed; conditions on different rows are ORed.
  3. 3. What does =FILTER(A2:C10, B2:B10="East") return when no row matches and there is no third argument?

    An empty result produces #CALC!. Supply the if_empty argument, such as "No rows", to show a friendly message instead.
  4. 4. A filter hides rows 5 to 20. What does SUM over that column do?

    SUM ignores filters. Use SUBTOTAL(109, range) to add only the visible rows.
  5. 5. What is a key difference between AutoFilter and the FILTER function?

    AutoFilter changes what is visible in the original list. FILTER builds a new block that recalculates when the source changes.

Summary

  • Ctrl+Shift+L toggles AutoFilter; filters across columns are ANDed, ticked values in one column are ORed.
  • Text, number and date submenus build conditions without formulas; wildcards ? and * work in text filters.
  • You can filter by cell color, font color or icon; copying a filtered range copies only the visible rows, and SUBTOTAL counts only visible cells.
  • Advanced Filter uses a criteria range (same row AND, different rows OR) and can copy results elsewhere.
  • FILTER(array, include, [if_empty]) returns live results; multiply conditions for AND, add for OR, and supply if_empty to avoid #CALC!.
  • In pandas the same job is done with boolean masks, isin, between and loc.

Next: Data Validation and Dropdown Lists.