20 C
London
Monday, July 21, 2025
HomeSoftware TutorialsGoogle SheetsGoogle Sheets: Convert Negative Numbers to Zero

Google Sheets: Convert Negative Numbers to Zero

Related stories

Learn About Opening an Automobile Repair Shop in India

Starting a car repair shop is quite a good...

Unlocking the Power: Embracing the Benefits of Tax-Free Investing

  Unlocking the Power: Embracing the Benefits of Tax-Free Investing For...

Income Splitting in Canada for 2023

  Income Splitting in Canada for 2023 The federal government’s expanded...

Can I Deduct Home Office Expenses on my Tax Return 2023?

Can I Deduct Home Office Expenses on my Tax...

Canadian Tax – Personal Tax Deadline 2022

  Canadian Tax – Personal Tax Deadline 2022 Resources and Tools...

You can use the following formulas to convert negative numbers to zero in Google Sheets:

Formula 1: Convert Existing Negative Numbers to Zero

=IF(A20, 0, A2)

For this particular formula, if the value in cell A2 is negative then it is converted to a zero. Otherwise, it is left alone.

Formula 2: Convert Negative Numbers to Zero in Formula

=MAX(0, B2-C2)

For this particular formula, if the result of the formula B2 – C2 is negative then the formula simply returns a zero. Otherwise, the formula returns the result of the subtraction.

The following examples shows how to use each formula in practice.

Example 1: Convert Existing Negative Numbers to Zero

Suppose we have the following dataset in Google Sheets:

We can use the following formula to convert each negative number in column A to a zero:

=IF(A20, 0, A2)

We can type this formula into cell B2 and simply click and drag the formula down to each remaining cell in column B:

Notice that all negative numbers have been converted to zero.

Example 2: Convert Negative Numbers to Zero in Formula

Suppose we have the following dataset in Google Sheets:

We can use the following formula to subtract the values in column C from the values in column B and return a zero if the result of the subtraction is negative:

=MAX(0, B2-C2)

We can type this formula into cell D2 and simply click and drag the formula down to each remaining cell in column D:

Notice that in each case where the formula would have produced a negative number, a zero is returned instead.

Additional Resources

The following tutorials explain how to perform other common operations in Google Sheets:

How to Sum Only Positive Numbers in Google Sheets
How to Use SUMIF Contains in Google Sheets
How to Use SUMIF with OR in Google Sheets

Subscribe

- Never miss a story with notifications

- Gain full access to our premium content

- Browse free from up to 5 devices at once

Latest stories