Excel Column Subtraction Formula: Examples and Tips

Formulas & functions · Updated November 18, 2026

To subtract values in one Excel column from the matching values in another, enter =A2-B2 in the first result cell, then fill the formula down. For example, if column A contains amounts to start with and column B contains amounts to deduct, the formula returns the difference for that row.

The basic column subtraction formula

Excel uses the minus sign for subtraction; there is no built-in worksheet function named SUBTRACT. The order matters: the value on the right of the minus sign is taken away from the value on the left. Put the result in a separate column so the source values remain available.

With starting amounts in A2:A10 and amounts to deduct in B2:B10, enter this formula in C2:

=A2-B2

If A2 is 125 and B2 is 38, C2 displays 87. If the values are reversed, the result is negative: 38 minus 125 is -87. A negative result is not an error; it means the second value is larger than the first.

Make sure both references point to the intended row. A formula in C2 should normally compare A2 with B2, not A2 with B3. This row-by-row comparison is what people usually mean by subtracting one column from another.

Fill the formula down the column

After entering the formula in the first result cell, copy it to the remaining rows. Excel adjusts ordinary cell references as it fills: the formula in C3 becomes =A3-B3, and the one in C4 becomes =A4-B4. These are relative references, which is why one formula can handle many rows.

  1. Enter the formula in the first row of the results column, such as C2.
  2. Select C2 and use the small square at the lower-right corner of the selection, called the fill handle.
  3. Drag the fill handle down to the last data row. If the neighboring column has a continuous block of data, double-clicking the fill handle can fill down alongside it.

You can also select the destination range and use Home > Fill > Down. On Windows, Ctrl+D fills selected cells downward; on Mac, the corresponding shortcut is Control+D. Check the last few rows after filling, especially if there are blank rows or the source columns have different lengths.

Worked examples and useful variations

Suppose A2:A4 contains 125, 90, and 60, while B2:B4 contains 38, 100, and 12. With those values in the stated ranges, enter the following in C2 and fill it through C4:

=A2-B2

The results are 87, -10, and 48. The middle result is negative because 100 is greater than 90. This example also shows why it is useful to keep the minus sign in the intended order rather than subtracting the larger column from the smaller one by habit.

If every row also needs the same fixed deduction, place that deduction in one cell and lock its reference with dollar signs. With starting amounts in A2:A10, deductions in B2:B10, and a shared adjustment of 5 in F1, enter this in C2 and fill down:

=A2-B2-$F$1

For A2 equal to 125, B2 equal to 38, and F1 equal to 5, the result is 82. The dollar signs keep the formula pointed at F1 as it is copied, while A2 and B2 continue changing by row.

Subtract column totals instead of individual rows

Sometimes the goal is to compare the overall totals, not calculate a difference for every record. Summing each column first and subtracting the sums gives one result. This is useful for comparing total expenses against total revenue, or planned values against actual values.

With values in A2:A5 and B2:B5, enter this formula in any empty cell outside those ranges:

=SUM(A2:A5)-SUM(B2:B5)

For example, if the first range totals 270 and the second totals 70, the formula returns 200. This is equivalent to subtracting the second total from the first, but it does not show which individual rows account for the difference. Use row formulas as well when you need to investigate each record.

A common misconception is that subtracting column totals always gives the same kind of information as subtracting corresponding rows. The grand difference may be the same as the sum of all row differences when both ranges contain the same records, but the single total hides positive and negative differences that may cancel each other out.

Subtract matching ranges with a dynamic array

In Excel for Microsoft 365, you can calculate several row differences with one formula by using a finite range. With values in A2:A6 and B2:B6, enter this formula in C2 and leave the cells below C2 empty:

=A2:A6-B2:B6

Excel returns a result for each corresponding pair and spills the results into C2:C6. For example, if the paired values are 20 and 8, 15 and 5, 11 and 13, 30 and 12, and 9 and 4, the results are 12, 10, -2, 18, and 5. If any cell in the intended spill area is occupied, Excel may show a #SPILL! error; clear the obstructing cells or use the fill-down approach instead.

Use a defined range rather than whole-column references for this method. A whole-column array operation can attempt to return more than a million results, which is rarely useful and may cause a spill problem. Dynamic arrays are available in Microsoft 365 and Excel 2021, but older perpetual editions may require filling a regular formula down.

What happens with blanks, zeros, and text

When a formula refers to an empty cell in a basic arithmetic operation, Excel treats that blank reference as zero. So if A2 contains 50 and B2 is genuinely empty, the difference is 50. If both referenced cells are empty, the formula generally displays 0. This can make an unfinished row look like a real zero result, so consider whether your worksheet should leave results blank until both inputs exist.

If you want a result only after both input cells contain something, use an IF formula. With input values in A2 and B2 and the result in C2, enter:

=IF(OR(A2="",B2=""),"",A2-B2)

This displays an empty result if either input is blank; otherwise, it subtracts B2 from A2. The distinction matters if a cell contains a formula that returns an empty string: it may look blank, but it is not the same as a truly empty cell for every Excel operation.

Text in a number column can cause a #VALUE! error if Excel cannot interpret it as a number. Imported data may contain spaces, currency symbols stored as text, or words such as Pending. Check the source cells, convert valid numeric text to numbers where appropriate, and keep explanatory text out of calculation ranges. Do not replace missing figures with zero unless zero is actually the intended value.

Use subtraction in an Excel table

Excel tables can make a row-by-row subtraction formula easier to read and maintain. First make sure the data has clear column headings, select it, and use Insert > Table. If the table headings are Revenue and Cost, enter this formula in the result column of the table:

=[@Revenue]-[@Cost]

The formula subtracts Cost from Revenue for the current row. Excel normally fills the calculated-column formula down the table and applies it to new rows added to that table. The structured references use the heading names, so they are less likely to become confusing if columns move. If a heading changes, Excel generally updates references to that heading automatically.

Use the exact heading text from your table. A reference to a column name that does not match the actual heading will not work as intended. Tables are especially useful for ongoing lists that gain rows over time; for a small, fixed worksheet, ordinary references such as A2 and B2 are perfectly suitable.

Subtract dates and understand the displayed result

Excel stores dates as serial numbers, so subtracting an earlier date from a later date returns the elapsed number of days. With a start date in A2 and an end date in B2, enter this formula in C2:

=B2-A2

If A2 is 1 January and B2 is 10 January of the same year, the result is 9 days. The order is important: end date minus start date gives a positive duration when the dates are in chronological order. If the result appears as another date rather than a day count, change the result cell’s number format to General or Number using Home > Number Format.

This simple calculation counts the difference between dates, not necessarily the number of working days or the number of days including both endpoints. For business-day calculations or inclusive counting, the required logic is different; decide what the result should mean before choosing a formula.

Common errors and how to fix them

  • Wrong subtraction order: Review which column is the starting value and which is the amount to deduct. A negative result may be correct, but it can also reveal reversed references.
  • Unexpected #VALUE!: Inspect both source cells for text, spaces, or labels. Convert values that are genuinely numbers, and handle nonnumeric entries separately.
  • Formula does not update down the rows: Check that the row references are relative. Dollar signs in a reference lock a column, row, or both; they are useful for fixed cells but usually unnecessary for the two changing row inputs.
  • Zeros appear for unfinished rows: A blank input is treated as zero in ordinary subtraction. Use a blank-check formula if incomplete rows should not show a result.
  • #SPILL! from a range formula: Clear cells in the intended output area, or put the calculation in a regular formula and fill it down.
  • Results look like dates or currency: The calculation may be correct while the number format is misleading. Select the result cells and choose an appropriate format from Home > Number Format.

Also check that the inputs use compatible units. Subtracting dollars from dollars is meaningful; subtracting a quantity from a price may produce a number without a useful interpretation. Excel calculates the arithmetic but cannot determine whether the columns represent comparable measures.

Formula versus Paste Special

A formula is usually the better choice when you want a transparent, updateable calculation. If either input changes, the formula result recalculates, and the original values remain in place. For an ongoing worksheet, that makes it easier to audit and correct the work.

Excel also has a Paste Special operation that can subtract a copied value from a selected range and replace the selected values. In Microsoft 365 for Windows, copy the value to subtract, select the destination cells, then choose Home > Paste > Paste Special > Paste Special and select Subtract under Operation. This is a bulk edit, not a continuing formula: it changes the destination values, so keep an untouched copy of the data first. On Mac, Paste Special is available through Excel’s menus, though the menu arrangement can differ. Use a formula if preserving the source values or showing the calculation matters.

Frequently Asked Questions

What is the formula to subtract one Excel column from another?

With corresponding values in A2 and B2, enter =A2-B2 in the result cell on row 2, then fill it down. Excel adjusts the row references as the formula is copied.

Can I subtract two entire columns with one formula?

For a manageable number of rows in Microsoft 365 or Excel 2021, use matching finite ranges such as A2:A100 and B2:B100 in a dynamic array formula. Avoid whole-column array references because they can try to return an extremely large spill range.

Why does my subtraction formula show a negative number?

A negative answer means the value being subtracted is larger than the starting value. Confirm the order of the cell references, then decide whether the negative result accurately represents your data.

How do I subtract but leave the result blank when an input is missing?

With inputs in A2 and B2, use =IF(OR(A2="",B2=""),"",A2-B2). It returns an empty display when either input is blank and calculates the difference once both are present.

Does Excel have a SUBTRACT function?

No. Use the minus operator between cell references or numbers. For multiple values, you can use repeated minus signs or subtract one sum from another, depending on whether you need row-level results or a total difference.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.