Excel does not have a function named NOTBLANK. To test whether A2 is not truly empty, use =NOT(ISBLANK(A2)); it returns TRUE when A2 contains a value, text, or a formula, and FALSE when the cell is genuinely empty. If you want a cell that contains a formula returning an empty string to count as blank too, use a comparison such as =A2<>"" instead.
How Excel’s not-blank test works
ISBLANK checks whether a cell is empty in the specific sense that it has no value or formula. It returns TRUE for a cell that has never been filled, or whose contents have been cleared. Wrapping that result in NOT reverses it: TRUE becomes FALSE, and FALSE becomes TRUE. That is why =NOT(ISBLANK(A2)) is the direct answer when you need to identify cells that contain something.
For example, with a number in A2, the test returns TRUE. It also returns TRUE if A2 contains text, a date, zero, or a formula—even if that formula displays nothing. A truly empty A2 produces FALSE. This last distinction is important: Excel treats a formula in a cell as content, regardless of what the formula displays.
Both functions are available in Microsoft Excel for Microsoft 365 on Windows, and the same formula works in current Excel for Mac and Excel for the web. There is no separate “not blank” function to install or enable.
Choose the test that matches “blank”
People use “blank” to mean two different things: a cell that is physically empty, or a cell that appears empty on the worksheet. Choose the test based on which meaning matters to your task.
- Truly empty versus anything stored: Use
=NOT(ISBLANK(A2)). A formula in A2 counts as content even if its displayed result is an empty string. - Visible or evaluated value versus no visible value: Use
=A2<>"". This treats a genuinely empty cell and a formula result of""as blank for this comparison. - Ignore cells containing only ordinary spaces: Use a trimmed-length test, but remember that some invisible characters are not removed by TRIM.
These tests are not interchangeable. If a column contains formulas intended to show a result later, the first test can report those formula cells as nonblank before they display a useful answer. The second is often more appropriate for checking whether a formula has produced a visible result. On the other hand, if you are auditing whether formulas have been entered, use the first test: it correctly recognizes a formula as present.
Use the test inside IF
A common reason to check for a nonblank cell is to show a status, calculate only after an entry, or avoid displaying a result before its input is provided. With the value or input in A2, this formula returns “Entered” when A2 is not truly empty and “Blank” otherwise: =IF(NOT(ISBLANK(A2)),"Entered","Blank"). If A2 contains 0, the result is “Entered,” because zero is a value, not an empty cell.
If the cell may contain a formula returning an empty string and you want that to produce “Blank,” use this alternative with the same A2 input: =IF(A2<>"","Entered","Blank"). It also reports “Entered” for zero. The comparison is useful in worksheets where formulas return "" until other cells are filled in.
For example, suppose A2 contains =IF(B2="","",B2*2). If B2 is empty, A2 displays nothing, but A2 still contains a formula. Consequently, =NOT(ISBLANK(A2)) returns TRUE, while =A2<>"" returns FALSE. The second test reflects what the user sees; the first reflects what is stored in A2.
Highlight nonblank cells with conditional formatting
You can use the same logic to color cells that contain data. In Excel for Microsoft 365 on Windows:
- Select the cells you want to format, such as A2:A100.
- Choose Home > Conditional Formatting > New Rule.
- Select Use a formula to determine which cells to format.
- With A2:A100 selected and A2 as its first cell, enter
=NOT(ISBLANK(A2)). - Choose Format, set the desired appearance, then select OK to create the rule.
The reference should match the first cell in the selected range. Excel adjusts the relative row reference as it applies the rule to A3, A4, and the remaining cells. If you instead use $A$2, the rule checks A2 for every cell in the selection, which is usually not what you want for a row-by-row highlight. Excel for Mac has the same conditional-formatting feature; ribbon labels and dialogs can vary slightly by release.
If formula-generated empty strings should not be highlighted, use =A2<>"" as the rule formula instead. That choice makes a visual difference in tables where formulas are prefilled down a column.
Count nonblank cells in a range
When you need a count rather than a TRUE/FALSE result, the best function depends on your definition of empty. COUNTA counts cells containing values or formulas, including formulas that return an empty string. For a range A2:A100, the formula =COUNTA(A2:A100) therefore measures cells with stored content, not necessarily cells that look populated.
If you want to count cells whose evaluated value is not an empty string, and the data is in A2:A100, use =SUMPRODUCT(--(A2:A100<>"")). It counts values such as zero and text, while excluding truly empty cells and formula results equal to "". For a small example, if A2 contains 12, A3 is empty, A4 contains a formula returning "", and A5 contains zero, the formula over A2:A5 returns 2: the entries 12 and 0 qualify.
COUNTBLANK is the complementary choice when you want a count of blanks. For the same range A2:A100, =COUNTBLANK(A2:A100) counts empty cells and cells whose formulas return an empty string. It does not mean “count cells with no formula”; use ISBLANK when that precise test is required for an individual cell.
Handle spaces and invisible characters
A cell containing one or more spaces is not truly empty, and a direct blank comparison treats spaces as content. This can happen when data is pasted from another system or when a person presses the spacebar instead of leaving a field empty. If A2 is the cell being checked and you want ordinary spaces trimmed before deciding whether it has text, use =IF(LEN(TRIM(A2))=0,"Blank or spaces","Has content"). A genuinely empty cell, an empty-string formula, or text made only of ordinary spaces produces “Blank or spaces”; text with other characters produces “Has content.”
TRIM removes leading and trailing ordinary spaces and reduces repeated ordinary spaces between words to one. It does not remove every possible invisible character. For example, a nonbreaking space copied from a web page can remain and make a cell appear blank while a test still treats it as populated. If imported data behaves unexpectedly, inspect or clean the source text rather than assuming every visually empty cell is truly empty. Also, do not use a space-cleaning test when spaces themselves are meaningful data.
Common errors and how to fix them
- Using ISBLANK and getting FALSE for a cell that looks empty: Select the cell and inspect the formula bar. It may contain a formula returning
"", a space, or another invisible character. Use a comparison with""for formula-generated empty results, or clean unwanted spaces. - Thinking zero is blank: Zero is a numeric value. Both
=NOT(ISBLANK(A2))and=A2<>""treat a zero in A2 as nonblank. If zero should be treated as missing in your specific task, add a separate zero condition rather than relying on a blank test. - Getting an error from a misspelled function: Excel’s function is
ISBLANK, not “ISNOTBLANK” or “NOTBLANK.” CombineISBLANKwithNOTto reverse its result. - Applying conditional formatting to the wrong cells: Check that the formula references the first cell in the selected range. A fixed reference can make every highlighted cell depend on one unrelated cell.
- Counting formulas instead of visible answers:
COUNTAincludes formula cells that return empty strings. Use an evaluated-value test when your goal is to count visible results.
When a result seems wrong, test one cell at a time in a spare column and compare the output with what is actually in the formula bar. That helps distinguish a genuinely empty cell from an empty-looking formula result or whitespace.
Frequently Asked Questions
What is the Excel formula for not blank?
For a cell that must contain something, use =NOT(ISBLANK(A2)), replacing A2 with the cell you want to test. It returns TRUE for a cell containing a value or formula, including a formula that displays nothing.
Does ISBLANK count a formula returning an empty string as blank?
No. A cell containing a formula is not truly empty, so ISBLANK returns FALSE for it. To treat that result as blank, compare the cell with an empty string, for example =A2<>"" as the nonblank test.
How do I check whether a cell is not empty in an IF formula?
With the input in A2, use =IF(A2<>"","Has data","Empty") if formula results of "" should count as empty. Use =IF(NOT(ISBLANK(A2)),"Has data","Empty") if the presence of any formula should count as data.
How do I count cells that are not blank?COUNTA counts cells containing values or formulas, including formulas returning empty strings. If you need to count evaluated results other than an empty string in A2:A100, use =SUMPRODUCT(--(A2:A100<>"")).
Is zero considered a blank cell in Excel?
No. Zero is a value, so both the ISBLANK-based test and the empty-string comparison treat it as nonblank. If your reporting rules define zero as missing, build that additional rule explicitly.
Related
- Access Database Import from Excel: Steps and Tips
- Excel Attendance Format: A Clear Monthly Attendance Tracker
- Excel Attendance File to Print: Set Up and Print It
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Replace a Circular Reference With an Excel FormulaYes—replace a circular reference by rewriting the calculation so it uses input cells or earlier results, rather than…
- Excel LAMBDA Function Examples for Reusable FormulasExcel LAMBDA lets you turn a formula into a reusable function: define it with =LAMBDA(parameters, calculation), save it…
- Excel Extract Date from DateTime: Formulas and FixesTo extract the date from an Excel date-time value in cell A2, enter =INT(A2). The formula removes the…
- How to Calculate Quality Percentage in Excel With FormulasTo calculate quality percentage in Excel, divide the number of checks that passed by the total number of…
- Excel Formula for the Greater of Two Values: MAX or IF?To return the greater of two numeric values in Excel, use =MAX(A2,B2), assuming the values are in cells…
- Excel Filter by Named Range: What Works and HowYes—but Excel’s standard Filter dropdown does not accept a named range as a direct filter condition. You can…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.