What Is The Microsoft Excel Formula For Subtraction

The formula for subtraction in Microsoft Excel uses the minus sign (-). Type =A1-B1 to subtract the value in B1 from A1, or =10-5 to subtract numbers directly. Every formula starts with an equals sign. You can also subtract several values, subtract totals with SUM.

How to subtract two numbers in Excel

Enter your formula in the cell where you want the answer to appear. For a calculation using fixed numbers, type =10-5 and press Enter. Excel displays 5.

The order matters: the value after the minus sign is taken away from the value before it. Reversing the formula to =5-10 gives -5. A negative answer can be useful, for example when expenses exceed income.

Excel does not have a built-in SUBTRACT, SUBTRACTION or MINUS worksheet function. Use the minus operator for ordinary subtraction; a formula such as =SUBTRACT(10,5) is not a valid built-in Excel function.

Subtract values stored in cells

Cell references let you change the inputs without editing the formula. To subtract one cell from another:

1. Enter 10 in A1.
2. Enter 5 in B1.
3. Select C1 and type =A1-B1.
4. Press Enter. C1 displays 5.

With automatic calculation enabled, changing either input updates the answer. Keep the formula in a separate cell from its inputs: entering =A1-B1 in A1 would create a circular reference because A1 would depend on itself.

Subtract several values or a range total

For a short list, repeat the minus sign. The formula =10-5-2 returns 3, and =A1-B1-C1 subtracts both B1 and C1 from A1. Subtractions at the same level are evaluated from left to right.

For a longer list, use SUM to add the amounts you want to deduct, then subtract that total. For example, =A1-SUM(B1:B5) subtracts the combined value of B1 through B5 from A1. This is useful when A1 contains a budget and the other cells contain individual expenses.

A colon identifies a continuous range. The formula =A1-A5 subtracts only A5 from A1; it does not add or subtract the cells between them.

Use brackets to control the calculation

Excel evaluates expressions inside brackets first. Compare =10-5-2, which returns 3, with =10-(5-2), which returns 7. In the second formula, Excel calculates 5 minus 2 before subtracting that result from 10.

Multiplication and division also happen before addition and subtraction. Add brackets when you need a difference calculated first, such as =(A1-B1)*C1.

Check the signs of your inputs too. Subtracting a negative number increases the result: =10-(-5) returns 15. If expenses are already stored as negative values, SUM can combine them with positive income without another subtraction.

Copy a subtraction formula down a column

For a list of matching values, place the first subtraction formula beside the first row of data. If A1 contains income and B1 contains expenses, enter =A1-B1 in C1. Drag the fill handle at the bottom-right corner of C1 down through the rows you need.

Excel adjusts relative references as it copies: the next row becomes =A2-B2, followed by =A3-B3. Check one copied formula to confirm that it uses the intended row.

If every row must subtract the same fixed value, lock that reference with dollar signs. For example, =A1-$F$1 becomes =A2-$F$1 when copied down one row. The reference to F1 stays fixed.

Subtract dates and times

Excel stores recognised dates and times as numbers, so the minus operator works with these values too. The result's format determines how the difference appears.

Find the number of days between dates

Put the earlier date in A1 and the later date in B1, then enter =B1-A1 in another cell. Format the result as General or Number to display the elapsed days. Use actual Excel dates rather than text that merely looks like a date.

Calculate elapsed time

For a start time of 10:00 in A1 and an end time of 13:30 in B1, =B1-A1 gives an elapsed time of three hours and thirty minutes. Apply a time format such as h:mm to display 3:30.

For work spanning midnight, include the date with each time so Excel can distinguish the days. Use the custom format [h]:mm when you need to display elapsed hours beyond 24, rather than a clock time.

Fix unexpected subtraction results

• If the formula appears as text, check for a leading apostrophe or a cell formatted as Text. Change the format to General, then edit and re-enter the formula. Also check whether Show Formulas is enabled.
• If you see #VALUE!, check the referenced cells for text or unwanted characters. Imported numbers may need converting before calculation.
• If the answer has the wrong sign, check the order of the references and whether an input is already negative.
• If decimal results look unexpected, distinguish the stored value from the displayed rounding. Use =ROUND(A1-B1,2) when the calculation specifically requires rounding to two decimal places.

For related tutorials, browse the Microsoft Office guides. If you are also setting up Office, separate guides cover Office activation using Command Prompt and finding an Office product key using Command Prompt.

Enter =A1-B1 beside two values in your worksheet, check the answer manually, then copy the formula to the remaining rows.