Learn how to build a conditional payment status formula in Excel using IF and IFS functions. Automatically identify paid, unpaid, partially paid, and overpaid invoices, calculate outstanding balances, and simplify payment tracking.
How to Build a Conditional Payment Status Formula in Excel
Keeping track of payments is much easier when Excel automatically tells you whether an invoice is paid, partially paid, or still outstanding. Instead of checking every payment record manually, you can create a conditional payment status formula that updates whenever you enter or change a payment amount.
This is particularly useful for small businesses, freelancers, shop owners, accountants, and anyone who manages customer invoices.
For example, imagine you have issued an invoice for $1,000. The customer has paid $400 so far. Rather than manually writing "Partially Paid" in your spreadsheet, Excel can identify the payment status automatically.
In this guide, you will learn how to build a payment status formula in Excel, understand how it works, and apply it to a practical invoice tracking system.
What Is a Conditional Payment Status Formula in Excel?
A conditional payment status formula checks invoice amounts and payment records to determine the current payment situation.
Depending on the values entered in your spreadsheet, Excel can display different results, such as:
Paid: The customer has paid the full invoice amount.
Partially Paid: The customer has paid some money, but a balance remains.
Unpaid: No payment has been received.
Overpaid: The customer has paid more than the invoice amount.
The formula generally uses Excel's IF function, along with logical conditions such as greater than, equal to, and less than.
The biggest advantage is automation. When you update the received payment amount, the status changes without requiring manual editing.
Set Up Your Payment Tracking Table
Before creating the formula, prepare a simple invoice tracking spreadsheet.
You can use the following column structure:
| Invoice No. | Customer | Invoice Amount | Amount Paid | Payment Status |
|---|---|---|---|---|
| INV-101 | John | $1,000 | $1,000 | Paid |
| INV-102 | Sarah | $800 | $300 | Partially Paid |
| INV-103 | David | $500 | $0 | Unpaid |
| INV-104 | Emma | $600 | $700 | Overpaid |
Here is what each column represents:
Invoice No.: A unique identification number for each invoice.
Customer: The name of the customer.
Invoice Amount: The total amount the customer needs to pay.
Amount Paid: The money received so far.
Payment Status: The automatically calculated payment condition.
Assume your data starts in row 2, with the invoice amount in column C, the amount paid in column D, and the payment status in column E.
Create a Basic Payment Status Formula
The first step is to create a formula that checks whether an invoice has been paid.
Click cell E2 and enter:
=IF(D2>=C2,"Paid","Unpaid")Press Enter and copy the formula down to the remaining rows.
This formula compares the amount paid with the invoice amount.
For example:
Invoice amount: $1,000
Amount paid: $1,000
Result: Paid
If the customer has paid only $500, Excel displays "Unpaid" because the full invoice amount has not been received.
However, this basic formula does not distinguish between partial payments and completely unpaid invoices. For that, you need a more detailed formula.
Build an Advanced Conditional Payment Status Formula
A better approach is to use multiple IF conditions to identify different payment situations.
Enter this formula in cell E2:
=IF(C2<=0,"Check Invoice",IF(D2<0,"Check Payment",IF(D2=0,"Unpaid",IF(D2<C2,"Partially Paid",IF(D2=C2,"Paid","Overpaid")))))This formula checks several conditions in a specific order.
How the formula works:
C2<=0: Checks whether the invoice amount is zero or negative.D2<0: Identifies an invalid negative payment.D2=0: Displays Unpaid when no money has been received.D2<C2: Displays Partially Paid when the received amount is lower than the invoice amount.D2=C2: Displays Paid when both amounts match.The final condition displays Overpaid when the received amount exceeds the invoice amount.
This formula is useful when you want to maintain accurate payment records without checking every invoice manually.
Understand the Formula with Practical Examples
Let's see how the formula responds to different payment situations.
| Invoice Amount | Amount Paid | Expected Status |
|---|---|---|
| $1,000 | $1,000 | Paid |
| $1,000 | $400 | Partially Paid |
| $1,000 | $0 | Unpaid |
| $1,000 | $1,200 | Overpaid |
| $800 | $800 | Paid |
| $800 | $250 | Partially Paid |
| $0 | $0 | Check Invoice |
| $800 | -$50 | Check Payment |
These examples show why multiple conditions are more useful than a simple IF formula.
For instance, if a customer purchases products worth $1,000 and pays $400 today, Excel displays "Partially Paid". When you later update the payment amount to $1,000, the status automatically changes to "Paid".
You do not need to change the status manually.
Calculate the Remaining Payment Balance
Knowing the payment status is useful, but tracking the outstanding amount is equally important.
You can add another column called Remaining Balance.
If the invoice amount is in C2 and the amount paid is in D2, enter:
=MAX(0,C2-D2)This formula calculates how much money is still due.
For example:
| Invoice Amount | Amount Paid | Remaining Balance |
|---|---|---|
| $1,000 | $1,000 | $0 |
| $1,000 | $400 | $600 |
| $800 | $250 | $550 |
| $500 | $0 | $500 |
| $600 | $700 | $0 |
The MAX function ensures that the remaining balance never becomes negative.
This is particularly helpful when a customer accidentally pays more than the invoice amount.
If you also want to track overpayments separately, use:
=MAX(0,D2-C2)This calculates the excess amount received.
Use Conditional Formatting to Highlight Payment Status
You can make your payment tracker easier to read by applying different colours to each status.
For example:
| Payment Status | Suggested Colour |
|---|---|
| Paid | Green |
| Partially Paid | Yellow |
| Unpaid | Red |
| Overpaid | Blue |
| Check Invoice | Grey |
| Check Payment | Orange |
Follow these steps:
Select the Payment Status column.
Go to the Home tab.
Click Conditional Formatting.
Select Highlight Cells Rules.
Choose Text That Contains.
Enter the status you want to highlight.
Select a formatting colour.
Repeat the process for the other payment statuses.
Now, your spreadsheet will visually distinguish paid, unpaid, and partially paid invoices.
Create a Payment Status Formula Using IFS
If you are using Excel 2019 or Microsoft 365, you can also use the IFS function.
It provides a cleaner way to manage multiple conditions.
Enter:
=IFS(C2<=0,"Check Invoice",D2<0,"Check Payment",D2=0,"Unpaid",D2<C2,"Partially Paid",D2=C2,"Paid",D2>C2,"Overpaid")The IFS function checks each condition from left to right and returns the result associated with the first TRUE condition.
This formula produces the same payment statuses as the advanced IF formula.
However, IFS is not available in some older Excel versions. If you need compatibility with older versions, use the nested IF formula instead.
Common Mistakes to Avoid
Even a well-designed payment tracker can produce incorrect results if the data is entered incorrectly.
Keep these points in mind:
Entering amounts as text: Excel may not compare payment amounts correctly if they are stored as text. Use numerical values and appropriate currency formatting.
Ignoring partial payments: A simple Paid or Unpaid formula cannot accurately represent customers who make payments in instalments.
Using the wrong cell references: Make sure your formula compares the invoice amount and amount paid columns correctly.
Forgetting overpayments: If a customer pays more than the invoice amount, the spreadsheet should identify the excess instead of simply showing Paid.
Not checking negative values: Negative invoice amounts or payment entries may indicate data-entry mistakes or adjustments that need separate handling.
Overwriting formulas: Protect formula columns when multiple people use the same spreadsheet.
Frequently Asked Questions
1. How do I automatically show Paid or Unpaid in Excel?
Use =IF(D2>=C2,"Paid","Unpaid") to compare the amount paid with the invoice amount.
2. How do I show Partially Paid in Excel?
Use a nested IF formula that checks whether the amount paid is greater than zero but less than the invoice amount.
3. Can Excel automatically update payment status?
Yes. The status changes automatically whenever you update the invoice amount or payment received.
4. How do I highlight paid invoices in green?
Use Conditional Formatting and create a rule that highlights cells containing the word "Paid". For greater accuracy, use a formula-based rule that matches the exact status.
5. Which Excel function is best for payment tracking?
IF is suitable for most payment trackers. IFS is another convenient option when you need to manage several payment conditions.
Final Thoughts
A conditional payment status formula can turn a basic Excel spreadsheet into a practical invoice management system. It helps you identify completed payments, track outstanding amounts, recognise partial payments, and detect overpayments.
For a simple tracker, the IF function is usually enough. If you manage several invoices and different payment situations, a nested IF or IFS formula offers more flexibility.
Once you combine automatic payment statuses with remaining balance calculations and conditional formatting, you can manage customer payments more efficiently and spend less time checking records manually.

COMMENTS