To filter a table in Microsoft Excel for Microsoft 365 on Windows, click a cell in the table and use the dropdown arrow in a column heading to choose which rows to show. If your data is not already an Excel table, select a cell in the data and choose Data > Filter, or press Ctrl+Shift+L, to add filter dropdowns.
What filtering an Excel table does
A filter temporarily hides rows that do not meet criteria you choose; it does not delete them or change the values in those rows. For example, a sales table with records for several regions can be filtered to show only West. The other regions disappear from view until you clear or change the filter, but their records remain in the worksheet.
Filtering is useful when a list is too large to scan manually, when you need to focus on a subset of records, or when you want to inspect values that match a condition. It works alongside sorting, but the two operations are different: sorting rearranges the visible rows, while filtering determines which rows are visible. You can use both at once.
An Excel table is a structured range with features such as a header row, consistent formatting, and automatic expansion when you add data below or beside it. Tables show filter arrows in their headings by default. A normal data range can also be filtered, but it does not have all the table-specific behavior.
Turn on filter dropdowns
If the data is already an Excel table, look for a small dropdown arrow in each header cell. Click an arrow to open that column’s filter menu. If the arrows are missing, click inside the table and check the Table Design tab for the Filter Button option; the tab appears when a table cell is selected.
To turn a plain, continuous data range into a table, select one of its cells and press Ctrl+T on Windows. Excel identifies the surrounding data and opens the Create Table dialog. Confirm the range and select My table has headers if the first row contains column names, then choose OK. Excel adds table formatting and filter arrows. On Mac, use Insert > Table or the relevant Table command on the ribbon; menu labels and shortcut assignments can vary by release.
If you want to keep a range as a range, select a cell within it and choose Data > Filter. On Windows, Ctrl+Shift+L toggles the filter arrows for a selected data range. On Mac or Excel for the web, use the Data tab’s Filter command if the Windows shortcut is unavailable or behaves differently. Make sure the list has one header row and avoid blank rows or columns splitting the data into separate blocks.
Apply a filter from a column menu
Click a heading’s dropdown arrow to see the available values and filtering commands. For a simple selection, clear Select All, check the value or values you want, and choose OK. Excel then hides rows whose entries in that column are not selected. The menu’s search box can help locate a value in a long list; selecting a search result does not alter the underlying cell.
The menu changes according to the data type. A text column can offer Text Filters such as Begins With, Contains, or Equals. A numeric column can offer Number Filters such as Greater Than or Between. A date column can offer date-oriented choices, including date ranges or grouped periods. Choosing a custom condition opens a dialog where you enter the comparison value.
You can filter more than one column at a time. Criteria applied in separate columns work together: if you filter Region to West and Amount to values greater than 500, Excel shows rows that satisfy both conditions. Within one column, selecting several listed values generally means show rows matching any selected value. This distinction explains why checking West and East does not require a row to be both regions at once.
Excel may also offer filtering by cell color, font color, or icon when the relevant formatting is present. These commands are useful for examining a manually highlighted subset, but color is not a substitute for a well-defined data field. If the color represents a business category, storing that category in a column is usually easier to filter consistently.
Practical filtering examples
Suppose a table has columns named Date, Region, Product, and Sales. To review sales for one region, open the Region menu and select West. To narrow the results to a product as well, use the Product menu and select the product name. To find large transactions, open Sales, choose Number Filters > Greater Than, and enter a threshold. The combination displays records that meet all the selected column conditions.
For dates, use the date filter options when you need a period such as a month or a range of dates. If dates are stored as real Excel dates, Excel can recognize them as dates and provide date-specific choices. If the column contains text that merely looks like dates, those options may not appear or may behave unexpectedly. Check the cell values and convert text dates into actual dates before relying on date comparisons.
Filtering for blanks can reveal incomplete records, such as transactions with no assigned region. Conversely, filtering out blanks can make a report easier to review. Be careful with cells that look empty but contain a space or a formula returning an empty string: they may not behave exactly like truly blank cells.
Excel table filters versus range filters
Both table filters and range filters use dropdown menus to hide rows, so the everyday filtering experience is similar. The difference is how Excel treats the data around them. A table has a defined name and structured columns, and it normally expands when you add adjacent records. Its formatting and calculated-column behavior can also extend to new rows. These features make tables convenient for lists that are updated regularly.
A range filter is useful when you do not want to convert the data into a table, or when you are working with a layout that is not suited to a standard table. It does not automatically provide all table features. In either case, keep the list rectangular: one header row, one record per row, one field per column, and no merged cells in the data area. Unique, descriptive headers make the filter menu easier to understand.
Filtering a table does not mean that formulas elsewhere in the workbook automatically operate only on visible records. Some functions and subtotaling approaches treat hidden or filtered-out rows differently, so check the function’s behavior before using a filtered view as the basis for a calculation. A visible subset is a way to inspect data, not a separate copy of the dataset.
Change or clear a filter
To change the criteria, reopen the dropdown for the filtered column and select a different set of values or condition. A filter indicator in the heading shows that a filter is active. To clear just one column’s filter, open its menu and choose Clear Filter From [column name], where Excel substitutes the actual heading. Other column filters remain active.
To clear every filter in a table or range while leaving the dropdown arrows available, use Data > Clear. To remove the dropdowns as well, use Data > Filter, or toggle the filter command off. The wording and placement can vary slightly by Excel version, especially in Excel for the web, but the Data tab is the place to look.
If you cannot see expected rows, check each column for an active filter. Several filters can be in force at once, and clearing only one may not restore the full list. Also check whether rows were hidden manually or whether the worksheet is sorted in an unexpected order; clearing a filter does not undo a sort or unhide rows that someone hid separately.
Common filter problems and fixes
- Dropdown arrows do not appear: Confirm that the selected cells are in the data range or table, and use Data > Filter. Check that the sheet is not protected in a way that prevents filtering and that the range has a clear header row.
- The filter misses some records: Look for blank rows or columns that break up the data, or records outside the selected range. Convert a consistent list to a table so added adjacent rows are included more reliably.
- Values appear duplicated in the menu: Entries that look alike may differ because of spaces, punctuation, capitalization, or data type. For example, the number 12 and text “12” can be treated differently. Inspect and standardize the source values when necessary.
- A number or date filter is missing: The column may contain text mixed with numbers or dates, or inconsistent values. Make the column’s contents consistent and verify that dates are stored as dates, not just displayed in a date-like format.
- Rows seem to have vanished: They are usually hidden by the active filter, not deleted. Clear the relevant filters to show them again, and check for manually hidden rows if clearing filters does not restore the view.
When a filter produces an unexpected result, first clear all filters and confirm the source data is intact. Then apply one condition at a time. This makes it easier to identify whether the issue comes from the criteria, inconsistent cell contents, or the boundaries of the range.
When to use the FILTER function instead
The dropdown filter is best when you want to interactively examine the original list. Excel also has a worksheet function named FILTER, available in Microsoft 365 and Excel 2021 or later, that returns matching rows as a separate, automatically expanding result. It is useful when a report should display selected records elsewhere on the sheet and update as source data changes. It does not replace the interactive dropdown menu.
For example, assume an Excel table named Sales has columns Region, Rep, and Amount, with the data rows West, Ava, 1200; East, Ben, 900; and West, Chen, 650. Enter this formula in a blank area outside the table:
=FILTER(Sales,Sales[Region]="West","No matches")
The result is two rows: West, Ava, 1200 and West, Chen, 650. The formula returns the matching table data columns and excludes the table’s header row. Leave enough empty cells below and to the right for the results to spill; existing content in that area can cause a spill error. The source rows remain in the original table. In versions without FILTER, use the table’s dropdown filters or another supported approach rather than expecting this formula to work.
Frequently Asked Questions
How do I filter a table in Excel?
Click the dropdown arrow in a table heading, select the values or condition you need, and choose OK. If the arrows are missing, select a table cell and enable the Filter Button on Table Design, or use Data > Filter for a normal range.
How do I filter multiple columns at once?
Set a condition in each relevant column menu. Excel combines conditions across different columns, so a row must satisfy every active column filter to remain visible.
Does filtering delete rows in Excel?
No. Filtering hides rows that do not match the criteria. Clear the filter to show them again; the underlying cell values are not removed.
How do I remove a filter but keep the arrows?
Choose Data > Clear to clear active filter criteria while retaining the filter controls. To remove the arrows, toggle Data > Filter off.
Why is my Excel filter not showing every value?
Check whether the data range includes every record and whether blank rows split the list. Also look for inconsistent entries, such as numbers stored as text, extra spaces, or manually hidden rows.
Related
- Find Similar Names in Excel: Fuzzy Matching
- Numbers vs. Excel: Key Differences and Which to Choose
- Is Excel on iPad? Features, Limits, and Costs
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Text Unique Values: Extract, Count, and CompareYes. In Excel for Microsoft 365, use UNIQUE to return a list of distinct text values from a…
- Cannot Filter in Excel? Causes, Checks, and FixesYes—Excel can filter both ordinary data ranges and tables. In Microsoft 365 for Windows, select a cell in…
- How to Concatenate a Long List in Excel with TEXTJOINTo concatenate a long list in Excel into one cell, use TEXTJOIN: with your items in A2:A1000, enter…
- Excel IF Formula Examples: Tests, Logic, and ErrorsExcel’s IF formula checks whether a condition is true or false and returns one result for each outcome.…
- How to Group the Same Numbers in Excel: Sort and CountTo group identical numbers in Excel, sort the entire data range by the column containing those numbers: select…
- Average in Excel Without Zeros: Formula and ExamplesTo average numbers in Excel while ignoring zeros, use =AVERAGEIF(A2:A8,"<>0"). Replace A2:A8 with your data range; Excel averages…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.