Subtract in Excel Practical Examples

Subtraction is one of the most common calculations in Excel. Whether you are balancing a budget, comparing sales figures, or measuring the difference between dates, Excel provides several ways to subtract values efficiently. This page presents clear, realworld examples that demonstrate the basic and advanced techniques for subtraction.

1. Simple CelltoCell Subtraction

The most straightforward way to subtract is to use the - operator between two cells.

A	B	C (Result)
150	45	=A2-B2
2000	375	=A3-B3

Enter the formula =A2-B2 in cell C2 and copy it down. Excel instantly shows the difference between each pair of numbers.

2. Subtracting a Constant from a Range

Sometimes you need to subtract the same number from many cells, for example applying a discount.

        =A2-$D$1    

Assume D1 contains the discount value (e.g., 10). Drag the formula down column B and each result will be A2-10, A3-10, etc. The dollar signs lock the reference to D1.

3. Using the SUM Function to Subtract

Excel does not have a separate subtraction function, but you can use SUM with a negative number:

        =SUM(A2, -B2)    

This is handy when you already have a SUM formula and want to add a negative term without rewriting the whole expression.

4. Subtracting Multiple Values with SUM

When you need to subtract several numbers from a total, wrap the positives in SUM and the negatives in another SUM:

        =SUM(A2:A5) - SUM(B2:B5)    

This calculates the total of column A less the total of column B in one step.

5. Subtracting Dates

Excel stores dates as serial numbers, so subtracting one date from another returns the number of days between them.

Start Date	End Date	Days Between
01/03/2024	15/04/2024	=B2-A2
06/01/2024	06/30/2024	=B3-A3

If you want the result in months or years, combine DATEDIF with subtraction:

        =DATEDIF(A2,B2,"m")   // months        =DATEDIF(A2,B2,"y")   // years    

6. Subtracting with Conditional Logic (IF)

Sometimes subtraction should only happen when a condition is met. Use IF to control it:

        =IF(A2>B2, A2-B2, 0)    

This returns the difference only when the first number is larger; otherwise it returns zero.

7. Subtracting Across Worksheets

Reference another sheet by adding the sheet name in single quotes:

        =Sheet1!A2 - Sheet2!B2    

This is useful for yearoveryear comparisons stored on separate tabs.

8. Subtracting with Absolute Values

If you need the magnitude of the difference regardless of order, wrap the subtraction with ABS:

        =ABS(A2-B2)    

This returns a positive number whether A2 is larger or smaller than B2.

9. Subtracting Text Representations of Numbers

When numbers are stored as text (e.g., imported from a CSV), use VALUE to coerce them:

        =VALUE(A2) - VALUE(B2)    

Without VALUE Excel would treat the subtraction as a string operation and return an error.

10. Array Subtraction (Dynamic Arrays)

Excel 365/2021 supports dynamic arrays, allowing you to subtract whole ranges at once:

        =A2:A6 - B2:B6    

Enter the formula in a single cell; Excel spills the results into adjacent rows.

11. Subtracting with the SUBTOTAL Function

When you need a subtraction that respects filtered rows, combine SUBTOTAL codes 109 (SUM) with a negative term:

        =SUBTOTAL(109, A2:A20) - SUBTOTAL(109, B2:B20)    

The result changes automatically as you filter the data.

12. Practical Business Example Net Profit

Assume you have the following columns:

• Revenue (Column B)
• Cost of Goods Sold (Column C)
• Operating Expenses (Column D)

The net profit formula is:

        =B2 - C2 - D2    

Or, using SUM for readability:

        =B2 - SUM(C2:D2)    

Copy the formula down to calculate profit for each row.

13. Subtracting Percentages

If you have a base amount in A2 and you need to reduce it by a percentage stored in B2 (e.g., 15%), use:

        =A2 - (A2*B2)    

Or a shorter version:

        =A2*(1-B2)    

This returns the amount after the percentage deduction.

14. Common Pitfalls

• Mixed Data Types: Subtracting a number stored as text yields #VALUE!. Use VALUE or clean the data.
• Incorrect Cell References: Forgetting absolute references ($) when copying formulas can cause unexpected results.
• Hidden Dates: Excel may display a number instead of a date if cell formatting changes after subtraction.

15. Quick Reference Cheat Sheet

Goal	Formula
Subtract two cells	=A2-B2
Subtract a constant	=A2-$D$1
Subtract ranges	=SUM(A2:A10)-SUM(B2:B10)
Days between dates	=B2-A2
Conditional subtraction	=IF(A2>B2, A2-B2, 0)
Absolute difference	=ABS(A2-B2)
Dynamic array subtraction	=A2:A6-B2:B6

With these examples you should be able to handle virtually any subtraction task in Excel, from simple oneoff calculations to complex, filteraware financial models. Feel free to adapt the formulas to match the structure of your own worksheets.