Country or region and language

    Choose your country or region and your language.

    Suggested for you

      All countries and regions

        Choose your character

        It shows in the top bar and on your profile. Change it any time.

        Subtraction formula in Excel

        Learn the subtraction formula in Excel to subtract cells, copy formulas down a column, and handle multiple payments with sign checks and practical error fixes.

        A person practicing on a laptop in a quiet workspace

        To write a subtraction formula in Excel, enter an equals sign, select the cell containing the starting amount, type a minus sign, and select the cell containing the amount to subtract. Press Enter to calculate the result; for an invoice balance, subtract payments from the invoice amount.

        You'll build a small invoice worksheet in Philippine pesos, check that the balances mean what you expect, and then include another payment. The example uses Excel for Microsoft 365 on Windows, with English function names and comma argument separators.

        Subtraction with the minus sign

        Excel subtracts with the minus operator, the keyboard minus sign, rather than a dedicated SUBTRACT function. The equals sign tells Excel to calculate an expression instead of treating the entry as ordinary text.

        A cell reference identifies a worksheet cell by its column letter and row number. In the exercise, column B holds invoice amounts and column C holds payments. Referring to those cells lets Excel update the balance whenever an amount changes, without requiring you to rewrite the calculation.

        The order matters: start with the invoice amount, then subtract the payment. Before copying a formula across several records, check one result against the business meaning. A payment smaller than the invoice should leave a positive balance, not a negative one.

        If cell references are new to you, try Formulas and cell references before building the worksheet.

        An invoice balance you can check

        An invoice balance is the invoice amount minus the payments assigned to that invoice. Use zero as the settled target, a positive number for an amount still owed, and a negative number for payments exceeding the invoice amount.

        Open a blank worksheet and follow the exercise below in order. The complete specification keeps the starting data, cell changes, checks, and additional-payment variation together. Paste only the tab-separated ledger into A1, not the surrounding instructions.

        Practice what this post teaches

        Fictional exercise: invoice balances in Philippine pesos
        Target: a settled invoice has a balance of 0.
        Balance = invoice amount minus payments.
        Positive balance = unpaid amount.
        Negative balance = payments exceed the invoice amount.
        Enter amounts as numbers, without currency symbols.
        
        Paste into A1 in a blank worksheet:
        Invoice	Invoice amount	Payment	Balance
        INV-101	12000	4500
        INV-102	8000	8000
        INV-103	6000	6500
        
        1. Select D2, enter =B2-C2, and press Enter.
        Expected D2: 7500
        Sign check: 12000 minus 4500 leaves 7500 still owed.
        Confirm that D2 is positive before copying the formula.
        
        2. Select D2:D4, then choose Home > Fill > Down.
        Expected formulas and results:
        D2: =B2-C2    Result: 7500
        D3: =B3-C3    Result: 0
        D4: =B4-C4    Result: -500
        
        Expected ledger:
        Invoice   Invoice amount  Payment  Balance  Interpretation
        INV-101   12000           4500     7500     Still owed
        INV-102   8000            8000     0        Settled
        INV-103   6000            6500     -500     Excess payment
        
        3. Verify that payment plus balance equals the invoice amount.
        Enter =C2+D2=B2 in F2.
        Select F2:F4, then choose Home > Fill > Down.
        Expected F2:F4: TRUE, TRUE, TRUE
        
        Variation: record another payment against INV-101.
        4. Enter Additional payment in E1 and 1500 in E2.
        Before changing D2, its value is still 7500 because its formula
        uses only the original payment in C2.
        
        5. Replace the formula in D2 with =B2-C2-E2.
        Expected D2: 6000
        
        6. Replace the formula in F2 with =C2+E2+D2=B2.
        Expected F2: TRUE
        
        7. Replace the formula in D2 with =B2-SUM(C2,E2).
        Expected D2: 6000
        The SUM version subtracts the total of both payments.
        F2 should still show TRUE.
        Leave D3:D4 and F3:F4 unchanged.

        Enter the amounts as plain numbers first. You can apply a peso currency format afterward; the display format doesn't change the subtraction. Keeping currency symbols out of the initial entries also makes it easier to spot values accidentally stored as text.

        A formula down the column

        Filling down copies the formula while adjusting its relative references, which are cell references that change position when copied. Selecting D2:D4 includes both the original formula and the destination cells; Home > Fill > Down copies the top cell's formula into the cells below it.

        Select D3 after filling down and look at the formula bar, the area above the worksheet that shows the active cell's contents. The calculation should use row 3, not row 2. D4 should use row 4. Those changing row numbers let each invoice use its own amount and payment.

        To practice another way to copy formulas, try Copy formulas with the fill handle. The Work Gym checks exercises automatically and provides a hint that names the mistake.

        Read the results before moving on. INV-102 is settled because its payment equals its invoice amount. INV-103 has an excess payment, so its negative balance is meaningful rather than automatically wrong.

        The check in column F adds each payment back to its balance and compares the total with the invoice amount. TRUE means the amounts reconcile under that calculation; FALSE means you should inspect the balance formula and referenced cells.

        Multiple payments in one calculation

        You can subtract multiple payments by chaining minus signs or subtracting the total returned by SUM, the function that adds numbers. Both approaches in the exercise produce the same updated balance.

        Follow the variation by entering the additional payment in E2 before changing D2. Notice that the balance doesn't change yet: Excel only uses cells referenced by the formula. A payment placed beside a calculation isn't automatically included in that calculation.

        Next, replace D2's formula as instructed and update F2's check to include the additional payment. The updated check matters because the original check accounts for only the original payment. Leaving the check unchanged would make a correct updated balance appear inconsistent.

        Finally, replace the chained subtraction with the SUM version. SUM adds the two payments; the minus sign before SUM subtracts their combined total from the invoice amount. Keep the other invoices unchanged because the variation applies only to INV-101.

        Both payment entries in the exercise are positive. If a transaction export represents payments as negative numbers, subtracting those values increases the balance. Check the export's sign convention before using the same formula on imported records.

        Sign and entry mistakes

        Most subtraction problems come from reversed references, omitted payment cells, or entries Excel doesn't recognize as numbers. Diagnose the cause before replacing a result or changing its sign.

        • A positive balance appears negative: inspect the operands, meaning the values on either side of the minus sign. The invoice amount must come first.
        • An excess payment looks inconvenient: don't remove the minus sign just to make the worksheet look correct. Compare the payment and invoice with their source records.
        • A new payment doesn't change the balance: select the balance cell and check whether the formula includes the new payment cell.
        • One copied result repeats the previous invoice's amount: inspect the references in that row. Confirm that you copied a formula rather than pasted a calculated value.

        If a cell displays the formula itself instead of the result, first confirm that the entry begins with an equals sign and doesn't have a leading apostrophe. If the cell uses the Text number format, choose General from the number format list on the Home tab, then re-enter the formula. Changing the format alone doesn't recalculate an existing text entry.

        If formulas appear throughout the worksheet, check whether Formulas > Show Formulas is enabled. Turn that setting off to return to calculated results.

        The worksheet's main limitation is payment assignment: it assumes each payment already belongs to the correct invoice. The arithmetic check doesn't match bank transactions to invoices or prove that the source amounts are correct. Compare the worksheet with the invoice and payment records before using the balances for follow-up.

        Common questions

        How do I subtract one column from another in Excel? Enter a formula in the first result cell that subtracts the second column's cell from the first column's cell on the same row. Check the result, then fill the formula down so relative references adjust for each row.

        Does Excel have a SUBTRACT function like SUM? Excel doesn't have a dedicated SUBTRACT worksheet function. Use the minus operator between numbers or cell references, or subtract a total calculated with SUM.

        How do I subtract multiple cells from one amount? Start with the original amount and subtract each payment reference in sequence. Alternatively, use SUM to add the payment cells and subtract that total from the original amount.

        Why does my subtraction formula return a negative number? A negative result means the amount being subtracted is larger than the starting amount. For an invoice balance, that can indicate an excess payment, but you should also check for reversed references or incorrect source entries.

        Practice what this post teaches

        Your Work Gym account

        Save your progress and pick up on another device. Your browser's progress comes with you.

        or

        You can keep practicing without an account.

        Help and feedback

        Ask a question, tell us what is not working, or share an idea. We reply by email.

        What is it about?

        We use it only to reply to you.