To show only negative numbers in Excel, select your data range, choose Data > Filter, open the dropdown on the number column, and select Number Filters > Less Than; enter 0 and confirm. This hides rows that do not match without deleting them. If you want a separate, live list of matching rows instead, use Excel’s FILTER function. The steps below are for Microsoft Excel for Microsoft 365 on Windows.
What You’ll Need
- A worksheet with a header row and a column containing the values to check.
- A consistent data range, with no unrelated notes or totals included in the range you plan to filter.
- For the formula method, Excel for Microsoft 365 or Excel 2021 or later. FILTER is not available in older perpetual versions such as Excel 2019.
You do not need to convert the range to a table, although tables make it easier to work with growing datasets. Check that the values are numbers rather than text that merely looks like numbers; Excel’s numeric filter and comparisons work best with true numeric values.
Step-by-Step
- Check the layout. Make sure each column has a clear heading, such as Date, Description, and Amount, and that each record occupies one row. Avoid blank rows inside the data because they can cause Excel to detect only part of the range. If the worksheet has several separate blocks of data, click inside the block you want to filter rather than selecting the whole sheet.
- Select the data or click inside it. Click any cell in the continuous range. Excel can usually identify the surrounding data and its header row automatically. If it does not, select the complete range yourself, including the headings. Do not include a title above the headings or a grand-total row beneath the records unless you specifically want those cells included.
- Turn on the filter arrows. On the ribbon, choose Data > Filter. Excel adds a dropdown arrow to each header. The Windows keyboard shortcut is Ctrl+Shift+L. In Excel for Mac, use Command+Shift+F to toggle the filters; the ribbon route is Data > Filter. In Excel for the web, use Data > Filter as well.
- Open the dropdown for the numeric column. Click the arrow in the column that contains the numbers you want to test, such as Amount or Balance. Use that column’s arrow—not the arrow in a name, category, or date column—so the condition applies to the intended values.
- Set the condition to less than zero. Choose Number Filters > Less Than. In the dialog box, enter 0 and confirm with OK. Excel now displays records whose value in that column is below zero. For instance, if the values are 125, -18, 0, and -4.5, the two negative entries remain visible; 125 and 0 are hidden.
- Check the result and keep the filter in place. Look at the filtered column and confirm that each visible value has a minus sign and is below zero. Excel hides rows that do not match; it does not erase them or change their values. Other records remain in the worksheet and will reappear when you clear or change the filter.
- Clear or adjust the filter when needed. To show all rows again, open the same column’s dropdown and choose Clear Filter From [column heading], or use Data > Clear to clear worksheet filters. To include zero as well as negative numbers, set the condition to Less Than Or Equal To and enter 0. To show only values below a chosen threshold, enter that threshold instead of 0.
Alternative: return matching rows in a separate area. This is useful when you want the original list to remain fully visible while a second list updates as the source changes. Assume the headers are in A1:C1, records are in A2:C6, and the numbers to test are in C2:C6. Put this formula in an empty cell, such as E2:
=FILTER(A2:C6,C2:C6<0,"No negative values")
With the example values 125, -18, 0, -4.5, and 42 in C2:C6, the result is the complete rows containing -18 and -4.5. The comparison C2:C6<0 creates a true-or-false test for each row, and FILTER returns the corresponding rows from A2:C6. Leave enough empty cells below and to the right of E2 for the result to spill; existing content in that area can block it. The optional message appears if there are no matching values. This formula returns results in a separate range and does not hide rows in the source data.
Alternative: use a helper column. A helper column can be convenient if you want to inspect the test result or combine it with other filtering steps. Assume the values to check are in C2:C6 and column D is empty. Enter this in D2:
=C2<0
Copy or fill the formula down through D6. It returns TRUE for negative numbers and FALSE for zero or positive numbers. Then filter column D to show TRUE. This method works in older Excel versions too, and you can remove the helper column after clearing the filter if you no longer need it.
Common Mistakes
- Using a text filter instead of a number filter. The dropdown may offer Text Filters when Excel thinks the column contains text. Check whether the entries are stored as text, then convert them to numbers before applying Number Filters.
- Expecting zero to count as negative. Zero is neither less than zero nor a negative number. Use Less Than Or Equal To 0 only when you intend to include zero.
- Filtering the wrong column or range. Applying a condition to a different field can produce a plausible-looking but incorrect list. Confirm the header and inspect a few visible records. Include all columns for each record so filtering keeps the row data together.
- Assuming hidden records were deleted. A filter only hides nonmatching rows. If you need a permanent list, copy the matching records to a separate location and verify the result before changing or deleting source data.
- Putting a formula result inside its source range. A FILTER result needs room to spill and should not overlap the data being filtered. Place it in a clear area or on another worksheet.
Tips
- Use a table for data that grows. Select a cell in the range and choose Insert > Table, confirm whether the range has headers, and select OK. Tables add filter arrows and usually extend to include new rows added directly below them. The Number Filters > Less Than condition still works on a table column.
- Use multiple conditions deliberately. You can apply a negative-value filter to one column and another filter to a different column, such as a date or department. Excel displays rows that meet both active column filters. Clear one column’s filter without clearing the others if you want to change just one condition.
- Keep a reusable formula list in a separate worksheet. With a live FILTER result on another sheet, the source data remains available for review. Keep the formula’s source and condition ranges the same size; mismatched ranges can cause a calculation error.
- Make the condition match the question. Less than 0 means any negative value, including a small amount such as -0.01. If you are looking for values below -100, enter -100 as the criterion instead. Values shown with accounting or currency formatting are still filterable as numbers.
- Save before major cleanup. Filtering itself is reversible, but deleting visible rows or replacing values afterward may not be. Save a copy before carrying out destructive edits on a filtered list.
Troubleshooting
- Number Filters is missing. Excel may have classified the selected column as text, or the dropdown may belong to a text field. Confirm you opened the arrow for the intended numeric column. If the entries are text, convert them: select the cells, use the warning icon if Excel offers Convert to Number, or use Data > Text to Columns and complete the wizard with the appropriate options. Check a few cells afterward to ensure they behave as numbers.
- Negative-looking values are not showing. A leading apostrophe, spaces, or imported characters can make a value text. Click a cell and inspect the formula bar; also try a simple arithmetic check in an empty cell. If Excel cannot calculate with the entry as a number, clean or convert the data, then reapply the filter. Parentheses may represent negatives in some reports but can arrive as text; convert them carefully and verify the resulting signs.
- The filter arrow or some rows are missing. Check for blank rows or columns that split the data block. Remove unintended gaps or select the entire range, including all record columns and the header row, before choosing Data > Filter again. If the range is a table, confirm that the rows you expect are part of the table.
- The FILTER formula shows #SPILL!. Clear the cells that block the output, including cells that appear blank but contain spaces or formulas. Make sure the formula is not inside an Excel table, where a dynamic array cannot spill as a range, and move it to an ordinary empty area.
- The formula returns the no-results message unexpectedly. Check that the source range and comparison range have the same number of rows and that the values being tested are genuine numbers. Confirm that the comparison uses <0, not >0, and that there actually are negative values in the selected range. If your Excel uses a localized function name or list separator, use the syntax recognized by that installation; Microsoft 365 on Windows with English settings uses the comma-separated formula shown above.
- Rows stay hidden after you finish. Open the filter dropdown and clear its condition, or choose Data > Clear. If rows are still hidden, they may have been hidden manually or by another filter; select the relevant row numbers, right-click, and choose Unhide.
Frequently Asked Questions
Does filtering negative values delete the other values?
No. The filter hides rows that fail the condition and leaves their contents in the worksheet. Clear the filter to display them again. Deleting visible rows is a separate action, so check carefully before using Delete.
How do I filter negative values and zero together?
Open the numeric column’s dropdown and choose Number Filters > Less Than Or Equal To, then enter 0. That includes zero and all values below it, while excluding positive numbers.
Can I filter values below a particular negative number?
Yes. Choose Number Filters > Less Than and enter the threshold. For example, entering -50 displays values below -50, such as -75, but not -20 or -50. Use Less Than Or Equal To if the threshold itself should be included.
Can I filter negative values in Excel for the web or Mac?
Yes. Excel for the web and Excel for Mac provide column filter dropdowns and numeric conditions; the ribbon path is Data > Filter, followed by Number Filters > Less Than. Ribbon layout can vary slightly by platform, but the condition is the same.
Why does Excel show no negative values when I can see minus signs?
The entries may be text rather than numbers, or the values may use a format such as parentheses that was imported as text. Convert the cells to numbers, verify that a negative value compares as less than zero, and apply the filter again.
Can I keep a separate list that updates automatically?
Yes. In Microsoft 365 or Excel 2021 and later, use FILTER with a less-than-zero condition, placing the formula in an empty area with room for the results to spill. The source list remains visible, and the output updates when its source values change.
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
- Excel MIN Formula Examples for Common TasksIf your numbers are in A2:A10, use =MIN(A2:A10) to return the smallest numeric value in that range. Excel’s…
- Filter Multiple Numbers in Excel: Exact and Range OptionsTo filter several exact numbers in Excel, open the column’s filter menu and select the numbers you want…
- Excel: Sum VLOOKUP Values Across Multiple Matching RowsTo total every row that matches a lookup value, use SUMIF, not a regular VLOOKUP: with the matching…
- Excel Concatenate String Examples: Formulas That WorkTo concatenate strings in Excel, join cell references and text with the ampersand operator (&), or use CONCAT…
- Excel Date Difference in Days: Formulas and ExamplesTo find the number of days between two dates in Excel, subtract the earlier date from the later…
- Rank in Excel Ignoring Blanks: Formula and ExamplesYes. In Microsoft Excel, RANK.EQ ignores blank cells in its reference range automatically. To also leave the rank…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.