HomeWinBuzzer TipsHow to Subtract in Excel (Numbers, Dates, Time, Percentages)

How to Subtract in Excel (Numbers, Dates, Time, Percentages)

Wondering how to subtract in Excel? This guide will show you everything you need to know, from subtracting numbers to text, dates, columns, percentages, and negative numbers.

-

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

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.

How to Subtract Cells in Excel (Simple Subtraction)

This method is the foundation of subtraction in Excel, ideal for straightforward calculations where you need to find the difference between two individual cells. It’s commonly used for basic arithmetic operations and is a great starting point for Excel beginners.

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.

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

When you have one cell that needs to be reduced by the values of several other cells, this method comes into play. It’s particularly useful for scenarios like adjusting a starting value by multiple deductions, such as a budget reduced by various expenses.

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.

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 approach streamlines the subtraction of multiple cells from a single cell by summing up the cells to be subtracted first. It’s efficient for consolidating multiple subtractions into one formula, making your spreadsheet cleaner and your calculations more manageable.

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.

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

This variation of the SUM function method is tailored for situations where the cells being subtracted contain negative numbers. It simplifies the process by converting the subtraction of negatives into an addition, thereby streamlining calculations involving losses or deficits.

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.

How to Subtract Columns in Excel

Subtracting columns in Excel is essential for comparing datasets row by row, such as calculating the difference between two sets of measurements or financial figures. This method is key for data analysis tasks where columnar comparisons are required.

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.

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

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 method is designed for situations where you need to apply a uniform deduction across a range of cells, such as decreasing a series of measurements by a constant value. It’s particularly useful for adjusting data sets en masse.

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.

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

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.

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:

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.

How to Subtract Time in Excel

If you’re wondering how to subtract time in Excel, the answer is simple. Time subtraction is vital for calculating durations or the difference between time points, such as work hours or event durations. This method allows users to manage schedules, track time spent on tasks, and more.

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.

How to Subtract Dates in Excel

Using Excel to subtract dates is also very easy. It works the same as subtracting a number or percentage. Date subtraction is used to calculate the interval between two dates, essential for project timelines, age calculations, and any scenario where understanding the span of days between dates is necessary.

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.

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.

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 method is useful for text manipulation, such as removing specific characters or words from cell content. It’s particularly handy for cleaning data, creating readable labels, or modifying text entries in bulk.

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,""))`

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",""))`

How to Matrix Subtract in Excel

If you’re unfamiliar, a matrix is a set of numbers. Matrix subtraction is a more advanced technique used for subtracting corresponding elements of two arrays or matrices. This method is essential for complex data analysis, mathematical modeling, and operations research, providing a powerful tool for handling multidimensional data.

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.

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.

How do I subtract a fixed value from multiple cells in Excel without repeating the value in each cell?

To subtract a fixed value from multiple cells without repeating the value, first, place the fixed value in a cell (e.g., B1). Then, in the cell where you want to start the subtraction (e.g., A1), enter the formula =A1-\$B\$1. The dollar signs make B1 an absolute reference, meaning as you drag the formula down or across, Excel will continue to reference B1 for each subtraction. This method keeps your data clean and your formulas consistent.

Can Excel subtract text values based on numerical identifiers within the text?

Excel cannot directly perform arithmetic operations on text values. However, you can extract numerical identifiers from text using functions like LEFT, RIGHT, MID, and FIND to locate and isolate the numbers. Once extracted using these functions, wrap the result with the VALUE function to convert the text to a number, which can then be used in subtraction operations. For example, if you have a cell A1 with text “Item123” and you want to subtract 100 from the number part, you could use =VALUE(MID(A1,5,3))-100.

How do you subtract a range of cells from a single cell without using the SUM function?

While using the SUM function is the most efficient way to subtract a range of cells from a single cell, you can also manually list each cell in your subtraction formula if the range is small. For example, if you want to subtract cells A2, A3, and A4 from A1, you would use =A1-A2-A3-A4. For larger ranges, consider using the SUM function for simplicity and to reduce the potential for errors, as manual entry becomes impractical.

What is the best way to subtract percentages in Excel to ensure accuracy?

To ensure accuracy when subtracting percentages, first, ensure that the cells containing percentages are formatted as ‘Percentage’. Then, use a straightforward subtraction formula like =Cell1-Cell2. For example, if A1 is 10% and A2 is 5%, =A1-A2 will yield 5%. For subtracting a percentage from a whole number, convert the percentage to a decimal in the formula, e.g., =100*(1-A1) to subtract 10% from 100.

How do I subtract time durations in Excel to get results in hours or minutes?

To subtract time durations and get results in hours or minutes, input your start and end times in two separate cells formatted as ‘Time‘. Use a subtraction formula like =EndTime-StartTime in another cell. To display the result in hours, format the result cell as ‘Custom‘ and type [h] in the Type field. For minutes, use [m]. This will display total hours or minutes, even if the result exceeds 24 hours or 60 minutes.

Is there a way to perform matrix subtraction in Excel without using array formulas?

Matrix subtraction typically requires array formulas to handle multiple values simultaneously. However, for simple cases or small matrices, you can subtract corresponding cells manually. For example, if you have two 2×2 matrices in ranges A1:B2 and C1:D2, you can subtract them by entering =A1-C1 in a new cell for the first element and repeating this for each corresponding element. For larger matrices or more complex operations, consider using array formulas with CTRL+SHIFT+ENTER or leveraging Excel’s built-in matrix functions.

How can I subtract a list of values from another list efficiently in Excel?

To efficiently subtract one list of values from another, organize both lists in separate columns. In the first cell of a new column where you want the results, enter a subtraction formula referencing the first cells of each list, like =A1-B1. Then, use the fill handle to drag the formula down the column. Excel will automatically adjust the cell references for each row, performing the subtraction for each pair of list values.

What’s the method to subtract only positive numbers from a total in Excel?

To subtract only positive numbers from a total, use the IF function to check the number’s sign before subtracting. For example, if you have a total in A1 and want to subtract B1 only if it’s positive, use =A1-IF(B1>0, B1, 0). This formula checks if B1 is greater than 0; if true, it subtracts B1 from A1; if false, it subtracts 0, leaving A1 unchanged.

How do I subtract a percentage from a total in Excel and display the result as a number?

To subtract a percentage from a total and display the result as a number, first, ensure the cell with the percentage is formatted as ‘Percentage‘. Then, use a formula like =Total*(1-Percentage) in a new cell. For example, to subtract 15% from a total of 200 in cell A1, with the percentage in B1, use =A1*(1-B1). This formula calculates the new total after subtracting the percentage and displays it as a number.

Can I use Excel to subtract dates and display the result in years or months?

Yes, you can subtract dates and display the result in years or months using the DATEDIF function. For example, =DATEDIF(StartDate, EndDate, “Y”) returns the number of complete years between two dates, while replacing “Y” with “M” gives the number of complete months. Ensure both StartDate and EndDate are formatted as ‘Date‘ for accurate calculations.

How do I ensure subtraction formulas are correctly applied when copying and pasting in Excel?

To ensure subtraction formulas are correctly applied when copying and pasting, use absolute references for constants (using \$) and relative references for cells that should adjust based on the new location. Use ‘Paste Special > Formulas‘ to paste only the formulas without formatting or values. This ensures that the formulas adjust correctly to their new locations while keeping fixed references unchanged.

What is the quickest way to subtract the same number from different cells across multiple sheets in Excel?

To quickly subtract the same number from different cells across multiple sheets, define a named range for the constant number (Formulas > Define Name). Then, use this named range in your subtraction formulas on each sheet. This approach maintains consistency and allows you to change the constant number in one place, automatically updating all related formulas across the workbook.

How can I subtract values in Excel based on criteria, such as subtracting sales in a specific region?

To subtract values based on criteria, use the SUMIFS function to sum values that meet your criteria and then subtract this sum from your total. For example, =TotalSales-SUMIFS(SalesRange, CriteriaRange, Criteria) subtracts the sum of sales that meet a specific criterion (like a certain region) from the total sales. This method is useful for conditional calculations based on multiple criteria.

Is there a way to automate subtraction in Excel based on cell color or formatting?

Excel’s standard functions do not consider cell color or formatting for calculations. However, you can use VBA (Visual Basic for Applications) to write a custom function that performs operations based on cell color. This involves using the Interior.ColorIndex property to determine a cell’s color and then applying your logic to perform subtraction or other operations based on this property.

How do I subtract a dynamically changing value from a cell in Excel, such as a live currency conversion rate?

To subtract a dynamically changing value like a live currency rate, consider using Excel’s data types feature (Data > Data Types > Currencies) for live financial data or connecting to an external data source that updates regularly. Reference the dynamic value in your subtraction formula. For example, if A1 contains your amount and B1 is linked to a live currency conversion rate, use =A1-B1 to subtract the live rate from your amount. Ensure the external data connection is set to refresh automatically for up-to-date calculations.

Related: How to Divide in Excel

In Excel, you can divide using cell references, handle errors like dividing by zero, and even combine division with other operations for more complex calculations. In our other guide, you will learn basic to advanced functions for division, handle errors, and optimize calculations for maximum efficiency.

Related: 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.

Related: How to Wrap Text in Excel (Automatically and Manually)

Knowing how to wrap text in Excel is important so that your spreadsheet doesn’t get any wider than it needs to be. The wrap text function in Excel lets you break text into multiple lines, therefore increasing the length of your cell. In our other guide, we show you how to wrap text in Excel, using both Excel line breaks and its automatic word wrap functionality.

Related: 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.

Last Updated on April 22, 2024 10:06 am CEST