If your values are in A2 and you want every negative number to become zero while leaving zero and positive numbers unchanged, enter =MAX(A2,0) in B2 and fill it down. This caps the result at zero; it does not perform ordinary decimal rounding.
The simplest formula: MAX
With the original value in A2 and the result in B2, use =MAX(A2,0). MAX returns the larger of its arguments, so a value below zero produces zero, while a value above zero is returned unchanged. For example, if A2 contains -12.7, B2 displays 0; if A2 contains 4.5, B2 displays 4.5; and if A2 is already 0, B2 displays 0.
This is often called clamping a value at zero or setting a lower bound. It is useful for calculations where a negative result is not meaningful or is not allowed, such as a remaining quantity that cannot be less than zero. It is also concise and easy to copy down a column. The formula changes the calculated result, not the source number in A2, so the original remains available for checking.
In Microsoft Excel for Microsoft 365 on Windows, type the formula in the first result cell, press Enter, then drag or double-click the fill handle at the lower-right corner of that cell to copy it down adjacent rows. Excel adjusts A2 to A3, A4, and so on as it fills the formula.
What does rounding a negative number to zero mean?
People use the phrase in two different ways. They may want every negative value replaced by zero, regardless of its size, or they may mean rounding to a chosen number of decimal places, where a small negative value happens to round to zero. Those are different operations, so decide which result you need before choosing a formula.
The MAX formula applies the first meaning: it removes all negative results from the output. It turns -0.01, -1, and -500 into zero alike. Excel’s ROUND function applies the second meaning: it rounds a number to a specified number of digits. It does not generally make negative numbers positive or force all of them to zero. For instance, rounding -0.4 to the nearest whole number gives 0, but rounding -0.6 to the nearest whole number gives -1.
Excel rounds a halfway value away from zero. Consequently, -0.5 rounded to zero decimal places becomes -1, not 0. If the requirement is that no negative output may remain, use a zero floor such as MAX after any rounding operation, rather than relying on rounding alone.
Use ROUND when decimal precision matters
If you need to round values to whole numbers and then prevent a negative result, combine ROUND with MAX. With the original value in A2 and the calculated result in B2, use =MAX(ROUND(A2,0),0). The inner function rounds to zero decimal places; MAX then changes any negative rounded result to zero. For A2 equal to -0.4, the result is 0. For A2 equal to -0.6, ROUND first returns -1, and MAX changes that to 0. A value of 2.6 becomes 3.
To round to two decimal places before applying the same lower limit, use =MAX(ROUND(A2,2),0), assuming the source is in A2 and the output is in B2. A value such as -0.004 rounds to -0.00, which Excel treats numerically as zero; -0.016 rounds to -0.02 and is then limited to zero. This pattern is helpful when the positive results must also be rounded to a defined precision.
Choose the number of digits deliberately. In ROUND, zero means whole numbers, a positive number means digits to the right of the decimal point, and a negative number rounds to the left of the decimal point. For example, a digits argument of -1 rounds to the nearest ten. The zero floor is still applied after rounding, so any negative rounded result becomes zero.
Apply the formula to a column
For a clean worksheet, keep the source values and formula results in separate columns. A typical setup has original values in A2:A100 and results in B2:B100. With the value in A2 and the result in B2, enter =MAX(A2,0), then fill the formula through the rows that contain data.
- Add a heading such as Adjusted value above the output column so it is clear that the figures are calculated.
- Enter the formula in the first output row and check a negative, a positive, and a zero input.
- Fill the formula down through the complete data range. If the source column is in an Excel table, entering the formula in the first row of a new table column generally fills the calculated column automatically.
- Review a few rows near the bottom and confirm that the formulas reference the corresponding source rows.
Keeping a separate result column is safer than immediately overwriting the source. It allows you to compare the adjusted figures with the originals, trace unexpected changes, and revise the rule if the requirements change. If the range is used in other formulas, check whether those formulas should refer to the original values or to the adjusted results.
Choose the right method for your rule
Use MAX when the rule is simply that results cannot fall below zero. Use ROUND inside MAX when you also need a specific decimal precision. An IF formula is an alternative when you want the condition to be especially explicit or expect to expand the rule later. With the source in A2 and the result in B2, =IF(A2<0,0,A2) returns zero when A2 is negative and otherwise returns A2. For this basic rule, it produces the same numerical result as MAX.
Sometimes the actual requirement is to treat only tiny values as zero, not to erase every negative value. For example, a calculation may produce a small residual because of decimal precision. If the rule is to set values with an absolute size below 0.005 to zero, while keeping larger values as they are, use =IF(ABS(A2)<0.005,0,A2) with the source in A2 and output in B2. This affects small positive values too, because ABS measures distance from zero. Adjust the threshold to match the units and tolerance in your data; do not use an arbitrary tolerance simply to hide a result that needs investigation.
If only negative values should be changed and small positive values must remain untouched, the ordinary MAX rule is more suitable. If the rule distinguishes categories, such as setting a negative balance to zero only for certain rows, add the relevant condition explicitly rather than applying a blanket change to the whole column.
Replace the original values if needed
A formula in a neighboring column is usually the most transparent approach, but sometimes a file needs fixed numbers rather than formulas. First calculate and review the results in a separate column. Then copy those results and paste them over the original range as values. In Excel for Microsoft 365 on Windows, select and copy the result cells, select the destination cells, and use Home > Paste > Paste Values. The exact presentation of the Paste Values control can vary with the ribbon layout.
This replaces the original entries, so make a backup or keep an untouched copy of the source column until the results have been checked. Pasting values removes the formulas and their link to the original data; later edits to the source will not update the pasted numbers. On Mac, the same general workflow is available through Excel’s paste options, though ribbon labels and menu placement can differ by version. In Excel for the web, use the available Paste Values option in the Home ribbon or paste menu.
Do not confuse changing a cell’s number format with changing its value. A custom format can hide a negative number or display it in a way that looks like zero, while the underlying value remains negative and continues to affect sums and other calculations. If calculations must treat the value as zero, use a formula or replace the value with a reviewed result.
Common problems and checks
- The formula still shows a negative number: Check whether you are looking at the source column rather than the formula result. MAX does not edit its input cell.
- The result is displayed with decimals: The basic formula preserves positive values as they are. Apply an appropriate number format or use ROUND inside MAX if the value itself needs rounding.
- The output is zero for a text entry: MAX ignores text stored in a referenced cell when evaluating its arguments. A number stored as text may therefore not behave like a numeric value. Convert the source data to real numbers if it should participate in calculations.
- The formula returns an error: If the referenced cell contains an error, such as #N/A or #VALUE!, MAX generally propagates that error. Decide how errors should be handled separately; turning them into zero can conceal a problem in the source data.
- Only one row changes: Fill the formula down the full range and check that the cell references move with each row. A formula copied from B2 should refer to A3 when placed in B3.
- A negative-looking zero appears: Excel may display a rounded negative zero depending on the calculation and formatting. Numerically, zero is not less than zero. If you need a consistent displayed result, use a formula that explicitly returns zero after the relevant rounding step and review the number format.
For an audit-friendly check, keep the source, adjusted value, and any explanation of the rule together. This is especially useful for shared workbooks: another person can see that a negative input was changed by a stated rule rather than mistaken for missing data.
Frequently Asked Questions
Does Excel ROUND make every negative number zero?
No. ROUND changes precision, not sign. Small negative values can round to zero, but larger negative values stay negative. To force every negative result to zero, use MAX with zero.
What formula changes only negatives to zero?
With a source value in A2 and the result in B2, use =MAX(A2,0). It returns zero for negatives and leaves zero or positive numeric values unchanged.
Can I make the original cell itself become zero automatically?
A normal worksheet formula cannot replace its own input without creating a circular reference. Calculate the result in another column, review it, then paste the results as values over the source if that is genuinely required.
How do I round to two decimal places and remove negative results?
With the source in A2 and the result in B2, use =MAX(ROUND(A2,2),0). It rounds first and then returns zero for any negative rounded result.
Will formatting a cell as zero fix a negative value?
No. Formatting affects how a value is shown, not the underlying number used in calculations. Use a formula or replace the value with a checked zero when calculations must treat it as zero.
Related
- Power Query Excel Number Format: What You Can Control
- Power Query: Append Excel Files Into One Table
- Power Query Background Refresh in Excel: How to Disable It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel INDIRECT Errors: Causes, Fixes, and ExamplesAn Excel INDIRECT error usually means the text supplied to INDIRECT cannot be turned into a valid cell…
- Add Workdays to a Date in Excel: Formula and ExamplesTo add workdays to a date in Excel, use =WORKDAY(A2,B2), where A2 contains the starting date and B2…
- Excel Lookup Value in Two Columns for MatchTo look up a value based on a match in two columns, use INDEX and MATCH with array…
- Excel IF SUM: How to Combine IF, SUMIF, and SUMIFS FormulasTo test a total and return one result or another, combine IF with SUM: for example, with amounts…
- Excel UPPER Function: Convert Text to UppercaseIn Excel, use the UPPER function to convert text to uppercase: with the text to change in cell…
- System Function in Ignition: How It WorksThe system function in an ignition system is to generate a high-voltage spark at the right moment to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.