To filter data in an Excel table, click a cell in the table and use the drop-down arrow beside a column heading to choose the values or conditions to display. If your data is not yet a table, select it and choose Insert > Table, confirm whether it has headers, and select OK; Excel adds filter arrows to the headings.
What filtering in an Excel table means
A filter temporarily hides rows that do not meet the conditions you choose. It does not delete those rows or change the values in the underlying table. Clearing the filter makes the hidden rows visible again. This is useful for narrowing a long list to a particular customer, date range, category, status, or range of amounts while keeping the full dataset in the workbook.
An Excel table is a structured range of related data, usually with one header row and one record per row. Tables have built-in sorting and filtering controls, distinct formatting options, and features such as structured references in formulas. A filtered table is still the same table: filtering changes which records are shown, not the table’s membership or stored data.
For example, a sales table might have columns for Date, Region, Product, and Sales. Filtering Region to West hides records from other regions. You can then filter Product to Desk to show only rows that meet both choices.
Turn a data range into a table
If the data already appears as a table with filter arrows, skip this step. Otherwise, first check that each column has a clear heading, there are no completely blank rows or columns splitting the data, and each row represents one record. In Excel for Microsoft 365 on Windows:
- Click a cell inside the data range.
- Select Insert > Table. Excel normally detects the surrounding range; check that the outline includes all the intended columns and rows.
- In the dialog, select My table has headers if the first row contains column names.
- Select OK. Excel formats the data as a table and adds filter arrows to its header cells.
You can also select the range and press Ctrl+T on Windows to open the Create Table dialog. If Excel has mistaken a data row for a heading, or treated a heading as data, cancel and correct the range or the header option before proceeding. Give columns unique, descriptive names; duplicate or blank headings can make later formula and data-management work confusing.
When you add a record directly beneath a table, Excel will often extend the table to include it. Check that the new row is actually inside the table before expecting its filter and formatting behavior. A blank row between the existing table and new data can prevent the range from expanding as intended.
Filter rows from a column heading
Click the arrow in the heading for the column you want to filter. The menu typically offers a search box, a list of values with checkboxes, and commands for sorting and filtering. Clear Select All, select the values you want to keep visible, and choose OK. For instance, in a Region column, selecting only East and North displays records from those two regions.
For text, number, or date columns, the menu may also offer type-specific filters, such as Text Filters, Number Filters, or Date Filters. These let you choose conditions such as begins with, greater than, or a date period. The available choices depend on the values Excel recognizes in that column. A text column may not offer useful number conditions if numbers have been stored as text.
- Use the search box when the value list is long; search for a name or term, then select the matching checkbox.
- Use checkboxes for a short list of exact values, such as a few status labels.
- Use a condition when you need a range, such as sales greater than a threshold or dates within a period.
When a filter is active, the column’s arrow changes to a filter indicator. A row number gap or missing row numbers in the worksheet margin is another sign that some rows are hidden by a filter. The indicator is useful because a table can have filters active in several columns at the same time.
Combine filters across columns
You can apply a filter to more than one table column. Each additional column narrows the rows that remain visible. In the sales example, choosing West in Region and Desk in Product shows records that are in the West region and have Desk as the product. Excel does not treat those choices as alternatives across different columns.
Within one column, selecting multiple checkbox values generally keeps rows matching any of those selected values. For example, selecting East and North in Region shows records from East or North. Together, these rules make it possible to express a useful condition: (Region is East or North) and (Product is Desk). If you are getting no rows, check whether your selections across columns are too restrictive or whether the source values differ slightly, such as “North” and “North ” with an extra space.
To remove one column’s condition, open that column’s menu and choose Clear Filter From followed by the column name. This leaves filters on other columns in place. To clear all active filters, use Data > Sort & Filter > Clear. The filter arrows remain available after clearing, so you can apply a different selection without setting up the table again.
Table filters compared with filters on a range
Excel can also filter a regular range without converting it to a table. Select a cell in the range and choose Data > Filter to add drop-down arrows. This is useful when you need filter controls but do not want table styling or table-specific behavior. The table option, by contrast, is a convenient fit for lists that will be extended, formatted, or referenced by formulas.
Tables also provide a contextual Table Design tab when selected, options for table styles and a total row, and named columns that can be used in structured references. For example, a formula that refers to a table’s Sales column can be easier to interpret than one that uses a long series of cell addresses. These are table features rather than requirements for filtering: both a regular range and a table can have filter arrows.
To hide a table’s arrows without converting the table back into a range, select a cell in the table and, on the Table Design tab, clear the Filter Button option in Table Style Options. The table remains a table. To show the arrows again, turn that option back on. For a regular range, Data > Filter toggles its filter controls.
Common filter problems and how to fix them
- The arrows are missing: Click inside the data and check whether it is a table. On the Table Design tab, confirm Filter Button is enabled. For an ordinary range, select a cell in the range and choose Data > Filter.
- Some records are not included: Check that the table or filtered range covers all the data, including the newest rows. Look for blank rows or columns that divide the list, and confirm that added records are inside the table.
- A value does not appear in the menu: Clear other active column filters first. A value may exist in the source but be hidden from the current filtered result. Also check spelling, extra spaces, and whether entries have inconsistent formats.
- Date or number conditions behave oddly: Confirm that the entries are stored consistently. A date typed as text may not behave like a real Excel date, and a number stored as text may not sort or filter as a numeric value. Correct the source data where appropriate, then reopen the filter menu.
- The table shows no rows: Open each filtered column and review its selected values or condition. Filters on different columns are combined, so a valid choice in one column can still produce no matches when combined with another.
Remember that a filter hides rows rather than removing them. If you intend to delete records permanently, inspect the selection carefully and use an appropriate deletion method instead of assuming a filter has removed data. Likewise, a filtered view is not a privacy or security control: people with access to the workbook may be able to clear the filter and see the other rows.
Using filtered data in calculations and sharing
Filtering affects what you see, but ordinary calculations may still include hidden rows. For example, SUM over a column normally adds values in rows hidden by a filter as well as visible ones. If you want a subtotal that responds to filtering, Excel’s SUBTOTAL function is designed to work with filtered lists. For reliable results, use a table’s total row or a suitable SUBTOTAL calculation and check that it matches the question you are trying to answer.
Sorting and filtering are separate actions. Sorting changes the order of rows; filtering hides rows that do not match. You can sort a table and still have filters applied, but sorting does not clear the conditions. Before sharing a workbook, consider whether you want recipients to see the current filtered view or the full dataset, and clear filters if the full list should be visible when it opens.
In Excel for the web and Excel for Mac, table headings also provide filter and sort menus, though the ribbon layout and some menu labels can vary by platform or release. If a named command is not in the same place, select a table cell and look for the contextual table tab or use the Data tab’s filter controls. On Windows, Ctrl+Shift+L toggles filter controls for a selected range; on Mac, use the Data menu if the shortcut differs in your Excel setup.
Frequently Asked Questions
How do I add a filter to an Excel table?
Click inside the table and make sure Filter Button is enabled on the Table Design tab. Excel tables normally show filter arrows in their header row by default. Click a heading arrow to choose values or a condition.
How do I filter multiple columns at once?
Apply a choice or condition from each column’s heading menu. Excel combines conditions from different columns, so a row must satisfy the active filters in all of them to remain visible.
Does filtering delete rows in Excel?
No. Filtering hides rows that do not match the criteria; it does not delete their contents. Clear the filter to display those rows again.
How do I clear a filter but keep the table?
Choose Clear Filter From in the relevant column menu to remove just that column’s condition. To clear all conditions, choose Data > Sort & Filter > Clear. The table and its filter arrows remain.
Why is my Excel filter not showing all values?
Another column may already be filtering out the rows that contain those values, or the data may be outside the table or range being filtered. Clear other filters and check the table boundaries, blank breaks, spelling, and data types.
Related
- Excel Data Validation List: Can You Pick Multiple?
- Excel Data Validation Lists: Can They Include Formatting?
- Excel Data Validation Lists: Hard-Coded or Flexible?
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- TEXTJOIN in Excel: Join Text from Arrays and RangesIn Excel, use TEXTJOIN to combine text from a range or array into one cell, with a delimiter…
- Round Down in Excel: Formulas for Specific Decimal PlacesTo round a value down to a chosen number of decimal places in Excel, use the ROUNDDOWN function.…
- Power BI XLOOKUP: DAX and Power Query Lookup OptionsPower BI does not have Excel’s XLOOKUP function in DAX. To match a value from another table, use…
- Excel Formula for Beginning of Month: Dates Made EasyIf A2 contains an Excel date, use =EOMONTH(A2,-1)+1 to return the first day of that date’s month. For…
- Excel SUM Formula That Changes With FiltersUse =SUBTOTAL(109,B2:B100) to sum only the currently visible values in B2:B100. When you filter the rows, Excel recalculates…
- Excel INDEX MATCH Examples and How to Use ThemIn Excel, INDEX MATCH looks up a value by finding its position with MATCH and returning the value…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.