67

How to do subtraction in Excel

Subtraction is one of the four fundamental arithmetic operations that everyone performs. Even a primary school student knows how to subtract or minus an integer from another. The same logic is also applied in Excel spreadsheets. Though it sounds easy for complex formulas, many times, Excel users go wrong with subtraction. With an Excel worksheet, you can subtract almost anything starting from numbers, decimals, percentages, time, days, months, hours, etc. Extends its capabilities and even enables its users to subtract matrices, text strings, and lists.

In this tutorial, we will cover all the various subtraction steps in numbers, text, lists, etc.,

Subtraction formula in Excel (minus formula)

Although there is no inbuilt subtract formula in Excel. To fetch a simple subtraction operation in your Excel worksheet, you can use the minus sign (-).

The syntax for primary Excel subtraction formula is as follows:

Steps to subtract one number from another in Excel

Let’s suppose you want to subtract 20 from 220, follow the below steps, and you will fetch the 200 as the output:

1. Select a cell and start your formula by typing the equality sign (=).
   
2. Enter the first number as 220. Next, put the minus sign followed by the second number, i.e., 20. Hence the equation would be = 220-20.
   
3. Press the Enter button from your keyboard. Excel will subtract both the numbers and will give you the output as 200.
   

Multiple Subtraction Operation

In Mathematics, we can perform more than one arithmetic operation within a single formula. Similarly, excel also enables the user to use two or more subtraction operations in the same formula.

Let’s suppose you have to subtract a few numbers from 100. Follow the below steps, and you will fetch the output.

1. Select a cell where you want to calculate the difference and start your formula by typing the equality sign (=).
   
2. Type all the numbers that you want to subtract from 100 and separate them by a minus sign.
   Formula =100-10-20-30
3. Hit the enter button and you will have your output.
4. You can also incorporate any other arithmetic operation along with subtraction. To direct which part of the formula should be operated first, we use parentheses. For instance:
   =(100-10)/(80-20)
5. Again, hit the enter button and Excel will calculate the output for you

How to subtract cells in Excel

So far we covered how to subtraction numbers in Excel. Since, with Excel we mostly play around with cell references and therefore it allows you to subtract one cell from another using the minus formula.

Let’s suppose you have two numbers in two different cells. Now, to subtract the number in cell C2 from the number in C1, we need to type the below given formula:

Though typing the cell reference manually in Excel is very rare as most of the time users quickly add the cell references to their respective formula by simply selecting the corresponding cells. Follow the below given steps:

1. Select a cell where you want to calculate the difference and start your formula by typing the equality sign (=).
   
2. Next, move your cursor to the cell that contains the minuend (a number from which another number is to be subtracted). Excel will automatically note the cell reference and will add the same in the formula. As you see, in our case it has added A1.
3. Continue the formula by typing a minus sign (-).
4. Move your cursor to the cell that contains a subtrahend (a number to be subtracted) and Excel will add the cell reference A2 to the formula.
5. Hit the Enter key to fetch the output.

Subtract multiple cells from one cell in Excel

With Excel, we manipulate the cell references to derive the desirable output. In the above section, we covered how we can subtract two different cell references. But what if we want to subtract multiple cells from the same cell.

You can easily subtract multiple cells from the same cell by using any of the below-given methods:

Method 1. By using arithmetic Minus

1. Select a cell where you want to calculate the difference and start your formula by typing the equality sign (=).
   
2. Next type the different cell references separated by a minus sign. It works in the similar way like we did while subtracting multiple numbers. Let’s Suppose you want to subtract A2, A3, A4 and A5 from A1. The formula will be as follows:
   Formula: = A1 – A2 – A3 – A4 – A5
3. Hit the enter button and you will have the desired output.

Method 2. Subtract using SUM function

Another method to subtract multiple cells from one cell in Excel is using the SUM function. The benefit of using this method is it makes the formula more compact.

Select a cell to calculate the difference and start your formula by typing the equality sign (=).

1. Next, we will find the sum of all the subtrahends using the SUM function and then subtract the sum from the minuend. In our case, the minuend is A1, where the subtrahends range is A2:A5. The formula will be as follows:
   Formula =A1-SUM(A2:A5)
   Hit the enter button, and you will have the desired output.

Method 3. Subtract by adding the negative numbers

You all might use this method particularly for solving arithmetic equations for your math course. Subtracting negative numbers gives the same output as adding them.

With the help of the SUM function, we will calculate the difference. But before using the SUM function, you need to put a minus sign before the subtrahends. Let’s suppose you want to subtract the subtrahend range A2:A5 from A1.

Firstly we will add a minus sign to the subtrahend range.

Select a cell to calculate the difference and start your formula by typing the equality sign (=). Type the SUM function to add up the negative numbers. The formula will be as follows:

=SUM (A1:A6)

Hit the enter button, and you will have the desired output.

Subtract one list from another

While working with Excel many times you come across Excel lists. You have been assigned a task to remove the elements of list B from list A.

Mathematically, the solution to the above problem is to subtract list B from list A.

List A: {“1”, “2”, “3”, “4”}

List B: {“1”, “4”}

Result after removing the same elements: {“2”, “3”}

In terms of Excel, we need to compare two lists for unique values, i.e. find the values that appear only in the larger list. For this, use the formula explained in How to compare two columns for differences:

=IF(COUNTIF($B:$B, $A2)=0, “Unique”, “”)

Where A2 is the first cell of the larger list and B is the column accommodating the smaller list.

As the result, the unique values in the larger list are labeled accordingly:

And now, you can filter the unique values and copy them wherever you want.

That’s how you subtract numbers and cells in Excel. To have a closer look at our examples, please feel free to download our Subtraction formula in Excel workbook.

---