Excel File With Table Filters: How to Add and Use Them

How-to basics · Updated November 19, 2026

Yes—an Excel file can use table filters to show only the rows that match criteria, without deleting or changing the other records. In Microsoft 365 for Windows, select a cell in your data, press Ctrl+T, confirm the header setting, and choose OK; Excel creates a table with filter arrows in its header row.

What a table filter does

A filter temporarily hides rows that do not meet the conditions you choose. For example, a sales table might contain hundreds of orders, but a filter can display only orders from one region, one month, or one salesperson. The underlying records remain in the workbook. Clear the filter and the hidden rows appear again.

An Excel table is more than a range with dropdown arrows. Tables have named columns, consistent formatting, and the ability to expand when you add adjacent records. When you create a table from data with headers, Excel normally adds a filter menu to each header. Those menus let you choose specific values, search a list, or set conditions such as contains, is greater than, or is after.

If you only want filter arrows on an ordinary range, you can use AutoFilter without converting the range to a table. Select a cell in the range and choose Data > Filter. The table option is often convenient for lists that will grow; AutoFilter can suit a fixed block of data or a workbook where you do not want table formatting and structured table behavior.

Create a table with filter arrows

Before creating a table, check that the data is arranged in a simple rectangular block: one header row followed by records, with a separate field in each column. Avoid blank header cells, merged cells, and completely blank rows or columns inside the list. These can make Excel guess the data boundaries incorrectly or make filtering harder to understand.

  1. Click a cell inside the data. If Excel does not identify the full block correctly, select the entire range yourself, including the headers.
  2. On Windows, press Ctrl+T, or use Insert > Table. In the Create Table dialog, check the range and select My table has headers if the first row contains column names.
  3. Choose OK. Excel formats the range as a table and displays dropdown arrows beside the column headings.
  4. For a meaningful table name, click inside it, open Table Design, and enter a name in the Table Name box. A descriptive name such as Orders is easier to recognize than Table1.

On Mac, use Insert > Table or the available table command on the ribbon; the dialog also asks whether the data has headers. Excel for the web has a similar Insert > Table workflow, though the ribbon layout can differ slightly. Once the table exists, the filter controls are in its header row across these versions.

Excel tables commonly include filter buttons by default. If a table exists but its arrows are missing, click inside it and look on Table Design for the Filter Button option. For a non-table range, use Data > Filter to turn AutoFilter on. On Windows, Ctrl+Shift+L toggles filter arrows for a selected range; on Mac, use Data > Filter if the shortcut differs on your keyboard or Excel build.

Filter rows by text, numbers, and dates

Click the arrow in the column you want to filter. The menu typically includes a search field, a list of values, and options for clearing or applying conditions. Selecting one or more values is a quick way to keep particular categories. For example, in a Status column, select Open and leave Closed unselected to show only open records. Choose OK to apply the selection.

Search is useful when a column has a long list of distinct values. Type a word, code, or part of a name in the filter menu’s search box, select the matching result, and apply the filter. The search narrows the checklist; it does not edit the cell contents. To include several categories, select each one that should remain visible before applying.

For more precise criteria, use the menu’s text, number, or date filter options. Text filters can find entries that begin with, end with, contain, or do not contain specified text. Number filters can show values greater than, less than, between, or equal to a threshold. Date filters can focus on a particular date or a relative period such as a month, depending on the values and Excel’s interpretation of them.

For example, to find invoices above 500, open the Amount filter, choose Number Filters > Greater Than, enter 500, and apply it. To show dates from a particular month, use the Date Filters options or select the relevant date groups in the checklist. If dates appear only as individual text values rather than grouped dates, check that Excel recognizes the cells as dates and not text.

Combine filters and understand the results

You can filter more than one column at once. If you show only the West region in one column and only Open records in another, the visible rows must meet both conditions. This is effectively an AND between columns. Within a single column, selecting several values usually means show any of these selected values, an OR condition. For instance, selecting West and East keeps records from either region, while also applying Open in Status limits the display to open records in those regions.

Filtering changes which rows are visible, not the order of the remaining records. Sorting is a separate operation: it rearranges visible rows, for example from newest to oldest or alphabetically by customer. You can sort and filter the same table together, but remember that clearing a filter does not necessarily undo a sort. If the original order matters, consider keeping an ID or sequence column before sorting.

Filtered totals can be confusing because a normal SUM formula may include values in rows hidden by a filter. If you need a total that responds to filtering, use SUBTOTAL. With amounts in D2:D5, where the currently visible values are 120, 80, and 50 and the fourth value is hidden by the filter, enter =SUBTOTAL(9,D2:D5) in an empty cell; the displayed result is 250. The 9 option sums the visible rows while excluding rows removed by a filter. If your visible records change, the subtotal updates.

Clear, change, or turn off a filter

To remove one column’s condition, open that column’s dropdown and choose Clear Filter From followed by the column name, or select all values again. The wording can vary slightly across Excel versions. To clear all active filters while leaving the filter arrows in place, use Data > Clear on the Windows ribbon. This restores the rows but keeps filtering available.

To remove the arrows as well, select a cell in the table and turn off Filter Button on Table Design, or use Data > Filter for an ordinary range. Turning off filter controls does not delete the data. If you want to convert a table back into a normal range, use Table Design > Convert to Range; that is a different action and removes table behavior, so use it only when that is your intention.

When sharing a workbook, remember that the visible rows may reflect an active filter. A reader can clear it to see all records, but a short note explaining the current view can prevent someone from assuming the workbook contains only the displayed rows. If preserving different ways of viewing the same data matters, consider Excel’s Sheet View feature where it is available, rather than repeatedly changing a shared sheet’s filter state.

Common filter problems and fixes

  • Some records are missing from the filter. Check whether the range selected for the table includes every row. If new data was typed outside the table, add it to the table or resize the table from Table Design. A table generally expands when you type or paste directly below it, but data separated by a blank row may not be included as expected.
  • The filter list contains blanks or unexpected entries. Look for empty cells, extra spaces, inconsistent spelling, or values stored in different formats. For example, North and North followed by a space may be treated as different text. Standardizing the source values makes the filter checklist more reliable.
  • A date or number filter behaves like a text filter. Cells may contain text that resembles a date or number. Check the source data format and correct the underlying values if necessary; changing only the display format does not always convert text into a real date or number.
  • Filter arrows are absent or disabled. Confirm that the selection is within the table or data range and that the worksheet is not protected in a way that prevents filtering. For a range, turn on Data > Filter. For a table, check the Filter Button setting on Table Design.
  • A filter seems to show too few matches. Review the search text and selected checkboxes, then clear the column filter and reapply it. Also check filters in other columns: several active conditions work together, so another column may be excluding the rows you expected to see.

One important misconception is that hidden filtered rows are deleted or erased. They are still part of the workbook and can reappear when the filter is cleared. However, filtering is not a substitute for removing sensitive information: a person with access to the workbook may be able to clear the filter and reveal those rows.

When to use a table filter or another tool

Use a table filter when the goal is to inspect or work with a subset of a list while keeping all records in the same worksheet. It is well suited to customer lists, inventory, task trackers, and transaction logs. A table is particularly helpful when records will be added over time because its columns and formatting can extend with the data.

If colleagues need large, clickable controls for selecting categories, insert slicers for a table from Table Design > Insert Slicer and choose the columns to control. Slicers show the available categories as buttons and make active selections easier to spot. They are not the same as ordinary dropdown filters, and they take up worksheet space; for a compact list, header dropdowns may be simpler.

For summaries that group, count, or total data across several fields, a PivotTable may be more appropriate than filtering the source list. A PivotTable can summarize records by categories, while a table filter mainly hides rows in the original dataset. You can also use both: keep a clean source table, filter it when inspecting individual records, and build a PivotTable when you need a grouped report.

Frequently Asked Questions

Can I add filters to an Excel file without making a table?
Yes. Select a cell in the data range and choose Data > Filter. Excel adds dropdowns to the range’s header row. Converting the range to a table is optional, though tables offer extra features for expanding and managing lists.

Do Excel table filters delete rows?
No. They hide rows that do not match the chosen criteria. Clear the filter to display the records again. To permanently remove data, you would need to delete the relevant rows separately.

Can I filter by two columns at once?
Yes. Apply a condition in each column. A row remains visible only when it meets the active conditions across the filtered columns. Clear one column’s filter without clearing the others if you want to relax just one condition.

Why does my table filter not include new rows?
The new records may be outside the table’s boundaries, particularly if there is a blank row or column between them and the table. Add the records directly below the table or resize the table using Table Design, then check the filter again.

Can I filter by cell color?
Excel can filter by cell color, font color, or icon when those formats are present in the column. Open that column’s filter dropdown and look for Filter by Color. The option appears only when Excel detects applicable formatting in the data.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.