To filter an Excel table in Microsoft 365 for Windows, click a dropdown arrow in its header row, select or search for the values to keep, and choose OK. Filtering hides rows that do not meet your choices; it does not delete them, so you can restore the full table by clearing the filter.
What filtering an Excel table does
An Excel table is a structured range created with Insert > Table or the Ctrl+T shortcut. Tables normally display filter dropdowns in their header cells. These controls let you show only rows that match selected values or conditions, such as orders from one region, invoices above a chosen amount, or dates within a period.
A filter changes which rows are visible, not the underlying records. Hidden rows remain part of the table and can reappear when you clear or change the filter. This distinction matters when reviewing totals, copying results, or handing a workbook to someone else: a filtered table is still holding the complete dataset unless rows have actually been deleted.
Filters are useful for exploring a list without making separate copies of the data. They can also be combined, so a reader could show orders for a particular sales representative and then narrow those orders to a specific month. Sorting is available from the same dropdowns, but sorting rearranges visible records rather than hiding records that do not match a condition.
How to filter a table step by step
- Click a cell in the table. Confirm that the table has a header row with meaningful names such as Date, Product, Region, and Amount.
- Click the dropdown arrow beside the column you want to filter. If the arrows are not visible, check that the range is an Excel table or turn on filtering from Data > Filter.
- Choose the values or condition you want. For a list of values, clear Select All and check the items to display. You can use the search box in the menu to find a value in a long list.
- Choose OK. Excel hides rows that do not match the filter. A filter indicator on the column’s dropdown shows that a filter is active.
- To narrow the results further, open a dropdown in another column and apply a second filter. To restore rows, use the relevant column’s dropdown and choose Clear Filter From [Column Name], or use Data > Clear to clear filters across the range.
For example, imagine a table with Date, Region, Product, and Sales columns. Filtering Region to West and Product to Desk shows records that meet both conditions. The table’s other rows still exist, even though they are temporarily out of view.
Filter by values, text, numbers, and dates
The menu options depend on the kind of data in the column. A text column offers Text Filters, a numeric column offers Number Filters, and a date column offers Date Filters. If a column contains inconsistent data types, Excel may show a less useful menu or group some entries unexpectedly.
- Specific values: Use the checkbox list to keep one or more listed values, such as the East and Central regions. The search field helps locate an item without scrolling through a long list.
- Text conditions: Choose Text Filters for conditions such as Begins With, Contains, or Equals. This can find all product names containing “chair,” for instance, rather than selecting each product name individually.
- Numeric conditions: Choose Number Filters for rules such as Greater Than, Between, or Top 10. To find sales amounts between 500 and 1,000, select Between and enter the two limits.
- Date conditions: Date Filters can offer choices such as Before, After, or a relative date period. Excel can group dates by year, quarter, or month when it recognizes the cells as dates.
- Blanks: Use the checkbox list to select or clear (Blanks), or use the blank-related options in the column’s filter menu when available. A cell containing a space is not necessarily treated as blank.
Search and condition filters serve different purposes. Searching the checkbox list selects matching displayed values, while a condition such as Contains can match a pattern across multiple distinct values. For repeatable analysis, a condition may be easier to maintain when new entries are added that also meet that rule.
Combining filters and understanding the logic
Filters on separate columns are combined as an AND condition: a row must satisfy every active column filter to remain visible. If Region is set to West and Status is set to Open, a row must be both in the West region and open. This is often the simplest way to define a useful subset.
Within one column, selecting several values generally works like OR: a row can match any selected value. Selecting West and East in Region shows rows from either region. For more complex text or number conditions, the Custom Filter dialog can combine two tests with And or Or. For example, a sales filter can keep values greater than 500 and less than 1,000, or keep values less than 100 or greater than 5,000.
Be deliberate about the relationship between tests. “Greater than 500 AND less than 1,000” selects a range, while “less than 500 OR greater than 1,000” selects values outside that range. If the results seem too broad or too narrow, revisit whether the condition should use And or Or and whether the filters on other columns are still active.
Filtering versus sorting and other ways to view data
Filtering and sorting are often mistaken for the same action because both appear in the header dropdown. Filtering hides rows that fail the chosen criteria. Sorting changes the order of records, such as arranging dates from newest to oldest, but does not normally exclude any records. You can sort a filtered result, and you can clear a filter without undoing the table’s sort order.
For a quick temporary view, ordinary table filters are usually enough. For a more interactive summary, add a slicer: click in the table, then choose Table Design > Insert Slicer and select one or more fields. Slicers present clickable buttons for filtering, which can be easier to understand in a shared workbook or on a dashboard. They take up worksheet space and are not a substitute for conditions such as “greater than 500.”
For a separate, formula-driven result that updates as the source changes, Excel for Microsoft 365 and Excel 2021 also include the FILTER function. That approach returns matching data in another area instead of merely hiding rows in the original table. It is useful when a report needs its own output range, but it requires a formula and a suitable space for the results. Standard table dropdowns are often simpler for browsing the source list.
Filtering a table that changes over time
One benefit of using a table rather than a plain cell range is that its boundaries can expand as records are added directly below it. The table’s formatting and filter controls generally extend to new rows, and filtering the column can include new values. Check that new records are actually part of the table: data separated from it by an empty row or entered outside its boundaries may not be included.
When a table is filtered, adding a new row or pasting data can be confusing because some rows are hidden. Before making a large edit, consider clearing filters so you can see the full list and confirm where the new records will go. Afterward, check the table’s last row and verify that all intended fields were entered under the correct headers.
Filtering can also affect what people see when they open a workbook. If the file is saved with a filter active, the next reader may see only the currently displayed subset. Clear filters before saving when the full dataset should be the default view, or leave a clear note if a particular filtered view is intentional.
Common filtering problems and how to fix them
- Filter arrows are missing: Click inside the data and check whether it is a table. If it is a regular range, select the range and use Data > Filter, or create a table with Insert > Table. A table’s filter controls can also be shown or hidden from Table Design > Filter Button.
- Some records do not appear in the menu: The table may not include all the rows, or a current filter in another column may already be limiting the visible results. Clear the filters and check the table boundary before changing the data.
- Numbers or dates behave like text: Cells may contain text-formatted values or inconsistent entries. A date that looks correct on screen is not always stored as a date value. Check the cell format and underlying data, and correct the source values if needed.
- Blank-looking cells are not filtered as blanks: A cell containing spaces, an apostrophe, or a formula that returns an empty string may look empty but not behave like a truly empty cell. Inspect and clean the source data when blank status matters.
- The filter seems to show the wrong rows: Look for active filters in other columns, hidden rows, and unusual spacing or spelling in the source values. “West” and “West ” may be distinct text values because one includes a trailing space.
- New data is excluded: Confirm that the new rows are inside the table. Click in the table and look for its outline, or use Table Design to check the table range and resize it if necessary.
Another common misconception is that filtering changes totals automatically in every situation. A regular SUM formula can include values in hidden rows, depending on the range and formula. If a displayed subtotal should reflect only visible rows, use a suitable SUBTOTAL function and verify its function number and range for the calculation you need. Do not assume that a number displayed beside a filtered table represents only the visible records.
Differences in Excel for Mac and the web
The basic workflow is similar in Excel for Mac and Excel for the web: use the header dropdown to choose values or conditions, and use the Data tab when you need to enable or clear filtering. Ribbon labels and the location of table-specific controls can vary by platform or window size, so the Windows paths in this guide may not appear in precisely the same place on every device.
On Windows, Ctrl+T creates a table, and Ctrl+Shift+L toggles filters for a selected range. Keyboard shortcuts can differ on Mac; use the ribbon commands if a shortcut does not match your keyboard or Excel version. Excel for the web can filter and sort tables in the browser, but some advanced workbook features and interface options may differ from the desktop app. The underlying principle remains the same: the filter changes which records are shown, not the stored data.
Frequently Asked Questions
How do I remove a filter from an Excel table?
Open the filtered column’s dropdown and choose Clear Filter From [Column Name] to remove that column’s condition. To clear filters across the data, use Data > Clear. Clearing a filter shows rows again; it does not remove the table or its records.
Can I filter an Excel table by more than one column?
Yes. Apply a filter in one column, then apply another in a different column. Excel displays rows that meet the conditions in all filtered columns. Within a single column, selecting multiple listed values usually keeps rows matching any of those selected values.
Does filtering delete or change my Excel data?
No. A normal filter hides rows that do not match your criteria; it does not delete or rewrite them. Clear the filter to display those rows again. Take care not to confuse hiding rows with selecting and deleting them.
Why are the filter arrows missing from my table?
Check that the data is an Excel table and that its header row is enabled. Click inside the table and look for Table Design > Filter Button. For a normal range, select the data and choose Data > Filter to turn on dropdowns.
Can I filter an Excel table by color?
Excel can filter by cell color, font color, or icon when the relevant options are available for the selected column. Open its dropdown and look under Filter by Color. The choice appears only when the column’s cells contain applicable formatting or icons.
Related
- Excel Slicer: Remove Old Values and Stale Items
- Excel Slicer No Connections Found? Causes and Fixes
- Excel Slicer Not Updating? Fixes for PivotTables and Tables
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Close Version History in Excel Without Losing DataTo close Version History in Excel for Microsoft 365 on Windows, open the Version History pane and select…
- Google Sheets: Count Cells With a Specific Dropdown ValueTo count cells that contain a particular Google Sheets dropdown choice, use COUNTIF. For example, if dropdowns are…
- Google Sheets IF Statements: Syntax and Examples ExplainedIn Google Sheets, an IF statement uses the IF function to test a condition and return one result…
- Excel Audit Trail: How to Check Changes and HistoryTo check an audit trail in Excel for Microsoft 365, open the workbook and select Review > Show…
- Add Text to an Excel Number Format Without Changing ValuesTo add text to a number’s display in Excel without changing the value, apply a custom number format:…
- Analysis Report in Excel: How to Build a Useful ReportTo make an analysis report in Excel, organize clean records in a table, summarize them with a PivotTable…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.