Hi. Welcome to another tutorial of our Google Sheets articles series. Today, we will learn how to subtract in Google Sheets. We all know, subtraction is one of the four basic math operations along with add, divide, and multiply. Subtract is also called minus in basic math. Unlike Sum, we don’t have any function for subtraction, but still, we have so many methods to perform subtraction on our data.
This tutorial will cover the subtraction feature from start to end. I will show you some basic functions of it, we will learn about order, sequential subtraction, matrices, and also the sum function for subtraction. I will use simple data and will show you simple examples to understand the nature of subtraction in Google Sheets.
Importance of using Subtract Function in Google Sheets
We need four basic math functions for countless things. We cannot work on data, calculations, functions, and math without these. These are the four basic pillars of the entire mathematics. So, we have already learned about Addition, Multiplication, and Division in this series, so how can we skip subtraction? Although, many beginners know how to subtract and they can perform simple subtraction easily.
But, in today’s article, I will show you some advanced and complex use cases of subtraction in Google Sheets. We will work with matrices, and you will see how smartly Google Sheets will respond. We need to learn it for basic value-to-value subtraction, cells-to-cells, row-to-row, and column-to-column subtractions. I will also show you how to make a custom formula using the SUM function to perform subtraction. So, these are some reasons we need to learn how to subtract in Google Sheets. Let’s get started.
How to Subtract in Google Sheets?
From this section, we will see all the methods we can use to perform subtraction in Google Sheets. After basic usage, we will learn some advanced and very useful ways to use subtract feature in Google Sheets. So, without further delay let’s get started.
How to Subtract in Google Sheets – Direct Subtraction
In this section, we will learn how to subtract in Google Sheets. We will see the direct subtraction method with the minus sign between two values, let’s see the simple steps below.
Open a Google Sheets file and select any cell
Write your subtraction logic such as 10-5 starting with an equal sign.
Press Enter key, and this way you will get the correct result.
This is the most simple and easy way to perform simple subtraction in Google Sheets. Let’s see how to do it with cell references.
Here, you should have your values in the cells
Write your logic and use cell references instead of direct numbers.
Press Enter key, and you will see the correct result
Note: This method is dynamic, which means if you change the value inside A3 or B3, then the result will be updated automatically.
How to Subtract in Google Sheets – Column to Column
In this section, we will learn how to subtract in Google Sheets, and here we will see how to subtract entire columns. We can have two columns that contain salary and deductibles in each column, and we may need to minus deductibles from salary to bring Net Salary in the new column, in such cases we need to perform subtraction on entire columns.
Write the custom formula for subtraction in a new column cell, this will work only for one cell from each column
Press Enter, and you can see you have got the answer for one row
Now you can drag this formula to autofill for the below rows
And this is how you will get the result just by using autofill functionality.
We have another way to do this, which is using ArrayFormula
Write ARRAYFORMULA in a cell in which you want to get the result
Pass the first entire column
Add a minus sign
Pass the second entire column, and press Enter key.
Now you can see even without using autofill, you have successfully subtracted an entire column from another.
How to Subtract in Google Sheets – Sequential Subtraction
In this section, we will learn how to subtract in Google Sheets, and here we will learn how to perform sequential subtraction without any function. So, let’s say you got 200 projects at the start of the year 2020, and now you are doing them month wise and at the end of the year you have completed some in December, now what you have left will be rolled to next year. We can easily use sequential subtraction here to calculate the number of projects left.
Consider the below sample data
Write = sign, the pass initial value
Now add – a sign, add the next value, and keep doing the same for all values
The final formula will be like below
Press Enter key, and you will get the result
This is how sequential subtraction works in Google Sheets. If you have other operators in this example, then the DMAS rule will be followed for order.
How to Subtract in Google Sheets – Using the SUM Function
In this section, we will learn how to subtract in Google Sheets using the SUM function. Well, you may be surprised why and how we can use a SUM function for performing absolute negative operations. Yes, this is the key point, we can use it because it’s the absolute opposite. Well, the SUM function can do both if you use + and – signs in the correct places. Let’s see the below example to understand this method.
Consider the same example as we used in the previous example
Now, we will not use the sequential custom formula, instead, we can use the SUM function, and write the SUM function first.
Now perform the sum of all the month’s values
Now, add a – minus sign, and pass the initial value i.e. 200
Press Enter key, and you will get -63 which means you have 63 projects remaining topics. Minus sign is representing that these are negative values. As we had small value on the first position.
This is how you can use the SUM function for subtraction in Google Sheets.
You can perform simple subtractions as well, such as =SUM(12-8), which will return 4, the same as you do this without the SUM function.
So, the SUM function is for both addition and subtraction based on the + and – signs placements, and also the position of brackets.
How to Subtract in Google Sheets – Matrices Subtraction
In this section, we will learn how to subtract in Google Sheets. Here we have metrics, which is a less used scenario but very significant and helpful when working with matrices data. We may have our data in the form of matrices, and we may need to perform calculations on it, such as addition, and subtraction. Let’s see and learn how to subtract in Google Sheets on matrices.
Let’s say you have the following matrices
Note: Make sure that the matrices are in the same structure i.e., 3×3 matrix – 3×3 matrix.
Pass first entire matrix as the first argument
Pass the second entire matrix as the second argument
Here you can see you have got each value compared and subtracted by the same positioned value and you have got the answer as the entire new matrix
Note: This also works for addition, multiplication, and division.
Sometimes, we need to perform subtraction on rows instead of columns, let’s see how it’s done.
How to Subtract in Google Sheets – Row to Row
In this section, we will learn how to subtract in Google Sheets using entire rows. Sometimes we have horizontal data and we need to perform subtraction on entire rows. Let’s see how we can do it as we did for columns.
We need to calculate the net profit by subtracting expenses from earnings.
Write the ArrayFormula
Pass the first entire row as the first argument.
Pass the second entire row as the first argument.
You can see you have got the result without dragging or using autofill. This is how ArrayFormula works with subtraction.
Note: You must not have any values or, any data in the cells where the ArrayFormula needs to expand because ArrayFormula does not overwrite the already written text. It will show an error.
So, this is how to subtract in Google Sheets using many ways.
Get a Free Template used in this Article
- Subtract function is not available in Google Sheets, because SUM works for both addition and subtraction
- With matrices, we can perform other operations as well such as addition, multiplication, and division.
- We can also use a newly added MINUS function in Google Sheets to get the difference between two numbers.
Frequently Asked Questions
How to use SUBTRACT function in Google Sheets?
There is no SUBTRACT function in Google Sheets. The SUM function can be used identically for Addition and subtraction operations. Or, we can use direct subtraction using the – sign, also we can use ArrayFormula for multiple cells, and then use + and – signs inside the formula to perform subtraction.
Can we subtract two columns in Google Sheets?
Yes, we can subtract directly and then drag the formula. Or, we can use ArrayFormula to subtract two columns or rows quickly without using autofill.
What is the MINUS function in Google Sheets?
The MINUS function can be said as a subtraction function that is used to display the difference between two numbers.
This is all about how to subtract in Google Sheets. I tried to cover every way to do subtraction in Google Sheets. I taught you how to use simple direct subtraction, use the SUM function for subtraction, use ArrayFormula to subtract, and how to subtract in Google Sheets on matrices. So that’s all from how to subtract in Google Sheets. I will see you soon with another useful guide. Take care and Keep learning with Office Demy.