Turn Negative Numbers Into Zero in Excel

To turn Excel negative numbers to zero, use =MAX(0,A2): it returns whichever is bigger, 0 or your number, so negatives become zero and everything else stays the same. It's one short formula, and it also works around a whole calculation, like stock left after orders. This guide shows the MAX and IF methods, how to hide negatives without changing them, and how to total only the positive values.

The sample data#

All the examples use this stock list. Left should show how many items remain, but you can't have fewer than zero items on the shelf.

A plain =B2-C2 filled down gives 15, -8, 0, -7 and 18. The two negative results are the problem.

Method 1: MAX(0, value)#

MAX returns the largest of the values you give it. Give it 0 and your number, and the answer can never be below 0. In D2, type:

=MAX(0,B2-C2)

Fill it down (Windows: Ctrl+D; Mac: ⌘D). The results are:

How it works for Notebooks: MAX looks at 0 and -8 and returns the bigger one, 0. For Pens it looks at 0 and 15 and returns 15.

If the value is already in a cell, the formula is even shorter: =MAX(0,A2). The order of the two values doesn't matter; =MAX(A2,0) gives the same result. The MAX function guide covers the other ways to use MAX.

Method 2: IF the value is below zero#

IF does the same job in a way that reads like a sentence: "if the result is below zero, show 0, otherwise show the result."

=IF(B2-C2<0,0,B2-C2)

This gives exactly the same results as the MAX version. It is longer, because the calculation appears twice, but IF has one advantage: you can return anything you like for negatives. The IF function guide explains the three parts.

A blank made with "" is really empty text. The guide to IF blank formulas explains how that differs from a truly empty cell, and returning a blank instead of zero from VLOOKUP solves the opposite problem.

Which method should you use?#

Use MAX for the simplest result. Use IF when you want something other than zero.

For an upper limit too, combine MAX with MIN. =MIN(100,MAX(0,A2)) keeps a value between 0 and 100, which is handy for percentages or scores. For checks like "between 50 and 100", see IF between two numbers.

A common mistake: MAX with a whole range#

MAX works one row at a time in this trick. If you give it a whole range, it returns one number, not a list. For example, in Microsoft 365 =MAX(0,B2:B6-C2:C6) returns 18, the largest single difference, in a single cell. That is not what you want.

In Excel 2021 or Microsoft 365, you can still get a whole column of results from one formula with IF, because IF works on each value in turn:

=IF(B2:B6-C2:C6<0,0,B2:B6-C2:C6)

This spills five results down the column: 15, 0, 0, 0 and 18. In older versions, use the one-row MAX formula and fill it down.

How to show negatives as zero without changing them#

A custom number format changes how a number looks, not its value. Select the cells, press Ctrl+1 (Mac: ⌘1), choose Custom and type:

0;"0";0

A number format has up to three sections separated by semicolons: positive; negative; zero. Here the negative section shows the text 0 instead of the number. The cell displays 0, but the stored value is still -8, and any formula that uses the cell still sees -8.

Use this only for display, such as a printed report where negatives would confuse readers. If the zero must count in totals, use MAX or IF.

How to change the original numbers in place#

A formula can't change the cell it reads from, so use a helper column and paste the values back:

1. In an empty column, write =MAX(0,B2) and fill it down.
2. Copy the helper column (Ctrl+C; Mac: ⌘C).
3. Select the first cell of the original column and use Paste Special > Values (Windows: Ctrl+Alt+V, then V; Mac: ⌃⌘V).
4. Delete the helper column.

The original column now holds plain numbers with no negatives. The old values are gone, so keep a copy of the raw data if you might need the negatives later.

How to sum or count only the positive numbers#

Sometimes you don't need the zeros in the sheet, just a total that ignores negatives. SUMIF does this in one cell:

=SUMIF(D2:D6,">0")

If column D holds the plain differences (15, -8, 0, -7, 18), the result is 33. The negatives are skipped. To count how many items ran short, use =COUNTIF(D2:D6,"<0"), which returns 2.

The same "never below a limit" idea appears in trackers, such as counting days left before something expires. The expiry date tracker uses it to stop overdue items showing negative days.

Practise it#

Try the exercise on this page: one MAX formula keeps the stock count at zero. Then go back to basics with your first formulas to get comfortable with calculations like B2-C2, and practise the IF function in a live spreadsheet to return zeros, blanks or words. Every formula you type is checked instantly.