Skip to main content

Explore the new, free version of Kutools for Outlook today!

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

Author: Zhoumandy Last Modified: 2024-12-11

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

A screenshot demonstrating subtraction of one cell's value from another in Excel using a formula

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.

A screenshot showing how to subtract multiple cells (B3, B4, B5) from one cell (B2) in Excel

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

A screenshot demonstrating the subtraction of multiple cells (B3, B4, B5) from one cell (B2) using the minus sign in Excel

🌟 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. 💪

A screenshot of Kutools for Excel's Operation Tools utility showcasing batch operations like add, subtract, multiply, and divide

📊 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)

A screenshot demonstrating the use of the SUM function to subtract the sum of multiple cells (B3:B5) from a single cell (B2) in Excel

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)

A screenshot showing how to subtract multiple cells from one cell in Excel by summing negative numbers using the SUM function

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

A screenshot showing how to subtract values row-by-row in Excel using the basic subtraction formula

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

A GIF showing the process of applying the subtraction formula row-by-row using the fill handle in Excel

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

A screenshot demonstrating the quicker way to subtract values in Excel for Microsoft 365 by using an array formula

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.

A screenshot showing to subtract a constant number from a column in Excel

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

A screenshot showing the formula to subtract the same number from a column in Excel using the minus sign

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

A GIF showing the process of dragging the formula down to apply it to the entire column in Excel

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

A screenshot showing how to subtract a number from an entire column in Excel for Microsoft 365

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.

Kutools for Excel offers over 300 advanced features to streamline complex tasks, boosting creativity and efficiency. Enhanced with AI capabilities, Kutools automates tasks with precision, making data management effortless. Detailed information of Kutools for Excel...         Free trial...

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.

A screenshot showing the Operation Tools dialog in Kutools for Excel with the Subtraction operation selected

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

A screenshot showing the result of subtracting a number from an entire column using Kutools

Kutools for Excel - Supercharge Excel with over 300 essential tools. Enjoy permanently free AI features! Get It 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.

A screenshot showing the copied value from cell E3 using Ctrl+C

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

A screenshot showing the right-click menu with Paste Special selected in Excel

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

A screenshot showing the Paste Special dialog with Subtract selected under Operation

Result

A screenshot showing the result after subtracting the same number from the entire column using Paste Special

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 the 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

A screenshot showing the result of subtracting dates in Excel with the formula =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.
A screenshot of the Date & Time Helper feature in Kutools for Excel calculating the difference between two dates or times

Date & Time Helper:

Get the difference between two dates/times

Effortlessly calculate the difference 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

A screenshot of subtracting times in Excel using the formula =B3 - A3

A screenshot of the Date & Time Helper feature in Kutools for Excel adding or subtracting date and time

Date & Time Helper:

Add or Subtract date and time

Quickly add or subtract years, months, days, hours, minutes, or seconds to a datetime 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 .