Function Problems

How to Show Paid or Unpaid Status Based on Excel Table Values

Muhammad TalhaMuhammad Talha Nov 11, 2026 869 views

Question details

The user wants to display a 'Paid' or 'Unpaid' status in an Excel table based on the value in a 'Difference' column, while ensuring the status cell remains blank if the 'Difference' cell is empty.

How to Show Paid or Unpaid Status Based on Excel Table Values
Product
Excel
Device & OS
not provided
Scenario
Tracking invoice or bill payments using an Excel table where a status column updates dynamically based on remaining balances.
Observed behavior
The user needs a formula to evaluate the 'Difference' column to output 'Paid', 'Unpaid', or a blank space.
Before you start

Ensure your data is formatted as an official Excel Table (Ctrl+T) so that structured references like '[@Difference]' work correctly, and verify that your 'Difference' column calculates the remaining balance accurately.

Solution 1Recommended

Use a Nested IF Formula with Structured References

Apply an IF formula that first checks for blank cells, then evaluates if the remaining balance is zero.

The IF function allows you to make logical comparisons. By nesting IF functions, you can test multiple conditions sequentially.

In an Excel table, using structured references like '[@Difference]' makes your formulas easier to read and automatically applies them to new rows.

1
Select the Status Cell

Click on the first empty cell in your 'Paid/Unpaid' status column within the table.

2
Enter the Formula

Type the following formula: =IF([@Difference]="","",IF([@Difference]=0,"Paid","Unpaid")).

3
Apply and Verify

Press Enter. Because it is an Excel Table, the formula should automatically fill down the entire column. Verify that rows with a zero difference show 'Paid', non-zero show 'Unpaid', and empty rows remain blank.

Use a Nested IF Formula with Structured References
Adjusting Thresholds: If your logic uses a different column name or threshold (e.g., balance < 0), replace '[@Difference]' and '0' in the formula with your specific criteria.
Smart Financial Tracking

Easily Manage Payment Tracking with WPS Spreadsheet

WPS Spreadsheet fully supports Excel's IF functions and structured table references. You can easily set up automated invoice tracking and financial tables for free, using a highly compatible and user-friendly interface.

  1. 1. Open WPS Spreadsheet: Launch WPS Office and create a new Spreadsheet or open your existing payment tracker document.
  2. 2. Format as Table: Select your data range and press Ctrl+L, or go to Home > Format as Table to enable structured references.
  3. 3. Insert the IF Formula: In your status column, enter the formula =IF([@Difference]="","",IF([@Difference]=0,"Paid","Unpaid")) to automate the payment status.
100% compatible with Microsoft Excel formulas and file formats (.xlsx)Built-in financial templates for invoice and payment trackingLightweight software that runs smoothly on older devicesFree to download and use with a familiar interface
microsoft office alternative - wps office

Frequently Asked Questions

Why is my formula returning an error instead of 'Paid' or 'Unpaid'?

An error like #NAME? usually occurs if the column name in the formula (e.g., [@Difference]) doesn't exactly match the header in your table. Double-check your column headers and ensure your data is formatted as a Table.

Can I highlight the 'Unpaid' cells in red automatically?

Yes, you can use Conditional Formatting. Select your status column, go to Home > Conditional Formatting > Highlight Cells Rules > Equal To, type 'Unpaid', and select a red fill format.

How do I make the formula work if I'm not using an Excel Table?

If you are using a standard range instead of a Table, replace the structured reference with standard cell references. For example, use =IF(C2="","",IF(C2=0,"Paid","Unpaid")) assuming C2 is the first cell containing the difference value.