To work out the difference between two numbers in Excel, subtract one cell from the other: if the first number is in A2 and the second is in B2, enter =B2-A2 in C2. Excel returns the signed difference, so the result is positive when B2 is larger and negative when it is smaller; use =ABS(B2-A2) when you want the size of the difference without a sign.
How subtraction works in Excel
Excel uses the minus sign to subtract one value from another. A formula starts with an equals sign, followed by the cell reference for the number you want to start with, a minus sign, and the reference for the number to subtract. For example, if A2 contains 40 and B2 contains 55, the formula =B2-A2 returns 15. If B2 contains 32 instead, it returns -8.
Order matters. Subtraction is not commutative: reversing the references reverses the sign of the result. With A2 containing 40 and B2 containing 55, =A2-B2 returns -15, not 15. Decide which value represents the starting point or the value being compared against before choosing the order.
You can enter numbers directly into a formula, such as =55-40, but cell references are usually more useful. If the source numbers change, a formula that refers to their cells recalculates automatically. That makes cell references preferable for budgets, measurements, inventory counts, scores, and other data you may update.
Choose between a signed and absolute difference
A signed difference preserves direction. It tells you whether the second value went up or down compared with the first. This is useful when tracking changes over time: a positive result can represent an increase, while a negative result indicates a decrease. The sign is part of the information, not an error.
An absolute difference reports only how far apart two values are. For instance, a gap of 12 is still 12 regardless of which number is larger. With the first value in A2 and the second in B2, enter =ABS(B2-A2) to return that nonnegative gap. ABS is an Excel function that converts a negative result to its positive equivalent and leaves a positive result unchanged.
Use the signed version for changes, gains or losses, and comparisons where direction matters. Use the absolute version for questions such as “How many units apart are these totals?” or “What is the gap between these measurements?” Do not use ABS automatically: it can conceal an important decrease or reversal.
Enter a difference formula step by step
For a basic comparison in Excel for Microsoft 365 on Windows, place the two numbers in separate cells and use a third cell for the answer. For example, put the earlier value in A2 and the later value in B2. Select C2, type =B2-A2, and press Enter. If A2 is 120 and B2 is 147, C2 displays 27.
To calculate the result for more records, select the cell containing the formula and drag the small square at its lower-right corner (the fill handle) down the column. Excel adjusts relative references as it fills: the next row uses B3-A3, then B4-A4, and so on. You can also copy the formula cell and paste it into the cells below.
On Excel for Mac, the same subtraction formulas and fill-handle method work. Ribbon labels can be arranged somewhat differently depending on the window and update, but the formula itself does not change. In Excel for the web, enter the formula in the result cell and fill or copy it down in the same way.
Calculate percentage change
A difference in units is not the same as a percentage change. A percentage change compares the size of the change with the original value. With the original number in A2 and the new number in B2, enter =(B2-A2)/A2 in C2. If A2 is 80 and B2 is 100, the change is 20, and 20 divided by 80 is 0.25, or 25%. Format C2 as a percentage to display 25% instead of 0.25.
For a decrease, the same formula returns a negative percentage. If A2 is 80 and B2 is 60, the difference is -20 and the percentage change is -25%. This expresses that the new value is one quarter lower than the original. The original value must be the denominator for the usual “percentage increase or decrease from the original” calculation.
A related calculation is the percentage difference between two values, where neither is treated as the original. One common convention divides the absolute gap by the average of the two values. With the values in A2 and B2, enter =ABS(B2-A2)/AVERAGE(A2,B2). For 80 and 100, the absolute gap is 20 and their average is 90, so the result is about 22.22%. This is not the same as the 25% change from 80 to 100. State which method you mean when reporting a percentage, because “percentage difference” can be used for more than one comparison.
Percentage change cannot be calculated by dividing by an original value of zero. Excel will show a division-by-zero error in that case. You need to decide how to describe a change from zero for your particular report rather than treating an undefined percentage as an ordinary result.
Compare two numbers down a list
For a table with many comparisons, keep each pair on the same row and add a result column. For example, use column A for the previous measurement, column B for the current measurement, and column C for the change. In C2, enter =B2-A2, then fill the formula down. Each row then compares the two values on that row, which is easier to audit than entering separate calculations manually.
If you need the gap regardless of direction, use =ABS(B2-A2) in C2 before filling down. If you need percentage changes, use =(B2-A2)/A2 instead, then apply percentage formatting. Keep headings clear—for example, “Change,” “Absolute gap,” or “% change”—so readers know what the result means.
Excel’s relative references are what make filling down convenient: A2 and B2 become A3 and B3 in the next row. If a formula should always refer to a single fixed cell, dollar signs can lock a reference, as in =$B$1-A2. That is useful when every row is compared with one shared target, but it is not needed for ordinary pair-by-pair comparisons.
Format and interpret the result
Excel may display a percentage calculation as a decimal until you format the cell. Select the result cell or range, then choose Home > Number > Percent Style. Increase or decrease the displayed decimal places with the decimal-place controls in the Number group if the default rounding is not suitable. Formatting changes how a value appears; it does not change the underlying calculation.
For ordinary differences, use a number format that suits the data. A result of 15 could mean 15 dollars, 15 items, or 15 degrees; Excel does not infer the unit from the values. Add a column heading or an appropriate number format so the result is understandable. Also distinguish a negative change from an absolute gap: a negative number is meaningful in a signed calculation, while an absolute difference should not be negative.
Common errors and how to fix them
- The result has the wrong sign: Check the order of the cell references. “New minus original” and “original minus new” produce opposite signs. Use ABS only if direction is irrelevant.
- A percentage looks like a decimal: Apply percentage formatting. A stored result of 0.25 represents 25%, not 0.25%.
- You see #DIV/0!: A percentage-change formula is dividing by zero, or its original-value cell is empty and treated as zero. Decide how that case should be handled rather than assuming Excel can calculate a meaningful percentage.
- The formula appears as text: Check that the cell is not formatted as Text and that the entry begins with an equals sign. If needed, change the format to General, edit the cell, and press Enter.
- The result is unexpectedly zero: The two source values may be equal, or their displayed values may hide small decimal differences. Increase the displayed decimal places to inspect them.
- A blank source produces an odd result: Excel treats a truly empty referenced cell as zero in basic arithmetic. If rows may be incomplete, use a formula that leaves the result blank until both inputs are present.
For that last case, with the two inputs in A2 and B2 and the result in C2, use =IF(OR(A2="",B2=""),"",B2-A2). It leaves C2 blank if either input is empty and subtracts when both contain values. If a zero is a valid input, this check still allows it; zero is not the same as an empty cell.
Which calculation should you use?
Choose the formula based on the question, not just the word “difference.” For “How much did the value rise or fall?” subtract the original from the new value. For “How far apart are these two values?” use an absolute difference. For “What percentage did it rise or fall from the original?” divide the signed change by the original. For “How large is the gap relative to both values?” an absolute difference divided by their average may fit, but label the convention clearly.
These calculations answer different questions. If a temperature changes from 10 to 20, the signed change is 10 degrees and the increase from the original is 100%. The absolute gap is also 10 degrees. Reporting only “10” loses the distinction between units and direction; reporting “100%” without saying it is relative to the starting value can also mislead.
Frequently Asked Questions
What is the simplest formula to subtract two cells in Excel?
Type an equals sign, the cell to subtract from, a minus sign, and the cell to subtract. If the numbers are in A2 and B2 and you want B2 minus A2, enter =B2-A2.
How do I get a positive difference in Excel?
Wrap the subtraction in the ABS function. With the values in A2 and B2, use =ABS(B2-A2). It returns the distance between them without indicating which value is larger.
How do I calculate the percentage difference between two numbers?
First decide what “percentage difference” means for your comparison. For change from an original value, subtract the original from the new value and divide by the original. If neither value is the baseline, an often-used alternative divides the absolute gap by the average of the two values. Format the result as a percentage and label the method.
Why does my Excel difference formula show a negative number?
A negative result means the first value in your subtraction is smaller than the value being subtracted. For a change, that may correctly indicate a decrease. Reverse the subtraction only if you intended the opposite direction; use ABS only when the sign should be ignored.
Can Excel subtract times, dates, or prices?
Yes. Excel stores dates and times as numeric values, so subtracting two valid date or time cells returns their interval in days or fractions of a day. Apply a suitable duration or date format to make the result readable. Prices can be subtracted like other numbers, but use a currency number format and make sure both values use the same currency and units.
Related
- Excel Template for a Social Media Calendar
- Excel Job List Template: Track Applications and Follow-Ups
- Excel To-Do List Templates: How to Choose and Use One
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Insert a Curved Line in Word and Adjust Its ShapeTo insert a curved line in Microsoft Word, choose Insert > Shapes, select Curve in the Lines group,…
- Google Search for Excel Files: Find SpreadsheetsYes. To search Google for publicly indexed Excel spreadsheets, add filetype:xlsx or filetype:xls to your search terms—for example,…
- How to Get Out of Track Changes Mode in Microsoft WordTo get out of Track Changes mode in Word, open the Review tab and turn off Track Changes.…
- Conditional Formatting for Negative Values in ExcelIn Excel for Microsoft 365 on Windows, select the cells you want to check, then choose Home >…
- How to Clear Cache in Outlook for WindowsTo clear Outlook’s local cache in Windows, first identify which Outlook you use: in classic Outlook, clear the…
- Excel Arrow Keys Move Screen? Turn Off Scroll LockIf Excel’s arrow keys move the worksheet view instead of moving the selected cell, Scroll Lock is usually…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.