To filter an Excel table by column in Microsoft 365 for Windows, select the table, open the drop-down arrow in the column’s header, and choose the values or conditions to keep. Excel hides rows that do not match; it does not delete them, and you can clear the filter to show them again.
How column filters work in an Excel table
An Excel table is a structured range with a header row and table formatting. Its header drop-downs let you filter or sort the records in that table. For example, a table of orders might have columns for Date, Region, Product, and Sales. Filtering Region to show “West” displays only the rows whose Region value is West, while the other records remain in the workbook but are temporarily hidden.
Filters apply to whole rows, not isolated cells. If you filter the Region column, Excel hides each nonmatching order row across all columns, so the date, product, and sales value stay together. This is important when reviewing records: filtering one column does not rearrange values independently and break the relationships between fields.
Excel tables normally show filter buttons in their header row when the header row is enabled. If you have a plain range rather than a formatted table, you can still use AutoFilter, but the table’s structured features and automatic expansion are not part of that range. You can create a table by selecting the data and choosing Insert > Table, then confirming that the data has headers if it does.
Filter a table by selecting column values
- Click a cell inside the table. Confirm that the first row contains meaningful column headings, such as Region or Status.
- Click the drop-down arrow in the header of the column you want to filter. For a Region filter, open the arrow in the Region header.
- In the list, clear Select All, then check the value or values to keep. You can select more than one item.
- Click OK. Excel displays rows matching the selection and marks the header with a filter indicator.
The value list is useful when you know the exact category. Suppose a Status column contains Open, In Progress, and Closed. Selecting Open and In Progress shows both groups and excludes Closed. If a column contains many distinct values, use the search box in the filter menu to find a value before selecting it. The search narrows the displayed list; you still need to select the value and apply the filter.
To show every record again, open that column’s drop-down and choose Clear Filter From [Column Name]. Clearing one column’s filter does not necessarily clear filters on other columns. To remove all active filters while retaining the table’s filter controls, use Data > Sort & Filter > Clear on the Windows ribbon.
Use text, number, and date conditions
Selecting listed values works well for categories, but a condition is more flexible when the exact values vary. Open the relevant header menu and use its Text Filters, Number Filters, or Date Filters options. The available category depends on the contents Excel recognizes in that column. For example, a column containing text offers text conditions, while a column of numeric values offers number conditions.
- Text: Use Text Filters > Contains to find descriptions that include a word, or choose a condition such as Begins With. For example, filtering Product with Contains “desk” can retain entries such as “Desk lamp” and “Standing desk.”
- Numbers: Use Number Filters > Greater Than or Between to keep values in a range. A sales filter between 100 and 500 can show orders in that interval.
- Dates: Use Date Filters to show dates such as this month, last quarter, or a custom date range, depending on the menu options and data. Excel may group recognized dates into years and months in the value list.
- Blanks: If the column has empty cells, the filter list may include a (Blanks) item. Selecting it isolates rows with an empty cell in that field.
For a custom text or number condition, choose the appropriate filter command, enter the comparison value, and apply it. Some custom filter dialogs let you combine two conditions with AND or OR. AND means a record must satisfy both conditions; OR means it can satisfy either. For example, a numeric column could be filtered to show values greater than 100 and less than 500, or values below 50 or above 900. Check the comparison choices and connectors in the dialog before applying the filter.
Combine filters across columns
You can filter more than one column at a time. Excel combines filters on different columns with AND logic: a row must meet the active condition in every filtered column to remain visible. For example, filtering Region to West and Status to Open shows only open orders in the West. It does not show all West orders plus all open orders from every region.
Within one column, selecting several values generally works like OR: a row can match any selected value. Selecting West and East in Region shows records from either region. This distinction helps explain why adding another column’s filter often narrows the results more sharply than checking another value in the same column.
When a result appears unexpectedly small, inspect every header for a filter indicator. A filter left over from an earlier search can still be active even if the current column selection seems correct. Clear filters one by one or use the Data tab’s Clear command, then apply the conditions again in a deliberate order.
Filter by color and use search carefully
If cells have fill color, font color, or conditional formatting, a header menu may offer Filter by Color. This can be convenient for examining color-coded records, but color is a visual property rather than a substitute for a well-defined data field. If a color represents a business status, a Status column with values such as Review or Approved is usually easier to search, filter, and interpret consistently.
The filter-menu search box is also different from a worksheet-wide Find operation. It helps locate entries in the current column’s filter list; it does not search every cell in the workbook. If you search for a value and see no rows, verify the spelling, the column you opened, and whether other filters are already restricting the table. Values that look alike can differ because of extra spaces, inconsistent spelling, or numbers stored as text.
Filtering dates deserves an extra check: a cell that looks like a date may actually be text. If Excel does not offer the expected date filters or groups, inspect the source values and their data type. Correcting inconsistent source data is often better than trying to work around it with repeated manual selections.
Filter controls, keyboard access, and platform differences
In Excel for Microsoft 365 on Windows, Ctrl+Shift+L toggles filter buttons for a selected data range or table. With a table, use this carefully: turning the buttons off removes the visible filter controls, but it does not delete the table or its data. The Data tab also contains filter controls. To filter a table in the normal way, opening the header arrow is usually the clearest method.
On Excel for Mac, select the table and use the Data tab’s Filter command if you need to show or hide filter controls; ribbon wording and layout can differ slightly by release. Excel for the web also supports table header filters, though the ribbon layout and menus may not match the Windows desktop exactly. If instructions refer to a Windows keyboard shortcut, use the ribbon or the header menu on another platform rather than assuming the same key combination.
For touchscreens or narrow windows, the header arrow can be small or partly obscured. Widen the column or the Excel window, or use the ribbon’s filter controls. A table’s header row must be enabled for the usual header-based workflow. If the arrows are missing, check that the data is actually a table, that it has a header row, and that filter buttons have not been switched off.
Filtering versus sorting, hiding, and slicers
Filtering and sorting solve different problems. A filter limits which rows are visible; sorting changes the order of rows. You can sort a filtered result, but sorting alone does not exclude records. If you want to review only overdue tasks, filter the Due Date or Status column; if you want the earliest visible due dates at the top, sort those results afterward.
Manually hiding rows is not the same as filtering. Hidden rows may have been concealed individually and can be difficult to manage consistently. A filter records a condition that can be changed or cleared through the column menu. For a repeatable review, filters are generally easier to understand than manually hiding records.
Slicers are an alternative visual control for filtering tables. Select a table, then use Insert > Slicer, choose one or more column names, and confirm. A slicer displays clickable buttons for its field values and can make a shared workbook easier to navigate for people who do not want to open header menus. Slicers take up worksheet space and are most useful when the same table is filtered interactively on a regular basis.
Common filtering problems and how to fix them
- No arrows appear: Check that you clicked inside the table, the table has a header row, and filter buttons are enabled. For a plain range, use Data > Filter or convert the range to a table with Insert > Table.
- Some expected rows are missing: Check for active filters in other columns. Clear all filters, then apply the desired conditions again.
- A value is absent from the menu: Look for spelling differences, extra spaces, blank cells, or values outside the table range. If new records were added outside the table, resize the table or enter the records directly beneath it so they become part of the table.
- Numbers or dates have the wrong filter options: The column may contain mixed data types, such as numbers stored as text or dates entered as text. Standardize the source values and check that the filter range includes the intended records.
- The filter seems to have removed data: Filtering hides nonmatching rows; it does not delete them. Clear the filter to restore their visibility. Avoid deleting visible rows unless you truly intend to remove those records.
One practical habit is to check the record count and the filter icons before drawing conclusions from a filtered view. A filtered table is a view of matching records, not a new copy of the data. If you copy a filtered selection elsewhere, inspect the pasted result so you know whether it contains only the visible records you intended.
Frequently Asked Questions
How do I filter only one column in Excel?
Open the drop-down arrow in that column’s table header, select the values or condition you want, and click OK. Excel hides rows that do not match while leaving the other columns of each matching row intact.
Can I filter an Excel table by two columns at once?
Yes. Apply a filter from each column’s header menu. Excel displays only rows that meet the conditions in both columns, so filtering Region to West and Status to Open shows open West records.
How do I remove a filter without deleting the table?
Open the filtered column’s menu and choose Clear Filter From its name. To clear filters across the table, use Data > Sort & Filter > Clear on Windows. The table and its data remain in place.
Why can’t I see the filter arrows?
The range may not be an Excel table, the header row may be disabled, or filter buttons may be turned off. Click in the data and check the Data tab’s Filter command, or select Insert > Table to create a table with headers.
Does filtering delete or change hidden rows?
No. Filtering changes which rows are displayed; it does not delete or edit the rows that fail the condition. Clear the filter to make those records visible again.
Related
- Excel: Find Where a Cell Is Used Across Sheets
- Excel Custom Number Format for Millions
- Excel Custom Number Format: Show Numbers in Thousands
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Function to Keep a Cell Constant in Formulas ExplainedTo keep a cell reference fixed when you copy an Excel formula, make it absolute by adding dollar…
- Excel Text Expansion: AutoCorrect, Formulas, and MoreExcel has no single feature called “text expansion,” but you can make a short abbreviation expand as you…
- Excel Filter Based on 2 Criteria: AND or ORTo filter Excel data using two criteria, apply a filter to two columns: Excel combines those conditions with…
- How to Copy a Formula in Excel All the Way Down a ColumnTo copy a formula all the way down in Excel, select the cell with the formula and double-click…
- Excel Filter Not Equal to Text? How to Exclude ValuesTo filter out a particular text value in Excel for Microsoft 365 on Windows, select a cell in…
- How to Filter Greater Than in Excel: Step-by-Step GuideTo show only values above a threshold in Excel, open the column’s filter menu and choose Number Filters…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.