SheetSense

Replace blank cells with a default

Comparing a cell to an empty string is true for blank cells, so IF can return a default for them and pass the value through otherwise.

fx
=IF(A2="","Missing",A2)

The sheet: Column A is Department and some rows are empty. In a new column you want the word Missing for those rows and the original value for the rest.

How it works, step by step

  1. 01A2="" is TRUE when A2 is empty, or contains an empty string from another formula.
  2. 02If it is, IF returns Missing.
  3. 03Otherwise it returns A2 unchanged.

The same logic in four tools

Excel
=IF(A2="","Missing",A2)
Google Sheets
=IF(A2="","Missing",A2)
SQL
SELECT COALESCE(NULLIF(department, ''), 'Missing') AS department
FROM employees;
Python (pandas)
df["department"] = df["department"].replace("", pd.NA).fillna("Missing")

Mistakes that break it

  • !A cell holding only a space is not blank. Test TRIM(A2)="" to catch it.
  • !ISBLANK is stricter: it is FALSE for a cell with a formula that returns an empty string.
  • !A number 0 is not blank. A2="" is FALSE for 0.

Common questions

How do I fill blanks with the value above?
That needs a different formula, such as =IF(A2="",B1,A2) in a helper column, or Fill Down in Go To Special.
How do I count blank cells?
Use COUNTBLANK(A2:A100).

Got a version of this with your own ranges?

Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.