To calculate the difference between two cells in Excel, subtract one from the other: with the earlier or starting value in A2 and the later or comparison value in B2, enter =B2-A2. The result is a signed difference: it is positive when B2 is larger and negative when B2 is smaller. If you mean statistical variance rather than an ordinary difference, use a variance function instead.
The basic formula: subtract one cell from the other
Excel does not need a special function for the everyday meaning of variance as the amount by which two values differ. Subtraction is the simplest and clearest approach. With the first value in A2 and the second value in B2, enter =B2-A2 in another cell, such as C2. If A2 contains 125 and B2 contains 148, the result is 23, because 148 minus 125 equals 23.
The order matters. The formula describes B2 relative to A2, so switching the references reverses the sign. A result of 23 says the second value is 23 units higher than the first; a result of -23 says it is 23 units lower. This directional information is useful for tracking changes in sales, inventory, temperatures, scores, or other measurements.
To enter the calculation, select the cell where you want the result, type the formula beginning with an equals sign, and press Enter. Excel displays the calculated value in that cell. The formula bar shows the formula when the result cell is selected, which makes it easier to check which cell is being subtracted from which.
What variance means in a spreadsheet
People use the word variance in two different ways. In ordinary business and everyday language, it often means a difference, change, or gap between two figures. For that meaning, subtracting the cells is appropriate. In statistics, variance has a specific definition: it measures how spread out a set of observations is around its mean. Statistical variance is not simply the difference between two individual cells.
This distinction matters when you are following a report, assignment, or established spreadsheet. A budget-versus-actual table might label the difference between two amounts as variance, even though the calculation is just subtraction. A statistics course asking for the variance of observations is asking for a measure of dispersion, calculated from the observations and their mean. Check the intended meaning before choosing a formula.
Units also help clarify what you calculated. If the two cells contain dollars, the subtraction result is in dollars. If they contain counts, the result is in counts. A statistical variance, by contrast, is expressed in squared units. That difference is one reason a statistical variance should not be substituted for a simple change when comparing two business figures.
Signed difference or absolute difference
A signed difference preserves direction. With the original value in A2 and the comparison value in B2, =B2-A2 returns 23 when the values are 125 and 148, and returns -23 if the values are 148 and 125. This is usually the best choice when you need to see whether a value rose or fell, or whether actual results were above or below a baseline.
Sometimes you care only about the size of the gap, not which cell is larger. With values in A2 and B2, use =ABS(B2-A2). The ABS function returns the absolute value, so the result is 23 whether the subtraction would otherwise be positive 23 or negative 23. This is useful for measuring an error amount or distance, but it removes the direction of the difference. Keep the signed result if the sign itself has meaning.
For example, if a target is in A2 and actual performance is in B2, a positive signed result can mean performance exceeded the target and a negative result can mean it fell short. If the goal is only to flag how far the result is from the target, the absolute difference may be more suitable. Make that choice deliberately rather than treating the two formulas as interchangeable.
Calculate percentage change between cells
A raw difference does not show how large a change is relative to the starting amount. A change of 20 is substantial when the original value is 50, but small when it is 10,000. For percentage change, put the original value in A2 and the new value in B2, then use =(B2-A2)/A2. With 125 in A2 and 148 in B2, the calculation is 23 divided by 125, or 0.184. Format the result cell as a percentage to display 18.4%.
In Microsoft 365 for Windows, select the result cell and choose Home > Number > Percent Style. You can adjust the displayed decimal places using the increase or decrease decimal buttons in the same Number group. Formatting changes how the result is displayed, not the underlying calculation. A value of 0.184 displayed as a percentage becomes 18.4%.
Percentage change is directional and depends on the starting value. If A2 is zero, the formula attempts to divide by zero and returns a #DIV/0! error, because percentage change from a zero baseline is undefined by this calculation. A negative starting value may also make the interpretation unintuitive. If you need a symmetric comparison that treats either cell as the reference, you might instead use a percent difference based on the average of the two values.
With the two values in A2 and B2, =ABS(B2-A2)/AVERAGE(A2,B2) calculates the absolute gap divided by their average. For 125 and 148, the average is 136.5 and the gap is 23, so the result is about 16.85%, displayed as a percentage. Percent difference is not the same as percentage change: use percentage change when one value is specifically the baseline, and use percent difference only when a symmetric comparison fits your purpose.
When to use Excel statistical variance functions
If you mean statistical variance, Excel provides VAR.S for a sample and VAR.P for an entire population. For two observations stored in A2 and B2, the sample variance formula is =VAR.S(A2,B2). If A2 is 10 and B2 is 14, their mean is 12; the squared deviations are 4 and 4, and the sample variance is 8 because their sum is divided by one, the sample size minus one.
For the same two observations when they represent the whole population being studied, use =VAR.P(A2,B2). The squared deviations still total 8, but population variance divides by two, producing 4. The choice between sample and population depends on what the data represent, not on which result looks more convenient. A sample variance applies a correction because the observations are being used to estimate the spread of a larger population.
With only two observations, these statistical measures can be calculated, but the small amount of data may not describe a larger group well. Also, neither result is the ordinary gap of 4 between 10 and 14. If a prompt says compare two cells or find the change, use subtraction. If it explicitly asks for sample or population variance, use the matching statistical function.
Apply a comparison formula to more rows
For a table with one pair of values per row, place the formula in the first result row and fill it down. For example, if original values are in A2:A20 and comparison values are in B2:B20, enter =B2-A2 in C2. When you copy or fill that formula down, Excel adjusts the relative references in each row: the next row compares B3 with A3, then B4 with A4, and so on.
On Windows, select C2 and drag the fill handle, the small square at the lower-right corner of the selection, down through the rows you want to calculate. You can also copy C2, select the destination cells, and press Ctrl+V. On a Mac, the same fill-handle approach is available; the common copy shortcut is Command+C and paste is Command+V. In Excel for the web, use the fill handle or copy and paste as well.
Check the first few filled results against the source rows before relying on a long column. If the reference cells should remain fixed while copying, an absolute reference such as $A$2 keeps that cell from changing. For a row-by-row comparison, however, both references normally need to move together, so ordinary relative references are usually right.
Common errors and data issues
If subtraction returns #VALUE!, inspect both input cells. One may contain text, a pasted number stored as text, or other unexpected content. A cell can look numeric and still be text, especially after importing data. Convert the input to a real number or correct the source data, then recalculate. If a referenced cell contains an Excel error, the result will generally carry that error forward.
A truly blank cell used in a direct arithmetic expression is treated as zero. That can create a plausible-looking but misleading answer if one value is missing. If a missing comparison should produce a blank result instead, and the values are expected to be numeric, place the values in A2 and B2 and use =IF(COUNT(A2,B2)=2,B2-A2,""). COUNT checks that both cells contain numbers; if either does not, the formula returns an empty string rather than a difference. This approach also returns a blank when a cell contains text, so it is a guard against incomplete numeric input, not a substitute for checking data quality.
For percentage change, a zero starting value produces #DIV/0!, as noted above. You can decide whether to show a label, a blank, or a separate message for that case, but do not silently treat the percentage as zero: zero change and an undefined percentage are different situations. Also verify that both values use the same units and scale. Comparing 1.5 with 1500 may be a unit-conversion issue rather than a meaningful 1498.5-unit change.
Choose the formula that matches the question
- Use ordinary subtraction when you need the signed amount of change and direction matters.
- Use ABS around the subtraction when you need only the size of the gap.
- Use percentage change when one value is a defined starting point and you want the relative increase or decrease.
- Use percent difference when neither value is the designated baseline and an average-based, symmetric comparison is appropriate.
- Use VAR.S or VAR.P when the question is about statistical spread across observations, and choose the function according to whether the data are a sample or the full population.
Labels can prevent confusion in a shared workbook. A heading such as Difference (B minus A), Absolute gap, or Percentage change makes the calculation and its direction easier to interpret. For a signed variance, document which cell or column is treated as the baseline. Two workbooks can both calculate a valid difference but show opposite signs simply because they subtract in opposite orders.
Frequently Asked Questions
What is the simplest Excel formula to find the difference between two cells?
Subtract the first cell from the second. With the first value in A2 and the second in B2, enter =B2-A2. The sign shows whether the second value is higher or lower.
How do I make the difference positive?
Wrap the subtraction in ABS. With the values in A2 and B2, use =ABS(B2-A2). It returns the magnitude of the gap regardless of which value is larger.
Is variance the same as difference in Excel?
Not always. In many reports, variance means the difference between actual and expected values, so subtraction is appropriate. In statistics, variance measures dispersion around a mean and is calculated with VAR.S or VAR.P.
How do I calculate percentage variance between two cells?
If A2 is the original value and B2 is the new value, use =(B2-A2)/A2 and format the result as a percentage. The starting value cannot be zero for this calculation.
Why does my formula show a negative number?
A negative result means the value being subtracted from is smaller than the starting reference in your formula. Check the order of the cell references and decide whether you want a directional result or an absolute gap.
Related
- Cash Flow Format in Excel: Build a Simple Statement
- CAGR Return in Excel: Calculate Investment Growth
- How to Calculate CAGR in Excel for 3 Years
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula for Start of Month: Dates, Examples & TipsTo return the first day of the month for a date in cell A2, use =DATE(YEAR(A2),MONTH(A2),1). If A2…
- Excel Formula for the Higher of Two Values: MAX or IFTo return the higher of two values in Excel, use the MAX function. If the values are in…
- Excel Formula for Checking If a Date Is Before Another DateTo check whether one date is earlier than another in Excel, use the less-than operator: with the date…
- Excel Data Validation INDIRECT for Dependent ListsYes. In Excel, INDIRECT can make a data-validation drop-down depend on another cell: name each child list after…
- Excel Sort Examples: Rows, Text, Dates, and Custom ListsTo sort a worksheet in Excel for Microsoft 365 on Windows, select a cell in the data, then…
- Excel Trim Decimal Places: Format or Change ValuesTo trim decimal places in Excel, select the cells and choose Home > Number > Decrease Decimal to…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.