How to subtract multiple cells from one cell in Excel?

Sometimes you may want to add or subtract the same value from multiple cells. The best way to do this is to have the value you want to add or subtract in a cell and then add/subtract multiple cells from this one cell.

Confused?

Let me explain with an example.

Suppose you have a dataset as shown below and you want to subtract all the cells in column B with the value in A2.

Data set to subtract multiple cells from one cell

This can easily be done in Excel using a simple formula or using Paste special.

Let me show you how this works.

Subtract Multiple Cells from One Cell Using Formula

Below is the dataset where I want to subtract all the cells in Column B from the value in A2.

Below are the steps to do this:

  1. Double-click on cell C2 (or select it and press F2 to get into the edit mode)
  2. Enter =A2-B2Enter the formula in cell C2
  3. With the cursor placed in next to A2 (before, after or in between the reference A2), press the F4 key. You would notice that A2 changes to $A$2Make the A2 cell reference absolute
  4. Hit the Enter key. This will give you the result
  5. Copy the formula for all the cells (C2:C10) to get the result.Result when multiple cells are subtracted from one cell

How does this Exel formula work?

When you press F4 and the reference A2 changes to $A$2, it becomes an absolute reference.

An absolute reference means that if you copy and paste the cell anywhere, any reference that is not absolute would adjust accordingly, but if the cell reference is locked (i.e., there is a dollar sign before the column alphabet and the row number), it will not change.

This allows you to subtract multiple cells from the same cell as the reference to that cell will not change and you copy the formula to other cells.

Note that in this example, I have used F4 to change the reference to an absolute reference. You can also manually enter the dollar sign if you want.

One of the benefits of using a formula to get this done is that you can change the value in cell A2, and the entire result would automatically update to reflect the change.

If you want to learn more about absolute and relative reference, here is an article on what the $ mean in an Excel formula.

Subtract Multiple Cells from One Cell Using VBA

If you’re alright using a little bit of VBA, you can also use this awesome technique to quickly subtract multiple cells without using a formula.

Suppose you have the same dataset as shown below and you want to subtract all the cells in Column B from the value in A2.

Below are the steps to do this:

  1. Select the range B2:B10
  2. Right-click on the sheet/tab name
  3. Click on view code. This will open the VB Editor in Excel.Right click on sheet name and then click on View code
  4. If you see an immediate window, go to the next step. And if not, click on View option in the menu and then click on Immediate WindowClick on Immediate window
  5. In the immediate window, copy the following code
    For Each cell In Selection: cell.Value = Range("A2") - cell.Value: Next cell
  6. Place the cursor at the end of the linePlace the cursor at the end
  7. Hit Enter

As soon as you hit the enter key, you will see that the values in column B changes and each has been subtracted from cell A2.

In case you don’t want to lose the original data, make a backup copy.

How does this work?

In this method, I am using a simple VBA macro code and running it through the immediate window.

This VBA code goes through each cell and subtracts it from the value in cell A2 (which has been hard-coded in the macro code)

The above two methods show you how to subtract multiple cells from one cell.

Now let me also show you a cool method that you can use in case you want to do it the other way round – i.e., subtract one cell from multiple cells.

Subtract One Cell from Multiple Cells (Using Paste Special)

Suppose you have a dataset as shown below and you want to subtract the value in cell A2 from all the cells in B2:B10.

Again, you can use the formula method as well as the VBA method, but let me show you another one using Paste Special (this one is my favorite).

Below are the steps to subtract one cell from multiple cells:

  • Select cell A2
  • Right-click on it and then copy it (or use the keyboard shortcut Control +C)
  • Now select B2:B10
  • Right-click and then click on Paste Special. This will open the Paste Special dialog box.Click on Paste Special option
  • In the Paste Special dialog box, select Value in the Paste options and Multiply in the Operations option.Select the Values and Subtract option in the Paste Special dialog box
  • Click on OK.

That’s it!

You would see that the values in B2:B10 has been changed. These are the values you get when each one of these subtracts the value in cell A1.

Note that the result you get here is a static value. The final result is not dependent on cell A2.

In case you want to keep the original data, make a copy of it and then do this on the copied data.

The techniques shown in this Excel tutorial can also be used in case you want to do other arithmetic operations such as addition, multiplication, or division.

Hope you found this Excel tutorial useful.

You may also like the following Excel tutorials: