Excel Tips: Dynamic Subtraction in Column in Excel Formula Explained

Dynamic subtraction in column in excel formula means that you can subtract values from a column based on a reference cell or a fixed value, and the formula will automatically adjust to the changes in the data. For example, if you want to subtract the values in column B from the values in column A, you can use a formula like =A2-B2 in cell C2, and then drag it down to fill the rest of the column C. This way, the formula will use relative references to subtract the corresponding values in each row.

There are different ways to perform dynamic subtraction in column in excel formula, depending on your needs and preferences. Some of the common methods are:

• Using the SUM function with a negative sign to subtract a range of cells from a single cell or a fixed value.
• Using the SUBTOTAL function with the function number 109 to subtract only the visible cells in a filtered range.
• Using the INDIRECT function with the SMALL function and an array formula to subtract a cell from the nth cell above or below it in the same column.
• Using the Paste Special command with the Subtract operation to subtract a fixed value from a whole column without using a formula.

Procedures

Here are the steps to perform each of the methods mentioned above:

Using the SUM function with a negative sign

• Select the cell where you want to store the result of the subtraction. For example, cell C2.
• Write the following formula in the formula bar: =A2-SUM(B2:B5)
• Press Enter to apply the formula.
• Drag or double-click on the Fill Handle icon down the column to copy the formula to the other cells.

This formula will subtract the sum of the values in the range B2:B5 from the value in cell A2, and then repeat the same calculation for the other rows. You can change the range or the fixed value as per your requirement.

Using the SUBTOTAL function with the function number 109

• Select the cell where you want to store the result of the subtraction. For example, cell C2.
• Write the following formula in the formula bar: =SUBTOTAL(109,A2)-SUBTOTAL(109,B2)
• Press Enter to apply the formula.
• Drag or double-click on the Fill Handle icon down the column to copy the formula to the other cells.

This formula will subtract the value in cell B2 from the value in cell A2, but only if both cells are visible after applying a filter. The function number 109 tells the SUBTOTAL function to ignore any hidden or filtered rows. You can change the function number to 9 if you want to include the hidden or filtered rows.

Using the INDIRECT function with the SMALL function and an array formula

• Select the cell where you want to store the result of the subtraction. For example, cell A2.
• Write the following formula in the formula bar: =A1-INDIRECT(“A”&SMALL(IF(A:A<>“”,ROW(A:A),“”),6))
• Press Ctrl + Shift + Enter to enter the formula as an array formula. This will add curly braces {} around the formula, but do not type them manually.
• Drag or double-click on the Fill Handle icon down the column to copy the formula to the other cells.

This formula will subtract the value in the 6th cell below cell A1 from the value in cell A1, and then repeat the same calculation for the other rows. The INDIRECT function creates a reference to a cell based on its address, which is composed of the column letter “A” and the row number returned by the SMALL function. The SMALL function returns the kth smallest value in an array, which is the array of row numbers of non-empty cells in column A, created by the IF function. The array formula allows the IF function to evaluate multiple conditions and return multiple results.

Using the Paste Special command with the Subtract operation

• Set a specific value that you want to subtract from the whole column. For example, 40.
• Copy the value to the clipboard by pressing Ctrl + C or using the Copy command from the Home tab.
• Select the column where you want to perform the subtraction. For example, column A.
• Go to the Home tab and click Paste from the Clipboard group.
• Select Paste Special from the Paste option.
• A Paste Special dialog box will appear. Select All in the Paste option and select Subtract from Operation and finally, click on OK.

This will subtract the value 40 from each cell in column A and replace the original values with the results. This method does not use a formula, so the results are static and will not change if you change the value 40.

Explanation

To illustrate the methods of dynamic subtraction in column in excel formula, let us use the following example table that shows the sales and expenses of a company in different months.

Suppose we want to calculate the profit for each month by subtracting the expenses from the sales. We can use any of the methods described above to achieve this. Here are the results of each method:

Using the SUM function with a negative sign

• Select cell C2 and write the formula =B2-SUM(C2:C7) and press Enter.
• Drag or double-click on the Fill Handle icon down the column to copy the formula to the other cells.

This formula will subtract the sum of the expenses in the range C2:C7 from the sales in each row and display the profit in column D. If we change the value of any cell in the range C2:C7, the formula will automatically update the profit in column D.

Using the SUBTOTAL function with the function number 109

• Select cell C2 and write the formula =SUBTOTAL(109,B2)-SUBTOTAL(109,C2) and press Enter.
• Drag or double-click on the Fill Handle icon down the column to copy the formula to the other cells.

This formula will subtract the expenses from the sales in each row and display the profit in column D, but only if both cells are visible after applying a filter. For example, if we apply a filter to show only the months with sales greater than 1000, the formula will show the profit for Jan, Feb, Apr, and May, and ignore the hidden rows Mar and Jun.

The function number 109 tells the SUBTOTAL function to ignore any hidden or filtered rows. If we want to include the hidden or filtered rows, we can change the function number to 9.

Using the INDIRECT function with the SMALL function and an array formula

• Select cell A2 and write the formula =A1-INDIRECT(“A”&SMALL(IF(A:A<>“”,ROW(A:A),“”),6)) and press Ctrl + Shift + Enter to enter the formula as an array formula.
• Drag or double-click on the Fill Handle icon down the column to copy the formula to the other cells.

This formula will subtract the value in the 6th cell below cell A1 from the value in cell A1, and then repeat the same calculation for the other rows. For example, cell A7 will show the result of A1-A7, which is 50. The INDIRECT function creates a reference to a cell based on its address, which is composed of the column letter “A” and the row number returned by the SMALL function. The SMALL function returns the kth smallest value in an array, which is the array of row numbers of non-empty cells in column A, created by the IF function. The array formula allows the IF function to evaluate multiple conditions and return multiple results.

Using the Paste Special command with the Subtract operation

• Select cell A2 and write the formula =A1-INDIRECT(“A”&SMALL(IF(A:A<>“”,ROW(A:A),“”),6)) and press Ctrl + Shift + Enter to enter the formula as an array formula.
• Drag or double-click on the Fill Handle icon down the column to copy the formula to the other cells.

This formula will subtract the value in the 6th cell below cell A1 from the value in cell A1, and then repeat the same calculation for the other rows. For example, cell A7 will show the result of A1-A7, which is 50. The INDIRECT function creates a reference to a cell based on its address, which is composed of the column letter “A” and the row number returned by the SMALL function. The SMALL function returns the kth smallest value in an array, which is the array of row numbers of non-empty cells in column A, created by the IF function. The array formula allows the IF function to evaluate multiple conditions and return multiple results.

Using the Paste Special command with the Subtract operation

• Set a specific value that you want to subtract from the whole column. For example, 40.
• Copy the value to the clipboard by pressing Ctrl + C or using the Copy command from the Home tab.
• Select the column where you want to perform the subtraction. For example, column A.
• Go to the Home tab and click Paste from the Clipboard group.
• Select Paste Special from the Paste option.
• A Paste Special dialog box will appear. Select All in the Paste option and select Subtract from Operation and finally, click on OK.

This will subtract the value 40 from each cell in column A and replace the original values with the results. This method does not use a formula, so the results are static and will not change if you change the value 40.