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.
Be the end of this tutorial, you will know all sorts of tips and tricks on 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
There are various approaches on how to do subtraction in Google Sheets. You can use basic formulas, 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 the same way a calculator would.
Additionally, you can do complex calculations. Just as you would when you write out a mathematical equation, parentheses are used to set the order of operations.
If a user enters:
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.
How to Subtract Cells in Google Sheets
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.
First of all, you can type them in so you could type:
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.
Various Methods of Subtraction in Google Sheets
The Google Sheets subtract formula is versatile and is used to subtract percentages, dates, and times.
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:
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 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:
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 you can use 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.
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 it is designed to subtract a set amount of hours, minutes, or seconds. This formula is written differently than above.
- 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:
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
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:
you will get the result of 8:46:50 AM.
Using Time Function
Lastly, you can use the built-in time function to subtract time in Google Sheets. This 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:
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:
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:
Additionally, like any other formula, you can use cell references in the Time function.
Google Sheets Subtract Function
Google Sheets has a built-in function specifically for subtracting variables. It is called the Minus Function. It is a basic function and simple to use, but it does have limitations. You can only use this function to subtract two values.
This function is written as follows:
It will automatically subtract the second reference cell from the first.
Google Sheets Subtract Entire Columns
If one is working with a large data set and needs to subtract entire columns, there are a few methods.
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 do this by using 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 wanted to see what your total after-tax is subtracted, you would enter the following:
=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.
Array Formulas are 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:
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, this tutorial will help you effectively subtract in Google Sheets.