Google Sheets is a great platform to perform complex mathematical equations. It is not complicated to subtract in Google Sheets, but many may not know the basics. There are numerous methods to subtract metrics, and we will work through them comprehensively.
By the end of this tutorial, you will know all sorts of tips and tricks to help you learn how to subtract in Google Sheets quickly and easily.Â
How to Create Formulas In Google Sheets
To start a formula, you will type the equal sign first. From there, Google Sheets uses standard formula operators:
- Equals: = (equal sign)
- Addition: + (plus sign).
- Subtraction: – (minus sign).
- Multiplication: * (asterisk).
- Division: / (forward slash)
- Exponents: ^ (caret)
How to Subtract in Google Sheets
We’ll cover every Google Sheets subtraction method, but here’s a quick look at the simplest way:
- Select an empty cell
- Input the value or cell reference you wish to subtract from
- Use the – symbol
- Enter the value or cell reference you wish to subtract from the initial value
- Press Enter or Return
There are various approaches on how to do subtraction in Google Sheets. You can use basic formulas, and built-in functions, and even run subtractions in an array. These tools will allow you to do even the most complex or laborious calculations hundreds at a time.
While working with Google Sheets, one will notice that Google Sheets works like a calculator when using a formula in a cell.
One can input the equal sign followed by a mathematical equation, and the cell will show the result.
For example, if you enter =5+6 into a cell, hit enter, and the cell will show the result of 11. This same example works with any basic mathematical symbol similarly to a calculator.
Additionally, you can do complex calculations. Like when you write out a mathematical equation, parentheses are used to set the order of operations.
If a user enters:
=5 +(6*4)-7Â Â Â Â Â Â Â
the system will run the equation in the proper order resulting in =5+24-7 and showing in the cell the result of 22. With this in mind, one can see how a subtraction formula is composed. Type =6-5, and you will get the result of 1.
The Lazy Google Sheets Subtraction Formula
When users attempt to subtract on a spreadsheet, they will likely want to use cell references to do this. There are two methods to selecting cell references.
Here’s how to subtract cells in Google Sheets the easy way:
=A1-B1
The second option is to determine the cell reference by clicking the cell needed. You do this by first inputting the equal symbol in the cell, and then you will be able to click the first cell in the equation. Next, input the mathematical symbol to use, and lastly, click the second cell to be used, and it will appear the same way as above: =A1-B1.
This method will automatically subtract the value in B1 from the value in A1. So if the value changes in either of the two reference cells, the results cell will automatically update.
Related Reading: How to Use Calculated Fields in Google Sheets
Various Methods of Subtraction in Google Sheets
The Google Sheets subtract formula is versatile and is used to subtract percentages, dates, and times.
Here’s a visual overview of how you can subtract with the minus symbol, the MINUS function, and the SUM function:
How to Subtract Percentages
To subtract a percentage from a number, you will input the following formula:
- Select the desired cell for the results ( ) and type the equal sign.
- Click on the reference cell ( ).
- Type the minus sign (-).
- Click on the same reference cell again.
- Type the multiplication sign (*) followed by the desired percentage to subtract (10%).
- The complete formula should look like this:
=B5-B5*10%
How to Subtract Dates
If you need to know the elapsed days between two dates, it is very straightforward in Google Sheets. You will subtract the dates using a subtract formula like the above. It will appear the same as above, =B1-A1, and you will have your start date in A1 and your finish date in B1.
Note:Â When subtracting dates in Google Sheets, one will notice that the start date does not count, so if you were to input January 10th – January 24th, you will get 14 days as it is counting January 11th to 24th. If you want to include the start date, you can add (+1) to the end of the formula. So the complete formula will be:
=B1-A1+1
How to Subtract Time
Google Sheets has three suitable methods to subtract time. These methods are a bit more complex but easy to remember. Once you have used these methods, you will recognize them easily for future use. Both ways can be used to subtract hours, minutes, and seconds.
With Formula to Find Duration or Time Elapsed
The first method is straightforward and looks the same as subtracting dates. You input the start time in A1 and the finish time in B1, then input the subtraction formula (=B1-A1) into the results cell. The one caveat is that you will need to select the Duration format for the results cell. To do that:
- Select the formatting drop-down in the menu bar and select Duration from the bottom of the list to change the format. Or you can choose the Format tab just below the sheet’s title and then find “number,” and the pop-out will have Duration.
- Select this, and it will convert the decimal results to a time.
With Formula and Time Conversion
The second method uses the same formula concept but is designed to subtract a set amount of hours, minutes, or seconds. This formula is written differently than above. Here’s how to subtract on Google Sheets with the second method for time.
- To subtract hours input:
=B1-(number of hours/24)
The number of hours can be a cell reference or a number. So if B1 has 11:00 AM and you would like to find what time was 2 hours before you would enter:
=B1-(2/24)
and you will get 9:00 AM.
- Subtract Minutes is the same method, but instead, you will divide the number of minutes by the total minutes in a day (1440). So if B1 has 10:00 AM and you would like to find out what was 30 minutes before that time, you input
=B1-(30/1440)
and you will get 8:30 AM
- Subtract Seconds functions precisely the same, except you divide the number of seconds you wish to subtract by the total seconds in a day (86400). So if B1 has 9:28:30 AM and you would like to see what was 2500 seconds before, you input:
=B1-(2500/86400)
you will get the result of 8:46:50 AM.
Using Time Function
Lastly, the built-in time function can subtract time in Google Sheets. This Google spreadsheet subtract option is slightly limited because it does not allow you to subtract more than 24 hours at once. But it is very straightforward.
The Time function:
=TIME(hours,minutes,seconds)
To use this to subtract time, you will nest it within your formula. For example, if you would like to remove 5 hours, it will appear as follows:
=B1-TIME(5,0,0)
As long as B1 is in a date format, this will subtract 5 hours. If you wanted to subtract 3 hours, 20 minutes, and 10 seconds you would write it as follows:
=B1-TIME(3,20,10)
Additionally, like any other formula, you can use cell references in the Time function.
Subtracting From MatrixesÂ
You can subtract one matrix from another by using an ARRAYFORMULA. Here’s an example:
- In an empty cell with enough subsequent surrounding space to fit a matrix of equivalent size, (it should be where the top left of the number matrix will finish up) type =ARRAYFORMULA((
- Select the range with the matrix you wish to subtract from. In the example above, it’s I2:K5
- Put a closing bracket )
- Type the minus symbol
- In brackets, enter the range you wish to subtract from the initial range, E2:G5Â in the example
- Put another closing bracket ) and press Enter
How to Do Subtraction in Google Sheets Across Multiple Worksheets
You can use all of the standard subtraction functions in Google Sheets across worksheets. You just have to use the sheet name as part of the cell reference. For example, let’s say you have a sheet named “Sheet1” and one named “Sheet2”, and you want to subtract cell C1 in Sheet 2 from C1 in Sheet 1. You could use the following formula:
=Sheet1!C1-Sheet2!C1
In the example below, we subtract Google sheet 1 from sheet 2:
The sheet name followed by the ! symbol lets Google Sheets know which sheet to search. Then, you just use the cell reference.
Google Sheets MINUS Function
Google Sheets has a built-in function specifically for subtracting variables. MINUS is the standard subtract formula in Sheets. It is a basic and simple function, but it has limitations. You can only use this function to subtract two values.
For example, if we wanted to take B1 away from A1, this function is written as follows:
=MINUS(A1, B1)
It will automatically subtract the second reference cell from the first. Remember, if you use the MINUS function, you should use commas instead of the minus symbol.
Google Sheets Subtract Entire Columns with the SUM Function
There are a few methods if one is working with a large data set and needs to subtract entire columns.
Subtracting Whole Columns from Each Other.
If you have a data set with 100 rows and want to get the total number from the data set, you can use the sum function to sum the desired ranges.
For example, say your sales amounts are listed in Column C, and your tax amount is in column D, and you want to see what your total after-tax is subtracted. Here is the subtraction formula in Google Sheets you would use:
=SUM(C1:C100) - SUM(D1:D100)
Subtracting Adjacent Values in a Whole Column
Using the same data set, perhaps we would like to see the total for each line item is after-tax; you can do this by typing the formula in the first cell. This formula would be =C1-D1. After this cell has its formula, click the cell once, and you will see a small square appear in the bottom right corner of the cell. Click hold this as you drag it down the entire Column (alternatively, you can copy and paste it down the whole Column).
Google Sheets Subtract Array
Array formulas take a bit more work, but they are much more efficient and simpler to modify.
The Array Formula will run the same formula in each cell down the whole Column, but you will only have to input it once at the top of the Column. Meaning it is much faster, and if you need to edit it, you only have to edit it in one cell.
A Google Sheet subtract formula with Array Formulas is written as follows:
=ARRAYFORMULA(RANGEÂ - RANGE)
So for our example, in cell E1, you will enter:
=ARRAYFORMULA(C1:C100Â - D1:D100)
As you will see, this will run the subtraction formula from E1-E100.
This method would work with all of the above methods, except it would not be effective if we worked on getting totals on the sheet like above when using the SUM function.
One example with time would be if you have a column of times in C1:C25 and we wanted to subtract 30 minutes from each; we could enter the following formula:
=ARRAYFORMULA(C1:C25-TIME(0,30,0))
Frequently Asked Questions
What Is the Subtraction Function in Google Sheets?
=MINUS is the standard Google Sheets subtract function. But, you can also use the – symbol as an operator instead. For example, =MINUS(A2,C2) is the same as =A2-C2.
How Do You Subtract 2 Numbers From a Number in Google Sheets?
You simply have to put the first number at the beginning of the equation and use the – operator twice. For example, =100-30-5. Alternatively, you can add the two other figures and subtract the result from the first number. For example =100-(30+5).
Conclusion
Google Sheets offers numerous methods to subtract and run mathematical equations. These methods are native to Google Sheets and are very simple to use. Hopefully, you now know how to subtract in Google Sheets using each function and how to use them with varied number formats. Let us know in the comments if you need any more help.
Want to become a Google Sheets expert? We highly recommend checking out Udemy’s Google Sheets Comprehensive Masterclass and the Complete Google Sheets Course.