How to Subtract in Excel: Cells, Columns and Dates

To subtract in Excel, type an equals sign and put a minus sign between the values or cells, like =B2-C2. Excel has no SUBTRACT function, so the minus sign does all the work, combined with SUM when you need to take away several numbers at once. This guide covers cells, whole columns, fixed numbers, percentages, dates and times, with a fix for each odd result you might see.

How to subtract two numbers in Excel#

Every formula starts with =. To subtract, use the minus key (the hyphen next to 0, or the - on the number pad).

1. Click an empty cell.
2. Type =100-25.
3. Press Enter. The cell shows 75.

If you forget the =, Excel stores the text "100-25" or may even read it as a date. The guide to writing your first Excel formula covers the basics.

How to subtract one cell from another#

Use cell references, so the answer updates when the numbers change. With sales of 4,800 in B2 and costs of 3,150 in C2, type in D2:

=B2-C2

The result is 1,650. Order matters: =C2-B2 gives -1,650. If you only care about the size of the gap, wrap it in ABS: =ABS(C2-B2) returns 1,650 either way.

To subtract a whole column, fill the formula down. Select D2 to the last row and press Ctrl+D (⌘D on a Mac), or double-click the fill handle, the small square at the bottom-right of D2. D3 becomes =B3-C3, and so on.

If a formula shows the old answer after you change a number, calculation may be set to manual. See why an Excel formula is not updating.

How to subtract multiple cells#

To take several numbers away from one value, subtract their SUM. Here is a simple monthly budget:

To find what's left over:

=B1-SUM(B3:B7)

The result is 655 (2,500 − 1,845). You could write =B1-B3-B4-B5-B6-B7, but the SUM version is shorter, easier to check, and still works if you insert a row inside the range. The SUM function guide explains the range part, and the AutoSum shortcut builds the SUM for you. For a full worked example, see how to build a monthly budget in Excel.

How to subtract a fixed number from a column#

To subtract the same number from every row, put it in one cell and lock it with $ signs. If F1 holds a 15 fee to deduct from each payment in column B:

=B2-$F$1

Fill it down, and every row subtracts F1. Without the $ signs, the next row would subtract F2, which is empty, so nothing would be deducted. Press F4 while editing to add the $ signs (fn+F4 or ⌘T on a Mac).

How to subtract percentages#

There are two different questions here, and they need different formulas.

• Take a percentage off a number (a discount): =B2*(1-C2). With a price of 80 in B2 and 25% in C2, the result is 60. Writing =B2-C2 would give 79.75, because 25% is stored as 0.25.
• Find the gap between two percentages: =C2-B2. If the margin went from 32% to 35%, the result is 3%, which is 3 percentage points.

How to subtract dates#

Excel stores each date as a whole number of days, so subtracting two dates gives the days between them. With a start date of 1 March 2024 in B2 and an end date of 15 March 2024 in C2:

=C2-B2

The result is 14. If it shows as a date, such as 14/01/1900, change the cell format to General or Number. You can also subtract days from a date: =C2-30 gives the date 30 days earlier. For whole months and years, DATEDIF is the right tool; the lesson on days between dates is a good next step.

How to subtract times#

Times subtract the same way, but the result needs a time format. With a start of 9:00 in B2 and an end of 17:30 in C2, =C2-B2 shows 8:30 when the cell is formatted as h:mm.

To get a decimal number of hours for a timesheet, multiply by 24:

=(C2-B2)*24

The result is 8.5. If the end time is earlier than the start time, the result is negative, and Excel shows #### in a time format. For shifts past midnight, use =MOD(C2-B2,1).

How to subtract in place with Paste Special#

To take the same amount off a block of numbers without a formula column, use Paste Special. Type the amount, such as 15, in an empty cell and copy it. Select the numbers, open Paste Special (Ctrl+Alt+V on Windows, ⌃⌘V on a Mac), choose Subtract under Operation and click OK. Every selected number drops by 15. The originals are overwritten, so this suits one-off corrections better than reports that must stay traceable.

Common subtraction errors#

Most subtraction problems come from one of these:

• #VALUE!: one cell holds text, like "3,150 USD". Keep units out of number cells.
• A date instead of a number: the cell kept a date format. Change it to General.
• Wrong sign: the cells are in the wrong order. Swap them or use ABS.
• Wrong after filling down: a fixed number without $ signs.

Simple subtraction and SUM questions are a staple of entry-level tests. See what a basic Excel test for employment covers to know what to expect.

Practise it#

Subtraction is quick to learn and easy to get slightly wrong under time pressure. Start with your first formulas in a live spreadsheet, then practise totals in the SUM and AutoSum lesson. Each answer is checked instantly. Try the budget exercise on this page first.