Featured - How to subtract in Excel

We all need to subtract numbers at one point or another, but sometimes a calculator just won’t cut it. For those situations, we have software, and so today we’re going to show you how to subtract in Excel.

The Excel subtraction formula and variations

Advertisement

While base subtraction is admittedly every easy, things start to get a bit more complex once you consider all the different ways you might want to subtract. You might need to subtract multiple cells from one cell with positive or negative numbers, subtract columns from each other, subtract the same number from a column of numbers, subtract percentages, text, dates…the list goes on.

The good news is that we’ll be covering almost everything in that list today. By the time we’re finished, you’ll be a master of Excel subtraction formulas and will be able to apply them in a variety of situations. Let’s get started with the basics:

How to Subtract Cells in Excel (Simple Subtraction)

As you’d expect, simple subtraction in Excel is incredibly easy. All you need to do is ensure that you have two cells with numbers in, or even just two numbers in your mind.

Then you can enter the following in your total cell:

  1. Type “=Cell1-Cell2” in your total cell

    After you type “=” you’ll get the ability to click on cells to select them. You can click on one cell, press the “-” key, then click on another cell and press Enter to get your total.

    Alternatively, you can type numbers straight into your spreadsheet. “=2-1” is a completely valid formula for example. Generally, it’s recommended you keep your numbers in separate cells so that you can change them later if you need to, however.

    Windows 11 - Excel - Substract Cells

How to Subtract Multiple Cells from One Cell using the Minus Sign (-)

To subtract multiple cells from a single cell, you have a few options. The most basic is simply using the minus sign:

  1. Minus each of the cells from the first cell

    Looking at our example below, you could minus all of the expenses from the income value with the following Excel subtraction formula:

    =D3-D4-D5-D6-D7-D8

    There is a faster way to do this, however, which we’ll show below.

    Windows 11 - Excel - Substract Multiple Cells from One

How to Subtract Multiple Cells from One Cell with the SUM Function

The SUM function in Excel will add up all numbers in a specified group of cells. This makes the formula more compact and saves time.

  1. Subtract numbers from your original cell with the SUM function

    In our example, we can type =D3-SUM(D4:D8). This will take our original number (1,200), and minus it from our rent, internet, power, food, and other expenses cells added together (855).

    You can quickly select numbers in a column with the SUM formula by clicking on the top cell after your first open brackets, then holding shift and clicking on the bottom cell. You can then type a closed bracket to complete the formula.

    Windows 11 - Excel - Substract Multiple Cells Using SUM

How to Subtract Multiple Cells from One Cell Using the SUM Function With Negative Numbers

As subtracting a negative number is the same as adding it, another way to solve our example is by making all of our expenses negative and performing a single SUM on all of the numbers:

  1. Add a – in front of the numbers you want to subtract from the original cell and type “=SUM(Cell1:Cell5)” in the formula box

    Of course, you should replace “Cell1” and “Cell2” with the actual references for your cells. In our case you can see that the final formula is =SUM(D3:D8), but the result is the same as if we were to do =D3-SUM(D4:D8) due to the negative expense numbers.

    Windows 11 - Excel - Substract Multiple Cells Using SUM with Negative Numbers

How to Subtract Columns in Excel

If you’d like to subtract two columns row by row, you can use the following trick:

  1. Type the subtraction for the two columns in the first row of your total, then click and drag the corner of the cell down to the bottom

    In this case, the two columns we want to subtract from one another is our income (column C) and our expenditure (column D). Therefore, the formula for the first cell of our total column would be =C4-D4.

    Now, rather than typing this out for each row of the columns, we can click and drag from the edge of the first cell of our total. Excel will automatically add one to the column numbers for each row it’s dragged, giving the correct calculation.

    Windows 11 - Excel - Substract Columns - Copy Formula

  2. Excel will automatically subtract each row of the columns from one another and display it in the total column

    Windows 11 - Excel - Substract Columns - Copy Formula - Result

How to Subtract the Same Number from a Column of Numbers

So, what if you want to subtract a single number from a column of different cells? This gets a little less intuitive, but we can modify the trick we performed earlier to speed things up.

  1. Minus an absolute cell from the first number in your column, then click and drag to copy the formula down

    Remember a second ago our cell numbers changed sequentially when we copied our formula down the column? In this case, we don’t want this. Instead, we want to define an absolute reference like so:

    =Cell1-$Cell$2 

    We’re telling our formula: minus our first cell, which is allowed to change, with our second cell, which is not allowed to change. Therefore, even though our first cell has changed, the number subtracted from it will always be the one in Cell2.

    Windows 11 - Excel - Substract Same Number from a Column of Numbers - Copy the Formula

  2. The formula will be copied down the row, only subtracting the specified static cell

    Windows 11 - Excel - Substract Same Number from a Column of Numbers - Copy the Formula - Result

How to Subtract Percentages in Excel

You can subtract two percentages in Excel the same as you would any other number: with the minus symbol. Things get a little more complicated when you want to subtract a percentage from a regular number, however.

Let’s look at both below:

  1. Subtract one percentage from another with the minus symbol

    Subtracting a percentage from another percentage is as easy as typing =cell1-cell2 in your formula bar. If both are percentages, you’ll automatically be presented with the difference between the two. However, you must make sure that your cell formatting is set to percentages for this to work.

    Windows 11 - Excel - Substract Percentage

  2. Decreasing a number by a percentage

    You can also use Excel to decrease a number by a percentage specified in another cell. All you need to do is set your percentage cells to percentage formatting and your regular number to number formatting. Then you can enter the following formula:

    Cell 1* (1-Cell2)

    You can see an example of this in action below:

    Windows 11 - Excel - Substract Percenge from a Number

  3. OR: Minus the percentage as an absolute reference

    Remember in one of the previous steps we used an absolute reference to minus the same number from multiple cells? We can do the same with percentages using this Excel subtraction formula:

    =Cell1* (1-$Cell$2)

    Again, make sure that your cells have their relevant formatting for the type of number presented.

    Windows 11 - Excel - Substract Absolute Percenge from Column of Numbers

How to Subtract Time in Excel

If you’re wondering how to subtract time in Excel, the answer is simple. Just set the cell format to time and use the minus symbol. Excel will do all of the hard work for you:

  1. Use the minus symbol to subtract one time from another

    Once you have your times down, it’s enough to just minus one cell from another as normal. The final Excel subtraction formula, then, would look something like this:

    =Cell1-Cell 2

    It’s really that easy.

    Windows 11 - Excel - Substract Time

How to Subtract Dates in Excel

Using Excel to subtract dates is easy. It works the same as subtracting a number or percentage. You can just set your formatting to dates place the trusty minus symbol between the two cells in your formula:

  1. Select your total cell and type “=Cell1-Cell2” in your formula bar

    Press Enter to complete the calculation. It will output the number of days between the two dates.

    Windows 11 - Excel - Substract Dates

  2. Drag down from the bottom-right corner of the cell to fill in the rest of your column

    The calculation will automatically apply sequentially to the dates in your table.

    Windows 11 - Excel - Substract Dates - Result

How to Subtract the Text of One Cell from Another in Excel

It’s a little more niche, but at times you may need to subtract the text of one cell from another or remove a number from the end of a cell. This could be useful if you have product items with a unique identifier attached, for example, but also want a human-friendly, readable name.

Here’s how you’d do that:

  1. Use the Trim and Substitute commands to remove text from the end of a cell

    To subtract the text of one cell from another you can use the substitute function and replace it with an empty space. Trim then removes any extra spaces. So, without our cells, the formula would look like this:

    =TRIM(SUBSITUTE(your full text, the text you want to subtract,""))

    Naturally, for this to work your cells must have the relevant formatting type, with text set to text and numbers to number.

    You can also perform subsitute/trim in a table by referencing the cell instead of typing out the text in full:

    =TRIM(SUBSITUTE(Cell1,Cell2,""))

    You can see the results in the example below:

    Windows 11 - Excel - Substract Text of One Cell from Another

  2. OR: Substitute a single phrase/number from a range of cells

    If the text you want to subtract is the same in every cell of your column, you don’t need a separate number column. Instead, you can just set the right formatting for each cells and then specify the text you want to remove in your substitute formula:

    =TRIM(SUBSITUTE(Cell1, "Text to remove",""))

    Windows 11 - Excel - Substract the Same Text from a Range of Cells

How to Matrix Subtract in Excel

If you’re unfamiliar, a matrix is a set of numbers. Sometimes you’ll want to subtract two sets of numbers from one another in the order that they appear in their respective tables and output the result. If this is hard to visualize, don’t worry – there will be examples below.

Here’s how to do matrix subtraction in Excel:

  1. Set up three tables and type the matrix subtraction formula

    Your tables should be laid somewhat like the example below, though technically they can be anywhere on your page. The formula itself isn’t complicated – but the steps to get the results are a little different from what you’re used to.

    First, highlight the empty cells of your results table so that Excel knows to output the result across the entire range. Then, type the following formula (replacing the words with the relevant cell references) and press CTRL + SHIFT + Enter:

    =(TopLeftCell1:BottomRightCell1)-(TopLeftCell2-BottomRightCell2)

    It’s important that you remember to press Ctrl + SHIFT + Enter, as this is what will make it an array formula.

    Windows 11 - Excel - Substract Matrix

  2. Check your result cells for curly braces

    To verify that the formula is an array, you can click on any of your result cells and see that the formula is surrounded by curly braces instead of regular ones. 

    Windows 11 - Excel - Substract Matrix - Result

    Extra: How to Combine Text Cells in Excel

    We’ve shown you how to subtract text in Excel, but what if you want to combine the text of two cells into one? For that, you’ll need the Concatenate formula. You can check our full guide on how to concatenate in Excel here.

    Extra: How to Square Root in Excel

    For more Excel math, you can check our how to square root in Excel with three different formulas guide. It should give you a better idea of how Excel formulas function in general.

    Windows 10 - Excel - SQRT

Advertisement