Skip to main content

Subtraction in Excel: Cells, Columns, Dates, and Times - A Full Tutorial

Subtraction is a fundamental operation in Microsoft Excel. In simple terms, it involves finding the difference between two values. Excel provides various tools for subtraction, from basic cell-to-cell operations to more advanced tasks involving columns, dates, and times. This tutorial will show you how to subtract in Excel by using the minus sign and SUM function, and guide you through scenarios, like subtracting cells, columns, dates, and times.

Excel Subtraction Formula

Before diving into specific scenarios, let's start with the basics of the Excel subtraction formula.

Because there is no SUBTRACT function in Excel, to perform a simple subtraction operation, you should use the - (minus sign) arithmetic operator. The subtraction formula follows a simple structure:

=number1 - number2
Here, "number1" and "number2" can be actual numbers, cell references, or ranges.

For example, to subtract 10 from 50, apply the below formula and get 40 as the result.

=50 - 10

Now, let's break down subtraction in different contexts in the following content.

Subtract Cells in Excel

When it comes to subtracting cells in Excel, understanding the basics is crucial. Learn how to subtract one cell from another using a simple formula like =A1-B1 . Explore more advanced techniques by subtracting multiple cells from one, utilizing the Minus sign, the SUM function, and even summing negative numbers for intricate calculations.

Subtract One Cell from Another Cell

To subtract the contents of one cell from another, use the basic subtraction formula by employing the minus sign. Ensure to input cell references instead of numerical values.

=cell_1 - cell_2

For example, to subtract the number in cell B3 from the number in cell A3, apply the formula below in cell C3 , and press the Enter key to get the result.

=A3 – B3

Subtract Multiple Cells from One Cell

Subtracting the contents of multiple cells from a single cell is a common operation in Excel. For example, we want to subtract cells B3, B4, and B5 from B2 like the below screenshot shows.

In this section, We'll delve into three methods to accomplish this goal.

Subtract Multiple Cells from One Cell by Using the Minus Sign

The most straightforward way to subtract the contents of multiple cells from one cell is by using the basic subtraction formula with the minus sign.

In cell B6, apply the formula below, and press the Enter key to get the result.

=B2 - B3 - B4 - B5

🌟 Batch add/subtract/multiply/divide values without formulas 🌟

Kutools for Excel’s Operation Tools utility makes it simple to apply operations without using formulas in cells. It lets you do basic operations like add, subtract, multiply, and divide. Beyond these, you can also do more complex calculations, such as rounding, exponentiation, or using custom expressions or functions. 💪

Kutools for Excel

📊 Kutools for Excel: Your Time-Saving Excel Companion 🚀

Download Now

Subtract Multiple Cells from One Cell by Using the SUM Function and the Minus Sign

Alternatively, you can use the SUM function to add up cells B3:B5 and then subtract the sum from the total in cell B2.

In cell B6, apply the formula below, and press the Enter key to get the result.

=B2 - SUM(B3:B5)

Subtract Multiple Cells from One Cell by Summing Negative Numbers

Adding a negative number is the same as subtracting it. To subtract multiple numbers from one number, please make sure the numbers you want to subtract are negative. (You can convert a positive number to negative by placing a minus sign before it.) Afterward, use the SUM function to add up all numbers.

In cell B6, apply the formula below, and press the Enter key to get the result.

=SUM(B2:B5)

Subtract Columns in Excel

Extend your subtraction skills to entire columns with this section. Discover how to subtract two columns row-by-row using basic formulas. Additionally, explore more advanced methods like subtracting the same number from a column using the Minus sign, leveraging smart tools for batch operations without formulas, and using the Paste Special feature for efficiency.

Subtract 2 Columns Row-by-Row

To subtract values in two columns row-by-row, use the basic subtraction formula with the minus sign in each row.

1. In cell C3, apply the formula below, and press the Enter key to get the result.

=A3 – B3

2. Drag the fill handle down to apply the formula to the entire column.

Formula explanation:

Because relative cell references are utilized, the formula will dynamically adjust for each row.

Tip:

If you are using Excel for Microsoft 365, you can subtract the values in one column from another column row-by-row in a quicker way. You just need to apply the formula below in cell C3, and press the Enter key to get all results.

=A3:A6 - B3:B6

Subtract the Same Number from a Column

When working with spreadsheets, the need to subtract a constant value from an entire column is a common task. For example, we want to subtract the number in cell E3 from column B, like the below screenshot shows.

In this section, We'll delve into three methods to accomplish this goal.

Subtract the Same Number from a Column by Using the Minus Sign

Subtracting the same number from an entire column involves using the minus sign.

1. In cell C3, apply the formula below, and press the Enter key to get the result.

=B3 - $E$3

2. Drag the fill handle down to apply the formula to the entire column.

Formula explanation:

The crucial step is to use the $ sign to create an absolute cell reference, locking the reference to the cell to be subtracted. This ensures that it doesn't change when the formula is copied. In our case, when dragging the formula down, the absolute reference ($E$3) remains constant, while the relative reference (B3) adapts to each row, changing to B4, B5… in column B.

Tip:

If you are using Excel for Microsoft 365, you can subtract the same number from a column even faster. You just need to apply the formula below in cell C3, and press the Enter key to get all results.

=B3:B6 - $E$3

Using a Smart Tool to Batch Subtract Without a Formula

If you are not a fan of formulas, the Operation Tools of Kutools for Excel is an excellent tool for you. This tool allows you to subtract a specific number from an entire column and output in the same range effortlessly. Additionally, it provides the flexibility to perform various operations like addition, multiplication, and more, making it a convenient option for users who prefer a formula-free approach.

After downloading and installing Kutools for Excel, select the data range (C3:C6) from which you want to subtract a number, and click Kutools > Operation. In the popping-up Operation Tools dialog, please do as follows.

  1. Select Subtraction in the Operation box.
  2. Input the number or cell reference in the Operand box; here I input E3. Click OK.

If you want to create formulas as well, you can check the Create formulas option.
Result

Tip: To use this feature, you should install Kutools for Excel first, please click to download and have a 30-day free trial now.

Subtract the Same Number from a Column by Using the Paste Special Feature

You can also use the Paste Special feature to subtract the same number from a column without formulas.

1. Select cell E3 and press Ctrl+ C to copy it.

2. Select range B3:B6, right-click on it, and click Paste Special from the drop-down menu.

3. In the Paste Special dialog, click Subtract in the Operation section. Click OK.

Result

Tip: To subtract numbers in two columns row-by-row using the Paste Special feature, at step 1, select a column instead of a cell.

Subtract Dates in Excel

To subtract dates in Excel, use the basic formula with the minus sign. For easy calculations, enter the dates in separate cells, and subtract one cell from the other.

=Date2 – Date1

For example, to subtract date in cell B3 from the date in cell A3, apply the formula below in cell C3, and press the Enter key to get the result.

=B3 – A3

Tip: This formula would only work with dates in a valid format. For instance, attempting to subtract an invalid date like '01.01.2024' will result in a #VALUE! error. To prevent such errors, it's crucial to enter your dates correctly and in a format that Excel recognizes as valid.

Date & Time Helper:

Get the difference between two dates/times

Effortlessly calculate the differen between two datetimes using the Date & Time Helper feature of Kutools for Excel. Output in various types, such as:

  • year + month + day
  • year + month + week + day
  • hours + minutes + seconds ...

🚀 Kutools for Excel: Your Time-Saving Excel Companion

Subtract Times in Excel

Similarly, subtracting times is also accomplished by using the subtraction formula. For easy calculations, enter the times in separate cells, and subtract one cell from the other.

=Time2 – Time1

For example, to subtract time in cell B3 from the time in cell A3, apply the formula below in cell C3, and press the Enter key to get the result. 

=B3 – A3

Date & Time Helper:

Add or Subtract date and time

Quickly add or subtract year, month, or days, or hours, minutes or seconds to a date time using the Date & Time Helper feature of Kutools for Excel.

🚀 Kutools for Excel: Your Time-Saving Excel Companion

In conclusion, Excel provides a plethora of methods to perform subtraction, whether you're dealing with cells, columns, dates, or times. Understanding these techniques will enhance your proficiency in data manipulation and analysis using Microsoft Excel. If you're looking to explore more Excel tips and tricks, please click here to access our extensive collection of over thousands of tutorials .

Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations