How to Subtract in Excel: There Is No SUBTRACT Function, So Use These Formulas

Excel has no SUBTRACT function. You subtract with the minus sign: type =A1-B1 in any cell and press Enter, or use SUM with negative numbers when you are working with a list.

How to subtract in Excel when there is no SUBTRACT function?

You subtract in Excel by writing a formula with the minus operator, such as =A1-B1; the operator itself does the job a SUBTRACT function would.

People hunt through the function library for a subtraction button because addition has one. It does not exist, and it is not an oversight. The operator is simply the function. Every formula starts with an equals sign, and after that you can mix cell references, typed numbers and operators the way you would on a calculator.

Here is the whole idea on a tiny budget sheet, with the formula in column C:

	A (Income)	B (Rent and bills)	C (Left over)
Row 2	3,200	1,450	=A2-B2 shows 1,750
Row 3	3,200	1,520	=A3-B3 shows 1,680

Click C2, type =A2-B2, press Enter. Then grab the little square at the bottom-right corner of C2 (the fill handle) and drag it down, or double-click it. Excel rewrites the row number for each line, which is why the pattern scales to thousands of rows.

Two small habits save a lot of grief. First, click cells instead of typing their addresses; Excel highlights what it is reading, so you catch a wrong reference immediately. Second, keep numbers out of formulas when they are really inputs. =A2-1450 works today and fails silently next month when rent changes. Put 1,450 in its own cell and point to it.

If you are brand new to formulas generally, 10 Excel formulas everyone should know covers the basics this guide builds on, and the Excel functions cheat sheet is handy for lookups once you move past arithmetic.

What is the best way to subtract many numbers from one total?

For one or two items, the minus sign is fine. For a long list, SUM is cleaner, and it is the closest thing Excel has to a “subtract function”.

The trick is that SUM adds, but adding a negative number is subtraction. Suppose a starting balance sits in B2 and the withdrawals underneath are entered as negatives:

Row	Item	Amount
2	Starting balance	3,000
3	Car payment	-450
4	Insurance	-120
5	Groceries	-380
6	Balance	=SUM(C2:C5) shows 2,050

When the data arrives as positive numbers, you have two options. Subtract the range from the total, =C2-SUM(C3:C5), which reads naturally as “balance minus all the spending below it”. Or flip signs inside the formula: =SUM(C2,-C3,-C4,-C5).

My own preference is the first form. A formula that says total minus SUM of the pieces is obvious to the next person who opens the file, including you in six months. Negative numbers scattered through a column, by contrast, tend to get typed with the wrong sign at the worst moment.

Can you subtract an entire column from another column?

Yes, and it is a good use of Excel’s fill handle. Write =B2-C2 in the first row and copy down. If you use Microsoft 365 or Excel 2021 or later, you can write =B2:B100-C2:C100 in one cell and the results spill down automatically. Older versions need the copy-down method, and a spill formula will show @ or return a single value there.

How do you subtract dates and find the number of days between them?

Dates are numbers in disguise. Excel stores each day as a counter, so subtracting one date from another gives the number of days between them. That makes date subtraction the same formula as ordinary subtraction, with one formatting catch.

Say a project started on March 2, 2026 and today is October 25, 2026. Put the dates in A2 and B2, and in C2 type =B2-A2. The answer is 237 days.

The catch: Excel often “helpfully” formats the answer cell as a date, so you see a date in 1900 instead of 237. Select the cell, open the number format dropdown on the Home tab, and choose General or Number.

Goal	Formula	Result for Mar 2 to Oct 25, 2026
Calendar days between	=B2-A2	237
Working days (Mon-Fri)	=NETWORKDAYS(A2,B2)	counts weekdays, and includes both end dates
Days from today until a deadline	=B2-TODAY()	changes every day

TODAY() recalculates each time the file opens, so a countdown stays current without you touching it. For business days that should skip public holidays, list the holidays in a range and pass it as the third argument: =NETWORKDAYS(A2,B2,E2:E12). Microsoft documents both the plain subtraction method and the other date functions on its calculate the difference between two dates page.

How do you subtract times, like a work shift?

Times follow the same logic, since a time is a fraction of a day. Clock-in 9:00 AM in A2 and clock-out 5:30 PM in B2 gives =B2-A2 equal to 8:30, as long as the cell is formatted h:mm. Take out a 30-minute unpaid lunch with =B2-A2-TIME(0,30,0), which gives 8:00.

Two traps. A shift that crosses midnight returns a negative time and Excel prints a row of ####; use =MOD(B2-A2,1) to fix it. And if you add up a column of durations and the total passes 24 hours, format the total as [h]:mm with square brackets, or Excel will roll over and show the wrong hours.

When should you use DATEDIF to get years, months and days?

Use DATEDIF when plain subtraction is not enough, which is when you need the answer as whole years or months, such as age, tenure or the length of a lease. It takes a start date, an end date and a unit code.

The function does not appear in Excel’s function list or autocomplete, which confuses people into thinking it is broken. It works fine; Microsoft documents it on the DATEDIF function page and notes some limitations.

Unit code	Returns	Example, Mar 2 to Oct 25, 2026
”Y”	Complete years	0
”M”	Complete months	7
”D”	Days	237
”YM”	Months left after the full years	7
”MD”	Days left after the full months	23

So =DATEDIF(A2,B2,"M") returns 7 and =DATEDIF(A2,B2,"MD") returns 23. You can stitch them into a readable sentence:

=DATEDIF(A2,B2,"Y")&" years, "&DATEDIF(A2,B2,"YM")&" months, "&DATEDIF(A2,B2,"MD")&" days"

That prints “0 years, 7 months, 23 days”. Two cautions. The start date must be earlier than the end date or you get #NUM!. And Microsoft warns that the “MD” unit can produce odd results in some month-end cases, so for anything contractual, check a few dates by hand before you trust the column.

How do you calculate percentage change by subtracting?

Percent change is subtraction followed by division: new minus old, divided by old. The subtraction is the “how much did it move” part, and dividing by the old value turns that into a rate.

If a product sold 80 units last month and 92 this month, =(B2-A2)/A2 returns 0.15. Format the cell as a percentage (Ctrl+Shift+5 on Windows) and it reads 15%. If sales fall from 5,000 to 4,250, the same formula returns -15%, and the negative sign is your signal of a decrease.

Old	New	Formula	Result
80	92	=(B2-A2)/A2	15%
5,000	4,250	=(B3-A3)/A3	-15%
0	40	=(B4-A4)/A4	#DIV/0!

That last row is the one that breaks dashboards. When the old value can be zero, wrap the formula: =IF(A4=0,"n/a",(B4-A4)/A4). Also remember that percentage change is not symmetrical. Going from 100 to 150 is up 50%, but going back from 150 to 100 is down only 33%, so do not “subtract” percentages from each other as if they were the same base.

How do you subtract cells from different worksheets?

Click the first cell, type the minus sign, then click the tab of the other sheet and click the second cell. Excel writes the reference for you. A February-minus-January comparison looks like this:

=Feb!B5-Jan!B5

If the sheet name has a space or a special character, Excel adds single quotes: ='Q1 Sales'!B5-'Q4 Sales'!B5. Typing it by hand without the quotes is the usual cause of a #REF! error, so let Excel build it by clicking.

Sheet	Cell B5 (Total sales)
Jan	3,200
Feb	3,650
Difference	=Feb!B5-Jan!B5 shows 450

The same works across workbooks. Open both files, click into the other file’s cell, and Excel inserts a path like [Budget.xlsx]Feb!$B$5. A word of warning from experience: if someone renames or moves the source file, those links break, and the formula shows the last known value until it is updated. For anything that gets emailed around, I prefer copying the data into one workbook.

How do you subtract only when a condition is met?

Use SUMIF (or SUMIFS) to total each group, then subtract the totals. This is the situation behind almost every personal budget: one column of amounts, one column that says whether each is income or an expense.

Row	Type	Amount
2	Income	2,800
3	Expense	35
4	Expense	62
5	Income	150
6	Expense	18

With types in B2:B6 and amounts in C2:C6:

=SUMIF(B2:B6,"Income",C2:C6)-SUMIF(B2:B6,"Expense",C2:C6)

Income adds to 2,950 and expenses to 115, so the result is 2,835. Microsoft explains the arguments on the SUMIF function page. To narrow by month as well, switch to SUMIFS, where the sum range comes first: =SUMIFS(C2:C6,B2:B6,"Expense",A2:A6,">="&DATE(2026,10,1)).

If you are building a full tracker from this, a pivot table will do the grouping for you, and the keyboard habits in 50 keyboard shortcuts that save hours will make filling and navigating the sheet far faster.

What about subtracting one value from a whole range in place?

Sometimes you want to subtract the same number from every cell without a helper column, like applying a fixed discount. Type the number in a spare cell and copy it. Select the target range, right-click, choose Paste Special, pick Subtract under Operation, and click OK. Excel overwrites the values in place. It is a one-way change, so duplicate the sheet first if you may want the originals back.

Why does my subtraction formula show an error or a strange number?

Most subtraction failures come from the data, not the formula. Here are the ones I see most.

What you see	Likely cause	Fix
#VALUE!	A cell holds text, a stray space, or a typed unit	Clean the cell or use =VALUE(A2)-B2
####	Negative time or a column too narrow	Use MOD for times, or widen the column
0.00000000000001 instead of 0	Binary floating-point rounding	Wrap in ROUND(...,2)
Formula shows as text	Cell formatted as Text, or a leading apostrophe	Set format to General, re-enter the formula
#NUM! with DATEDIF	Start date later than end date	Swap the cells
Result is a date in 1900	Output cell formatted as a date	Change to General

One case I have watched happen, described here as a composite rather than a single real person: someone exports a bank statement as CSV and subtracts the “Debit” column from the “Credit” column. Half the rows return #VALUE!. The cause is amounts such as “1,200 USD” or numbers with a trailing space, which Excel reads as text. The fix was Data, then Text to Columns, then Finish on that column, which forces Excel to re-read the entries as numbers. A quick check is to select the column and look at the status bar: if it shows Count but no Sum, the cells are text.

Floating-point noise is the other quiet offender. A formula like =A1-B1 can return 0.00000000000001 when you expected zero, and then an IF(...=0,...) test fails. Wrapping the subtraction in ROUND(A1-B1,2) is the cure for currency.

Which Excel versions and tools handle these formulas?

Everything above works in Excel for Microsoft 365, Excel 2019 and 2021, Excel for the web, and the Mac version. The spill behavior (=B2:B100-C2:C100 filling down by itself) needs Microsoft 365 or Excel 2021 or later. Google Sheets accepts the same minus sign, SUM, SUMIF and DATEDIF, with minor differences in how it displays results.

If you spend your days in spreadsheets, a few other tools tend to pay off. Best productivity apps in 2026 compares note and task apps that pair well with a budget workbook, and AI productivity tools for work covers assistants that can write or debug formulas for you, though I would still verify any generated formula on three test rows before trusting it.

A good routine for any new subtraction sheet is simple: format the input cells as numbers, write the formula in the first row only, check it against a calculator for two rows, and then fill down.

This guide is general information for everyday spreadsheet use as of October 2026. Menus and function availability differ between Excel versions, so check Microsoft’s current documentation for your edition, and test formulas on a copy before using them for financial, payroll or legal decisions.