Subtracting numbers in real life is a no-brainer. We learn to do this so early in life that we never even consider it a skill. The advent of calculators has made it easier to subtract even large numbers.
Subtracting data values in Google Sheets, on the other hand, is a whole other thing.
When you’re dealing with large amounts of data, it becomes impractical to use your calculator to perform subtractions one at a time. In fact, why bother working on data one at a time when you can perform thousands of subtractions in one go?
Google Sheets uses Formulas and Functions to help you subtract.
In this tutorial, we will show you how to subtract in Google Sheets.
Table of Contents
Learning How to Subtract in Google Sheets
Google Sheets and, more or less, all spreadsheet software thrive on functions and formulas. Both provide excellent ways to perform calculations and obtain results. That’s precisely why, out of all the different ways in which you can subtract on Google Sheets, we have chosen to highlight these methods. The easiest way to get the Google Sheets difference between two numbers is using the minus sign.
Here’s how to subtract in Google Sheets:
- Type the equal sign in the results cell
- Type the first value
- Type the minus sign
- Type the second value
- Hit Enter
Out of all the options, this is the simplest subtraction Google Sheets function. To understand how to quickly subtract cell values in Google Sheets, we are going to use the following dataset:
In this dataset, we want to subtract each Expenditure value (column B) from each Income value (column A) and put the resultant Balance in column C.
Subtracting in Google Sheets Using a Simple Formula
A formula is just a statement that a user makes to perform a calculation. A formula consists of a combination of values along with one or more operations, like addition, subtraction, etc.
The values used in a formula can be numeric, or text values reference to cells or defined names.
A formula can be simple, as the ones shown below, with just two variables and an operation. The MINUS function is the simplest Google Sheet formula for subtraction:
= 6 - 5
= A2 – B2
It can also be complex, consisting of a series of formula operators.
In any case, a formula should always start with an ‘equal to’ sign (=). Whenever you use a formula in a cell, it is displayed on the formula bar.
When you finish entering a formula in a cell, it performs the operations as soon as you press the return key.
At this point, the result of the formula gets displayed in the cell, while the actual formula is displayed in the function bar when the cell is selected:
To use a formula to subtract two cell values in Google Sheets, follow these steps:
- Select the cell where you want the result to appear (cell C2)
- Put an equal to sign (=) in the cell to start the formula. If you look up at the formula bar you will notice an equal to (=) sign appearing there too.
- Select the cell containing the number you want to subtract from, or the minuend(cell A2)
- Follow this with a minus sign (-).
- Select the cell containing the number you want to subtract, or the subtrahend (cell B2)
- Press the return key.
- The difference between the values in A2 and B2 should now be displayed as the result in cell C2.
- Once you get the first formula ready, getting results for the rest of the cells in the same column is easy. You will notice a small square at the bottom right corner of cell C2. This is called the Fill Handle. Hover your mouse over it.
- When your mouse changes into a plus sign, just double click on the square. You will find the formula copied to all the cells in column C. Alternatively, you could just drag the fill handle down the column till you reach the last row.
Each cell in column C should now contain the value of the corresponding Expenditure, subtracted from the Income!
Wasn’t that easy?
Here’s another way…
Subtracting in Google Sheets using the MINUS Function
A function is a kind of formula that has been pre-built to perform specific tasks. For example, there are functions to find sums, multiple, differences, averages, etc. MINUS is the Google Sheets subtract formula.
Just like formulas, functions always start with an ‘equal to’ sign (=). This is followed by the name of the function and parentheses. Inside the parentheses, you can list parameters (or arguments), if applicable.
Arguments are input values that the function uses to perform its task. The number and type of arguments depend on the syntax of the function. For example, Google Sheets has a function called MINUS that is used to subtract two numbers. This function’s syntax takes two parameters – the subtrahend and the minuend. In a standard subrtaction equation like:
8 - 6 = 2 The minuend would be 8 and the subtrahand would be the 6.
If there is more than one argument, then they are separated by commas. So the MINUS function’s syntax is as follows:
=MINUS(value1, value2)
Here:
- value1 is the minuend.
- value2 is the subtrahend.
In this formula, value1 and value2 can either be numbers, dates, or references to cells containing numbers and/or dates.
So, you can write either:
=MINUS(6, 5)
or
=MINUS(A1 , B1)
Like formulas, whenever you use a function in a cell, it is displayed on the formula bar.
When you finish entering the function in a cell, it performs the operations as soon as you press the return key.
At this point, the result of the function gets displayed in the cell, while the actual formula is displayed in the formula bar when the cell is selected:
How to Do Subtraction in Google Sheets With the SUM Function
Although the SUM function in Google Sheets is usually for adding numbers, you can also use it for subtraction. You just have to use the minus symbol with the parenthesis between the two values. You can use SUM as a subtract function Google Sheets like so:
SUM(A2-B2)
How to Subtract Time and Date In Google Sheets
Unfortunately just using MINUS or SUM won’t allow you to subtract time effectively. Instead, you have to use the TIME function as well. You can use it with the following formula:
=cell reference-TIME(N hours,N minutes,N seconds)
Let’s take a look at an example. Let’s say we needed to take away 2 hours, 31 minutes and 20 seconds away from the time in cell A1. Our formula would look like:
=A1-TIME(2,31,20)
When subtracting dates, you don’t need to use the time function in Google Sheets. You can just subtract them like normal values or use cell references. For example, if we want to subtract the 7th of August and the 15th of January, we can use the formula below.
The result will be 207 days. If you want to change the format for this result, for example, changing it to months and days or even percentage, you can follow the following steps:
- Select the cell
- Go to Format > Number
- Choose the format you want to use
One thing to note is that with any method for subtracting dates in Google Sheets, the first day is usually not counted. If you need the first day counted as well, then you can just add +1 to your equation.
How to Subtract Numbers in Google Sheets Using Arrays
ARRAYFORMULAS can be super handy to fetch whole columns’ worth of data at once and complete calculations on them. Here is how you would use an array formulas with the MINUS function:
=ARRAYFORMULA(MINUS(array1, array2)
Let’s substitute some figures into an example and see what it looks like:
You can click and drag over the cell references rather than entering them manually too.
How to Do Subtraction on Google Sheets Across Multiple Worksheets
All you have to do to subtract across sheets is use the sheet prefix and a cell reference with the minus sign in between. Here is an example:
The above example has subtracted cell B5 in Sheet2 from B5 in Sheet1. Just don’t forget to add the exclamation mark between the sheet name and the cell reference. That’s how to subtract cells in Google Sheets from separate worksheets.
How to Subtract Cells in Google Sheets
You can also use cell references in your formula instead of manually typing in the numbers themselves. This is useful, especially when you’re dealing with large numbers, and it helps to prevent errors.
Here’s how you can type out a function to subtract two cell values in Google Sheets:
- Select the cell where you want the result to appear (cell C2)
- Put an equal to sign (=) in the cell to start the formula. If you look up at the formula bar you will notice an equal to (=) sign appearing there too.
- Type function name MINUS, followed by opening parentheses. Your formula bar should now show:
- Select the cell containing the number you want to subtract from, or the minuend(cell A2). This will display the cell reference of the selected cell after the opening parentheses.
- Follow this with a comma (,)
- Select the cell containing the number you want to subtract, or the subtrahend (cell B2)
- Close the parentheses. Your formula bar should now show:
- Press the return key.
- The difference between the values in A2 and B2 should now be displayed as the result in cell C2.
- Once you get the first formula ready, getting results for all the rest of the cells in the same column is easy. Hover your mouse over the Fill Handle (located at the bottom left of cell C2).
- When your mouse changes into a plus sign, double click on the square. You will find the formula copied to all the cells in column C. Alternatively, you could drag the fill handle down the column till you reach the last row.
Each cell in column C should now contain the value of the corresponding Expenditure, subtracted from the Income.
How to Subtract Multiple Cells in Google Sheets
It is also possible to subtract multiple cells in Google Sheets depending on how you want to subtract the values. If you want to subtract the cells in a sequence or linear form then you can use the MINUS function. To do this, you will need to
For example, to find the balance in our sheet below, we can subtract all the values or cell references in one go. This gives us the balance which is 72$
You can also use the SUM function to subtract in the same example. You simply need to create the sum formula and then minus the total value to that sum. For example, in our sheet below, we used the sum of the values from B2 to B7 and subtracted it from the total.
Both these two ways will give you the same answer, so it is up to you to choose which method is most suitable for you.
Subtraction Equations
Sometimes the function you want to carry out is not as simple as getting the difference between two values in Google Sheet. You might want to do a more complex equation.
You can create subtraction equations using the minus function and other signs like divide, multiply and add. This is more convenient since you can combine multiple formulas to get one return much faster.
For example, in our sheet below, we subtracted the 2 values and then divided them by 10 to get the answer.
Frequently Asked Questions
How Do You Create a Subtraction Formula in Google Sheets? / How Do You Subtract Numbers in Google Sheets?
There are a few different ways to create a Google spreadsheet subtraction formula. The simplest way is to simply use the MINUS function. For example, to do the calculation 5-4 you’d just have to type:
=MINUS(5,4)
How Do You Add and Subtract on a Spreadsheet?
If you need to add and subtract in one formula, you should use the SUM function with the appropriate operators to signal to Google Sheets where to subtract and where to add. Let’s say you wanted to find out 5 +5 -7. You could use the following formula:
=SUM(5+5-7)
How Do You Subtract Fractions in Google Sheets?
You can subtract fractions by using the Google Sheet subtract formula, the MINUS function. For example, to take one quarter away from one half, you could use the following formula:
=MINUS(1/2,1/4)
You could use cell references containing the fractions instead of manually typing them. Note that the results will display as a decimal.
Related: How to Round Numbers
Wrapping Up Subtraction In Google Sheets
So this is how to subtract in Google Sheets using simple Google Sheets difference formulas. Familiarize yourself with each way to make building bigger formulas easier as your knowledge progresses.
I hope you found this article useful! You can also read more in our article on how to find absolute value in Google Sheets.
Other Google Sheets articles you may like:
5 thoughts on “How to Subtract in Google Sheets”
I found this really useful. Thank you. 🙂
Very useful thankyou google ❤
This is helpful – BUT – what happens in the case where you have, let’s say one cell B1 at a fixed dollar amount and the second cell B2 with a varying number. My example refers to the closing price of a Stock the previous day at say $110.00. And B2 is a current and continually changing number until the end of the day. But you want to find out your gain or loss during the day between 9:30 am and 4 pm. If you have the function set up for Minus to subtract B2 from B1 and B2 is a larger number, how will that work ?
The result will show as a negative integer. For example, if you perform $100 – $200 the result would be -$100.
Unfortunately I don’t see the formula to subtract a percentage from a cell?
any help would be great thank you in advance.
steve.