What is Subtract in Google Sheets?
Subtracting numbers in Google Sheets is an important operation, especially during financial calculations. With Google Sheets, it is very easy to subtract numbers from one another, and there are various ways to do it. Students, individuals, or businesspersons can learn how to subtract in Google Sheets easily, saving them a lot of time and effort.
Let us dive deeply into the topic and look at various subtraction methods. Here, we will guide you through some widely used subtraction methods in Google Sheets. The image below shows two numbers in cells A1 and A2. To subtract the first number from the second, we use the minus sign as:
=A1-A2.
Press Enter, and you will get the result.
Key Takeaways
- Google Sheets makes it simple to subtract numbers or date values. We can either use the minus sign, the MINUS function, the SUM function, or the DATEIF in case of dates.
- You can also subtract an entire column from another using the ARRAYFORMULA function. For example, the values in range A2:A6 are subtracted from their corresponding values in B2:B6 by using:
- =ARRAYFORMULA(B2:B6,A2:A6).
- You can also use ARRAYFORMULA with the MINUS function for subtracting entire columns or multiple cells.
- Always check that the data being used in the formulas like SUM and MINUS are valid numbers or dates for the Google Sheets difference formula.
How to Subtract in Google Sheets?
Several methods are available when we have to subtract values in Google Sheets. Besides the minus sign we saw above, we can also use the MINUS and SUM functions to subtract. Let us look at how to do the same with each of them.
#1 – Using Minus Sign
Let us look at the steps below to subtract two numbers using the minus sign. We can use direct numbers or their cell references.
Step 1: Enter the two numbers to be subtracted.
Step 2: In an empty cell, say A3, type the equal sign. Then, type the reference of the first number.
Step 3: Add the minus sign. Then add the reference of the number you want to subtract.
Step 3: Press ‘Enter’ to see the result.
#2 – Using MINUS Function
Follow these easy steps to use the MINUS function in Google Sheets to use the subtraction formula for two numbers.
Step 1: Enter two numbers in a sheet. Select the cell where you want the result to appear.
Type the MINUS function in the cell.
The syntax of the MINUS function is as follows:
=MINUS(number1, number2)
Therefore, we enter the following formula:
=MINUS(C1,C2)
Step 2: Press Enter. The result of the subtraction will appear in the selected cell.
You can also enter numbers directly as
=MINUS(12,10)
The MINUS function will subtract the second number (or value) from the first.
#3 – Using SUM Function
Let us look at how to implement SUM using the below steps.
Step 1: Enter any two numbers. In another cell, SUM( function.
Step 2: Enter the cell reference of the first number, followed by a comma. Then, type the minus sign followed by the second number and close the parenthesis.
Step 3: Press ‘Enter’ to see the result. It is as shown below.
#4 – Using ARRAYFORMULA Function
The ARRAYFORMULA function can work on an array of data and perform mathematical calculations for each element. For the uninitiated, the syntax is: ARRAY FORMULA(array_formula)
Step 1: Let us suppose we have entered two ranges of numbers. To perform subtraction on these ranges, let us use the ARRAYFORMULA function to subtract each value in Column B from the corresponding value in Column A.
Step 2: Let us use ARRAYFORMULA as follows in cell I1.
=ARRAYFORMULA(G1:G5-H1:H5). Press Enter.
Examples
To subtract in Google Sheets, we can use the basic subtraction sign with cell references or a formula like ARRAYFORMULA and MINUS to subtract entire columns at once. Here are some examples to help you understand the topic in detail.
Example #1 – Subtract Multiple Cells
We have seen how to subtract single cells in Google Sheets. Subtracting multiple cells is similar to that. Let us look at an example of how to perform this subtraction. In this example, we have the cost of some items. The discount amount is entered in a cell.
To subtract the discount from cist, to get the final amount, we use:
Step 1: You must subtract cell C2 from cells A2 through A6. Select the range from A2 to A6 after typing = in an empty cell.
Step 2: Type =A2:A6-C2. Then, as shown below, wrap it up as an array formula using ARRAYFORMULA.
=ARRAYFORMULA(A2:A6-C2)
Press Enter. The results will appear in the selected range.
Step 3: Another way of subtracting columns is to select the range of cells you want to subtract from. After this, type the minus sign (-), and select the range of cells you want to subtract.
=A9:A13-B9:B13
Step 4:Press Ctrl + Shift + Enter. The ARRAYFORMULA will wrap the formula as shown below.
Press Enter.
Example #2 – Subtract Columns
Implementing the subtraction formula in Google Sheets is similar to the above example. Here, we have two columns. We can subtract two columns directly using the MINUS function.
Step 1: To apply two columns directly to the MINUS function, enter the following formula:
=ArrayFormula(MINUS(A1:A5,B1:B5)).
Step 2: Press Enter. You get the result as shown below.
Example #3 – Subtracting Dates
Google Sheets stores dates as serial numbers. The value represents the number of days since December 30, 1899. If the dates are stored in this form, we can perform arithmetic operations on them, such as addition and subtraction.
Step 1: Suppose we have two dates in cells B2 and A2. Let us subtract them using the formula:
=B1-A1
Step 2: Press Enter.
Step 3: We can also achieve the same result by using the DATEDIF function:
=DATEDIF(A1, B1, “d”).
For the difference in months, we type,
=DATEDIF(B1, A1, “m”)
Important Things to Note
- When using the MINUS function, ensure that the number we will subtract from is placed first and then the number we are subtracting is placed second. For instance, =MINUS(11, 6) returns 5, but =MINUS(6, 11) returns -5 when using the MINUS function.
- Note that the MINUS function subtracts just two values at a time. We may have to use the minus (-) sign to subtract more than two numbers.
- As mentioned in the previous point, we can subtract multiple numbers by adding the minus sign continuously for additional numbers or cell references. For example, =B1-B2-B3-B4.
- Always remember that the order in subtraction matters. When using the subtraction formula in Google Sheets, the numbers should be placed in the correct order to get the desired result.
Frequently Asked Questions (FAQs)
There are various methods you can use to perform subtraction in Google Sheets. Some of them include:
1. Using the minus sign
2. Using the MINUS function
3. Using the SUM function.
In each of these functions, we can type the values directly or use cell references of the cells holding these values.
The above methods can also be sued to subtract dates to find the difference in days.
For other unit options in dates, like months and years, you can use the DATEDIF function.
In such cases, we can use the ARRAYFORMULA function to subtract entire columns.
=ARRAYFORMULA(A2:A – B2:B)
This formula subtracts the values in column B from their corresponding values in column A.
In case of errors, check for the following conditions.
1. A syntax error when you enter the MINUS formula.
2. When using the SUM function, ensure that the number you want to subtract is mentioned with a minus sign. For example, SUM(A1,-B1)
3. Always check that the cells being subtracted contain numbers or dates and not text.
4. If working with dates, always ensure that both the date values are correctly formatted as dates.
Download Template
This article must help understand Subtraction Formula in Google Sheets with its formulas and examples. You can download the template here to use it instantly.
Recommended Articles
Guide to What is Subtract in Google Sheets. We learn the different ways to subtract like using the SUM function & minus sign, with examples. You can learn more from the following articles. –
Leave a Reply