Errors
There is no dedicated SUBTRACT function in Excel — instead, you use the minus operator (-) to subtract values. The basic subtraction formula in Excel is =A1-B1, which subtracts the value in cell B1 from the value in cell A1. This simple operator handles everything from subtracting two numbers to calculating differences across entire columns.
While subtraction seems straightforward, Excel users frequently encounter issues with cell references, date calculations, time differences, and applying the formula across large datasets. This guide covers every common subtraction scenario with practical examples you can use immediately.
All examples work in Excel 365, Excel 2021, Excel 2019, Excel 2016, and Google Sheets.
Click any empty cell, type =A1-B1 (replacing A1 and B1 with your actual cell references), and press Enter. The result is the difference between the two cells.
Method 1: Basic Subtraction with the Minus Operator
The minus sign (-) is all you need for basic subtraction in Excel. You can subtract numbers directly, cell references, or a combination of both.
Click an Empty Cell for the Result
Click the cell where you want the subtraction result to appear.
Type the Subtraction Formula
Type an equals sign followed by your subtraction formula. Examples: =100-30 subtracts numbers directly. =A1-B1 subtracts cell B1 from A1. =A1-50 subtracts a fixed number from a cell.
Press Enter to Calculate
Press Enter and the cell displays the result. In the formula bar, you can still see and edit the formula.
Subtract Multiple Values
You can chain subtractions: =A1-B1-C1-D1 subtracts B1, C1, and D1 from A1 in a single formula.
Tip: If your result shows as a date or strange format instead of a number, right-click the cell, select Format Cells, and change the format to Number or General.
Method 2: Subtract an Entire Column from Another Column
When you need to subtract column B from column A for hundreds of rows, you do not need to write a formula for each row. Use AutoFill to apply the formula instantly.
Write the Formula in the First Row
Click cell C1 (or your result column) and type =A1-B1. Press Enter.
AutoFill the Formula Down
Click cell C1 again to select it. Hover over the small square at the bottom-right corner of the cell (the fill handle) until the cursor becomes a black cross. Double-click or drag it down to fill the formula for all rows with data.
Verify the Results
Click any cell in the result column and check the formula bar. The cell references should adjust automatically (C2 shows =A2-B2, C3 shows =A3-B3, etc.).
Method 3: Subtract Dates to Calculate the Difference in Days
Excel stores dates as numbers (serial values), so subtracting one date from another gives you the number of days between them.
Ensure Both Cells Are Formatted as Dates
Select both date cells, right-click, choose Format Cells, and verify they are in Date format.
Subtract the Earlier Date from the Later Date
In an empty cell, type =B1-A1 where B1 is the later date and A1 is the earlier date. Press Enter.
Format the Result as a Number
The result may display as a date. Right-click the result cell, select Format Cells, choose Number (0 decimal places), and click OK. The cell now shows the number of days between the two dates.
Method 4: Subtract Times in Excel
Subtracting times works similarly to dates, but the result is a decimal fraction of a day. Formatting it correctly shows hours and minutes.
Enter Times in Two Cells
Enter a start time in A1 (e.g., 9:00 AM) and an end time in B1 (e.g., 5:30 PM).
Subtract Start from End
In cell C1, type =B1-A1 and press Enter. The raw result is a decimal number.
Format as Hours and Minutes
Right-click cell C1, select Format Cells, choose Custom, and type h:mm in the format field. Click OK. The cell now shows 8:30 representing 8 hours and 30 minutes.
Warning: If the end time is earlier than the start time (e.g., subtracting across midnight), the result will be negative and may display as ########. Add 1 to the formula: =B1-A1+1 to handle overnight time spans.
Method 5: Use the SUM Function for Bulk Subtraction
While SUM is typically used for addition, you can use it for subtraction by making the values you want to subtract negative.
Use SUM with Negative Numbers
To subtract B1 through B5 from A1, type: =A1-SUM(B1:B5). This subtracts the total of B1:B5 from A1 in one formula.
Alternative: Negate Inside SUM
You can also write: =SUM(A1,-B1,-C1,-D1). The negative signs before cell references subtract those values within the SUM function.
Why Is There No SUBTRACT Function in Excel?
Microsoft designed Excel so that the minus operator (-) handles all subtraction needs. Unlike addition (SUM), multiplication (PRODUCT), and other operations that benefit from dedicated functions for handling ranges, subtraction is inherently a two-operand operation. The minus operator is more intuitive and faster to type than a hypothetical SUBTRACT function would be. For bulk subtraction, combining the minus operator with SUM (as shown in Method 5) covers every use case efficiently.
Frequently Asked Questions
Excel uses the minus operator (-) for subtraction because it is simpler and more intuitive than a function. For bulk operations, combining SUM with negative values achieves the same result.
To subtract 20% from a value in A1, type =A1*(1-20%) or =A1-A1*20%. Both formulas give you 80% of the original value.
Use Paste Special. Copy the cell containing the value to subtract, select the target cells, right-click > Paste Special, select Subtract, and click OK. This subtracts the copied value from each selected cell.
The cell is formatted as a Date. Right-click the cell, select Format Cells, and change the format to Number or General.
Yes, use sheet references like =Sheet1!A1-Sheet2!A1. This subtracts cell A1 on Sheet2 from cell A1 on Sheet1.
Wrap your formula in ABS: =ABS(A1-B1). The ABS function returns the absolute value, converting any negative result to positive.
Subtract the time cells directly (=B1-A1) and format the result cell as h:mm using Custom number format. Excel handles time arithmetic natively.
The column is too narrow to display the result. Double-click the column border in the header to auto-fit, or manually widen the column.
Enter the number to subtract in an empty cell, copy it, select the target column, right-click > Paste Special > Subtract. Or use a helper column with a formula referencing an absolute cell (=A1-$D$1).
Google Sheets has a MINUS function: =MINUS(A1,B1) is equivalent to =A1-B1. Both work in Google Sheets, but only the minus operator works in Excel.