How to Subtract Multiple Cells in Google Sheets
How to Subtract in Google Sheets? You can subtract multiple cells in Google Sheets using an array formula. An array formula is similar to a normal formula in that it can include additions, subtraction, multiplications and divisions. However, an array formula also includes comparison operators.[1]These comparison operators use the value in each cell to determine the result of the formula. If cells in an array formula are empty, the comparison operator treats them as zero. This makes it possible to multiply two arrays that have different values in one row and add them together.
Subtract in Google Sheets
Subtracting is one of the most popular math operations in spreadsheets. It is easy to perform and allows users to make hundreds of calculations in a short period.[2]
Google Sheets has several ways to subtract numbers and cells. You can use a formula, built-in functions, or even run them on arrays of data.
Subtraction Operator
Subtracting is one of the most basic arithmetic functions that you can use in spreadsheets. Despite its simplicity, it can be frustrating to find the right formula that works exactly how you want it to.[3]
When it comes to subtracting, Google Sheets offers a few methods that make it easier than ever to do it. For instance, it has a function called MINUS that can subtract two values at a time.
However, MINUS does have some limitations that you should know about. First, it only accepts two cell references. So if you have more than two references, it will subtract them from each other and return the results in one cell.[4]
Another limitation of MINUS is that it can only subtract two cells at a time. So if you have many references and need to subtracted from them, you may be better off using formulas to accomplish the task.
Alternatively, you can create an ArrayFormula in your spreadsheet. An ArrayFormula allows you to input arrays (ranges of data) into a single formula. This is especially helpful when you’re working with a large amount of data and need to have one formula handle all of it.
The next step is to insert the number from which you want to subtract into the reference cell that you are going to use for this calculation. You can do this by clicking on the cell that contains the minuend or the value from which you want to subtract.[5]
Subtracting Two Manual Entries
Subtraction is a great way to make your spreadsheet look neater. Luckily for you, Google Sheets comes with a variety of functions to help you get the job done. The most interesting one, in my opinion, is the ArrayFormula function. It’s like the sexiest office assistant you’ve ever met and makes the impossible task of sorting through hundreds of rows and thousands of columns a snap. To prove it, we’ve created a simple matrix to showcase this clever little formula. [6]
It even works with non-numeric data. The results are a sight to behold for your colleagues and an eye catching addition to your own work space. If you’re looking for more info on this handy function, be sure to check out our ArrayFormula guide. The ArrayFormula function is the best way to do the math on matrices and arrays of all sizes.
Subtracting Using Two Cells
Spreadsheet applications like Google Sheets are powerful tools that can perform a lot of calculations. These calculations can involve basic data entry or mathematical functions, like subtraction.[7]
One of the most common ways to subtract in Google Sheets is using a formula, but you can also use cell references. This way, the subtraction formula automatically updates if you change a value in a cell that is part of the formula.
If you want to create a subtraction formula that works in multiple cells, you can use Array formulas. These work much faster than sequential subtraction and are easier to modify.
Another option is to use relative cell referencing. This method works in a similar fashion to sequential subtraction, but the result will be based on the reference of the first number in the range, not the first number itself.[8]
You can use this technique if you have a large data set and you’d like to save time calculating the difference between a series of numbers. You can even combine this method with the SUM function to create a more complex formula.
To use this method, simply select the cell you want to display the result, enter “=” (equal) and the cell reference of the first number then “-” (minus) and the number you want to subtract.[9] Then, copy this formula down the column to the rest of the cells in the range, and you’ll see the subtraction results displayed.
Subtracting Using Many Cells
Experienced Google Sheets users with an Excel background can easily perform complex mathematical operations with the free program. Its formulas and functions are similar to those found in Excel, but it also allows you to use cell references instead of hardcoding your calculations into a formula.[10]
Using cell references lets you quickly enter subtraction formulas and then see the results automatically when you change one of the cells or values that are part of your formula. This saves time, especially if you have to make many subtraction calculations.
You can also subtract a range of cells without having to use a formula by using the ARRAYFORMULA function together with the MINUS function. This function allows you to select a range of cells and then type in the minuend and subtrahend values, followed by a comma.
The MINUS function in Google Sheets is similar to the minus sign on a calculator. [11]It follows the order of operations, can handle numbers and decimals, and works with matrices.
However, the MINUS function can only handle two values, so if you need to subtract more than that, you’ll have to use another function. For example, if you need to add a number and subtract it from another, you’ll have to use the SUM function.
In this example, we have the number of initial volunteers for an experiment and the number of resigned volunteers. We want to calculate the total number of current volunteers by subtracting the resigned volunteers from the initial volunteers.[12]
To do this, we’ll write an array formula that tells Google Sheets to treat these matrices as arrays, rather than single values. We’ll then copy this formula rightwards and downwards to as many cells as the matrices have rows and columns. This makes it easier to work with the formula because it eliminates the need for separate formulas for each row.
Subtracting Using Absolute Reference
Subtracting values can be a tricky process, especially when you’re working with large numbers. However, with a little bit of knowledge you can avoid errors and make calculations quicker.[13] Using absolute reference in Google Sheets is one way to do this.
Unlike relative references, absolute references do not change when you copy or fill a cell. Hence, this type of reference is ideal for formulas that you’ll be copying and re-copying across multiple cells.
For example, if you want to subtract two dates from each other in a Google Sheets spreadsheet, you can use the MINUS function with an absolute reference. This function will calculate the difference between each date and deduct it from the previous date.[14]
You can also use the MINUS function to subtract dates from each other using spreadsheet cell references. This method can be more accurate than manually typing in each date, as it’s much easier to copy and paste the reference.
Another way to subtract dates in Google Sheets is by using the SUM function.[15] This function works well for cell range totals, and it’s easy to incorporate the function within a formula.
A similar function is the COUNTIFS function. This function counts the number of cells in a specified range that meet specific criteria.
There are a few ways to accomplish this in Google Sheets, including adding an asterisk symbol to the formula and using the ARRAYFORMULA function. This can be a great way to quickly multiply one array by another, or even to multiply multiple arrays by each other. [16]
