How to Show Paid or Unpaid Status Based on Excel Table Values
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.

- 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.
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.
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.
Click on the first empty cell in your 'Paid/Unpaid' status column within the table.
Type the following formula: =IF([@Difference]="","",IF([@Difference]=0,"Paid","Unpaid")).
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.

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. Open WPS Spreadsheet: Launch WPS Office and create a new Spreadsheet or open your existing payment tracker document.
- 2. Format as Table: Select your data range and press Ctrl+L, or go to Home > Format as Table to enable structured references.
- 3. Insert the IF Formula: In your status column, enter the formula =IF([@Difference]="","",IF([@Difference]=0,"Paid","Unpaid")) to automate the payment status.

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.




