ThisCalculator

Everyday

If Cell Is Not Blank? Excel Formula Guide

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

This guide answers the common spreadsheet question: “If cell is not blank?” In Excel and Google Sheets, you use the IF function combined with a logical test to check if a cell is not empty, returning one value if it contains data and another if it is blank. The standard formula is =IF(A1<>"", "Not Blank", "Blank"), where <>"" means “not equal to an empty string.”

Formula

The core formula is:

=IF(cell_reference <> "", value_if_not_blank, value_if_blank)

Where:

  • cell_reference – the cell you want to test (e.g., A1).
  • <> “” – the logical test meaning “not equal to an empty string.”
  • value_if_not_blank – what to return if the cell has any content.
  • value_if_blank – what to return if the cell is empty.

You can replace the return values with numbers, text (in quotes), or other formulas.

Worked Example

Using the default values from the calculator above, we test cell A1. If A1 contains the number 42, the formula =IF(A1<>"", "Not Blank", "Blank") evaluates as:

  1. The logical test checks if A1 is not equal to “”. Since A1 has 42, the test is TRUE.
  2. The formula returns the value_if_not_blank, which is “Not Blank”.

If A1 were empty, the test would be FALSE, and the formula would return “Blank”.

Why Use the Not Blank Test?

Checking for non-blank cells is essential in data cleaning, conditional formatting, and dynamic dashboards. For example, you might want to:

  • Flag rows where a required field is missing.
  • Calculate averages only on cells that have data.
  • Create a dropdown list that ignores empty cells.
  • Send alerts when a cell is filled in.

The <>"" test is the most straightforward way to detect any content, including numbers, text, errors, and even spaces (though spaces are tricky, see below).

Common Mistakes and Pitfalls

Several issues can trip up even experienced users:

  • Spaces as content: A cell containing a space (" ") is not truly blank. <>"" will treat it as non-blank. To ignore spaces, use =IF(TRIM(A1)<>"", ...).
  • Formulas returning empty strings: A cell with =IF(1>2,"","ok") shows “ok” but if the formula returns "", the cell looks blank but is not. The <>"" test will see it as non-blank.
  • Using ISBLANK() incorrectly: ISBLANK(A1) returns TRUE only if the cell is truly empty (no formula, no space). It is the opposite of what you want here.
  • Case sensitivity: The <>"" test is not case-sensitive; it doesn’t matter if the text is uppercase or lowercase.

Alternatives to IF with Not Blank

Depending on your goal, other functions might be more efficient:

  • COUNTA – counts non-empty cells in a range: =COUNTA(A1:A10).
  • COUNTBLANK – counts empty cells: =COUNTBLANK(A1:A10).
  • ISBLANK – returns TRUE/FALSE for a single cell: =ISBLANK(A1).
  • IFERROR – handles errors that might appear when a cell is blank, e.g., =IFERROR(1/A1, "No data").

For conditional formatting, you can use a formula like =A1<>"" to highlight all non-blank cells.

When to Use Each Method

Choose the method based on what you need:

  • Use IF(A1<>"", ...) when you need a custom result for both cases (blank vs. not blank).
  • Use COUNTA when you only need a count of filled cells.
  • Use ISBLANK when you need a TRUE/FALSE flag and you are sure the cell has no formulas returning empty strings.
  • Use TRIM in combination when you want to ignore cells that contain only spaces.

Frequently Asked Questions

Why does my formula return “Not Blank” for a cell that looks empty?

The cell likely contains a space, a formula that returns an empty string (""), or a non-printing character. Use =IF(TRIM(A1)<>"", ...) to ignore spaces, and check if the cell has a formula by selecting it and looking at the formula bar.

How do I check if a cell is not blank in Google Sheets?

Google Sheets uses the same syntax: =IF(A1<>"", "Not Blank", "Blank"). The <>"" operator works identically.

Can I use this with conditional formatting?

Yes. In conditional formatting, select “Use a formula to determine which cells to format” and enter =A1<>"" (adjust the cell reference to the top-left of your range). Apply the format to highlight non-blank cells.

What is the difference between <>"" and ISBLANK()?

<>"" checks if the cell is not equal to an empty string, which is true for any content, including spaces and formulas that return "". ISBLANK() returns TRUE only when the cell is completely empty—no formula, no space, no nothing.

How do I count all non-blank cells in a range?

Use =COUNTA(A1:A100). This counts all cells that are not empty, including cells with formulas that return an empty string. If you want to exclude cells with formulas returning "", you’d need a more complex array formula.

Does this work with numbers and dates?

Yes, the <>"" test works for any data type. A cell with a number, date, or time is considered non-blank. Even a cell with a zero (0) is non-blank.

Related

Formula used
in cell | Cell reference | - | A1 | 0
out result | Result | - | IF(cell <> "", "Not Blank", "Blank") | 0

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.