Formula Errors

Use an Excel IF Formula to Show Complete or Pending Only When a Cell Has Data

Emma BrownEmma Brown Nov 20, 2026 869 views

Question details

The user needs a formula that displays Complete or Pending only when an input cell has data.

Show Complete or Pending Only When a Cell Has Data in Excel
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.
Before you start

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.

Solution 1Recommended

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.

1
Select the result cell

In Excel, select M2 or another empty cell where you want the status to appear.

2
Enter the formula

Type =IF(A2="","",IF(L2<>"","Complete","Pending")) in the selected cell, then press Enter.

3
Fill the formula down

Select the formula cell and drag its fill handle down the column to apply the formula to other rows.

4
Check the results

Enter data in A2 and L2 to display Complete, clear L2 to display Pending, or clear A2 to leave the result blank.

Use a Nested IF Formula to Display the Row Status
Cells containing spaces: A cell containing a space is treated as having data. Clear the cell if it should count as empty.
WPS Spreadsheets

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. 1. Open your worksheet: Open WPS Spreadsheets and select Open to choose your spreadsheet file.
  2. 2. Enter the status formula: Select M2 and enter =IF(A2="","",IF(L2<>"","Complete","Pending")), then press Enter.
  3. 3. Apply it to other rows: Select M2 and drag the fill handle down the status column.
  4. 4. Save the workbook: Choose File > Save or Save As to save your changes, using XLSX if you need Excel compatibility.
Display Complete, Pending, or a blank result based on two cellsFill the formula down to apply it to multiple rowsOpen and edit Microsoft Excel XLSX filesWork in a familiar spreadsheet interface
microsoft office alternative - wps office

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.