To filter in Excel, click a cell in your data, then choose Data > Filter; use the arrow in a column heading to select the values or conditions to show. Filtering hides rows that do not match your choices without deleting them, so you can focus on the records you need and restore the full list later.
The steps below are for Microsoft Excel for Microsoft 365 on Windows. Your data should have one clear header row, with each column containing one kind of information, such as names, dates, or sales figures. If your worksheet has unrelated information beside the list, select only the range you want to filter before turning filtering on.
What You’ll Need
- An Excel workbook containing a list or table with a header row.
- Permission to edit the workbook, if it is protected or shared with restricted access.
- A moment to check that each column has a heading and that there are no completely blank rows or columns splitting the data.
Step-by-Step
- Check and prepare the data. Make sure the first row of the list contains descriptive headings, such as Region, Order Date, or Status. Keep one record per row and one type of information per column. Remove completely blank rows within the list if you can; a blank row can make Excel treat part of the list as a separate block. Check for merged cells in the header or data area, since they can interfere with filtering. You do not have to sort the list first.
- Select the data to filter. If the list is a single, uninterrupted block, click any cell inside it; Excel will usually detect the surrounding data. If other content is directly beside the list, select the exact range, including the heading row, so you do not accidentally add filter arrows to unrelated columns. Another option is to turn the range into an Excel table: select a cell in the list, press Ctrl+T, confirm the range, and check My table has headers if appropriate. Tables include filter arrows by default and generally expand to include new rows added directly below them.
- Turn on the filter controls. On Windows, choose Data > Filter on the ribbon, or press Ctrl+Shift+L. Small dropdown arrows appear in the header cells. On Mac, choose Data > Filter; keyboard shortcuts may differ by Excel version and keyboard settings. In Excel for the web, use Data > Filter. If arrows appear on the wrong row, turn filtering off, select the correct range including its header row, and turn it on again.
- Open the dropdown for the column you want to narrow. Click the arrow in a heading such as Status. A menu shows the available values and, depending on the column’s contents, options for searching or applying conditions. Filtering one column does not change the underlying data; it temporarily hides rows that do not meet the selection.
- Show selected text or category values. In the dropdown, clear Select All, select the values you want to keep visible, and click OK. For example, to see only orders marked Shipped, clear Select All, check Shipped, and confirm. You can use the Search box in the menu to find a particular value in a long list. The search narrows the choices displayed in the menu; select the matching item and click OK to apply it. If the exact value is not listed, check whether spelling, spaces, or inconsistent labels have created different entries.
- Apply a condition instead of choosing individual values. For text, number, or date columns, open the dropdown and point to Text Filters, Number Filters, or Date Filters, as applicable. Choose a condition such as Contains, Greater Than, or Before, enter the value or date, and confirm. For example, use Number Filters > Greater Than to show records above a chosen amount. In a custom-filter dialog, choose And when both conditions must be true, or Or when either condition can be true. Check the selected logic before applying it, especially when using two conditions.
- Filter by color or blanks when useful. If cells have fill or font colors, the dropdown may offer Filter by Color; choose the relevant color to show matching rows. To find incomplete records, use the value checklist to select (Blanks), if that option is available. To show completed entries instead, exclude blanks. Blank-looking cells can sometimes contain spaces or formulas that return an empty string, so they may not behave exactly like truly empty cells.
- Filter another column to narrow the results further. Open a second column’s dropdown and choose another value or condition. Excel applies both column filters at the same time: a visible row must meet the active filter in each column. For instance, filtering Region to East and Status to Shipped shows only shipped orders in the East region. The arrows or filter icons in the headings indicate that filtering is active.
- Review the visible rows and clear filters when finished. Check the results against the headings and the condition you intended. To remove just one condition, open that column’s dropdown and choose Clear Filter From [column name]. To remove all active conditions while keeping the arrows, choose Data > Sort & Filter > Clear. To remove the arrows as well, choose Data > Filter again or press Ctrl+Shift+L on Windows. Turning off the filter controls reveals the hidden rows; it does not delete them.
Common Mistakes
- Including the wrong heading row: If the first row of the selected range is a data record, Excel may treat it as a heading and omit it from the choices. Select the real header row and data together.
- Selecting only one column: Filtering one column without including the rest of the records can separate the results from their related information. Apply the filter to the full rectangular list, not just the column you want to search.
- Assuming filtered rows are deleted: They are hidden from view, not removed. Avoid copying or editing a partial selection unless you have confirmed which rows are visible and what the action will affect.
- Expecting similar text to count as identical: “In progress,” “In Progress,” and “In progress ” with a trailing space may be separate checklist values. Standardize inconsistent entries if the filter results are incomplete.
- Using the wrong kind of filter: A date or number stored as text may not offer the date or number conditions you expect. Check the cell format and underlying values before choosing a condition.
- Forgetting another active filter: A second column can still be restricting the list even after you change the first. Clear each condition or use Data > Sort & Filter > Clear to see all records again.
Tips
- Use tables for lists that change often. A table gives each column a header filter and usually extends its formatting and filtering when you add records beneath it. Create one with Ctrl+T on Windows, then verify that Excel identifies the header row correctly.
- Use the menu search for long lists. Searching within a dropdown is faster than scrolling through hundreds of distinct names or codes. Remember that you still need to select the result and click OK to apply it.
- Use custom conditions for partial matches and ranges. Text Filters can find entries that contain a word, while Number Filters can limit a list to values above, below, or between thresholds. Date Filters offer date-based choices such as a period or date range; check the actual dates in the cells if a result seems unexpected.
- Keep a visible sense of the whole dataset. Freeze the heading row using View > Freeze Panes > Freeze Top Row if you need to scroll while reviewing a long list. This changes what stays on screen, not what the filter includes.
- Be careful when copying visible results. If you need to share a filtered subset, review the selected range before copying and check the destination after pasting. You can also copy the visible results to a separate sheet for a review-only extract, leaving the original list intact.
- Remember that filtering and sorting do different jobs. A filter hides records that do not match a condition; sorting rearranges records. You can use both, but clearing a filter does not necessarily undo a sort.
Troubleshooting
- No dropdown arrows appear: Click inside the data and choose Data > Filter. If the worksheet is protected, filtering may be unavailable unless the sheet was set up to allow it. Check the Review tab for protection controls, or ask the workbook owner before changing protection. Also check that you are not editing a cell when you try to use the ribbon command.
- The filter arrow is on a data row or the range is too small: Turn filtering off, select the complete range including the correct header row, and turn filtering back on. Remove blank separator rows if Excel is treating the list as separate blocks. Converting a well-formed list to a table can help keep the range together.
- Expected records are missing: Open each filtered column’s dropdown and check its selections. One active condition in another column can hide otherwise matching records. Also inspect the source values for extra spaces, spelling variations, numbers stored as text, or dates stored as text.
- Filter options do not match the data: Excel chooses available filter types based on the column’s contents. If a column mixes numbers, text, and blanks, the menu may not offer the expected number or date conditions. Make the entries consistent, then reopen the dropdown. For dates, confirm that the cells contain real Excel dates rather than text that only looks like a date.
- New records are not included: If you are filtering a regular range, a newly added row may be outside the original range. Turn off filtering, extend the selected range, and apply the filter again, or convert the list to a table so adjacent new rows are more likely to be included automatically.
- You cannot see all rows after finishing: Clear the filter conditions with Data > Sort & Filter > Clear. If rows are still missing, they may have been hidden manually, excluded by a different outline or grouping, or removed by another operation. Check row numbers and other workbook settings rather than assuming a filter is still responsible.
Frequently Asked Questions
Does filtering delete rows in Excel?
No. Filtering hides rows that do not meet the active conditions. Clear the filter to show them again. Deleting rows is a separate action.
Can I filter more than one column at a time?
Yes. Apply a filter in each relevant column. Excel displays only rows that meet all the active column filters, so each added filter narrows the current results.
How do I show only cells that are blank?
Open the column dropdown, clear Select All, select (Blanks), and click OK. If blank-looking cells are not included, they may contain spaces or formulas rather than being genuinely empty.
How do I remove a filter but keep the dropdown arrows?
Choose Data > Sort & Filter > Clear to clear the active conditions while leaving the filter controls on. To clear just one column, use Clear Filter From [column name] in that column’s dropdown.
Why do some dates or numbers not filter correctly?
They may be stored as text, or the column may contain mixed types. Check the cell values and make the column consistent. For dates, verify that Excel recognizes the entries as dates; changing the display format alone does not always convert text into a date value.
Can I filter by a word that appears anywhere in a cell?
Yes. Open the text column’s dropdown, choose Text Filters > Contains, enter the word or phrase, and apply the condition. This can find partial matches without selecting each full cell value individually.
Related
- How to Duplicate Microsoft Excel Files, Sheets, and Cells
- How to Duplicate an Excel Sheet in Excel for Windows
- How to Duplicate an Excel Document on Windows Safely
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Split Cells in Excel: Text to Columns and FormulasTo split text stored in one Excel cell into separate columns, use Data > Text to Columns on…
- Excel Data Analysis Cheat Sheet: Formulas and ToolsAn Excel data analysis cheat sheet is a quick guide to preparing data, summarizing it with formulas or…
- Excel IFS Function With Dates: Examples and Date RulesYes. Excel’s IFS function can test dates just like other values: compare a date cell with TODAY(), another…
- AVERAGEIF Across Multiple Excel Sheets: Formulas & TipsYes, you can average values that meet a condition across multiple Excel sheets, but a single ordinary AVERAGEIF…
- Show Formulas in Excel on iPad: View Cell FormulasIn Excel on iPad, tap a cell to see its formula in the formula bar. To display formulas…
- SUMIF in Excel: Examples and How to Use ItExcel’s SUMIF function adds values that meet one condition. For example, with product names in A2:A7 and sales…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.