To subtract one cell from another in Excel, click the cell where you want the answer, type =A2-B2, and press Enter. Excel shows the result and updates it whenever A2 or B2 changes. There’s no SUBTRACT function. You use the minus sign, and for subtracting several cells at once you combine it with SUM. Below are examples for single cells, whole columns, a fixed number, dates, and percentages.
Subtract one cell from another
- Click the cell for the result, for example C2.
- Type =, click A2, type –, then click B2. The formula reads
=A2-B2. - Press Enter.
If A2 is 150 and B2 is 40, C2 shows 110. If B2 is larger, the answer is negative.
Subtract down a whole column
After entering the formula in C2, double-click the small square at the bottom-right of the cell (the fill handle). Excel copies it down, adjusting to =A3-B3, =A4-B4, and so on. See how to subtract two columns in Excel and how to use the fill handle.
Subtract several cells
- Chain them:
=A2-B2-C2-D2. - Or subtract their total:
=A2-SUM(B2:D2). This is easier with many cells.
Subtract the same number from many cells
With a formula
Put the number in a cell, such as F1, and lock it with dollar signs so it doesn’t shift when copied: =A2-$F$1. See how to keep one cell constant in Excel.
Without a formula (Paste Special)
- Type the number to subtract in an empty cell and copy it (Ctrl + C).
- Select the cells you want to change.
- Right-click, choose Paste Special, select Subtract under Operation, and click OK.
This changes the original values permanently, so keep a backup if you might need them.
Subtract dates and times
=B2-A2 with two dates gives the number of days between them. Format the result as Number, not Date. For times, format the result as [h]:mm. See how to calculate the difference between two dates and times.
Subtract a percentage
To take 15% off a price in A2: =A2*(1-15%), or with the percentage in B2, =A2*(1-B2). See how to multiply by a percentage in Excel.
Show the difference without negatives
=ABS(A2-B2) always returns a positive number, useful for the size of a gap regardless of direction. =MAX(0,A2-B2) returns 0 instead of a negative result.
Troubleshooting
Excel shows the formula as text
The cell is formatted as Text, or the formula is missing the equals sign. Change the format to General and re-enter it.
#VALUE! error
One of the cells contains text, such as a number with a space or a currency symbol typed as text. Clean up the value or use =VALUE().
The answer is a strange date
Excel copied a date format to the result. Change the cell format to Number or General.
Frequently asked questions
Can I subtract in Excel without a formula?
Yes, for one-time changes, use Paste Special > Subtract as shown above. The status bar also shows sums but not differences, so a formula is usually best.
Is it the same in Google Sheets?
Yes. =A2-B2 works the same, and Google Sheets also has a MINUS function: =MINUS(A2,B2).
Summary
Type =A2-B2 to subtract one cell from another, fill it down for a whole column, use =A2-SUM(B2:D2) for several cells, and lock a constant with $F$1 or use Paste Special > Subtract to take the same value from many cells.
Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.
His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.