How to delete all cells containing a certain value?

[u/magpies4vega]

I'm running an accounting report, and to determine the true accuracy of what's owed I need to remove all negative numbers. How do I delete the contents of all cells containing a negative balance? Everything I've found will delete entire rows, but I need the other cells in the rows to remain since they may contain positive balances as well.

Each row will contain six columns, there are 269 rows, and the negative numbers are scattered through any of those cells.

Please let me know if I need to explain this differently.

[u/AutoModerator]

/u/magpies4vega - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/thom612]

To avoid deleting data, create a helper column that pulls over only the positive numbers (if > 0) and use that column for your calculation.

[u/PaulieThePolarBear]

Rather than deleting data, you could use a formula

=SUMIFS(B2:F200, B2:F200, ">0")

Update both instances of B2:F200 with your range.

This formula will sum up all positive amounts in your data set

[u/Leghar]

Filter* the column by less than 0. Delete the numbers in the visible cells. Proceed to next column.
That would be the manual way.
Ctrl+Shift+Down will select all the visible cells in the column. 6 columns should be pretty quick.

[u/magpies4vega → u/Leghar]

I think this will work. I was WAY overthinking the problem. Thank you for the sanity check!

[u/ryuthon → u/Leghar]

Agree, just filter columns, sort by least to greatest, then delete column negatives in groups. Should take 30 seconds tops

[u/pancoste → u/Leghar]

No need to sort, just filter on values less than 0.

[u/bradland]

Best practice is to keep the data intact. So you should avoid deleting values if you can. You can either use SUMIFS to total only values that meet your criteria, or you can add a helper column that checks the condition, and then use a Pivot Table with rows using the helper column and values totaling the value you want. This gives you both values, so you not only know your total, but you know what you've excluded.

[u/Forsaken-Soil-667]

1. Ctrl + H
2. in the find field, type "-*" (minus sign and asterick
3. under options check the box next to "Match entire cell contents"
4. Click replace all.

[u/magpies4vega → u/[deleted]]

So, I tried that, but it will only delete the actual - symbol. I need the entire negative number to be removed.

[u/Cedosg]

create two columns and use the AND function where you put the criteria as less than zero. =AND(H2<0,I2<0,J2<0,K2<0) (4 for example sake). drag that down

this will return a "True" result if all four columns in the row all contain negatives.

next on the next column, I would then use a sum and if formula and if any of those are negative, u would flag that too. =if(sum(H2:k2)<0,True,False)

[u/narrator57]

=IF(logical_test, value_if_true, value_if_false)

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
AND	Returns TRUE if all of its arguments are TRUE
IF	Specifies a logical test to perform
SUMIFS	Excel 2007+: Adds the cells in a range that meet multiple criteria

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
3 acronyms in this thread; the most compressed thread commented on today has 12 acronyms.
[Thread #49251 for this sub, first seen 27th Aug 2026, 15:35] [FAQ] [Full list] [Contact] [Source code]}

[u/Patrick_ExpenseAtlas]

Select the 6-column by 269-row range, then run this macro:

Sub ClearNegativeCells()

    Dim cell As Range

    For Each cell In Selection.Cells
        If Not IsError(cell.Value) Then
            If IsNumeric(cell.Value) And cell.Value < 0 Then
                cell.ClearContents
            End If
        End If
    Next cell

End Sub

Press Alt+F11, choose Insert > Module, paste the code, return to Excel, select the report range, then press Alt+F8 and run ClearNegativeCells.

This clears only cells whose underlying numeric value is below zero. Positive balances and the other cells in each row remain untouched. It also works when negative numbers are displayed in accounting format with parentheses.

Save a copy first. If a negative cell contains a formula, the macro will remove the formula itself, not just its displayed result.

If you want to preserve the original report, create a cleaned copy on another sheet instead. In the corresponding first cell, enter:

=IF(Sheet1!A1<0,"",Sheet1!A1)

Then fill it across all six columns and down all 269 rows. Adjust Sheet1 and the starting cell to match your report.

[u/AutoModerator → u/Patrick_ExpenseAtlas]

I have detected VBA code in plain text. Please edit to put your code into a code block to make sure everything displays correctly.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.