Kawser
Adding and subtracting are the two most common mathematical operations that we do in our everyday lives. In this post, you will learn adding and subtracting in Excel in one formula with four easy and handy ways.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
Subtract Two Numbers in Excel
In Excel, you will not find any function called SUBTRACT that will perform the subtraction operation. You have to use the mathematical operator minus sign (-) to subtract two numbers.
For example, you want to subtract 50 from 500. Write a formula like the following:
500 – 50 = 450
So, a general formula to subtract one number from another is:
Number1 – Number2
Notes: You get the SUM function to add numbers or a range of cells.
Add and Subtract in one Excel Formula
Addition and subtraction can be done in one mathematical expression. The following example may help in this regard.
100 – 50 + 30 – 20 + 10
We can evaluate this expression in two ways:
Way 1: Performing Calculations from Left to Right
100 – 50 + 30 – 20 + 10
= 10 + 30 +50 – 20
= 40 + 30
= 70
Way 2: Using Parentheses
100 – 20 + 30 – 50 + 10
= (100 + 10 + 30) – (20 + 50)
= 140 – 70
= 70
4 Handy Ways of Adding and Subtracting in Excel in One Formula
You can create complex formulas and perform certain operations directly in Excel. The addition is much easier in Excel. But subtraction becomes tough as it doesn’t have any direct formula. In this article, we will show you some easy ways of adding and subtracting in Excel in one formula. For the purpose of demonstration, we have used the following sample dataset.
1. Subtracting and Adding Cell References in One Formula
Suppose you want to subtract or add two cells in Excel. You need to follow the steps below to complete the operation.
Steps:
- Firstly, select cell F5.
- Input an equal sign (=).
- Now, select cell reference C5.
- Input a minus sign (–).
- Then, insert the first bracket.
- After that, select cell D5 and input a plus sign (+).
- Finally, select cell E5 and close the first bracket.
=C5-(D5+E5)
- Now, press the Enter key on your keyboard to get the result.
- Select the cell and apply the AutoFill tool to the entire column in order to get the data for the whole column.
- Finally, You will get the following data after completing addition and subtraction.
Read More: How to Add or Subtract Based on Cell Value in Excel (3 Ways)
2. Subtracting and Adding Multiple Cells from One Cell
Subtracting multiple cells from one cell can be done in two different ways. The first one is by using a minus (–) sign and parentheses. Another one is by using the SUM function.
2.1 Using Minus (-) Sign
For example, in one cell (C11) we have entered the total salary expense of the company and in other cells (D4:D9), we put the basic salary in dollars. The total salary includes some allowances also. So, in order to find out the total allowances of the employees, we have followed the below procedure.
Steps:
- Select a cell and write a formula like the following one.
=C11-(D5+D6+D7+D8+D9)
- Finally, you will get your desired output.
2.2 Using SUM Function
In mathematics, subtracting a number from another number is the same as summing a positive and a negative number. For example, 50 – 20 and 50 + (-20) are actually the same things.
This method is quite similar to the previous one. In our example, we have completed the addition part with the help of the SUM function.
Steps:
- At first, select a cell and write down the following formula.
=C11-SUM(D5:D9)
- After that, press Enter, and the final value will appear.
Read More: How to Add and Subtract in One Cell in Excel (6 Ways)
3. Addition and Subtraction in Two Columns
Suppose you want to add the cells of the ranges C5:C9 and D5:D9 and then subtract the sum of the second range from the first one. In order to get your desired outcome, follow the below steps.
Steps:
- Click on any cell outside the data.
- Then, write down the following formula.
=SUM(C5:C9)-SUM(D5:D9)
- After that, press the Enter button to get the value.
Read More: How to Add and Subtract Columns in Excel (5 Easy Methods)
4. Subtraction and Addition for Percentage in Excel
It is easy to subtract two percentage values in Excel. Moreover, the process is similar to subtracting and adding cell references in one formula. Follow the below steps to complete the operation in a handy manner.
Steps:
- Firstly, select a cell and enter a formula like the following one.
=C5-(D5+E5)
- Then, press the Enter button on the keyboard. You will get your required answer.
- Afterward, select the cell and apply the AutoFill tool to the whole column.
- Finally, you will find your required values in the following image.
Read More: How to Subtract Sum of Several Cells from Fixed Number in Excel
Conclusion
These are all the steps you can follow for adding and subtracting in Excel in one formula. Hopefully, you can now easily make the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
For more information like this, visit Exceldemy.com
I am trying to add and subtract across an Excel spreadsheet, ex. add cells B1, D1, F1 and then subtract the sum of cells C1, E1, G1. I thought I had it figured out, but . . . . this is what I came up with, but no go. I may be making this too hard! I can add columns, but can I not add across?
=SUM(B71,D71,F71,H71,J71,L71,N71,P71,R71)-SUM(C71,E71,G71,I71,K71,M71,O71,Q71,S71)
Hello Queda,
If you want to add and subtract across an Excel spreadsheet, it means if you want to give a cell reference in your formula from another sheet, the formula will look like the following one.
Without giving the proper sheet and cell reference, the formula won’t work or show an error. If this reply doesn’t give a proper answer to your problem, then send the Excel file to [email protected]. Moreover, you can find the Excel file here.