How to subtract in Google Sheets (Subtract numbers, cells, columns, & rows)

Google Sheets makes it very easy to do math, such as subtracting numbers, cells, and entire columns / rows. Spreadsheets make calculating / subtracting easy in so many ways, from being able to do multiple subtraction formulas at once, to being able to use cell references so that your subtraction formula criteria is easily controlled by entering values into cells.

In other words, you can set up your formula once and then it automatically performs the subtraction calculation as the data input changes.

In this lesson I will show you how to do all of that and more.

To subtract in Google Sheets, follow these steps:

1. Select the cell where you want to create a subtraction formula, then type an equals sign (=)
2. Type the number or the cell reference that contains the number that you want to subtract from
3. Type a minus sign (-)
4. Type the number or the cell reference that contains the number that you want to subtract. The final formula will look like this: =7-5 (Or like this for cell references: =A1-A2)
5. Press “Enter” on the keyboard

After following the steps above, Google Sheets will display the result of the subtraction calculation in the cell where you entered the formula.

Let’s say that you want to subtract the number “5” from the value that is in cell A1 (7). To do this simply enter the formula =A1-5 in a cell, then press enter on the keyboard and the formula will display the result. In this case the result would be “2”, since 7-5=2 (shown in the image below).

=A1-5

Click here to learn how to do all the different types of math in a Google spreadsheet.

See the detailed examples below that teach a variety of ways to subtract in a Google spreadsheet.

Subtracting numbers

First let’s go over a basic example of subtracting by using numbers that are entered directly into the formula.

Sometimes you may want to do this to simply solve a quick subtraction problem without using cell references. I use numbers entered directly into my subtraction formulas when there is a constant that I want to subtract (when I know that the number will stay the same).

To subtract numbers in Google Sheets, follow these steps:

1. Select the cell where you want to create a subtraction formula, then type an equals sign (=)
2. Type the number that you want to subtract from
3. Type a minus sign (-)
4. Type the number that you want to subtract. The final formula will look like this: =100-50
5. Press “Enter” on the keyboard

In this case the result would be “50”, since 100-50=50 (shown in the image below).

=100-50

Subtract symbol / operator

The easiest and most common way to subtract in Google Sheets, is by using the subtract operator / symbol, which is a minus sign (-).

There is also a MINUS function that allows you to subtract, but this function is not commonly used, and the operator method is much easier / more versatile, so we will stick to using operators in this lesson.

But here is an example of the MINUS function: =MINUS(100,A1)

Subtract by using cell references

What makes spreadsheets really useful for doing subtraction, is the ability to use cell references in your formulas. This allows you to refer to a cell that contains a number, rather than entering numbers directly into the formula.

When you use a cell reference for your subtraction formula, the value inside the cell can easily be changed and the formula will automatically perform the calculation with the new value, making it very fast and easy to do subtraction, especially when you are calculating a large amount of data that changes frequently.

To subtract cells in Google Sheets, follow these steps:

1. Select the cell where you want to create a subtraction formula, then type an equals sign (=)
2. Type the cell reference that contains the number that you want to subtract from
3. Type a minus sign (-)
4. Type the cell reference that contains the number that you want to subtract. The final formula will look like this: =B1-B2
5. Press “Enter” on the keyboard

Let’s say that you want to subtract the value in cell B2 (100) from the value that is in cell A2 (50). To do this simply enter the formula =A2-B2 in a cell, then press enter on the keyboard and the formula will display the result. In this case the result would be “50”, since 100-50=50 (shown in the image below).

=A2-B2

Using autofill (or copy / paste) to copy subtraction formulas

If you want to copy your subtraction formula down the column or through a row, this can be done easily by either using autofill or by copying and pasting your formulas.

Later I will show you how to use the ARRAYFORMULA function to apply a subtraction formula to a range of cells by using a single formula, but for now let’s go over how to quickly copy individual formulas.

Method 1: To copy and paste your subtraction formula into other cells, copy the cell with the formula in it by pressing Ctrl + C on the keyboard, select the cell that you want to copy the formula to, then paste by pressing Ctrl + V on the keyboard.

Method 2: To copy your subtraction formula by using autofill, select the cell that has the formula in it, hover your cursor over the bottom-right corner of the cell until the plus sign appears (the fill handle), click your mouse and hold the click, drag your cursor across the cells that you want to copy the formula into, then release your click when you have selected all of the cells that you want to copy the formula into.

With both of these methods, Google Sheets will automatically adjust the cell references when the formulas are copied into adjacent cells, so that each new formula is correctly referring to the appropriate column / row.

For example if you enter the formula =A1-2 (in cell B1), and then copy the formula from cell B1 into cell B2, then Google Sheets will automatically adjust the reference so that the new formula will be =A2-2.

So when you copy a formula down the column, the row references will adjust, and when you copy a formula throughout a row, the column references will adjust.

Locking cell references for subtraction formulas

If you have a situation where you want to lock the references in your subtraction formula so that they do not change when you copy them into other cells, use a dollar sign ($) before the reference that you want to lock.

Put a dollar sign before the column letter if you want the column reference to be locked, and put a dollar sign before the row number if you want the row reference to be locked.

• =$A1-2 (Locked column reference)
• =A$1-2 (Locked row reference)
• =$A$1-2 (Locked column and locked row reference)

Below are several detailed examples of using autofill to copy subtraction formulas.

Subtracting columns of data with autofill

In this example we will go over how to copy subtraction formulas vertically down a column.

To subtract columns in Google Sheets, follow these steps:

1. Enter your subtraction formula in the first cell at the top of the column
2. Select the cell with the formula in it, and hover your cursor over the bottom-right corner of the cell until a plus sign appears
3. Click your mouse and hold the click, then drag your cursor down the column
4. Release your click when you have selected all of the cells that you want to copy a formula into

As you can see in the image below, the following formula is entered into cell C2:

=A2-B2

We are going to copy the formula down the column into the cells below by using autofill / the fill handle.

First we select cell C2, then hover the cursor over the bottom-right corner of the cell until the plus sign / cross appears, as shown in the image below.

Then click your mouse, hold the click, and drag your cursor downwards until you have reached cell C5, then release your click.

As you can see in the image below, the formula from cell C2 has now been copied into the cells below it, into the range C2:C5. You can also see that the formula in cell C5 has had its row references adjusted, and the formula in cell C5 is A5-B5. This is exactly what we wanted Google Sheets to do, since our references are pointing at the correct rows.

Subtracting rows of data with autofill

You can also use the fill handle to copy formulas to the left and right. Now let’s go over how to copy subtraction formulas horizontally through a row.

To subtract rows in Google Sheets, follow these steps:

1. Enter your subtraction formula in the first cell on the left of the row
2. Select the cell with the formula in it, and hover your cursor over the bottom-right corner of the cell until a plus sign / cross appears
3. Click your mouse and hold the click, then drag your cursor to the right
4. Release your click when you have selected all of the cells that you want to copy a formula into

As you can see in the image below, the following formula is entered into cell B3:

=B1-B2

We are going to copy the formula into the cells to the right in the same row, by using autofill / the fill handle.

First we select cell B3, then hover the cursor over the bottom-right corner of the cell until the plus sign / cross appears, as shown in the image below.

Then click your mouse, hold the click, and drag your cursor to the right until you have reached cell E3, then release your click.

As you can see in the image below, the formula from cell B3 has now been copied into the cells to the right of it, into the range B3:E3. You can also see that the formula in cell E3 has had its column references adjusted, and the formula in cell E3 is E1-E2. This is exactly what we wanted Google Sheets to do, since our references are pointing at the correct columns.

Using ARRAYFORMULA to extend formulas

There is a very useful formula called ARRAYFORMULA that allows you to perform calculations on an entire range of data, such as subtracting entire columns or rows, in a single formula. Here’s how to subtract entire columns and rows using ARRAYFORMULA.

Subtracting entire columns

In this example we will use ARRAYFORMULA to subtract an entire column by using a single formula.

To subtract entire columns with a single formula, follow these steps:

1. Enter your subtraction formula in a cell at the top of the column, like this: =A2-B2
2. Press Ctrl + Shift + Enter on the keyboard to turn the subtraction formula into an ARRAYFORMULA formula
3. Change any cell references to a range / column reference, like this: =ARRAYFORMULA(A2:A-B2:B)
4. Press enter on the keyboard

This formula tells Google Sheets to subtract each corresponding cell in column B from the respective cell in column A.

As you can see in the image below, the formula in cell C2 is calculating subtraction for the entire range C2:C5 by using a single formula.

Subtracting entire rows

In this example we will use ARRAYFORMULA to subtract an entire row by using a single formula. This is very similar to subtracting entire columns, but in this case we will specify rows for our references rather than columns. Notice how in the previous example the reference looked like this A1:A, but in this example the reference looks like this A1:1.

To subtract entire rows with a single formula, follow these steps:

1. Enter your subtraction formula in a cell on the right of the row, like this: =B1-B2
2. Press Ctrl + Shift + Enter on the keyboard to turn the subtraction formula into an ARRAYFORMULA formula
3. Change any cell references to a range / row reference, like this: =ARRAYFORMULA(B1:1-B2:2)
4. Press enter on the keyboard

This formula tells Google Sheets to subtract each corresponding cell in row 2 from the respective cell in row 1.

As you can see in the image below, the formula in cell B3 is calculating subtraction for the entire range B3:E3 by using a single formula.

Now you know all of the different ways to subtract in Google Sheets!

Click here to get your Google Sheets cheat sheet

Or click here to take the dashboards course