The first reference (B2) is not locked, so it changes for each row.Īs the result, in cell C3 you will have the formula =B3-$F$1 in cell C4 the formula will change to =B4-$F$1, and so on: This creates an absolute cell reference that does not change no matter where the formula is copied. The key point is to lock the reference for the cell to be subtracted with the $ sign. To subtract one number from a range of cells, enter that number in some cell (F1 in this example), and subtract cell F1 from the first cell in the range: Subtract the same number from a column of numbers To subtract 2 columns row-by-row, write a minus formula for the topmost cell, and then drag the fill handle or double-click the plus sign to copy the formula to the entire column.Īs an example, let's subtract numbers in column C from the numbers in column B, beginning with row 2:ĭue to the use of relative cell references, the formula will adjust properly for each row: So, make all the numbers you want to subtract negative (for this, simply type a minus sign before a number), and then use the SUM function to add up the negative numbers: To make your formula more compact, add up the subtrahends (B2:B6) using the SUM function, and then subtract the sum from the minuend (B1):Īs you may remember from a math course, subtracting a negative number is the same as adding it.
Simply type several cell references separated by a minus sign like we did when subtracting multiple numbers.įor example, to subtract cells B2:B6 from B1, construct a formula in this way: To subtract multiple cells from the same cell, you can use any of the following methods. How to subtract multiple cells from one cell in Excel
0 kommentar(er)
