To filter out one value in Excel, select a cell in your data, choose Data > Filter, open the column’s dropdown, and select Text Filters or Number Filters > Does Not Equal. To exclude several values, clear their checkboxes in the dropdown, or use Advanced Filter or a formula when you need a reusable result. These methods hide or return rows that do not match the values you specify; they do not delete your data.
What You’ll Need
- A worksheet with a header row and a consistent column of values to filter, such as Status, Region, or Amount.
- A clear idea of which value or values you want to exclude. For several exclusions, have the values handy so you can check each one in the filter list.
- For the formula method, Excel for Microsoft 365 or Excel 2021, plus a blank area where the results can spill without overwriting other cells.
These instructions are for Excel for Microsoft 365 on Windows. On a Mac, use the same ribbon commands where available; if a shortcut differs, the menu path is given instead. In Excel for the web, use the Data tab and the filter dropdowns; some Advanced Filter options may not be available there.
Step-by-Step
- Prepare the data. Keep one header row at the top, with a distinct heading for each column, and avoid completely blank rows or columns inside the dataset. Each row should represent one record, and each column should contain one kind of information. For example, a sales list might have Order ID, Region, Product, and Amount headings. Click inside the data before turning on a filter. If you use an Excel table, its headers already have filter dropdowns; otherwise, select a cell in the range and go to Data > Filter. On Windows, Ctrl+Shift+L toggles filters on or off. If that shortcut is unavailable on your Mac, use Data > Filter.
- Choose the column that contains the value to exclude. Click the dropdown arrow in that column’s header. If you do not see arrows, check that Filter is turned on and that the header row is included. The dropdown contains a checklist of values, search, and filter commands. For a short, known list of items, using the checklist is usually the simplest method. For a text or number condition, use the filter command instead.
- Exclude one text value with Does Not Equal. In the dropdown, point to or select Text Filters, then choose Does Not Equal. Enter the exact text to leave out in the Custom AutoFilter dialog, then select OK. Excel displays rows where that column’s text is not equal to the entered value. For example, in a Status column, entering “Cancelled” hides rows with that status while leaving other statuses visible. If the column contains numbers, choose Number Filters > Does Not Equal instead. Date columns have date-specific filter options; choose a suitable date condition if you want to exclude a particular date or period.
- Exclude several listed values using the checklist. Open the column dropdown and look at the checked values. Clear (Select All) to uncheck everything, then check only the values you want to keep. Alternatively, leave the current selections checked and uncheck just the values you want to remove. Select OK to apply the filter. For instance, to show all regions except East and West, uncheck East and West while leaving the other regions checked. This works well when the column has a manageable list of distinct values. The checklist may show many entries, so use its search box to find a particular item, then confirm the checked items before applying the filter.
- Use a two-condition custom filter when it fits. For text or numbers, open the dropdown and choose Text Filters or Number Filters, then choose an appropriate condition such as Does Not Equal. In the Custom AutoFilter dialog, you can enter a second condition and choose And or Or. To exclude “East” and “West,” set the first condition to Does Not Equal East, select And, then set the second condition to Does Not Equal West. Both conditions must be true for a row to remain visible. Do not choose Or for this example: a value cannot be equal to both East and West at once, so the resulting logic would not exclude them as intended. Custom AutoFilter is limited to two conditions for a column; use the checklist, Advanced Filter, or a formula for a longer exclusion list.
- Use Advanced Filter for a larger or reusable criteria set. Create a small criteria range in a blank area. For a simple “not equal” criterion, put the same column heading above the criterion and enter a formula condition beneath it, such as
<>East. For several values to exclude, use a formula criterion under a blank heading, referencing the first data row; for example, if Region is in column B and the first record is row 2, enter=AND(B2<>"East",B2<>"West"). Then click in the source list and choose Data > Advanced. Set the List range to the data, including its headers, and the Criteria range to your criteria cells. Select Filter the list, in-place to hide rows in the original list, or choose Copy to another location and specify a destination to create a separate filtered copy. Formula criteria must refer to the first data row, and the formula header should be blank or different from the source column heading. Advanced Filter is useful when the criteria are more complex than the dropdown can handle. - Return matching rows with a formula if you want a separate live result. This does not hide rows in the original list; it spills a filtered copy into another area. With source data in A2:D100, the values to test in C2:C100, and the excluded values listed in H2:H3, enter this formula in an empty cell outside the source range:
=FILTER(A2:D100,COUNTIF(H2:H3,C2:C100)=0,"No matching rows"). The formula returns each source row whose value in column C is not found in H2:H3. If the excluded list contains East and West and those are the only excluded matches, every other region’s rows are returned. Keep the output area clear because the result expands automatically. FILTER is available in Excel 2021 and Microsoft 365. In older Excel versions, use the AutoFilter or Advanced Filter method instead. - Check the result and remove the filter when finished. Confirm that the excluded values are no longer visible and that rows you meant to keep remain. A filter hides rows; it does not remove them. To restore all rows, open the column dropdown and choose Clear Filter From followed by the column name, or turn off filtering with Data > Filter. If several columns have filters, clear each one or use the clear option on the Data tab to show the complete list again.
Common Mistakes
Choosing the wrong logic. When excluding multiple values with two custom conditions, use And: a row must be not equal to East and not equal to West to remain. Or can produce unexpected results because almost every value is different from at least one of those two items. For many exclusions, the checklist or a formula is less error-prone.
Filtering only part of the data. If a blank row splits the list, Excel may treat the records below it as a separate block. Remove unnecessary blank rows or convert the whole range to a table before filtering. Also check that every column is included; filtering just one column can make the visible records misleading.
Assuming similar-looking text is identical. “East”, “East ” with a trailing space, and a differently spelled version may be treated as distinct entries in the checklist. A Does Not Equal condition may leave a variation visible. Inspect the source values and clean inconsistent entries if appropriate.
Thinking filtered rows were deleted. They are only hidden from view. Avoid copying a visible selection without checking what Excel will copy, and do not delete rows unless you really intend to remove records. If you need a permanent change, first make a backup copy and review the selected rows.
Putting formula results over existing content. A FILTER formula needs room to spill. If cells in its output area are occupied, Excel may show a spill error. Move the formula to a clear range rather than overwriting data you need.
Tips
Convert a regularly used list to a table with Insert > Table or Home > Format as Table. Tables expand as you add records and keep filter controls attached to the headers. Check My table has headers when the first row contains column names; otherwise, Excel may treat the first record as a header.
For a short exclusion list, the checklist is quick and easy to inspect. For an exclusion list that changes often, keep the values in a dedicated range and use the FILTER formula, or maintain criteria for Advanced Filter. This makes it simpler to update the exclusions without repeatedly rebuilding a long checklist.
Excel’s ordinary text filters are generally not case-sensitive. If East and EAST should be treated differently, the standard Does Not Equal filter is not a reliable way to distinguish them; use a case-sensitive formula approach instead. Also decide how blanks should behave. A blank cell is not the same as a named value, so it may remain visible unless you exclude blanks explicitly through the checklist or add a condition for them.
Before sharing a filtered workbook, tell readers that rows are hidden by a filter. A recipient may otherwise assume the visible records are the entire dataset. If you want to share only the selected results, copy the filtered rows to a new worksheet and verify that the copied range contains the records and headers you intend.
Troubleshooting
The filter arrow is missing. Click inside the list and select Data > Filter. If arrows still do not appear, check whether the sheet is protected, whether you selected the header row, or whether merged cells are interfering with the range. Unmerge cells in the list where possible, and make sure the data has one clear header row.
Rows with the excluded value still show. Check whether more than one column has an active filter, then inspect the source text for spaces, spelling differences, or values that look the same but are stored differently. Open the dropdown and confirm that the intended item is unchecked or that the Does Not Equal dialog contains the exact value. If you used a formula, confirm that its tested range starts and ends on the same rows as the source data.
Some wanted rows disappeared too. Reopen the dropdown and review the checklist; it is easy to clear a value you meant to keep. For a custom filter, verify that the conditions and And/Or setting express the rule you intend. Clear the filter and reapply it if you cannot tell which criteria are active.
Advanced Filter returns no rows or too many. Confirm that the List range includes the header row and all records, and that the Criteria range includes the criteria heading and condition. For formula criteria, use a reference to the first data row, not the header row, and ensure the formula is entered as a criterion rather than as ordinary text. Remember that criteria on the same row are combined with AND, while criteria on separate rows are treated as alternatives.
The FILTER formula shows an error or no results. If Excel reports a spill problem, clear the cells where results would appear or move the formula. If the formula name is not recognized, your Excel version may not support FILTER; use AutoFilter or Advanced Filter. If it returns “No matching rows,” inspect the excluded list and the tested column for unexpected spaces, blanks, or mismatched values.
Frequently Asked Questions
Is there a “NOT IN” option in Excel’s filter menu?
Not as a single command for an arbitrary list. Use Does Not Equal for one value, uncheck multiple values in the checklist, or use Advanced Filter or a FILTER formula for a longer list.
How do I exclude two values?
For values visible in the dropdown checklist, uncheck both and apply the filter. For a custom text or number filter, set two Does Not Equal conditions joined with And. Both approaches keep rows that match neither excluded value.
Can I filter out blank cells?
Yes. Open the column dropdown and clear the checkbox for (Blanks), if it appears. You can also select nonblank entries from the checklist. Check the source range if some apparently blank cells contain spaces or formulas that return an empty string.
Does filtering delete the excluded rows?
No. Excel hides rows that do not meet the filter criteria, and you can show them again by clearing the filter. If you delete visible or hidden records, that is a separate action and may be difficult to reverse after saving.
Can I make the filtered results update when exclusions change?
Yes. In Excel 2021 or Microsoft 365, use the FILTER formula with an exclusion range, as shown above. Changing values in that exclusion range updates the spilled results. The regular dropdown filter does not automatically use a separate list of exclusions.
Will the same instructions work in Excel for Mac or the web?
The main dropdown options, including filter checkboxes and Does Not Equal, are available in current Excel versions, though the ribbon layout can differ slightly. Use the Data tab if a Windows shortcut does not apply. Advanced Filter availability and placement can vary in Excel for the web, so use desktop Excel for that method if the command is missing.
Related
- How to Enable Paste Special in Excel
- Link Quantities in Excel: A Step-by-Step Guide
- How to Enable Freeze Panes in Excel
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Filter Duplicates in Excel: Keep One or Remove All?To remove duplicate entries in Excel for Microsoft 365 on Windows, select your data and choose Data >…
- How to Calculate a Unique Count in Excel with FormulasTo count distinct, nonblank values in Excel for Microsoft 365, use =IFERROR(ROWS(UNIQUE(FILTER(A2:A100,A2:A100<>""))),0), replacing A2:A100 with your data range.…
- VLOOKUP Between Two Sheets in Excel: A Practical GuideTo look up a value from one Excel sheet on another, enter a VLOOKUP formula such as =VLOOKUP(A2,Products!$A$2:$C$100,3,FALSE).…
- Text After an Excel Formula: Add Labels and Sheet NamesTo put text after an Excel formula, join the formula result to a quoted text string with an…
- Find a Name in Excel: Search, Match, and TroubleshootTo find a name in an Excel spreadsheet, press Ctrl+F on Windows, type the name, and select Find…
- Excel SORT Function Examples: Data, Names, and DatesExcel’s SORT function creates a sorted copy of a range with a formula: for example, with names in…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.