How to Subtract Multiple Cells in Excel

To subtract multiple cells in Excel, total the cells you want to take away with SUM and subtract that total from your starting cell: =B2-SUM(B3:B7). For two or three cells, a plain chain such as =B2-B3-B4 works just as well. These steps apply to Excel for Microsoft 365, Excel 2024, Excel 2021, Excel 2019 and Excel 2016 on Windows, with Mac differences noted where the keys or menus change.

A few terms before you start

Excel has no SUBTRACT function. Every subtraction uses the minus sign (-), which Excel calls an arithmetic operator, either on its own or next to SUM. If you have never written a subtraction formula at all, the basics are covered in this beginner walkthrough of subtracting in Excel. The rest of this page assumes you know that every formula starts with an equal sign (=).

  • Starting value: the number you subtract from (math books call it the minuend). It is usually a total, a budget or a balance.
  • Cell reference: an address such as B3 that points Excel at a cell instead of a typed number, so the result updates when the cell changes.
  • Range: a block of neighboring cells written with a colon, such as B3:B7 (every cell from B3 down to B7).
  • Absolute reference: a reference with dollar signs, such as $D$2, that stays pointed at the same cell when you copy the formula. A plain reference like D2 is relative and shifts as you copy.

Subtract a range of cells from one cell with SUM

This is the method to reach for first. It keeps the formula short no matter how many cells you subtract, and it handles stray text better than a long chain of minus signs. Suppose your starting value is in B2 and the amounts to take away are in B3 through B7.

  1. Click an empty cell where you want the answer, such as B8. Keep it outside the range you are subtracting, or Excel will warn you about a circular reference.
  2. Type = and then click B2 (or type B2). This is the starting value.
  3. Type a minus sign, then SUM(.
  4. Drag across B3 to B7 to select the cells to subtract, or type B3:B7. Close the parenthesis with ). The formula bar now reads =B2-SUM(B3:B7).
  5. Press Enter (Return on a Mac). Excel adds B3 through B7 together and subtracts that total from B2 in one go, and the remainder appears in the cell.

The cells you subtract don’t have to sit next to each other. Separate them with commas inside SUM, for example =B2-SUM(B3,D5,F8), or mix ranges and single cells, such as =B2-SUM(B3:B7,D3). The SUM function reference allows up to 255 arguments, and a whole range counts as just one of them.

Other ways to take away several cells

SUM covers most jobs, but the shape of your data sometimes points to a different formula. Use this quick guide to pick one.

Your situationBest methodExample
Two to four cells to subtractChain of minus signs=B2-B3-B4
Many cells subtracted from one starting valueStarting value minus SUM=B2-SUM(B3:B7)
Expenses already entered as negative numbersOne SUM over everything=SUM(B2:B7)
One fixed cell taken off every rowAbsolute reference, copied down=A2-$D$2
Every row subtracted from one fixed cellAbsolute reference on the other side=$D$2-A2
One group total minus another group totalSUM minus SUM=SUM(B2:B7)-SUM(C2:C7)
Change the original numbers, no formulas left behindPaste Special, SubtractCopy one cell, paste onto a range

Chain minus signs for a handful of cells

Type each reference with a minus sign between them: =B2-B3-B4-B5. Excel works left to right, so B3 comes off B2, then B4 comes off that result, and so on. This is easy to read when there are only a few cells. It gets awkward past five or six, and if you later insert a row in the middle of your list, the chain won’t pick it up. A SUM range expands automatically when you insert rows inside it.

Add negative numbers with a single SUM

Adding a negative number is the same as subtracting a positive one. If your sheet already stores money going out as negative values (common in checkbook-style registers), you don’t need a minus sign at all. =SUM(B2:B7) adds the income and the negative expenses together and returns what is left. Excel’s own example, =SUM(100,-32,15,-6), returns 77. If the result is off, check that every outgoing amount really has its minus sign; a value shown in parentheses under the Accounting format is still negative.

Subtract one cell from a whole column

Say a $15 service fee sits in D2 and you want to take it off every price in A2:A6. Write the formula once with an absolute reference and copy it down. The same idea, applied column against column, is explained in the walkthrough for subtracting one column from another.

On Windows:

  1. Click B2 and type =A2-D2, but don’t press Enter yet.
  2. Click inside D2 in the formula bar and press F4. It changes to $D$2. Pressing F4 again cycles through the mixed forms, so stop when both dollar signs appear.
  3. Press Enter.
  4. Select B2 and drag the fill handle (the small square at its lower-right corner) down to B6, or select B2:B6 and press Ctrl+D to fill down.

On a Mac:

  1. Click B2 and type =A2-D2.
  2. Click inside D2 in the formula bar and press Command+T until it reads $D$2. You can also type the dollar signs yourself.
  3. Press Return.
  4. Rest the pointer on the lower-right corner of B2 until it turns into a plus sign, then drag down to B6.

Each copy now reads =A3-$D$2, =A4-$D$2 and so on: the price reference moves, the fee stays put. To do the reverse (subtract every row from one fixed number, such as finding how far each score falls short of a target), flip the sides: =$D$2-A2. Full details on reference types are in the Excel guide to relative, absolute and mixed references.

Let a single formula spill down (Microsoft 365, Excel 2021 and 2024)

Excel for Microsoft 365, Excel 2021 and Excel 2024 support dynamic arrays, which means a formula can return several results that “spill” into the cells below. Type =A2:A6-D2 in B2 and press Enter, and Excel fills B2:B6 with each price minus the fee. No dollar signs and no dragging are needed. Two caveats: the cells the results need must be empty, and only the top cell (B2) can be edited. If your copy of Excel doesn’t spill, the result may show as a single value or a legacy array formula; use the fill-handle method above instead.

Subtract one group total from another

When both sides are lists, total each list and subtract: =SUM(B2:B7)-SUM(C2:C7). This gives, for example, total sales minus total refunds, without a helper column. The two ranges don’t have to be the same size.

Overwrite values with Paste Special

Paste Special can subtract a copied value from every cell in a range and leave plain numbers behind, with no formulas. That is useful for a one-time correction, but it changes your original data. Copy the column to a spare sheet first if you might need the old numbers. Every option in the dialog is listed in the Excel paste options reference.

On Windows:

  1. Type the amount to subtract in an empty cell, for example 15 in D2, and copy it with Ctrl+C.
  2. Select the cells to change, such as A2:A6.
  3. Open the Paste Special box with Ctrl+Alt+V, or go to Home > Paste > Paste Special.
  4. Under Operation, choose Subtract. Choose Values under Paste if you don’t want D2’s formatting carried over.
  5. Click OK. Each selected cell drops by 15. Delete the helper value in D2.

On a Mac, copy the cell, select the target range, then go to Home, click the arrow next to Paste, choose Paste Special, pick Subtract and click OK. In many versions, Control+Command+V also opens the dialog. Copying a range of the same size instead of a single cell subtracts each copied value from its matching cell.

Worked example: Priya’s monthly budget

Priya wants to know how much of her take-home pay is left after five bills. Her sheet looks like this:

RowA (Item)B (Amount)
2Take-home pay4,200
3Rent1,450
4Groceries520
5Utilities185
6Transit140
7Phone65
8Left over=B2-SUM(B3:B7)

SUM(B3:B7) adds the bills to 2,360, and 4,200 minus 2,360 leaves 1,840 in B8. The chain version, =B2-B3-B4-B5-B6-B7, gives the same 1,840. The difference shows up next month: when Priya inserts a new row for a streaming subscription between Transit and Phone, the SUM range grows to B3:B8 on its own and the formula moves down to B9, while the chain version would ignore the new bill.

Her friend Jordan tracks the same budget but types bills as negatives (-1,450, -520 and so on). For Jordan, =SUM(B2:B7) returns 1,840 directly.

When the answer looks wrong

  • #VALUE! error. A cell used with the minus sign contains text, a space or a hidden character. The minus operator can’t handle text, but SUM skips text values, so =B2-SUM(B3:B7) is more forgiving than a chain. If the starting cell itself holds text, even SUM won’t save you: retype the number or clear the spaces with Find & Replace. The ISTEXT function helps find cells stored as text. On Windows, a list separator set to the minus sign in your region settings can also trigger this error.
  • Circular reference warning. The answer cell is inside its own range, for example =B2-SUM(B3:B8) typed into B8. End the range one row above the formula.
  • The result has the wrong sign. Order matters: =B2-B3 and =B3-B2 give the same number with opposite signs. Put the starting value first. A negative result is correct when you subtract more than you started with.
  • The result is too small or too large. A number stored as text is silently skipped by SUM, so it is never subtracted. Look for a small green triangle in the cell’s top-left corner, select the flagged cells, click the warning icon and choose Convert to Number.
  • Every copied row points at the wrong cell. The fixed cell wasn’t locked, so it slid down as you copied. Add the dollar signs ($D$2) and fill down again.
  • #SPILL! error. Something is sitting in the cells a spill formula needs. Clear or move that data and the results appear.
  • The answer shows as a date or a weird decimal. The result cell inherited a date or time format. Set it to General or Number on the Home tab. Subtracting real dates and times follows its own rules, since Excel stores them as serial numbers.

Confirm the result and undo a mistake

Select the cells you are subtracting and glance at the status bar at the bottom of the window: it shows their sum. Your starting value minus that figure should match the formula. To see every formula on the sheet at once, press Ctrl+` (the grave accent key, usually left of the 1 key) or click Show Formulas on the Formulas tab; repeat to switch back. Clicking an answer cell also shows its formula in the formula bar, with each referenced range outlined in color on the sheet.

If a Paste Special subtraction went wrong, press Ctrl+Z (or click Undo) right away. Undo can’t bring back changes made by a macro, because running a macro clears Excel’s undo history, so keep a copy of the data before using any VBA approach. If you only notice the error later, the backup column you copied before the paste is your fix.

Common questions about subtracting several cells

Is there a SUBTRACT or MINUS function in Excel?

No. Excel uses the minus operator, or SUM with negative numbers. Google Sheets is different: it does have a MINUS function, though it only takes two values. If you work in both programs, subtracting in Google Sheets walks through the differences.

Can I subtract cells from another worksheet?

Yes. Put the sheet name and an exclamation point before the reference: =Summary!B2-SUM(Expenses!B2:B20). If the sheet name contains spaces, wrap it in single quotes, as in 'March Bills'!B2. Clicking the other sheet’s tab while typing the formula inserts the reference for you.

Will SUM skip blank cells?

Yes. Empty cells inside the range add nothing, so you can leave room for future entries, for example =B2-SUM(B3:B50), and the formula keeps working as you fill the rows in.

How do I subtract only some cells, such as visible rows after a filter?

SUM includes hidden and filtered rows. If you want to subtract only what’s visible, swap SUM for SUBTOTAL: =B2-SUBTOTAL(109,B3:B50). The 109 tells SUBTOTAL to sum and to ignore hidden rows.

What about taking a percentage off several cells?

Multiply instead of subtracting a fixed amount: =A2*(1-$D$2) with 10% in D2 knocks 10 percent off each value as you fill down. More variations are in the article on subtracting a percentage in Excel.

Next step

Open a sheet you already use, pick one total, and replace any long minus chain with =start-SUM(range). Then insert a test row inside the range to watch the formula adjust on its 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.