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

The following sample dataset contains Id No, Name, Salary, House Rent, Grocery and Bill columns. We will use 6 methods to subtract multiple cells from this table.

How to Subtract Multiple Cells in Excel


Method 1 – Using Excel Formula to Subtract Multiple Cells

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

➤ Enter the following formula in cell I5 and press ENTER.

=D5-E5-F5-G5

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

How to Subtract Multiple Cells in Excel

It will output the result in cell I5.

➤ Drag down the formula with the Fill Handle tool.

It will show all the savings in the Savings column.

How to Subtract Multiple Cells in Excel

Read More: How to Subtract Columns in Excel


Method 2 – Applying Paste Special Tool to Subtract Single Cell from Multiple Cells

We’ll use the paste special feature to 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

➤ Right-click on cell I5.

A context menu will appear.

➤ Click on Copy.

➤ Select the cells in the Salary column from D5 to D12 and right-click.

A context menu will appear.

➤ Click on Paste Special.

A Paste Special window will appear.

➤ Select Subtract and click OK.

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


Method 3 – Inserting Excel SUM Function to Subtract Multiple Cells

We will use the SUM function in the Savings column to subtract multiple cells from the Salary column.

➤ Enter 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.

It will output the subtracted value in cell I5.

➤ Drag down the formula with the Fill Handle tool.

It will output all the subtracted values in the Savings column.

How to Subtract Multiple Cells in Excel

Read More: How to Subtract from a Total in Excel


Method 4 – Subtracting Text of One Cell from Another Cell

We have added a column Last Name. 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 this case.

How to Subtract Multiple Cells in Excel

➤ Enter 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. 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.

We can see the first name Mark in cell I5.

➤ Drag down the formula with the Fill Handle tool.

It will output all the first names in the First Name column.

How to Subtract Multiple Cells in Excel


Method 5 – Subtracting Multiple Cells from One Cell in Excel

We will subtract the cells in the Salary column from the Expected Salary value of $4000.

➤ Enter the following formula in cell K5 and press ENTER.

=$I$5-D5

$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.

It will output the difference between the Expected Salary from Salary in the Underpay column.

➤ Drag down the formula with the Fill Handle tool.

It will output all the values in the Underpay column.

How to Subtract Multiple Cells in Excel

Read More: How to Create a Subtraction Formula in Excel


Method 6 – Using VBA Macro to Subtract Multiple Cells

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

➤ Select the salary column cells from D5 to D12.

➤ Go to the Developer tab > select Visual Basic.

➤ In the VBA application window, select Insert > Module.

A VBA editor window will open.

➤ Enter 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

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

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

➤ Run the code.

A Macro window will appear.

➤ Select VBA Project Module 4 and click on Run.

➤ Close the VBA editor window and go to our worksheet.

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


Download Workbook


Related Articles


<< Go Back to Subtract in Excel | Calculate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Afia Kona
Afia Kona

Afia Aziz Kona, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Afia exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Apart from creating Excel tutorials, she is also... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo