To filter by a column in Excel, select a cell in your data, choose Data > Filter, then open the arrow in the column heading and select the values or conditions to display. Filtering hides rows that do not match; it does not delete them. Before you start, make sure your data has a single header row and is arranged in a continuous range or an Excel table.
What You’ll Need
- An Excel workbook containing the records you want to filter, such as a list of orders, names, dates, or products.
- A clear header for each column. Headers help Excel show the right filtering options and make the dropdowns easy to identify.
- Microsoft Excel for Microsoft 365 on Windows for the ribbon paths below. Excel for Mac and Excel for the web have similar filtering controls; differences are noted where relevant.
Step-by-Step
- Check the layout of your data. Each column should contain one kind of information, with a descriptive heading in the first row, such as Region or Order Date. Keep the records together without completely blank rows or columns inside the list. Avoid merged cells in the header or data area because they can interfere with selecting and filtering the full range. If your worksheet has notes or totals above the records, leave a blank row between them and the data.
- Click inside the data range. Select any cell in the list you want to filter. Excel will usually detect the surrounding contiguous data automatically. If it does not, select the entire range, including the header row, before turning on filtering. Check that the selected area includes every column that belongs to the records; otherwise, some information may not hide with its matching row.
- Turn on the filter controls. On Windows, go to Data > Filter. Excel adds a dropdown arrow to each detected header. You can also use the Windows shortcut Ctrl+Shift+L to toggle filters on or off. On Mac, use Data > Filter; keyboard shortcuts can differ by Excel version and keyboard, so the ribbon command is the dependable option. In Excel for the web, use Data > Filter as well.
- Open the dropdown for the column you want to filter. Click the arrow beside that column’s heading, not an arrow in another column. For example, to show orders from one region, open the Region dropdown. The menu contains a search field and a list of values in that column, along with filtering commands suited to its data type.
- Choose the values to keep. In the menu, clear Select All, then select the value or values you want visible. For a long list, type a word or part of a value into the search box, then select the matching item. Click OK. Excel hides rows whose cells in that column do not match your selection. A filter icon or changed dropdown indicator appears in the heading to show that the column is filtered.
- Use a condition when selecting individual values is not practical. Open the column dropdown and choose a command such as Text Filters, Number Filters, or Date Filters, depending on the contents. Options can include a text value that begins with a phrase, numbers greater than a limit, or dates within a period. Choose the condition, enter the value or values in the dialog, and confirm. The available commands vary with the type of data Excel detects.
- Add another column filter if needed. Open a second column’s dropdown and select its criteria. Excel displays only rows that satisfy the filters currently applied across the columns. For example, choosing Region = East and Status = Shipped shows rows that meet both conditions, not rows that meet either one. You can keep filtering additional columns to narrow the results, then adjust any one dropdown to broaden or change the view.
- Review and clear the results. Check the visible records and the filtered column indicators to confirm the criteria are what you intended. To remove one column’s filter but leave other filters active, open that column’s dropdown and choose Clear Filter From followed by its column name. To clear filters across the range, use Data > Sort & Filter > Clear on Windows. To remove the dropdown arrows altogether, select Data > Filter again. Turning filtering off restores hidden rows, but it does not erase data.
Common Mistakes
Selecting only the column, not the whole dataset: Filtering works on rows of records. If the range does not include neighboring columns, related information may not be treated as part of the same list. Click inside the list and let Excel detect the range, or deliberately select all columns and the header row before choosing Filter.
Leaving a blank row in the middle of the records: A fully blank row can make Excel treat the data above and below it as separate blocks. The filter may then cover only one block. Remove the gap or select the complete range explicitly before enabling filters.
Forgetting that filters combine: Filtering a second column narrows the existing results. If the screen suddenly shows fewer rows than expected, open each filtered column and review its selections. Clear a criterion that should no longer apply.
Assuming hidden rows were deleted: A filter temporarily hides records that do not meet the selected criteria. They remain in the worksheet and return when you clear the filter. Be careful when copying, deleting, or editing while a filter is active, and check the visible row numbers before making a change.
Mixing data types in one column: A column containing dates stored as text alongside real dates, or numbers mixed with labels, may show unexpected filter options or groupings. Keep each column consistent and correct entries that use a different format or data type.
Tips
- Convert a regular range to a table for a growing list. Select a cell in the data and choose Insert > Table, confirm that the table has headers, and select OK. Tables include filter arrows by default and usually extend formatting and filtering as you add records beneath them. This is useful for lists that change over time.
- Use search for long value lists. Searching in a dropdown is faster than scrolling through hundreds of entries. After typing, make sure the intended result is checked before confirming; a search narrows the displayed choices but does not itself apply a filter until you confirm the selection.
- Use a condition for ranges. If you need every number above a threshold or dates in a defined period, a Number Filter or Date Filter is clearer than selecting many separate values. Read the condition shown in the dropdown after applying it, especially when combining two conditions in the same column.
- Keep headings descriptive and unique. Names such as Sales, Region, and Delivery Date make the dropdowns easier to recognize. When turning filtering on, duplicate or blank headings can be confusing, so label each column before working with the list.
- Remember that filtering does not sort. Filtering changes which rows are visible, while sorting changes their order. If you need both, apply the filter and then choose a sort command from the relevant column dropdown or the Data tab.
- Save a useful view by keeping criteria simple. If you often need the same subset, a named table and consistent column values make it easier to reapply. For a one-time review, note the criteria before clearing them so you can reproduce the view later.
Troubleshooting
No dropdown arrows appear: Click within the intended range and choose Data > Filter. If nothing changes, check whether the worksheet is protected or whether the selected range includes a blank row that divides the data. You can also select the full list, including its headings, and try again. If arrows are already present but difficult to see, inspect the right edge of each header cell.
The filter covers only part of the list: Turn filtering off, remove any fully blank rows or columns separating the records, select the complete range, and turn it back on. If the list has notes or unrelated information nearby, convert just the intended range into a table rather than relying on automatic detection.
A value seems to be missing from the dropdown: Clear the search text in the menu and check whether another column is already filtered, since that can affect which records are currently visible. Also inspect the source cells for extra spaces, inconsistent spelling, or values stored in different formats. For example, East and East followed by a space may be treated as different entries.
Date or number filters behave unexpectedly: Check that the cells contain genuine dates or numbers rather than text that merely looks like a date or number. Look for mixed entries, leading apostrophes, or inconsistent formats. Correct the underlying values, then clear and reapply the filter. Formatting alone changes how a value looks; it does not always change the value’s data type.
Rows do not return after filtering: Open the dropdown for every filtered column and clear its filter, or use Data > Sort & Filter > Clear on Windows. If rows remain hidden, check for manually hidden rows or an active filter in a different range. The row numbers on the left can help reveal gaps.
New records are not included: If you are filtering a normal range, new records may have been entered outside it. Turn filtering off, select the expanded range, and turn it on again, or convert the data to a table so it can expand with added rows.
Frequently Asked Questions
Can I filter by more than one column in Excel?
Yes. Apply a filter to one column, then choose criteria in another column’s dropdown. Excel shows rows that meet the criteria in all filtered columns.
Does filtering delete the rows that do not match?
No. It hides those rows temporarily. Clear the filter to show them again; filtering by itself does not remove or change the underlying data.
How do I filter for blank cells?
Open the dropdown for the column and select (Blanks) in the values list, then confirm. If you do not see that choice, check for cells containing spaces or formulas that return an empty string; those may not behave like truly empty cells.
Can I filter for text that contains a word?
Yes. Open the text column dropdown, choose Text Filters > Contains, enter the word or phrase, and confirm. This filters for matching text without requiring the entire cell to equal your search.
Why can I see only some of the values in a filter menu?
A search term in the dropdown or a filter on another column may narrow the records shown. Clear the dropdown search and check other active filters. Also confirm that the data range includes all the rows you expect.
Do the steps work in Excel for Mac and Excel for the web?
The basic process is similar: select the range or a cell in it, then use Data > Filter and choose criteria from a header dropdown. Ribbon arrangement and keyboard shortcuts can differ, so use the Data tab if the Windows shortcut does not apply.
Related
- How to Enable an Audit Trail in Excel: Track Changes
- Freeze Panes in Excel: How to Edit Rows and Columns
- How to Edit Custom Number Formats in Excel Step by Step
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Find an Average in Excel With AVERAGE FunctionTo find the average in Excel, enter the AVERAGE function in an empty cell and point it to…
- Excel Formula for the Lower of Two Values in Two CellsTo return the lower of two values in Excel, use =MIN(A2,B2). If the values are in cells A2…
- Excel Difference Between Two Numbers: Formula GuideTo find the signed difference between two numbers in Excel, subtract one cell from the other, such as…
- How to Edit Filter Options in Excel for Better ResultsTo edit filter options in Excel, select a cell in your data, choose Data > Filter, then open…
- Excel LEFT Function for Dates: What It ReturnsExcel’s LEFT function can take a date, but it does not read the date as it appears on…
- Excel Table Name: Find, Change, and Use Names in FormulasAn Excel table name is the workbook-level identifier assigned to a formatted Excel table, such as Table1 or…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.