Excel Subtraction Formula: How To Subtract Cells And Ranges

How to Subtract in Excel

Unlike addition, Excel doesn't have a dedicated =SUBTRACT() function. Instead, you'll use the minus operator (-) for standard cell-by-cell math, or adapt the =SUM() function with negative values when working with larger datasets.

Getting the right result depends entirely on how your data is structured. Whether you're finding the difference between two cells, subtracting an entire column, or working with dates and percentages, using the correct formula structure prevents common #VALUE! errors and keeps your spreadsheets accurate.

Using the Minus Operator for Basic Subtraction

The most direct way to subtract numbers in Excel is by using the standard minus sign (-). This approach works best when you're comparing two specific cells or subtracting a fixed value from a cell.

To write your formula, click the cell where you want the answer to appear, type an equal sign (=), select your first cell, type -, and then select the cell you want to take away.

1. Type =B2-C2 in cell D2 to find the difference between your budgeted amount and your actual spend.
2. Press Enter to calculate the result.
3. Click the fill handle in the bottom-right corner of the cell and drag down to apply the formula to the rest of your rows.

You can also chain multiple cells together in a single formula if you're tracking sequential deductions. For example, writing =A2-B2-C2 subtracts both B2 and C2 from the initial value in A2.

Always keep order of operations in mind. If you mix multiplication or division with your subtraction, wrap your subtraction parts in parentheses—like =(A2-B2)*C2—to ensure Excel calculates the correct sequence.

Subtracting an Entire Column Using the SUM Function

When you need to subtract an entire column of numbers from a single base value, writing out a long chain of minus signs becomes impractical. The workaround is using the =SUM() function by passing your values as negative numbers.

Excel treats numbers inside a SUM function natively, so you can combine positive ranges and negative ranges inside the exact same formula.

=SUM(A2:A50, -B2:B50)

In practice, this approach shines when you have columns of revenue and expenses on separate worksheets. Instead of referencing individual cells row by row, you can pass entire column ranges into the formula.

• Revenue Cell: =SUM(Sheet1!A2:A100)
• Expense Cell: =SUM(Sheet2!B2:B100)
• Combined Formula: =SUM(Sheet1!A2:A100, -Sheet2!B2:B100)

This method keeps your formulas clean and automatically adjusts when you insert new rows into your data tables.

Subtracting Percentages and Applying Discounts

Calculating percentage decreases is a frequent requirement in retail, accounting, and general data analysis. If you want to subtract a specific percentage discount from a price, you can't just type =100-20%. You have to multiply the base value by the remaining percentage.

To find a discounted price when your original price is in cell A2 and your discount rate (like 15%) is in cell B2, use this formula:

=A2*(1-B2)

If you want to calculate the raw dollar amount of the discount instead of the final total, multiply the price by the percentage directly: =A2*B2. You can then subtract that result from the original price using =A2-(A2*B2).

Subtracting Dates and Times in Excel

Excel stores dates and times as sequential serial numbers, which means you can subtract them just like regular numbers to find durations.

To find the number of days between two dates, ensure both cells are formatted properly, then use the standard minus operator.

• Start Date: 01/05/2026 in cell A2
• End Date: 01/15/2026 in cell B2
• Formula: =B2-A2 yields 10 days.

If your result shows up as a strange date instead of a number, right-click the cell, select Format Cells, and change the category to General or Number.

Time subtraction follows the same logic, but with a catch. Times are stored as fractions of a 24-hour day. If you subtract a start time of 8:00 AM from an end time of 5:00 PM, Excel calculates the decimal fraction of that elapsed span. To display the result correctly as hours and minutes, apply a custom format like [h]:mm. The square brackets around the h ensure Excel displays total accumulated hours even if the duration exceeds 24 hours.

Fixing Common Subtraction Errors

When your subtraction formula fails, Excel usually returns an error code. Understanding what causes these errors makes troubleshooting much faster.

• #VALUE! Error: This happens when your formula tries to subtract text instead of a number. Check your referenced cells for hidden spaces, text labels, or accidental apostrophes before numbers.
• #NUM! Error: Usually caused by invalid numeric arguments or formula syntax errors that result in unresolvable numbers.
• Incorrect Results: If your numbers look right but the math is off, check your cell formatting. A cell formatted as text will cause Excel to ignore its value or throw an error.

To protect your reports from breaking when source data is missing or incomplete, wrap your formula in the IFERROR function.

=IFERROR(A2-B2, 0)

This tells Excel to output a clean zero (or any custom text like "Pending") instead of displaying an ugly error code if cell A2 or B2 is blank.

Frequently Asked Questions

Can I subtract data from another worksheet?

Yes. Reference the other sheet by adding its name followed by an exclamation mark before the cell reference. For example, writing =Sheet1!A2-Sheet2!A2 subtracts a value on Sheet 2 from a value on Sheet 1.

Why is my date subtraction returning a decimal?

If you subtract two timestamps or dates and get a decimal number, it means the result cell is formatted to display raw time fractions. Change the cell format to General or Number to see the actual number of days.

How do I subtract a constant value from an entire column?

You can use an absolute reference by adding dollar signs. If you want to subtract the tax rate stored in cell $C$1 from a whole column of prices in column A, write =A2-$C$1 in row 2 and drag the formula down. The dollar signs lock the reference to cell C1 so it doesn't shift as you copy the formula down.

What is the fastest way to subtract multiple cells without typing them all?

Use the =SUM() function with negative inputs, such as =SUM(A2, -B2, -C2, -D2), or reference a contiguous range of numbers to subtract by combining them with negative ranges.