How to Filter Data in Excel: A Practical Guide

Filtering in Excel hides rows that do not match your criteria and keeps the matching records visible. Unlike sorting, it does not rearrange the list. You can narrow a report to one region, find orders above a threshold, show dates from the current month, or combine several conditions.

Excel offers two distinct filtering methods. AutoFilter works directly on a range or Table and temporarily hides rows. The FILTER function produces a separate, dynamic result in another part of the worksheet. This guide shows when and how to use both.

Choose the filtering method that fits the task

This article focuses on AutoFilter and the FILTER function, the two methods most readers need for everyday lists.

Prepare a clean data range

Filters work most reliably when the data is rectangular: one header row, one field per column, and one complete record per row. Avoid blank rows or columns inside the list, merged cells in the header area, and mixed data types within a column.

The examples use this order log in A1:G9:

This sample order log in A1:G9 is the source data used for the filtering examples.

Remember: filtering hides complete worksheet rows; it does not delete their contents. A filtered workbook can therefore contain records that are not currently visible.

Turn on AutoFilter and select values

To show only North region orders:

1. Select any cell inside the order log.
2. Choose Data > Filter. Excel adds a filter arrow to each header.
3. Open the arrow in the Region header.
4. Clear (Select All), select North, and choose OK.
5. Check the row numbers and Region values to confirm that only the intended records remain visible.

The sample now shows orders OR-201, OR-204, and OR-208. The other rows still exist; Excel has only hidden them from the current view.

After turning on AutoFilter, the Region column is filtered to show only North records.

You can also type in the filter menu’s Search box instead of scanning a long checklist. The asterisk * represents any sequence of characters, while the question mark ? represents one character. For example, *book* finds text containing “book.” Use a tilde before a wildcard when you need the literal character, such as ~*.

Combine filters across columns

Filters applied to different columns work together as an AND rule. After filtering Region to North, open the Status filter and select Open. Excel shows rows where Region is North and Status is Open: OR-201 and OR-208.

Filters in different columns work together as an AND condition, so only rows that match both Region = North and Status = Open remain visible.

The checklist in a single column works differently. Selecting North and East in the Region menu means North or East because a cell can match either selected value. This distinction is useful:

• Several values selected in one column create an OR choice.
• Filters placed on separate columns combine as AND conditions.

As filters accumulate, the filter icon in each affected header identifies the columns restricting the view. Open that menu to inspect or clear the column’s rule.

Filter text, numbers, and dates by condition

The available menu changes according to the column’s data type. Microsoft documents Text Filters, Number Filters, and Date Filters in its range and table filtering guide.

Text conditions

Open the Product filter and choose Text Filters. Options include Equals, Does Not Equal, Begins With, Ends With, Contains, and Does Not Contain. Use Contains with note, for example, to display Notebook records without typing the complete value.

A Text Filter using Contains with "note" returns the Notebook records without requiring the full product name.

Number conditions

Open the Units filter and choose Number Filters. To show orders from 10 through 25 units, choose Between, enter 10 and 25, then select OK. You can also use Greater Than, Less Than, Above Average, Below Average, and Top 10.

A Number Filter set to Between keeps only rows with Units from 10 through 25.

Despite its label, the Top 10 filter can return a different number of items or a percentage. It ranks the original list rather than the visible subset produced by another column’s filter.

Date conditions

A column containing valid Excel dates offers options such as Before, After, Between, This Month, Last Month, and Year to Date. Dynamic choices such as Today or This Week can return different rows when the date changes and the filter is reapplied.

If Excel displays Text Filters instead of Date Filters, the dates may be stored as text. Convert them to valid date values before filtering; changing only the display format does not necessarily convert the underlying text.

Use two conditions in one column

A Custom AutoFilter can apply two tests to the same field. To display revenue from 500 through 900:

1. Open the Revenue filter.
2. Choose Number Filters > Custom Filter or Between, depending on the Excel interface.
3. Set the first test to is greater than or equal to 500.
4. Select And.
5. Set the second test to is less than or equal to 900, then choose OK.

A custom number filter keeps only rows where Revenue is between 500 and 900.

Use And when both comparisons must be true. Use Or when either test can be true—for example, revenue below 400 or above 1,000.

Filter blanks, colors, and icons

If a column contains empty cells, its checklist includes (Blanks). Select only that entry to locate incomplete records, or clear it to exclude empty values.

Excel can also filter by cell color, font color, or conditional-formatting icon. Open the relevant column’s menu, choose Filter by Color, and select the format to keep visible. This works best when the formatting has a consistent meaning. A dedicated Status or Priority column is usually easier to validate than color alone.

Convert the range to an Excel Table

For a list that grows, an Excel Table is safer than a fixed range. Select the data and choose Insert > Table, then confirm that the table has headers. Filter controls appear automatically, and the table expands when you append records directly below it.

Table filters and sort criteria are saved with the workbook. They can be reapplied after data is added, changed, deleted, or recalculated.

Count and total only visible rows

A regular SUM still includes values in filtered-out rows. Use SUBTOTAL when a summary should respond to the filter.

This formula counts visible, nonblank Order IDs:

=SUBTOTAL(103,A2:A9)

This formula adds revenue from visible rows:

=SUBTOTAL(109,F2:F9)

SUBTOTAL updates with the filter and returns a count of visible orders and the sum of visible revenue only.

Filtered-out rows are excluded by SUBTOTAL. Function numbers in the 100 series, including 103 and 109, also exclude rows hidden manually. Microsoft’s SUBTOTAL reference lists the available operations and function numbers.

Clear, reapply, or remove filters

These commands have different effects:

• Clear one column: open its filter menu and choose Clear Filter From followed by the column name.
• Clear all criteria: choose Data > Clear. All rows reappear, but the filter arrows remain.
• Remove AutoFilter: choose Data > Filter again. The arrows disappear.
• Reapply: choose Data > Reapply after source values or formula results change.

A filter does not always refresh merely because a cell value changed. Reapply it when an edited value, a new row, or recalculation should alter which records qualify. Microsoft provides the current commands in its guide to reapplying and clearing filters.

Copy only the visible filtered rows

Copying a filtered range can include hidden cells in some Excel workflows. In the Windows desktop app, use this explicit method when only visible records should be copied:

1. Select the filtered range.
2. Choose Home > Find & Select > Go To Special.
3. Select Visible cells only and choose OK.
4. Copy the selection, choose the upper-left destination cell, and paste.

Excel for the web does not offer the same visible-cells-only command for an ordinary range. Microsoft notes that a filtered Excel Table can be copied with only its visible cells, or the workbook can be opened in the desktop app. See Copy visible cells only for platform-specific instructions.

Create a dynamic result with FILTER

AutoFilter hides rows in the original list. The FILTER function leaves that list untouched and returns matching rows in a separate spill range. It is available in Microsoft 365, Excel 2021, and Excel 2024 on supported platforms.

Enter a region in J2, then place this formula in an empty area:

=FILTER(A2:G9,B2:B9=J2,"No matches")

The FILTER function returns all rows from A2:G9 where the Region value matches the criterion entered in J2.

The first argument is the returned data. The second creates a row-by-row TRUE/FALSE test, and the third supplies a readable result when nothing matches. Leave enough empty space for the output or Excel will return #SPILL!.

Require multiple conditions

Enter a minimum unit count in K2. Multiplication makes both conditions mandatory:

=FILTER(A2:G9,(B2:B9=J2)*(E2:E9>=K2),"No matches")

Multiplication inside the FILTER function applies AND logic, returning only rows where Region matches North and Units are at least 20.

This returns rows where the Region matches J2 and Units are at least the value in K2.

Allow either condition

Enter a status in L2. Addition creates an OR test:

=FILTER(A2:G9,(B2:B9=J2)+(G2:G9=L2),"No matches")

This returns a row when its Region matches J2 or its Status matches L2. To sort the matching records by Revenue from highest to lowest, nest the result inside SORT:

=IFERROR(SORT(FILTER(A2:G9,B2:B9=J2),6,-1),"No matches")

If your regional settings use semicolons as formula argument separators, replace the separating commas with semicolons. Microsoft’s FILTER function reference documents its syntax, supported versions, and spill behavior.

Fix common filtering problems

Frequently asked questions

Build filters that remain trustworthy

Start with a clean list and decide whether you need a temporary view or a separate output. AutoFilter is fast and interactive; an Excel Table helps the filtered source expand; FILTER is better for a live report elsewhere on the sheet.

Before using or sharing the results, check the filter icons, confirm the visible row count, and remember that hidden records still exist. Those small checks keep an apparently tidy view from masking incomplete or outdated criteria.