If you’re new to Excel and wondering how to subtract in Excel, you’re at the right place. In this tutorial, I will show you how to subtract in Excel (subtract cells, ranges, columns, and more). I will start with the basics and then would cover some advanced subtraction techniques in Excel. I also cover how to subtract dates, times, and percentages in Excel. So let’s get to it!
Subtracting Cells/Values in Excel
Let’s start with a really simple example, where I have two values (say 200 and 100) and I want to subtract these and get the result. Here is how to do this: The above steps would perform the calculation in the cell and display the result. Note that this is called a formula in Excel, where we start with an equal-to sign and then have the formula or the equation that we want to solve. In the above example, we hard-coded the values in the cell. This means that we manually entered the values 200 and 100 in the cell. You can also use a similar formula when you have these values in cells. In that case, instead of entering the values manually, you can use the reference of the cell. Suppose you have two values in cell B1 and B2 (as shown below), and you want to subtract the value in cell B2 from the value in cell B1 Below is the formula that will do this: It’s the same construct, but instead of manually entering the values in the formula, we have used the cell reference which holds the value. The benefit of doing this is that in case the values in the cells change, the formula would automatically update and show you the correct result.
Subtracting a Value from an Entire Column
Now let’s get to slightly more advanced subtraction calculations. In the above example, we had two values that we wanted to subtract. But what if you have a list of values in a column, and you want to subtract one specific value from that entire column. Again, you can easily do that in Excel. Suppose you have a data set as shown below and you want to subtract the value 10 from each cell in column A. Below are the steps to do this: When you do this, Excel will copy the formula in cell B2, and then apply that to all the cells where you pasted the copied cell. And since we are using a cell reference in the formula (A2), Excel would automatically adjust the cell reference as it goes down. For example, in cell B3, the formula would become =A3-10, and in cell B4, the formula would become A4-10.
If Using Microsoft 365
The method that I’ve covered above will work with all the versions of Excel, but if you using Microsoft 365, then you can use an even easier formula. Suppose you have the same data (with data in column A) and you want to subtract 10 from each cell, you can use the below formula in cell B2: That’s it! You don’t need to worry about copying and pasting the formula in other cells as Excel would take care of it. This is called a dynamic array formula, where the result does not return one single value, but an array of values. And these are values are then spilled over to other cells in the column Note: For these dynamic array formulas to work, you need to make sure the cells where the result would be populated are empty. In case there is already a number of text in these cells, you will see the #SPILL! error in the cell where you enter the formula.
Subtracting a Cell Value from an Entire Column
In the above example, I subtracted 10 from multiple cells in a column. We can use the same concept to subtract a value in a cell from an entire column. Suppose you have a dataset as shown below where you want to subtract the value in cell D2 with all the cells in column A. Below are the steps to do this: In this example, I have used the formula A2-$D$2, which makes sure that when I copy the formula for all the other cells in column C, the value that I am subtracting remains the same, which is cell B2. When you add a dollar before the column alphabet and the row number, it makes sure that in case you copy the cell with this reference and paste it somewhere else, the reference would still remain $D$2. This is called absolute reference (as these don’t change). When you copy this formula in cell C3, it would become A3-$D$2, and in cell C4, it would become A4-$D$2. So while the first part of the reference keeps changing from A3 to A4 as we copy it down, the absolute reference doesn’t change, This allows us to subtract the same value from all the cells in column A.
If Using Microsoft 365
If you’re using Microsoft 365 and have access to dynamic arrays, you can also use the below formula: With dynamic arrays, you don’t have to worry about references changing. It will take care of it itself.
Subtract Multiple Cells From One Cell
Similar to the above example, you can also delete values in an entire column from a single value or the cell that holds the value. Suppose you have a data set as shown below and you want to subtract all the values in column B from the value in cell A2. Below are the steps to do this: The logic used here is exactly the same as above, where I’ve locked the reference $A$2 by adding a $ sign before the column alphabet and row number. This way, the cell reference $A$2 doesn’t change when we copy it in column C, but the second reference of the formula (B2), keeps changing when we go down the cell.
If Using Microsoft 365
If you’re using Microsoft 365 and have access to dynamic arrays, you can also use the below formula:
Subtracting Cells in Two Columns
In most practical cases, you will have two columns where you want to subtract the cells in each column (in the same row) and get the result. For example, suppose you have the Revenue and Expense values in two columns and you want to calculate the Net Income (which is the difference between revenue and expenses) Here is how to do this: The above formula will automatically adjust the reference as it’s copied down and you will get the difference between Revenue and Expense in that row.
If Using Microsoft 365
The method that I’ve covered above will work with all the versions of Excel, but if you using Microsoft 365, then you can use an even easier formula. Suppose you have the same data and you want to subtract the two columns, you can use the below formula: Note that this is made possible because Excel in Microsoft 365 has something called Dynamic Arrays. If you don’t have these, then it’s won’t be able to use this formula.
Subtract Dates in Excel
Dates and Time values are stores as numbers in the backend in Excel. For example, 44197 represents the date 01 Jan 2021, and 44198 represents ṭhe date 2 Jan 2021. This allows us to easily subtract dates in Excel and find the difference. For example, if you have two dates, you can subtract these and find out how many days have elapsed between these two dates. Suppose I have a dataset as shown below where I have the ‘Start Date’ and the ‘End Date’ and I want to find out the number of days between these two dates. A simple subtraction formula would give me the result. There is a possibility that Excel will give you the result in the date format instead of a number as shown above. This sometimes happens when Excel tries to be helpful and pick up the format from the adjacent column. You can easily adjust this and get the values in numbers by going to the Home tab, and select General in the number format drop-down. Note: This formula would only work when you using a date that Excel recognizes as a valid date format. For example, if you use 01.01.2020, Excel won’t recognize it as a date and consider it a text string. So you won’t be able to subtract dates with such a format You can read more about subtracting dates in Excel here
Subtract Time in Excel
Just like dates, even time values are stored as numbers in Excel. While the day part of the date would be represented by the whole number, the time part would be represented by the decimal. For example, 44197.5 would represent 01 Jan 2021 12:00 PM and 44197.25 would represent 01 Jan 2021 09:00 AM In case you have time values in Excel, what you really have in the back end in Excel are decimal numbers that represent that time value (which are formatted to be shown as time in the cells). And since these are numbers, you can easily subtract these. Below I have a data set where I have the start time and the end time, and I want to calculate the difference between these two times. Here is the formula that will give us the difference between these time values: There is a high possibility that Excel will change the format of the result column to show the difference as a time value (for example, it may show you 9:00 AM instead of 0.375). You can easily change this by going to the Home tab and selecting General from the format dropdown. Note that in case of times, you will get the value in decimals, but if you want it in hours, minutes, and seconds, you can do that by multiplying the decimal value with 24 (for getting hours), or 2460 for getting minutes and 2460*60 for getting seconds. So if you want to know how many hours are there in the given time in our dataset, you can use the below formula:
Subtract Percentages in Excel
Subtracting percentages from a number in Excel is a little different than subtracting two whole numbers or decimals. Suppose you have two percentage values (as shown below) and you want to subtract one from another, you can use a simple subtraction formula But if you want to subtract a percentage value from a non-percentage value, you need to do it differently. Suppose you have 100 in cell B1 and you want to subtract 20% from this value (i.e., subtract 20% of 100 from 100). You can use the below formal in this case: You can also use the below formula: In case you have the percentage value in a cell, then you can use the cell reference as well. For example, if you have the dataset as shown below, and you want to subtract the percentage value in cell B2 from the number in B1. Then you can use the below formula:
Subtract Using Paste Special
And finally, you can also use Paste Special trick to subtract in Excel. This is useful when you want to quickly subtract a specific value from an entire column. Suppose you have the dataset as shown below and you want to subtract 100 from each of these numbers. Below are the steps to do this: The above steps but simply subtract the value that you copied from the selected column. The result you get from this is a static value. The benefit of this method is that you do not need an additional column where you need to apply a formula to subtract the values. In case you want to keep the original values as well, simply create a copy of the column and then use the above steps So these are different ways that you can use Excel to subtract values/percentages, cells, and columns. Understanding these basic concepts will help you use the Excel spreadsheet tool the most efficient way. I hope you found this tutorial useful. Other Excel tutorials you may also like:
How to Add or Subtract Days to a Date in Excel (Shortcut + Formula)How to Multiply in Excel Using Paste SpecialHow to Sum Only Positive or Negative Numbers in ExcelChange Negative Number to Positive in Excel [Remove Negative Sign]How to Subtract Multiple Cells from One Cell in ExcelCalculate Percentage Change in Excel (% Increase/Decrease Formula)
