The Excel formula for subtracting cells, columns, dates, and times

Even though Excel is a fantastic tool for data analysis, many users also use it to do simple mathematical operations like addition, subtraction, multiplication, and division.

You’ve come to the correct spot if you’re new to Excel and are wondering how to subtract in Excel.

I will demonstrate how to subtract in Excel in this lesson (subtract cells, ranges, columns, and more).

I’ll start with the fundamentals before covering some sophisticated subtraction strategies in Excel. The removal of dates, timings, and percentages from Excel is another topic I address.

Let’s begin, then!

This instruction explains: 1. Excel Subtracting Values/Cells 2. Taking a Value Out of the Whole Column 3. Taking a Cell Value Out of the Whole Column 4. Multi-cell subtraction from a single cell 5. Cell Subtraction in Two Columns 6. Date subtraction in Excel 7. Excel time subtraction 8. Excel percentage subtraction 9. Using Paste Special, subtract

1. Excel Subtracting Values/Cells

Let’s begin with a very basic scenario in which I want to subtract two values—let’s say 200 and 100—to get the result.

How to accomplish it is as follows:

1. Enter the equal to the symbol (=) after selecting the cell you wish to subtract from.
2. Put the first number in.
3. Type the negative symbol here (minus sign -)
4. Put in the following number.
5. Tap “Enter”

The computation would be done in the cell and the result would be shown using the aforementioned procedures.

Recall that in Excel, anything like this is referred to be a formula, where the formula or equation we wish to solve is preceded by an equal-to sign.

We hard-coded the values in the cell in the aforementioned example. This indicates that the numbers 200 and 100 were manually inserted into the cell.

When these values are present in cells, a similar formula can be used as well. In that instance, you can utilize the cell reference rather than manually inputting the data.

Assume you wish to subtract the value in cell B2 from the value in cell B1 if you have two values in cells B1 and B2 (as seen below).

The equation to accomplish this is given below:

=B1-B2

The only difference is that we used the cell reference that contains the value rather than manually entering the values in the formula.

The advantage of doing this is that the formula would automatically update and show you the right result if the values in the cells changed.

2. Taking a Value Out of the Whole Column

In the aforementioned example, we desired to remove two numbers.

What happens, though, if you wish to remove one specific value from a column that contains a list of values?

Again, Excel makes it simple to accomplish that.

Consider the following data collection, where you wish to deduct 10 from each cell in column A.

The procedures are as follows:

1. Put the following formula in cell B2: =A2-10
2. Copies cell B2
3. Choose cells B3–B12
4. copy the cell, then paste it

Excel will copy the formula in cell B2 and apply it to every cell where you pasted the copied cell when you do this.

Additionally, Excel would automatically change the cell reference as it decreased because we used a cell reference in the formula (A2).

As an illustration, the formula would change to =A3-10 in cell B3 and A4-10 in cell B4.

When using Microsoft 365

The approach I’ve described above is compatible with all versions of Excel, but if you’re using Microsoft 365, you may use an even simpler formula.

If you have the same data (in column A) and wish to remove 10 from each cell, enter the formula in cell B2 as follows:

=A2:A12-10

I’m done now!

Excel will take care of copying and pasting the formula into other cells, so you don’t need to worry about it.

This is referred to as a dynamic array formula since the outcome returns an array of values rather than just one. These numbers then overflow into the column’s other cells.

Note: You must ensure that the cells that would contain the result are empty for these dynamic array formulae to function.  You will get the #SPILL! error in the cell where you enter  the formula if there are already several texts in these cells.

 3. Taking a Cell Value Out of the Whole Column

In the last illustration, I took away 10 from various cells in a column.

The same idea may be applied to removing a value from a cell from a whole column.

Consider the following dataset, where you wish to deduct the value in cell D2 from every cell in column A.

The procedures are as follows:

Put the following formula in cell C2: =A2-$D$2.

Copies cell C2

Choose cells C3–C12

copy the cell, then paste it

In this example, I’ve used the formula A2-$D$2, which ensures that cell B2—the value I’m subtracting—remains the same when the formula is copied for all the other cells in column C.

By placing a dollar sign between the row number and column alphabet, you ensure that the reference will stay $D$2 even if you copy and paste the cell with this reference. Since these don’t change, this is referred to as an absolute reference.

If you copy this formula to cell C3, it will appear as A3-$D$2, and if you copy it to cell C4, it will appear as A4-$D$2.

Therefore, even though the absolute reference changes as we copy it down from A3 to A4, the first part of the reference keeps changing from A3 to A4.

As a result, we can subtract the same amount from each cell in column A.

When using Microsoft 365

You may also use the following formula if you’re using Microsoft 365 and have access to dynamic arrays:

=A2:A12-D2

You don’t have to be concerned about references changing while using dynamic arrays. It will look after itself.

4. Multi-cell subtraction from a single cell

Similar to the example above, you can also remove all of the values from a column from a single value or the cell where the value is stored.

Assume you have the data set depicted below and wish to remove the value in cell A2 from all the values in column B.

The procedures are as follows:

1. Put the following formula in cell C2: =$A$2-B2.
2. Copies cell C2
3. Choose cells C3–C12
4. copy the cell, then paste it

The reasoning is precisely the same as in the last example, where I locked the reference to $A$2 by placing a $ symbol in front of the column name and row number.

By doing it this way, the cell reference $A$2 remains the same when we duplicate the formula into column C, but the formula’s second reference (B2) changes as we move down the cell.

When using Microsoft 365

You may also use the following formula if you’re using Microsoft 365 and have access to dynamic arrays:

=A2-B2:B12

5. Cell Subtraction in Two Columns

In the majority of real-world scenarios, you’ll have two columns with cells in the same row that you wish to subtract to obtain the result.

For instance, let’s say you want to determine the net income and you have the revenue and cost numbers in two columns (which is the difference between revenue and expenses)

How to accomplish it is as follows:

Put the following formula in cell C2: =B2-C2.

Copies cell C2

Choose cells C3–C12

copy the cell, then paste it

The reference in the formula above will automatically change as it is copied lower, giving you the difference between revenue and expense in that row.

When using Microsoft 365

The approach I’ve described above is compatible with all versions of Excel, but if you’re using Microsoft 365, you may use an even simpler formula.

If you wish to remove the two columns from a set of identical data, you may use the formula below:

=B2:B11-C2:C11

Note that this is made possible by a feature called Dynamic Arrays in Microsoft 365’s Excel. You won’t be able to utilize this formula if you don’t have these.

6. Date subtraction in Excel

Excel’s backend saves date and time data as numbers.

For instance, 44197 stands on January 1, 2021, and 44198 on January 2, 2021.

This makes it simple for us to discover the difference and subtract dates in Excel.

If you have two dates, for instance, you may subtract them to see how many days have passed between them.

Let’s say I want to calculate the number of days that pass between two dates in a dataset with the “Start Date” and “End Date” as shown below.

It would be easy for me to calculate the outcome using subtraction.

=B2-A2

Excel may provide you with the result in date format rather than the above-described number format.

This occasionally occurs when Excel makes an effort to be helpful and takes the format from the column next to it.

By heading to the Home tab and selecting General in the number format drop-down, you can quickly make changes to this and retrieve the values in numbers.

Note: This formula would only function if you were entering a date that Excel accepts as a legitimate date format.  For instance, if you enter 01.01.2020, Excel will treat it as a text string rather than a date. Therefore, using this format to subtract dates won't work.

7. Excel time subtraction:

Even time values are kept in Excel as integers, just like dates.

The full number would stand in for the day of the date while the decimal would stand in for the hour.

For instance, 44197.5 would represent noon PM on January 1, 2021, while 44197.25 would represent 9:00 AM on January 1, 2021.

If you have time values in Excel, what you have there are decimal numbers that reflect that time value in the background (which are formatted to be shown as time in the cells).

Additionally, you can readily subtract them as they are integers.

I want to determine the difference between the start time and the end time in the data set that is shown below.

The following equation will calculate the difference between these time values for us:

=B2-A2

Excel is likely to alter the format of the result column to display the difference as a time value. Simply select General from the format dropdown menu on the Home tab to change this.

It should be noted that when dealing with times, you will receive the value in decimals; however, if you would prefer to receive it in hours, minutes, or seconds, you can do so by multiplying the decimal value by 24 (to obtain hours), 24*60 (to obtain minutes), or 24*60*60 (to obtain seconds).

Therefore, you can use the following formula to determine how many hours are in the given time in our dataset:

=(B2-A2)*24

8. Excel percentage subtraction:

In Excel, removing percentages from a value differs slightly from removing two decimals or full integers.

If you have two % numbers and wish to subtract one from the other, you may do so by using the formula given below.

However, you must do differently if you wish to take a % value away from a non-percentage variable.

Assume you wish to deduct 20% from the value of 100 in cell B1 (i.e., deduct 20% of 100 from 100).

In this situation, you can use the following formal:

=B1*(1-20%)

 

Use the formula below as well:

=B1-B1*20%

You may also use the cell reference if the percentage value is stored in a cell. For instance, suppose you have the dataset depicted below and you wish to remove the number in cell B1 from the % value in cell B2.

 

You may then apply the following formula:

=B1-B1*B2

9. Using Paste Special, subtract

Additionally, you may subtract in Excel by using the Paste Special technique.

When you need to rapidly remove a certain value from a whole column, this is helpful.

Let’s say you want to deduct 100 from each of the numbers in the dataset that is depicted below.

The procedures are as follows:

1. Enter the amount you wish to take away from the entire column in an empty cell. I’ll enter this value in cell D1 in this example.
2. “Clip cell D1” (which is the cell where you have entered this value you want to subtract)
3. The whole column that you wish to deduct the copied value from should be selected.
4. After making a right-click, select the Paste Special option.
5. Choose the Values option for the Paste command in the special dialogue box.
6. Choose to Subtract under Operations.
7. Input OK.

The value that you typed in the cell in Step 1 should be removed.

The values you copied from the chosen column were simply subtracted during the aforementioned steps. This yields a static value as the result.

The advantage of this approach is that you do not require a second column where a formula is applied to deduct the values. Create a copy of the column and then follow the instructions above if you want to maintain the original values as well.

Therefore, there are many techniques for subtracting values/percentages, cells, and columns in Excel.

You can utilize the Excel spreadsheet application more effectively if you understand these fundamental ideas.

I sincerely hope this tutorial was helpful.