IF Not Blank in Excel: Formulas That Skip Empty Cells

An IF not blank formula in Excel does something only when a cell has data in it, and shows something else, often nothing, when it is empty. The usual way is =IF(C2<>"","Shipped","Pending"). This guide explains how that works, when to use ISBLANK instead, and how to deal with cells that only look empty.

How to write IF not blank in Excel#

Compare the cell with "", which means empty text, using the not-equal sign <>. Wrap the test in IF to choose the result.

All examples use this order sheet. The Shipped column is empty for orders that haven't left yet:

In D2, type:

=IF(C2<>"","Shipped","Pending")

Then fill down (Windows: Ctrl+D; Mac: ⌘D). Orders A-101, A-103 and A-105 show Shipped; A-102 and A-104 show Pending.

Read it as: "If C2 is not empty, show Shipped, otherwise show Pending." The IF function guide covers the three arguments in detail, and the article on the IF function has more everyday examples.

How to use IF NOT ISBLANK#

ISBLANK checks whether a cell is truly empty, and NOT flips the answer. Together they give the same result on the sheet above:

=IF(NOT(ISBLANK(C2)),"Shipped","Pending")

ISBLANK returns TRUE for an empty cell and FALSE for anything else. NOT turns TRUE into FALSE and FALSE into TRUE, so NOT(ISBLANK(C2)) means "C2 is not empty".

You can also flip the IF instead of using NOT. =IF(ISBLANK(C2),"Pending","Shipped") gives the same results and is a little shorter. The guide to checking if a cell is blank shows that version in full.

Should you use <>"" or ISBLANK?#

Use <>"" in most cases, because it treats cells that only look empty as empty. The two tests disagree on a few kinds of cell:

The second row is the one that catches people out. Many sheets fill columns with formulas like =IF(A2="","",A2*2). Those cells look empty, but ISBLANK says they are not, because they contain a formula. C2<>"" looks at the value instead, so it agrees with what you see.

How to calculate only when a cell is not blank#

Put the calculation in the "true" part of IF and "" in the "false" part. To count the days each order took to ship:

=IF(C2<>"",C2-B2,"")

A-101 shows 2, A-103 shows 3 and A-105 shows 0. The orders without a ship date stay empty instead of showing a large negative number. If the result appears as a date, change the cell's format to General.

This pattern keeps reports clean while data is still coming in. For more ways to work with dates in IF, see IF formulas with dates.

How to check that several cells are not blank#

Use AND when every cell must be filled, and OR when any one is enough:

=IF(AND(B2<>"",C2<>""),"Complete","Missing")
=IF(OR(B2<>"",C2<>""),"Started","Not started")

For A-102, the first formula returns Missing, because C3 is empty. The second returns Started, because B3 has a date. The article on IF with AND explains how these combine.

For a long row, counting is shorter than a chain of AND tests. =IF(COUNTA(B2:F2)=5,"Complete","Missing") checks that all five cells from B to F are filled. Keep in mind that COUNTA counts formulas that return "" as filled, just as ISBLANK does.

How to ignore cells that contain only spaces#

A cell with a space in it is not blank, so C2<>"" returns TRUE. This often happens with data pasted from other systems. To treat spaces as empty, remove them with TRIM and check the length with LEN:

=IF(LEN(TRIM(C2))>0,"Shipped","Pending")

TRIM strips the spaces, LEN counts what is left, and >0 means something real is in the cell. Use this version whenever you can't trust the data to be clean.

How to count or sum only non-blank cells#

You don't always need IF. To count filled cells, use =COUNTA(C2:C6), which returns 3 on the sample data. COUNTIF with the criteria "<>", meaning "not empty", gives the same count:

=COUNTIF(C2:C6,"<>")

The result is again 3. The same "<>" criteria works in SUMIF, so =SUMIF(C2:C6,"<>",E2:E6) would add an amount column only for shipped orders. If the column contains formulas that return "" and you want to skip them, use =SUMPRODUCT(--(C2:C6<>"")), which counts only cells with a visible value.

Common mistakes with IF not blank#

Most broken blank checks come from a small typing slip. Look for these first:

1. A space between the quotes. C2<>" " tests for a single space, not for an empty cell. The two quotes must touch: "".
2. Quotes around the cell. "C2"<>"" compares the letters C and 2 with empty text, so it is always TRUE. Cell references never go in quotes.
3. Using the cell as the test. =IF(C2,"Shipped","Pending") treats any non-zero number as TRUE, and returns #VALUE! when C2 holds text. Always write the full comparison.
4. Mixing up the order. In =IF(C2<>"","Pending","Shipped") the results are swapped. Read the formula aloud: the first result is for a filled cell.

When a result still looks wrong, select the cell and check the formula bar. It shows whether the cell holds a space, a formula or an apostrophe.

Practise it#

Blank checks appear in almost every real sheet and in many job tests. Practise the IF function in a live spreadsheet: you write each formula and the lesson checks it instantly. Then practise ISBLANK and the other IS functions to see exactly what Excel thinks is inside a cell.