Excel Subtraction Formula: How to Subtract Numbers, Cells, and More

How to Subtract in Excel

Excel doesn't have a dedicated SUBTRACT function — subtraction in Excel is done using the minus sign (-) directly inside a formula. If you've been looking for a SUBTRACT function in the formula library and not finding it, that's why: the minus sign is the subtraction operator, and you use it the same way you'd write a subtraction on paper. The formula =10-3 gives you 7 in a cell, and the formula =A1-B1 subtracts whatever value is in B1 from whatever is in A1.

This approach is actually more flexible than a dedicated function would be. You can chain as many subtractions as you need in a single formula, subtract the results of other functions, mix constants and cell references, and combine subtraction with addition, multiplication, and division all in one expression. Understanding how the minus operator works in Excel — including how it handles percentages, dates, negative numbers, and arrays — gives you the building blocks to solve almost any calculation you encounter in a spreadsheet.

The most common use case is subtracting one cell from another: =A1-B1. But the same logic scales. You might subtract a fixed discount from a list of prices, find the difference between budgeted and actual costs across a financial model, calculate net profit by subtracting total costs from total revenue, or figure out how many days remain between two calendar dates. In every case, the formula starts with = and uses the minus sign to separate what you're subtracting from what you're subtracting it from.

This guide walks through every practical subtraction technique in Excel — basic cell-to-cell subtraction, subtracting multiple values at once, using SUM with negative numbers, handling percentages and dates, working with absolute references, avoiding common errors, and applying subtraction across ranges of cells.

Before diving into specific techniques, one point is worth establishing: Excel's arithmetic operators follow standard mathematical order of operations. Multiplication and division are evaluated before addition and subtraction. If you need subtraction to happen before a multiplication in the same formula, use parentheses to control the order: =(A1-B1)*C1 subtracts B1 from A1 first, then multiplies the result by C1. Without the parentheses, =A1-B1*C1 would multiply B1 by C1 first and then subtract from A1 — a meaningfully different calculation. Getting this right matters whenever you combine subtraction with other operators in the same formula.

Subtracting Percentages in Excel

Subtracting a percentage in Excel requires understanding whether you're subtracting a percentage point or reducing a value by a percentage. These are different operations with different formulas. If you want to subtract 10 percentage points from 85% — for instance, finding that a pass rate dropped from 85% to 75% — you use the same basic formula as any cell subtraction: =A1-B1 where A1 is 85% and B1 is 10%. The result is 75%.

The more common scenario is reducing a value by a percentage — for example, applying a 20% discount to a price. Here, the formula is =A1*(1-B1), where A1 is the original price and B1 is the discount percentage formatted as a decimal (0.20 for 20%) or as a percentage cell (20%). This formula works because 1-B1 gives you the proportion of the original price you're keeping: if you're discounting by 20%, you're keeping 80%, so multiplying the original price by 0.80 gives the discounted price.

You can also write this as =A1-A1*B1, which explicitly subtracts the discount amount from the original price. Both formulas produce identical results — =A1*(1-B1) is more compact, while =A1-A1*B1 is more explicit. If you need the discount amount itself (not the discounted price), use just =A1*B1.

A common mistake when working with percentage subtraction is entering the percentage as a whole number rather than a decimal. If your discount is 20% and you type 20 rather than 0.2 (or format the cell as a percentage first), the formula =A1*(1-20) produces -19 times the original price, which is dramatically wrong. Format your percentage cells as Percentage in the Format Cells dialog, or enter values as decimals (0.20 rather than 20).

If you're building a pricing model and need to apply multiple percentage-based adjustments — a wholesale discount, then a loyalty discount, then a handling fee — the order of operations matters significantly. Applying a 20% discount followed by a 10% discount on the discounted price is not the same as applying a 30% total discount. =A1*(1-0.2)*(1-0.1) applies each discount sequentially to the running price, giving 72% of the original. =A1*(1-0.3) applies a flat 30% reduction, giving 70% of the original. Knowing which calculation your business actually intends is as important as knowing the correct formula syntax.

When subtracting VAT or tax from a price to find the pre-tax amount, the formula is different again. If a price includes 20% VAT and you want the ex-VAT amount, you divide by 1.20 rather than subtracting: =A1/1.2. Subtracting 20% of the total (=A1-A1*0.2) gives you the wrong answer because 20% of a VAT-inclusive price is not the same as the VAT component itself.

Using SUM to Subtract Multiple Values at Once

While chaining minus signs works perfectly well — =A1-B1-C1-D1 — there's an alternative that becomes more readable when you're subtracting many values: using the SUM function with negative numbers. The formula =SUM(A1,-B1,-C1,-D1) adds A1 to the negative versions of B1, C1, and D1 — which is mathematically identical to subtracting them. The result is the same; the formula is sometimes easier to read because SUM clearly signals that multiple values are involved.

This technique is particularly useful when you want to subtract a range of cells at once. You can't write =A1-B1:D1 directly in Excel — ranges don't work with the minus operator alone. But you can use =A1-SUM(B1:D1), which subtracts the total of B1 through D1 from A1 in a single clean formula. If you have 20 cost items to deduct from a revenue figure, =Revenue-SUM(Costs) is far more manageable than chaining 20 minus signs.

Another variation is using SUMIF or SUMIFS for conditional subtraction. If you only want to subtract values that meet certain criteria — for instance, subtracting costs marked as 'direct' from total revenue — =Revenue-SUMIF(Category_Range,'direct',Cost_Range) handles this precisely. This is effectively subtraction with a filter, and it's one of the most powerful patterns in financial modelling within Excel.

SUMIFS with subtraction is another powerful pattern for financial reporting. Suppose you have a transaction log with a Category column and an Amount column, and you want to subtract total expenses from total income to get a net figure. =SUMIFS(Amount,Category,'Income')-SUMIFS(Amount,Category,'Expense') sums only the income rows, sums only the expense rows, and subtracts one from the other — all in a single formula.

This pattern scales from simple two-category calculations to complex multi-condition financial reconciliations without requiring pivot tables or helper columns. Combining SUMIFS with subtraction this way is one of the most efficient patterns for summarising categorised data directly in a formula cell.

When combining SUM and subtraction with large datasets, performance is generally not a concern — Excel handles these calculations almost instantly for thousands of rows. However, in very large models with millions of rows or complex dependent calculations, minimising unnecessary recalculation by structuring formulas efficiently (and using Tables so Excel only recalculates changed ranges) becomes relevant.

Subtracting Across Worksheets and Workbooks

Subtraction formulas in Excel aren't limited to cells on the same sheet. You can subtract values from different worksheets within the same workbook, or even from cells in a completely separate workbook file. The syntax extends the standard cell reference to include the sheet name and, if needed, the workbook name.

To subtract a cell on another sheet in the same workbook, use the format =A1-Sheet2!B1, where 'Sheet2' is the name of the other sheet and B1 is the cell on that sheet. If the sheet name contains spaces, enclose it in single quotes: =A1-'Budget Summary'!B1. You can build this formula by typing = in your cell, clicking A1 on the current sheet, typing -, then navigating to the other sheet and clicking B1 — Excel fills in the sheet reference automatically.

To subtract from a cell in a different workbook, both workbooks must be open. The reference format is =[WorkbookName.xlsx]SheetName!CellReference. For example, =[Budget2026.xlsx]Sheet1!B1. When the source workbook is closed, Excel stores the full file path in the reference and the formula still works — it reads the last saved value from the closed file. If the source file moves or is renamed, the reference breaks and you'll need to update it using Data → Edit Links.

Cross-workbook subtraction is common in financial consolidation work, where actuals from one file are subtracted from a budget in another to produce a variance report. For this use case, maintaining a consistent naming convention for workbooks and sheet tabs is essential — small changes to file or sheet names break these references silently, showing outdated values without any error indicator.

Paste Special Subtract: Modifying Values In Place

One of Excel's less-known features is Paste Special with the Subtract operation, which lets you subtract a value from a range of existing cells without writing a formula in a separate column. This is useful when you need to adjust a list of values by a fixed amount — for example, reducing all prices in a column by 5 — and you want the adjusted values to replace the originals rather than appear in a new column.

The process works like this: enter the value you want to subtract (5, in this example) into any empty cell, then copy that cell with Ctrl+C. Select the range of cells you want to modify — your list of prices. Go to Home → Paste → Paste Special (or press Ctrl+Alt+V). In the Paste Special dialog, choose Values under Paste and Subtract under Operation. Click OK. Excel subtracts your copied value from every cell in the selected range, updating the values in place.

Paste Special Subtract is a destructive operation — it replaces the original values permanently. Before using it, either keep a backup of the original data or be confident you don't need to recover the pre-subtraction values. It also only works with constants, not formulas: if the cell you copied contains a formula result, Paste Special Subtract uses the current value, not the formula. Undo (Ctrl+Z) works immediately after if you need to reverse the operation.

This technique is particularly valuable when working with data received from others — adjusting prices for a currency conversion, applying a blanket cost reduction, or normalising a dataset by subtracting a baseline value from every row. It saves creating a helper column, writing a formula, copying the formula results as values, and then deleting the helper column.

When auditing a file built by someone else, look for unexplained value changes in columns of figures — they may be the result of a Paste Special Subtract operation applied at some point in the file's history rather than a formula-driven adjustment you can trace.

Subtraction in Excel Tables and Named Ranges

When your data is stored in an Excel Table (created with Ctrl+T or Insert → Table), subtraction formulas behave differently and more powerfully than in a standard range. Inside a Table, formulas use structured references — column names rather than cell addresses. Instead of =A2-B2, a Table formula reads =[@Revenue]-[@Costs]. This makes formulas far more readable, and they automatically expand when you add new rows to the Table.

Structured references also prevent the common error of a formula referencing the wrong row because a row was inserted or deleted above the data. In a standard range, inserting a row can shift your formula references in unexpected ways if you're not careful with absolute references. In a Table, structured references always point to the correct column regardless of row insertions or deletions within the Table range.

Named ranges offer similar benefits outside of Tables. You can name a cell or range (Formulas → Name Manager → New, or type directly in the Name Box to the left of the formula bar), then reference that name in your subtraction formula. =TotalRevenue-TotalCosts is immediately meaningful to anyone reading the formula, whereas =C2-D2 requires knowing what columns C and D represent. Named ranges also make it easier to use absolute references without dollar signs — a named range is inherently absolute.

Dynamic named ranges, created with OFFSET or using Excel Tables, automatically include new data as you add rows. A standard named range that covers a fixed set of cells (say B2:B50) stops being accurate once your data grows beyond 50 rows. By contrast, a Table-based structured reference always covers exactly the rows in the Table — you don't need to update the reference manually. For datasets that grow over time, using a Table and referencing column names in your subtraction formulas is a substantially more reliable setup than static named ranges or coordinate references.

For large models with many subtraction formulas, using Tables or named ranges consistently from the start prevents the most common maintainability problems. When formulas reference named business concepts rather than coordinate addresses, auditing the model and catching errors becomes significantly easier — both for you when you return to the file weeks later, and for colleagues who need to work with or verify the spreadsheet.