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.
=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
- 01A2="" is TRUE when A2 is empty, or contains an empty string from another formula.
- 02If it is, IF returns Missing.
- 03Otherwise it returns A2 unchanged.
The same logic in four tools
=IF(A2="","Missing",A2)=IF(A2="","Missing",A2)SELECT COALESCE(NULLIF(department, ''), 'Missing') AS department
FROM employees;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.