How to Subtract Two Columns in Excel

To subtract two columns in Excel, type a formula such as =B2-C2 in the first row of an empty column, press Enter, and copy it down so every row gets its own difference. In Microsoft 365, Excel 2021 and Excel 2024 you can also enter one formula, =B2:B6-C2:C6, and let the results spill down. These steps cover Excel on Windows and Mac.

A few terms and what you need first

You need two columns of numbers that line up row by row, plus one empty column for the answers. These terms come up below.

  • Minus operator: the hyphen key, -. Excel has no SUBTRACT function, so subtraction formulas use this operator.
  • Cell reference: a column letter plus a row number, like B2. Results update when those cells change.
  • Relative reference: the default kind. When you copy =B2-C2 one row down, it becomes =B3-C3 on its own.
  • Absolute reference: a reference with dollar signs, like $F$1, that stays pointed at the same cell wherever you copy it.
  • Fill handle: the small square at the lower-right corner of the selected cell. Dragging it copies the formula into neighboring cells.
  • Spill: what happens when one formula returns several values and Excel places them in the cells below it.

Decide which column is the starting amount. =B2-C2 and =C2-B2 give the same size of answer with opposite signs. If basic subtraction of single cells is new to you, the beginner walkthrough of the Excel minus formula covers that first step.

Subtract one column from another, row by row

This method works in every current version of Excel on Windows and Mac. The example assumes the starting amounts are in column B, the amounts to take away are in column C, row 1 holds headers, and the data runs from row 2 to row 6.

  1. Click cell D2, the first empty cell beside your data.
  2. Type an equals sign, =. Every formula must start with it; without it, Excel treats what you type as an ordinary entry and does no math.
  3. Click cell B2 (or type B2), type a minus sign, then click C2. The cell now shows =B2-C2.
  4. Press Enter (Return on a Mac). D2 shows the difference for that row.
  5. Click D2 again and point at the lower-right corner until the pointer turns into a plus sign.
  6. Drag down to D6 and release. Each row now has its own formula, such as =B3-C3 in D3 and =B6-C6 in D6.

Prefer the keyboard? Select D2 through D6 with the formula in D2, then press Ctrl+D to fill down. Home > Fill > Down does the same. If you don’t see a fill handle at all, it has been switched off: on Windows go to File > Options > Advanced and check Enable fill handle and cell drag-and-drop; on a Mac go to Excel > Preferences > Edit and check Allow fill handle and cell dragging and dropping.

One spilling formula for the whole column (Microsoft 365, Excel 2021 and 2024)

Newer versions of Excel support dynamic arrays, which let one formula return a whole column of answers. Microsoft lists dynamic array and spill behavior for Excel for Microsoft 365, Excel 2024 and Excel 2021 on both Windows and Mac.

  1. Make sure the cells from D2 down to D6 are empty. Anything in the way blocks the result.
  2. Click D2 and type =B2:B6-C2:C6.
  3. Press Enter. Excel subtracts each pair of cells and fills D2 through D6 with the answers.
  4. Click any cell in D3 through D6. A border appears around the whole spill range, and the formula looks greyed out in the formula bar because only D2 can be edited.

Both ranges need the same number of rows. Spilled formulas don’t work inside an Excel table, and in versions without dynamic arrays the formula may open as an older-style array formula that doesn’t resize. If anyone editing the file uses an older version, stick with the row-by-row method.

Take the same number away from every row

Sometimes the second “column” is really one value, such as a flat fee, that comes off every row. Put it in its own cell (F1 here) and lock the reference so it doesn’t slide down as you copy.

On Windows

  1. Type the value to subtract in F1.
  2. In E2, type =B2-F1 but don’t press Enter yet.
  3. With the cursor on F1 in the formula, press F4. It changes to $F$1. Pressing F4 again cycles through the other reference types, so stop when both dollar signs are there.
  4. Press Enter, then drag the fill handle down to E6.

On a Mac

  1. Type the value to subtract in F1.
  2. In E2, type =B2-F1.
  3. Click F1 in the formula bar and press Command+T until it reads $F$1. You can also type the dollar signs yourself.
  4. Press Return, then drag the fill handle down to E6.

Every row now subtracts the same cell, and changing F1 updates the entire column at once. If you need to take several cells away from one starting value instead, the guide to subtracting multiple cells at once shows the chained and SUM-based versions.

Let an Excel table extend the formula for new rows

If you add rows often, format the data as a table. A formula entered in a table column becomes a calculated column that Excel fills into every row, including rows added later.

  1. Click any cell in your data and press Ctrl+T on Windows to open the Create Table dialog. Confirm the range, keep My table has headers checked if row 1 has labels, and click OK. On a Mac (or on Windows without the shortcut), go to the Home tab, choose Format as Table and pick a style to open the same dialog.
  2. Type a header, such as “Difference,” in the first empty cell to the right of the table. The table grows to include the new column.
  3. In the first data cell of that column, type =, click the first cell in the starting column, type -, and click the first cell in the column to subtract.
  4. Press Enter. Excel fills the formula down the column for you.

Instead of =B2-C2, Excel usually writes a structured reference that uses the header names, for example =[@Budget]-[@Spent]. The @ means “this row.” It reads more clearly than cell addresses.

Replace the original numbers with Paste Special

Paste Special has a Subtract operation that takes the copied values away from the cells you paste onto. It leaves no formulas behind. Copy the column you want to take away, then paste onto the column you want to reduce.

Warning: this overwrites the destination cells. Copy the sheet or the destination column somewhere safe first, because once you save and close the file you can’t undo the change.

Paste Special on Windows

  1. Select C2:C6, the values to take away, and press Ctrl+C.
  2. Select B2, the top cell of the column to reduce.
  3. Go to Home > Paste > Paste Special, or press Ctrl+Alt+V.
  4. Under Paste, choose Values so the source formatting isn’t copied. Under Operation, choose Subtract.
  5. Click OK. Column B now holds the differences as plain numbers.

Paste Special on a Mac

  1. Select C2:C6 and copy it from the Home tab.
  2. Select B2.
  3. On the Home tab, click the arrow next to Paste and choose Paste Special.
  4. Pick Values, then Subtract under Operation, and click OK.

To take one number off a whole column this way, copy a single cell holding that number and paste it onto the whole range instead of a matching column. The Paste Special options reference describes the other operations (Add, Multiply, Divide) and Skip blanks, which leaves destination cells alone when the copied cell is empty.

Picking the right method

SituationBest methodResults update when data changes?
Any version, any file you might share=B2-C2 copied downYes
Microsoft 365, Excel 2021 or 2024, fixed-size data=B2:B6-C2:C6 spilledYes
One fee or target taken off every row=B2-$F$1 copied downYes
Rows added regularlyExcel table with a calculated columnYes, including new rows
One-off change, no formulas wantedPaste Special > SubtractNo

Worked example: budget versus spending

Here is a small fictional team budget. Column B holds each person’s budget, column C what they spent, and column D uses =B2-C2 copied down to show what is left.

RowA: NameB: BudgetC: SpentD: Remaining (=B-C)
2Alex1,200950250
3Jordan800845-45
4Priya1,5001,5000
5Sam640412228
6Morgan9751,010-35

Jordan and Morgan went over budget, so their results are negative. That is correct: the second column was larger. Priya spent exactly her budget, so her row shows 0.

Getting one total difference

To see how far the whole team is under or over, you don’t need the helper column at all. Any of these formulas returns 398 for the data above:

=SUM(B2:B6)-SUM(C2:C6)      returns 398 (5,115 - 4,717)
=SUMPRODUCT(B2:B6-C2:C6)    returns 398
=SUM(D2:D6)                 returns 398 (adds the row results)

SUMPRODUCT accepts a minus sign between ranges and then adds up the results, but both ranges must be the same size, or the formula returns an error instead of a total.

Subtracting more than one column

If spending were split across columns C, D and E, use =B2-C2-D2-E2 or the shorter =B2-SUM(C2:E2).

When the answer looks wrong

Most problems come from the data rather than the formula.

#VALUE! appears

One of the cells holds text instead of a number. Numbers stored as text are usually left-aligned and often show a small green triangle in the corner. Select those cells, click the warning button that appears, and choose Convert to Number. Another fix: type 1 in a blank cell, copy it, select the text numbers, and use Paste Special > Multiply. The support article on fixing numbers formatted as text also suggests TRIM or CLEAN to remove stray spaces and hidden characters first.

On Windows there is a rarer cause: if even a fresh test like =B1-A1 returns #VALUE!, the Windows list separator may be set to a minus sign. Open the Region settings in Control Panel, choose Formats > Additional settings, and change the List separator to a comma. The #VALUE! troubleshooting page walks through that fix and the text-date problem.

#SPILL! appears

A spilling formula like =B2:B6-C2:C6 can’t write its results because something already sits in the cells below it, or the formula is inside a table. Clear or move the blocking cells and the results appear. Inside a table, switch to a normal row formula.

The formula shows as text, or every row shows the same answer

  • If you see B2-C2 written out in the cell, the equals sign is missing, or the cell was formatted as Text before you typed. Set the cell format to General and enter the formula again.
  • If every row shows the first row’s answer, calculation may be set to manual. On Windows, go to File > Options > Formulas and set Workbook Calculation to Automatic; on a Mac, go to Excel > Preferences > Calculation and select Automatic.
  • If every row subtracts the same cell when it shouldn’t, you have dollar signs where you don’t want them. Remove them so the reference is relative again.

Signs are backward, or blanks give odd results

If positive results come out negative, the columns are swapped in the formula; change =C2-B2 to =B2-C2. If you only care about the size of the gap, wrap it in ABS, which removes the sign: =ABS(B2-C2). Empty cells are normally treated as zero, so a row with nothing in column C simply repeats the column B value. To leave the result blank until both cells have data, use =IF(OR(B2="",C2=""),"",B2-C2).

Dates and times

Subtracting two date columns returns a number of days, but Excel may format the answer as a date. Change the result cells to General or Number to see the day count. Dates typed as text cause #VALUE!, as described above. For day counts, working days and time sheets, see the separate walkthroughs on finding the days between two dates and subtracting times.

Check the results and undo mistakes

  • Spot-check a row by hand. Pick one row, do the math yourself, and compare. In the example, 1,200 minus 950 should be 250.
  • View the formulas. Press Ctrl+` (the grave accent key) on Windows, or select Formulas > Show Formulas, to see every formula in place of its result. Check that the row numbers change down the column.
  • Undo right away. Press Ctrl+Z on Windows or Command+Z on a Mac to reverse a fill, a spill or a Paste Special while the file is still open.
  • Freeze results if needed. To keep the answers but drop the formulas (before deleting a source column, for example), copy the difference column and use Paste Special > Values on top of it.

Other things readers ask about subtracting columns

Can I subtract columns that are on different sheets?

Yes. Put the sheet name and an exclamation point in front of the reference, as in =March!B2-February!B2. If the sheet name contains a space, wrap it in single quotes: ='Q1 Sales'!B2-'Q1 Costs'!B2. Clicking the cells on each sheet while building the formula fills this in for you.

How do I show the difference as a percentage?

Divide the difference by the starting value and format the result as a percentage: =(C2-B2)/B2 shows the change from B to C. For Alex in the example, =(950-1200)/1200 is about -20.8%, meaning spending came in about 21% below budget. The parentheses matter, because Excel divides before it subtracts. To take a percentage off a column of prices instead, read up on reducing a number by a percentage.

Once your difference column is working, try adding a header and turning the range into a table so next month’s rows calculate on their own.

Matthew Burleigh

Matthew Burleigh has been writing technology tutorials online since 2008. He holds Bachelor's and Master's degrees in Computer Science from Arizona State University and has worked in IT since 2003, from small-business IT support to IT management and consulting.