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.
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.
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:
- Double-click on cell C2 (or select it and press F2 to get into the edit mode)
- Enter =A2-B2
- 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$2
- Hit the Enter key. This will give you the result
- Copy the formula for all the cells (C2:C10) to get the result.
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.
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:
- Select the range B2:B10
- Right-click on the sheet/tab name
- Click on view code. This will open the VB Editor in Excel.
- 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 Window
- In the immediate window, copy the following code
For Each cell In Selection: cell.Value = Range("A2") - cell.Value: Next cell
- Place the cursor at the end of the line
- 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.
- In the Paste Special dialog box, select Value in the Paste options and Multiply in the Operations option.
- Click on OK.
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.
Hope you found this Excel tutorial useful.
You may also like the following Excel tutorials: