How to Subtract in Google Sheets

In Google Sheets, subtracting numbers is a essential arithmetic operation that can be performed in several ways depending on your data.

Write a Simple Formula to Subtract Numbers in Google Sheets

The best way is to use the minus (-) operator to subtract the number in Google Sheet. This is quick way to write formula:

1. Select the Cell: Firstly, click on the cell where you want the result of your subtraction to appear. It is where you will enter your subtraction formula.
2. Enter the Formula: Start your formula by entering ‘=’ into the selected cell. All formulas in Google Sheets start with an equal sign, which tells you you’re about to enter a formula.
3. Write the Formula: You need to specify the cell which has the first number. In our example, it is A, so that’s why we have A1 in our formula. And after that, enter the “-“ sign to tell Google Sheets to that you want to subtract numbers. Now, specify the cell where you have the second number.
4. Hit Enter: To get the result press enter and the result will be calculated in the cell.

=A1 - B1

And if you want to subtract numbers by adding directly into the formula, you just need to ue a formula like the following:

= 200 - 50 

If you want to subtract values from two cells from two different sheets:

=Sheet1!A1-Sheet2!A1

Subtracting Multiple Cells in Google Sheets

If you want to subtract multiple cells, extend the basic subtraction formula to include more than two cell references.

=A1-B1-A2-B2

In this formula, cell A1 acts as the initial number here, and B1, A2, and B2 are the subtrahends subtracted in sequence from the value in the cell A1.

And you can also write a formula using the SUM function in which all the  subtrahends can be enter in the SUM function to get a sum and then subtract all of them from the initial number.

=A1-SUM(B1,A2,B2)

Using ArrayFormula for Columns or Row Subtraction

If you need to subtract the same range of cells across multiple rows, using an ARRAYFORMULA can be super helpful and save time.

=ARRAYFORMULA(A1:A4-B1:B4)

With the ARRAYFORMULA function, you can get output and perform calculations across a range of cells without manually entering the formula into each cell. So, this formula subtracts the value in each cell of the range B1:B4 from the corresponding cell in the range A1:A4.

When you enter =ARRAYFORMULA(A1:A4-B1:B4) into a cell: The formula subtracts the value in B1 from A1, B2 from A2, B3 from A3, and B4 from A4. The result is a new array that you get in the range C1:C4 with the same number of rows and columns as the original ranges, where each cell contains the result of the subtraction.

Using MINUS Function to Subtract Numbers

 In Google Sheets, subtraction is typically performed using the subtraction operator (-) as we have leraned in the above method. But, if you prefer a specific function over a formula, you can use the MINUS function.

The MINUS function subtracts one number from another. The syntax is:

• value1 is the minuend (the number from which another number is subtracted).
• value2 is the subtrahend (the number to be subtracted from the first number).

=MINUS(A1,B1)

This formula subtracts B1 from the A1. That means values from the cell B1 from the value which you have in the cell A1. The MINUS function can only handle two arguments at a time. This can be problematic when you need to subtract multiple values. Alternatively, use a combination of SUM and MINUS:

= MINUS(A1, SUM(B1, A2, B2))

Using the SUM function, this formula works by first adding the values from the cells B1, A2, and B2. Then, it subtracts the result of this from the value in cell A1 using the MINUS function. The SUM(B1, A2, B2) inside the brackets adds the values in cells B1, A2, and B2. Then, MINUS(A1, SUM(B1, A2, B2)) subtracts the sum of B1, A2, and B2 from the value in cell A1.

Subtracting Dates

You can subtract dates between two in Google Sheets the way you subtract numbers. When you subtract one date from another, the result is the number of days between them.

This formula will give you the difference in days between 11-Mar-2024 and 24-Feb-2024 which is 16 days and that’s what we have got in the result.

#VALUE! Error when Subtracting in Google Sheets

This error occurs when one or more cells in the subtraction formula contain non-numeric value.

If A1 contains 100 in text format or any other value other than a number and B1 contains 50, this formula would result in a #VALUE! error.

Avoid Circular Dependency while Subtracting

A circular dependency occurs when a formula refers back to its own cell, either directly or indirectly, causing an infinite loop.

If cell C1 has the formula =A1-C1, it directly causes a circular reference because it attempts to add 1 to its current value, which continually recalculates.