Use an Excel IF Formula to Show Complete or Pending Only When a Cell Has Data
Question details
The user needs a formula that displays Complete or Pending only when an input cell has data.

- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A worksheet uses column A as the required input and column L to determine whether a row is complete.
- Observed behavior
- The desired result is blank when A2 is empty, Complete when A2 and L2 have data, and Pending when A2 has data but L2 is empty.
Identify the required input and completion cells in your worksheet. The example uses A2 and L2, with the result in M2; adjust the references if your columns differ.
Use a Nested IF Formula to Display the Row Status
Use this formula when one cell controls whether a status appears and another determines whether it is Complete or Pending.
In Excel, select M2 or another empty cell where you want the status to appear.
Type =IF(A2="","",IF(L2<>"","Complete","Pending")) in the selected cell, then press Enter.
Select the formula cell and drag its fill handle down the column to apply the formula to other rows.
Enter data in A2 and L2 to display Complete, clear L2 to display Pending, or clear A2 to leave the result blank.

Create Complete or Pending Statuses in WPS Spreadsheets
Use the same nested IF formula in WPS Spreadsheets to show a status only when the required input cell contains data.
- 1. Open your worksheet: Open WPS Spreadsheets and select Open to choose your spreadsheet file.
- 2. Enter the status formula: Select M2 and enter =IF(A2="","",IF(L2<>"","Complete","Pending")), then press Enter.
- 3. Apply it to other rows: Select M2 and drag the fill handle down the status column.
- 4. Save the workbook: Choose File > Save or Save As to save your changes, using XLSX if you need Excel compatibility.

Frequently Asked Questions
Why does the formula return a blank result when A2 is empty?
The first IF test checks A2="" and returns an empty string when that condition is true, so no status is displayed.
How can I use different columns for the input and completion cells?
Replace A2 and L2 in the formula with the appropriate cell references. For example, use B2 for the required input and C2 for the completion check.
How do I apply the formula to every row?
Select the cell containing the formula and drag its fill handle down. The relative references will adjust for each row.
Why does a cell that looks empty return Complete?
L2 may contain a space or another hidden value. Select L2 and press Delete to clear it, then check the status again.




