How to Subtract Multiple Cells in Excel (6 Effective Methods)

There are several ways to subtract multiple cells in Excel. Among them, we will describe to you the 6 most effective methods.

Download Workbook

6 Methods to Subtract Multiple Cells in Excel

The following Employee Cost List Table shows Id No, Name, Salary, House Rent, Grocery, and Bill columns. We will use 6 methods to subtract multiple cells from this table. Here, we used Excel 365. You can use any available Excel version.

How to Subtract Multiple Cells in Excel


Method-1: Using Arithmetic Formula to Subtract Multiple Cells

In this method, we will use the arithmetic formula to find out the Savings after subtracting the House Rent, Grocery, and Bills from the Salary column.

➤ First of all, we will write the following formula in cell I5, and press ENTER.

=D5-E5-F5-G5

Here,

D5-E5-F5-G5 Subtracts cells E5, F5, G5 from cell D5.

How to Subtract Multiple Cells in Excel

We can see the result in cell I5.

➤ We will drag down the formula with the Fill Handle tool.

Finally, we can see all the savings in the Savings column.

How to Subtract Multiple Cells in Excel

Read More: How to Subtract Columns in Excel (6 Easy Methods)


Method-2: Paste Special Feature to Subtract Single Cell from Multiple Cells

In this method, using a paste special feature we will subtract the value in cell I5, which is  $300 in the Health Insurance column from the Salary column cells.

How to Subtract Multiple Cells in Excel

➤ First of all, we will right-click on cell I5.

A context menu will appear.

➤ We will click on Copy.

➤ After that, we will select the cells in the Salary column from D5 to D12, and we will right-click.

A context menu will appear.

➤ We will click on Paste Special.

A Paste Special window will appear.

➤ We will select Subtract, and click OK.

Finally, we can see that the salary column contains cell values that subtract $300 from them.

How to Subtract Multiple Cells in Excel

Read More: Subtraction for Whole Column in Excel (with 5 Examples)


Method-3: Using SUM Function

Here, we will use the SUM function in the Savings column to subtract multiple cells from the Salary column.

➤ First of all, we will type the following formula in cell I5, and press ENTER.

=D5-SUM(E5:G5)

SUM(E5:G5) Adds the cells from E5 to G5.

D5-SUM(E5:G5) Subtracts the add-up value of cells E5 to G5 from cell D5.

After that, we can see the subtracted value in cell I5.

➤ We will drag down the formula with the Fill Handle tool.

Finally, we can see all the subtracted values in the column Savings.

How to Subtract Multiple Cells in Excel

Read More: How to Subtract from a Total in Excel (3 Quick Methods)


Method-4: Subtract Text of One Cell from Another Cell

Here, we add a column Last Name, and we want to subtract the last name from the Name column and store the result in the First Name column. We will use the TRIM function along with the SUBSTITUTE function in that case.

How to Subtract Multiple Cells in Excel

➤ First of all, we will type the following formula in cell J5, and press ENTER.

=TRIM(SUBSTITUTE(C5,D5," "))

SUBSTITUTE(C5,D5,” “) Replaces new text for old text in a text string. Here, C5 is the reference text for where we’ve stored the text to substitute characters. D5 is the text we want to replace, and ” “ is the text we want to replace with.

TRIM(SUBSTITUTE(C5,D5,” “)) Removes all spaces from the text in a text string and gives the output.

Then, we can see the first name Mark in cell I5.

➤ We will drag down the formula with the Fill Handle tool.

Finally, we can see all the first names in the First Name column.

How to Subtract Multiple Cells in Excel

Read More: How to Subtract Two Columns in Excel (5 Easy Methods)


Method-5: Subtract Multiple Cells from One Cell

In this method, we will subtract the cells in the Salary column from the Expected Salary value of $4000.

➤ First of all, we will type the following formula in cell K5, and press ENTER.

=$I$5-D5

Here,

$I$5-D5 Subtracts D5 from cell I5. We put a dollar ($) sign before I5 by pressing F4, because we wanted to lock this cell, and we do not want the values of this cell to change.

Afterward, we can see the difference between the Expected Salary from Salary in the Underpay column.

➤ We will drag down the formula with the Fill Handle tool.

Finally, we can see all the values in the Underpay column.

How to Subtract Multiple Cells in Excel


Method-6: Using VBA Code

Here, we will use VBA code to subtract Salary column cells from Expected Salary, which is $4000.

➤ First of all, we will select the salary column cells from D5 to D12.

➤ Then, we will go to the Developer tab > select Visual Basic.

We will see that a VBA application window appears.

➤ We will select Insert > Module.

We will see a VBA editor window appear.

➤ We will type the following code in the VBA editor window.

Option Explicit

Sub Subtraction()

Dim cRef As Range

For Each cRef In Selection

cRef.Value = Range("I5") - cRef.Value

Next cRef

End Sub

Here, we’ve created a sub-procedure Subtraction which goes through each selected cell reference because of using the FOR EACH loop.

Then, it subtracts the value of the selected cell references from cell I5. Here, we stored the result in the selected cells as well

➤ After that, we will click on the red marked box green button to run the code.

A Macro window will appear.

➤ Here, we selected VBA Project Module 4, we will click on Run.

➤ After that, we will close the VBA editor window, and go to our worksheet.

Finally, we can see that all the cells in the Salary column contain a value that is subtracted from Expected Salary value of $4000.

How to Subtract Multiple Cells in Excel

Read More: Excel VBA: Subtract One Range from Another (3 Handy Cases)


Conclusion

Here, we tried to show you 6 methods to subtract multiple cells in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, feel free to know us in the comment section.


Related Articles

Afia
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo