Excel Column Minus Column Formula: Examples and Fixes

Formulas & functions · Updated November 18, 2026

If your values are in A2:A100 and B2:B100, enter =A2-B2 in C2 to subtract the value in column B from the value in column A on that row. Copy the formula down column C to calculate the difference for each matching row.

How the basic subtraction formula works

Excel uses the minus sign, or hyphen, for subtraction. In a formula, the cell before the minus sign is the value being reduced, and the cell after it is the value to subtract. So A2 minus B2 is not the same calculation as B2 minus A2. The order matters whenever the result could be negative.

For example, suppose A2 contains 125 and B2 contains 38. With those values in A2 and B2, the formula =A2-B2 entered in C2 returns 87. If A3 contains 90 and B3 contains 105, the same pattern in C3 returns -15. A negative number simply means the second-column value is larger; it is not automatically an error.

Put the result in a separate column so the original data stays intact. A useful heading for column C might be Difference, Balance, or Remaining, depending on what the numbers represent. Excel recalculates the result when either input cell changes.

Subtract two columns row by row

For ordinary lists, subtract corresponding cells: row 2 from row 2, row 3 from row 3, and so on. First check that the records line up. If A contains this month’s sales and B contains last month’s sales, each row should refer to the same product or location in both columns. A correct formula cannot compensate for mismatched records.

  1. Enter a heading such as Difference in C1.
  2. Click C2, the first result cell.
  3. With the first values in A2 and B2, type =A2-B2 and press Enter.
  4. Confirm the result against the two input values, then fill the formula down for the remaining rows.

When A2 is 125 and B2 is 38, C2 should show 87. The references are relative: when the formula is copied down one row, Excel adjusts them to A3 and B3. This is why you normally enter the formula once rather than typing a different row number into every formula.

Subtract ranges with a dynamic array

In current Microsoft 365 versions of Excel for Windows, a range-to-range formula can return multiple row differences at once. This uses dynamic arrays: one formula entered in a single cell spills its results into the cells below. It is an alternative to filling a single-row formula down, not a different subtraction rule.

With values in A2:A6 and B2:B6, enter =A2:A6-B2:B6 in C2. Excel returns five results in C2:C6, subtracting each value in B from the value in A on the same row. For instance, if A2:A6 contains 125, 90, 70, 40, 15 and B2:B6 contains 38, 105, 20, 12, 10, the results are 87, -15, 50, 28, and 5.

Leave the cells where the results need to spill empty. If something is already in the way, Excel shows a #SPILL! error rather than overwriting it. Use the row-by-row formula and fill method if you need to control each result cell individually, or if your Excel version does not support dynamic arrays. Avoid attempting to subtract two entire columns in one cell: a full Excel column contains more than a million rows, creating an impractical spill range and likely a spill obstruction.

Subtract column totals instead of matching rows

Sometimes the question is not what each row’s difference is, but how much one column totals compared with another. In that case, subtract the two sums. The formula below assumes the figures to compare are in A2:A100 and B2:B100, with the result placed in another cell:

=SUM(A2:A100)-SUM(B2:B100)

If the first range totals 4,250 and the second totals 3,900, the formula returns 350. This gives the same result as adding the row-by-row differences when both calculations cover the same rows and contain suitable numeric values. It does not show which individual rows contributed to the difference, so use row-level results when you need to investigate specific items.

Keep the ranges consistent. Comparing A2:A100 with B2:B99 omits a row from one side, and a total can look plausible even when the ranges do not match. If new records are added regularly, an Excel Table can make formulas easier to maintain, as described below.

Subtract one column from a fixed value

A related task is subtracting each row’s amount from one fixed number, such as a shared budget or target. If you use a normal relative reference to the fixed cell, that reference changes when you copy the formula down. Put dollar signs around the fixed cell reference to make it absolute.

With row amounts in A2:A100 and a fixed target in D1, enter =D$1-A2 in C2 if the formula may be copied down but could also be copied across. The row number in D$1 stays fixed while the column can adjust. If the formula will only be filled down, =$D$1-A2 keeps both the D column and row 1 fixed. For example, with D1 equal to 500 and A2 equal to 125, the result is 375.

Choose the subtraction order to fit the question. Target minus amount answers how much remains; amount minus target shows how far the amount is above or below the target. Those calculations have opposite signs, so label the result column clearly.

Fill the formula down efficiently

For a short list, drag the fill handle—the small square at the lower-right corner of the selected cell—down through the rows that need results. Excel updates relative references as it fills. Double-clicking the fill handle can fill down alongside a neighboring column of continuous data, but check where it stops, especially if there are blank rows.

For a larger range, enter the formula in the first result cell, select that cell and the cells below it, then use Home > Fill > Down. On Windows, Ctrl+D also fills the selected range downward. Excel for Mac has a different shortcut environment, so use Home > Fill > Down if the keyboard shortcut differs on your setup. If the result column is an Excel Table calculated column, a formula entered in one row usually propagates through that column automatically.

After filling, spot-check the first, middle, and last rows. Click a result cell and look at the formula bar to confirm that its row references match the data beside it. This catches common mistakes such as a formula that skipped a row, stopped too early, or was copied over a header.

Blanks, text, and error results

A blank cell referenced in ordinary arithmetic is treated like zero. Thus, if A2 is 50 and B2 is blank, subtracting B2 gives 50. If A2 is blank and B2 is 12, the result is -12. This can be useful when a missing value genuinely means zero, but it can also disguise incomplete data. Decide whether blank entries should count as zero or should be flagged for review.

Numbers that look like numbers but are stored as text can behave differently. A direct subtraction involving text may return #VALUE!. This can happen after importing data, especially when cells contain spaces, currency symbols typed as text, or apostrophes used to force text entry. Check the cell format and the actual contents, and convert valid numeric text to numbers before relying on the result. A green error indicator or the VALUE stored as text warning may help identify the issue.

Other errors in either source cell, such as #N/A or #VALUE!, generally carry through to the subtraction result. Rather than hiding every error, inspect the source cells first. If you use an error-handling formula, be aware that it can make bad source data less visible. For reporting, it is often better to flag rows needing attention and correct the input than to replace every problem with zero.

Also check number formats. A result may be correct but displayed with too few decimal places, or shown as a date because the result cell inherited a date format. Select the result cells and choose an appropriate format in Home > Number. Formatting changes how a value is displayed, not the underlying subtraction.

Use an Excel Table for growing data

Tables are useful when rows are regularly added, because formulas and formatting can extend as the table grows. To create one, select the data and choose Insert > Table, then confirm whether the data has headers. Give the relevant columns clear names, such as Budget and Actual, and add a Difference column.

With an Excel Table whose columns are named Budget and Actual, enter =[@Budget]-[@Actual] in the Difference column. The formula subtracts the Actual value from the Budget value in the current row. Excel uses the table’s structured references instead of ordinary cell addresses, and normally fills the calculated column for existing rows. When you add table rows, the calculated-column formula can extend to them as well.

Structured references are easier to read in a table, but they do not change the logic: the formula still subtracts one matching row’s value from another. If column names contain spaces, Excel handles them in structured references; using simple names can make formulas easier to scan. If a table formula does not propagate as expected, check Excel’s calculated-column behavior and whether the new row is actually part of the table.

Difference versus percentage change

Subtracting columns produces an absolute difference, measured in the same units as the original numbers. A difference of 25 could mean 25 dollars, 25 items, or 25 units of another measure. Percentage change expresses the difference relative to a baseline, so it answers a different question and requires a clear choice of which column is the baseline.

If A2 is the new value and B2 is the old value, enter =(A2-B2)/B2 in C2 to calculate the change relative to B2. For example, with A2 equal to 125 and B2 equal to 100, the result is 0.25; format C2 as a percentage to display 25%. If B2 is zero, the formula cannot calculate a percentage because it divides by zero. Choose the baseline deliberately, since reversing the columns changes the percentage and its interpretation.

Frequently Asked Questions

What formula subtracts one Excel column from another?
With the first pair of values in A2 and B2 and the result in C2, use =A2-B2. Fill the formula down to subtract matching rows.

Can I subtract two entire columns in one formula?
A full-column calculation would involve over a million rows and may need an equally large spill area. Use matching bounded ranges, such as A2:A100 and B2:B100, or enter a single-row formula and fill it down.

Why does my subtraction formula show a negative number?
The value being subtracted is larger than the starting value. For example, 90 minus 105 is -15. If that sign is unexpected, confirm the order of the cell references and that the two rows represent matching records.

Does Excel treat blank cells as zero in subtraction?
In ordinary cell arithmetic, a referenced blank acts like zero. That may be appropriate for genuinely empty amounts, but if blank means missing information, check the row instead of assuming it represents zero.

How do I subtract column totals rather than row values?
With the values to compare in A2:A100 and B2:B100, use =SUM(A2:A100)-SUM(B2:B100). Make sure both ranges cover the same records.

What is the difference between subtraction and percentage change?
Subtraction gives the absolute amount of change. Percentage change divides that difference by a chosen baseline, so it expresses the change relative to the baseline and is undefined when that baseline is zero.

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.