To filter several exact numbers in Excel, open the column’s filter menu and select the numbers you want to keep. For a numeric range, choose Number Filters; for a longer list of exact values, use Advanced Filter or a formula. Filtering hides nonmatching rows without deleting them, so you can choose the approach that fits your data and change it later.
What You’ll Need
- A worksheet with a header row and one number column you want to filter.
- The exact values or range boundaries you want to see, if you already know them.
- For the formula option, Microsoft Excel for Microsoft 365 or Excel 2021 or later, where the FILTER function is available.
These instructions are for Excel for Microsoft 365 on Windows. The main filtering commands are also available in Excel for Mac and Excel for the web, though ribbon placement and dialog appearance can vary slightly.
Step-by-Step
- Check the data and decide what “multiple numbers” means. You might want to show a particular set of values, such as 1042, 1088, and 1105, or all values within a range, such as 1000 through 1200. These are different tasks: selecting exact values keeps only those values, while a range includes every number between its boundaries. Make sure the number column contains actual numeric values, not numbers stored as text. Keep one clear header row, avoid blank rows within the data, and check that each row’s information stays together across the columns.
- Turn on the column filters. Click a cell inside the data range, then choose Data > Filter. Excel adds a drop-down arrow to each header in the range. If Excel has not identified the full range correctly, select the entire data block, including its headers, before choosing the command. You can also convert the range to an Excel table with Insert > Table; a table adds filter arrows automatically and usually expands when you add rows.
- Select several exact numbers using the checklist. Open the arrow in the number column’s header. Clear (Select All), then tick the check boxes beside each number you want to keep. Use the search box in the menu to find a value when the list is long; search for and select each required value before applying the filter. Click OK. Excel hides rows whose value is not selected, while retaining the matching rows and their other column data. This is usually the quickest method for a short or moderate list of values already present in the column.
- Filter a numeric range with Number Filters. Open the column’s filter arrow and choose Number Filters > Between. Enter the lower and upper limits, then confirm. For example, setting the limits to 1000 and 1200 shows values from 1000 through 1200, including the endpoints. For a one-sided condition, choose an option such as Greater Than, Less Than, or Equals under Number Filters. To combine two conditions, choose Number Filters > Custom Filter, enter both rules, and choose And or Or. Use And when a value must meet both rules, as with greater than 1000 and less than 1200; use Or when either rule is sufficient.
- Use Advanced Filter for many exact numbers. Create a small criteria range in an empty part of the sheet. Copy the number column’s header exactly into the first criteria cell, then put each required number in its own cell underneath that header. For example, if the source header is Invoice No, use the same text as the criteria header and list 1042, 1088, and 1105 on separate rows below it. Select a cell in the source data and choose Data > Advanced. In the dialog, check that List range covers the source including headers and set Criteria range to the header and values you made. Choose Filter the list, in-place and click OK. Values on separate criteria rows mean “this value or that value,” so Excel displays rows matching any listed number. If you choose Copy to another location, specify a destination outside the source list.
- Optionally create a formula-driven result with FILTER. This is useful when you keep the wanted numbers in a separate list and want the results to update when that list changes. With complete records in A2:C100, the numbers to test in B2:B100, and the wanted values in F2:F4, enter this formula in an empty cell outside the source data:
=FILTER(A2:C100,ISNUMBER(MATCH(B2:B100,F2:F4,0)),"No matches"). Excel returns each matching row from columns A through C, in the original order; it returns “No matches” if none of the values in column B appears in F2:F4. For example, if B2:B6 contains 1042, 1070, 1088, 1105, and 1042, and F2:F4 contains 1042, 1088, and 1105, the result includes rows 2, 4, 5, and 6. Leave room for the results to spill into adjacent cells. This formula creates a separate result; it does not hide rows in the original list.
Common Mistakes
- Using the wrong kind of filter. A Between rule includes all numbers inside its limits, not just chosen values. For an exact set, select values in the checklist or use Advanced Filter.
- Putting several criteria across one row in Advanced Filter. Criteria in the same row are treated as conditions that must all be true. To match any of several exact numbers in one column, list them on separate rows under the same header.
- Leaving a value selected unintentionally. If you forget to clear (Select All) before ticking a few values, the filter can still show every number. Clear the selection first, then choose only the wanted entries.
- Including a blank row or mismatched header in the source. A blank row may make Excel treat only part of the data as the list, while an Advanced Filter criteria header that differs by even a space may not work as expected. Use a continuous data range and copy the original header.
- Expecting a filter to delete records. AutoFilter only hides rows that do not match. Clear the filter to see them again; do not delete rows unless you have checked that removing them is actually your goal.
Tips
- For a quick review, use the drop-down checklist. For a range, use Number Filters. For a long, reusable list of exact numbers, keep the criteria list on the sheet and use Advanced Filter or the FILTER formula.
- To remove an AutoFilter condition, open that column’s menu and choose Clear Filter From followed by the column name. To clear all active column filters, use Data > Clear. The arrows remain available until you turn filtering off with Data > Filter.
- Freeze the header row with View > Freeze Panes > Freeze Top Row if you need to scroll through a long filtered list and keep the column names visible.
- When you expect to add records regularly, format the source as a table. This makes the filter controls easier to maintain and helps new rows become part of the data range.
- In Excel for Mac, use the Data tab’s Filter and Advanced commands; wording is generally the same, but the ribbon layout can differ. In Excel for the web, basic AutoFilter options are available, while some Advanced Filter controls may be limited or arranged differently.
Troubleshooting
A number you expect is missing. First clear the filter with Data > Clear and inspect the original cell. The value may be stored as text, contain a leading or trailing space, or have a different underlying value from its displayed, rounded appearance. If the cell has a warning indicator, use Excel’s convert-to-number option where appropriate. You can also check the number format and increase displayed decimal places. Be cautious about converting identifiers such as account or product codes: those may need to remain text, especially if they have leading zeros.
The filter arrow or Advanced Filter gives unexpected results. Confirm that the source has one header row, no merged cells in the list, and no blank row splitting the data. For Advanced Filter, make sure the criteria header exactly matches the source header, the criteria range includes both its header and values, and each alternative number is on a separate row. Check that List range includes the complete data block, not just the number column, if you want complete records to be filtered.
The FILTER formula shows an error or does not update. Make sure the formula is entered in a blank area with enough empty cells to display all returned columns and rows. A blocked output area causes a spill error; move the formula or clear the obstructing cells. Confirm that the ranges have matching row counts and that the wanted numbers are in F2:F4, as assumed in the example. The FILTER function requires Microsoft 365 or Excel 2021 and later. In older versions, use the filter menu or Advanced Filter instead.
Frequently Asked Questions
Can I filter for several exact numbers without a formula?
Yes. Open the number column’s filter menu, clear (Select All), tick each exact value, and select OK. For a long set of values, use Advanced Filter with one number on each criteria row.
How do I filter numbers that are not next to each other?
Select each value separately in the filter checklist, even if the values are far apart numerically. If the list is long, place the wanted values on separate rows under a matching header in an Advanced Filter criteria range.
Can I use both a range and an exact value?
Yes, but decide whether you mean a range with an extra condition or a specific set of numbers. Use Custom Filter for two conditions on the same numeric column, joined with And or Or. If you need a more complex combination across multiple columns, Advanced Filter criteria rows can express alternatives, with conditions on the same row combined together.
Will filtering change or delete my original numbers?
No. AutoFilter hides rows that do not meet the condition; it does not change cell contents or remove the records. Use the column menu’s clear option or Data > Clear to show hidden rows again.
Why does Excel treat a number as text?
Imported data, an apostrophe before the digits, or inconsistent formatting can leave digits stored as text. That can affect numeric filters and comparisons. Check the cell’s contents and convert values only when they represent quantities; preserve text when the digits are identifiers where leading zeros matter.
Related
- Enter Serial Numbers in Excel: Manual and Automatic Methods
- How to Enable Word Wrap in Excel and Adjust Cell Text
- How to Enable Wrap Text in Excel and Adjust Row Height
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF for Numbers in Cells: Examples and FixesTo count cells that equal a number in Excel, use =COUNTIF(B2:B20,25), replacing the range and 25 with your…
- How to Filter Excel by Date: Ranges, Months, and YearsTo filter Excel by date, select a cell in your data, choose Data > Filter, open the arrow…
- Calculate Hours in Excel: Formulas and Time FormatsTo calculate elapsed hours in Excel, put the start time in A2 and end time in B2, then…
- SUMIFS Excel Expression: Syntax and ExamplesThe SUMIFS Excel expression adds numbers that meet multiple conditions. Its basic form is =SUMIFS(C2:C100,A2:A100,"East",B2:B100,"Lee"): this adds values…
- Outlook Address Examples and How to Choose the Right OneCommon Outlook address examples include first.last@outlook.com, initials.number@outlook.com, and a work address such as first.last@company.com. The first two show…
- Excel Filter Command for Multiple Criteria ExplainedIn Excel for Microsoft 365 on Windows, use Data > Filter, then set a condition from each relevant…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.