Subtract Multiple Cells from One Cell in ExcelHere are several methods to subtract multiple cells from a cell in Excel

In Excel, you can perform various mathematical calculations via formulae and other functions (e.g. paste special).

For example, if you want to add the values in two cells, the cells being A2 and B2, you can just enter the formula =A2+B2 or =SUM(A2,B2) in another cell.

Of course, you can perform other mathematical calculations as well such as subtraction, multiplication, and division.

Speaking of, what if you want to subtract multiple values from a single value?

For example, you’re tallying scores for a game show where each contestant has the same starting scores.

Every round, the contestants lose points depending on certain actions and conditions.

At the end of the game, whoever has the most points left wins the game.

In Excel, there are several ways you can perform the calculation easily and efficiently.

One way is to put the number (initial score) in one cell (e.g. cell A2 or A1).

Then, in the same sheet, put the other numbers (the scores that each player loses) in other cells, preferably in the adjacent column to the single number.

From there, you can simply use a formula to subtract multiple cells from one cell.

There are also other simple and easy-to-perform ways to do so in Excel.

In this article, we will be exploring several ways to subtract multiple cells from one cell in Excel. Let’s get started!

Use a Formula to Subtract Multiple Cells From a Cell

Let’s give a visual presentation of our example above:

With the dataset above, we want to know the final score of each contestant so that we can find who wins.

In column A, we see the initial score of each contestant, which is 100.

In column C, we can see the points that each contestant lost.

The easiest way to do this is to use a formula.

• Click on a cell of one of the empty columns (in our example, we will be selecting cell D2). Manually enter the formula =$A$2-C2. Make sure to include the dollar signs. What this does is that it locks the value after the dollar sign (which are A and 2 in this case; more on this later).

• You can also enter the dollar signs by typing the formula =A2-C2. But before typing the minus sign (-), press F4 once. This should automatically insert the dollar signs before A and 2.
• After you enter the formula, press the enter key. The formula should return a value that is the difference between cells A2 and C2.

• Copy the formula and paste it on the rest of the cells in the column. You can do this by using the copy + paste function or by using the fill handle after selecting the cell that contains the formula. Simply click and drag the fill handle to copy the formula to the rest of the cells (which are cells D3 to D11 in our illustration).
• You should now have a column that contains the differences between cells A2 and cells C2 to C11.

Do note that this formula is dynamic.

This means that if there are changes to the concerned cells, the result will automatically update.

Why the Dollar Signs ($)?

By including the dollar signs, we make cell A2 an absolute reference rather than a relative reference.

When a cell is an absolute reference, it never changes wherever you paste it in the sheet.

This is different from the default which is a relative reference.

If a cell is a relative reference, it will change depending on how many rows and/or columns it moved from the cell that contains the original formula.

For example, let’s say that the formula in cell C2 is =A2.

If we copy this formula and paste it on cell C3, it will change to =A3 because it moved by one row downwards.

If we paste on cell D3, the formula will change to =B3 because it moved by one column.

But if the formula is =$A$2, it will remain as is wherever we paste it on the sheet.

Since we wanted to subtract multiple cells from a single cell, we need to make the minuend (which is the number we will subtract from) an absolute reference.

Thus, the need for dollar signs.

Use Paste Special to Subtract Multiple Cells From a Cell

I don’t know if you already noticed but the previous method requires us to enter the formula in another cell.

But what if you want the result to replace the values in the cells that contain the subtrahends?

For example, in our illustration, we want to show the final scores in column C (which currently contains the points that each player lost).

How to Use Paste Special to Subtract Multiple Cells From a Cell

To do so, we’ll have to use the Paste Special function.

• Select the cell that contains the number which we’ll subtract from (the minuend). Press the keyboard shortcut Ctrl+C to copy the cell. Alternatively, you can right-click the cell and then select Copy. (In our illustration, this cell will be cell A2)
• Select the cells that contain the subtrahends. In our illustration, this will be cells C2 to C11.
• Right-click anywhere on your selection. Then from the options, select Paste Special. This will open the Paste Special dialog box.

• This will open the Paste Special dialog box. In the Operations section, tick the button before Subtract. Click the OK button after doing so.

• You should notice that the destination cells have new values. However, they’re likely to be negative. This is because we subtracted the larger number from the smaller number. Don’t worry though. This can easily be fixed by still using the Paste Special function.

• In an empty cell, type -1. Select the cell and copy it.
• Select the cells that contain negative numbers. Right-click anywhere on the selection. Then select Paste Special to open its dialog box.
• In the Operations section, tick the button before Multiply. Then click the OK button.

• The number should be positive now. You can now delete the cell that contains the -1. (I also changed the header of Column C to reflect the change)

Do note that this method is static. The result will not automatically update whenever there are changes in the cells.

Use VBA Code to Subtract Multiple Cells From a Cell

If you’re comfortable with using VBScript, you can use a VBA code to subtract multiple cells from a cell.

Here’s the VBA code that we will be using (copy it so that you can paste it later).

Sub SubtractfromCell()

For Each cell In Selection

cell.Value = Range("A2") - cell.Value

Next cell

End Sub

The A2 in the third line is a reference to the cell that contains the minuend.

You can change it depending on where the value is located.

• Select the cells that contain the subtrahends.
• Open the Developer tab. Then, click on the Visual Basic button. This will open the VBA window.

• In the VBA window, open the Insert tab. Then select Module.

• Did you already copy the code I provided above? If not, copy it. Then paste it on the Module window.

• Press the play button to run the code.

• Close the VBA window and go back to your sheet. You should notice that values in the selected cells have changed. Do note that this is a permanent change (you cannot use Ctrl + Z to undo it).

Conclusion

And there you have it, three ways to subtract multiple cells from one cell.

The third method requires a little bit of VBScript though, but once you get the hang of it, it should be easy and accessible for you.

And oh, before we go, let’s find out who won the game.

It’s contestant J!