Excel has no built-in ISNOTNULL or NOTNULL function. To test whether A2 is not empty, use =A2<>""; it returns TRUE for a value and FALSE for a genuinely blank cell or a formula that displays an empty string. If you need to know whether a cell is physically empty, use NOT(ISBLANK(A2)) instead—the distinction matters when formulas or imported data are involved.
What “not null” means in Excel
“Not null” is familiar from databases and programming, where NULL represents missing or unknown data. Excel does not use a single NULL value in the same way. A worksheet cell may be genuinely empty, contain a number such as 0, contain a space, hold text, or contain a formula whose displayed result is an empty string. These cases can look similar on screen but behave differently in tests and counts.
That is why there is no one Excel function that exactly answers every interpretation of “not null.” Before writing a formula, decide what you mean by present:
- Has a visible or usable value: a comparison with an empty string, such as
<>"", is often appropriate. - The cell contains anything at all: test whether it is not truly blank with
NOT(ISBLANK(...)). A formula is content even if its result looks blank. - Count cells with stored content: use
COUNTA, keeping in mind that it counts formulas returning an empty string. - Count cells that evaluate to something other than an empty string: compare the range to
""and sum the results.
In practical spreadsheets, the first choice is usually what someone means when asking for a “not null” test. The other interpretations are useful for auditing formulas, imported data, and data-entry rules.
Choose the test that matches the question
Use A2<>"" when you want a TRUE/FALSE answer based on whether A2 evaluates to a non-empty value. It treats zero and the logical value FALSE as present, because neither is the same as an empty string. A cell containing one or more spaces also passes this test: spaces are text, not emptiness.
Use NOT(ISBLANK(A2)) when you need to distinguish an unused cell from a cell containing a formula, even when that formula displays nothing. ISBLANK is TRUE only when the referenced cell is genuinely empty. A cell containing a space, a number, text, or a formula is not blank in this sense.
For a status label rather than TRUE or FALSE, the same comparison can be put inside IF. With a value in A2, the formula =IF(A2<>"","Present","Missing") displays “Present” for a value that is not empty and “Missing” for a blank or formula-produced empty string. If formulas themselves should count as present, change the condition to NOT(ISBLANK(A2)). These formulas are ordinary worksheet formulas; they do not require a special “not null” add-in or setting.
Test one cell with a formula
For a direct check, enter the formula in a spare cell and replace A2 with the cell you want to inspect. With the value to test in A2, =A2<>"" returns TRUE for text such as “North,” a date, or the number 0. It returns FALSE if A2 is empty or if a formula in A2 returns "". This is a convenient test for conditional formulas and helper columns.
If the result should be a word, use this version. With the value to test in A2, enter =IF(A2<>"","Complete","Enter a value"). A zero in A2 produces “Complete,” not “Enter a value.” That is often important in quantity or financial data, where zero can be a legitimate answer rather than missing information.
To check whether A2 is literally unused, regardless of what it displays, use =ISBLANK(A2) with the value to test in A2. This returns TRUE for a genuinely empty cell and FALSE for a formula returning an empty string. To reverse that result and label cells that contain something, use =NOT(ISBLANK(A2)). These are the built-in functions closest to the idea of “is null” and “is not null,” but they answer the physical-blank question, not necessarily the visible-value question.
Count nonempty cells in a range
COUNTA is the simple option when you want to count cells containing data or formulas. Suppose A2:A6 contains the text Alice, a genuinely blank cell, a formula returning "", the number 0, and the text Bob. With those entries in A2:A6, =COUNTA(A2:A6) returns 4: the two text entries, the formula, and zero all count. Use it when a formula itself means that the cell is occupied.
If instead you want to count cells whose evaluated result is not an empty string, compare each cell with "" and add the TRUE results. With the same example values in A2:A6, =SUMPRODUCT(--(A2:A6<>"")) returns 3: Alice, 0, and Bob. The empty cell and the formula returning an empty string do not contribute. The double unary operator converts TRUE/FALSE comparisons to 1/0, and SUMPRODUCT adds them.
Excel’s COUNTBLANK can help check the other side of the same dataset. With the same contents in A2:A6, =COUNTBLANK(A2:A6) returns 2, counting the genuinely empty cell and the formula result that is an empty string. These examples show why COUNTA and a comparison against "" can return different totals. Select the one that reflects what your report calls a completed or populated entry.
Filter rows with a value
In Microsoft 365 or Excel 2021 and later, FILTER can return only rows whose key column is not empty. Suppose names are in A2:A10 and related details are in B2:B10. Enter =FILTER(A2:B10,A2:A10<>"","No matches") in an empty area. Excel spills the matching rows into nearby cells and excludes rows where the name is blank or evaluates to an empty string. The optional third argument displays “No matches” if there is nothing to return.
This is useful for creating a clean live list from a larger table without deleting source rows. If formulas that display an empty string should still qualify, use a condition based on NOT(ISBLANK(A2:A10)) instead, but check the result with your data because array behavior and the intended definition both matter. In particular, a helper column that returns empty strings may be deliberately treated as absent in a visible list.
FILTER is available in Microsoft 365 and Excel 2021 or later, not in older perpetual versions such as Excel 2019. In those older versions, use a table filter or a helper column with the one-cell test, then filter the helper column for TRUE. In Excel for the web, dynamic-array formulas such as FILTER are supported; the formula itself is the same. The Mac edition also uses the same formula syntax, though ribbon locations and dialog layouts can vary slightly.
Common mistakes and edge cases
- Confusing zero with missing data:
0<>""is TRUE. Do not use a not-empty test if zero should count as missing; that is a different condition and should be stated explicitly. - Overlooking spaces: a cell containing a space is not empty. If entries may include accidental leading or trailing spaces, clean or normalize the data rather than assuming the basic test removes them.
- Assuming a formula that looks blank is actually blank: a formula such as one that returns
""still occupies its cell.ISBLANKreturns FALSE for it, while a comparison to""treats its displayed result as empty. - Counting errors as missing values: an error such as
#N/Ais not a blank. A direct comparison such asA2<>""can itself return an error if A2 contains an error. Handle errors separately if the source data can contain them. - Expecting a blank-looking cell to be empty: formatting can hide a value, and a formula may return an empty string. Check the formula bar or test with both
ISBLANKand a comparison to""to identify what is actually there.
For data imported from a database or text file, also consider whether the source uses an empty string, whitespace, a placeholder such as “N/A,” or a true missing value. Excel may represent these as ordinary text or blank cells after import. Decide on a consistent rule during cleanup so that counts, filters, and charts agree.
Excel checks versus database NULL
In SQL, missing-value checks commonly use IS NULL and IS NOT NULL; a comparison such as column = NULL does not work as an ordinary equality test. Excel’s <>"" is not a direct translation of SQL IS NOT NULL. It tests whether a cell’s evaluated value differs from an empty string, and it treats formula-generated empty strings differently from the physical-occupancy test in ISBLANK.
This difference matters when a workbook is a report or staging area for a database. A zero, an empty string, a truly empty cell, and a text placeholder may each map differently during export or import. Do not infer database NULL semantics from how a cell looks. Document the workbook’s rule—for example, “blank or empty-string results mean missing; zero is valid”—and apply that rule consistently in formulas and data validation.
Frequently Asked Questions
Does Excel have an ISNOTNULL function?
No. Excel has ISBLANK, but it does not have a built-in ISNOTNULL or NOTNULL worksheet function. For a typical not-empty test, compare the cell with an empty string using <>""; use NOT(ISBLANK(cell)) when formulas that display nothing should still count as occupied.
How do I check if a cell is not blank in Excel?
For the common visible-value test, use =A2<>"", replacing A2 with the cell to test. It returns TRUE for zero, text, and other non-empty results, and FALSE for a blank or an empty-string result.
What is the difference between ISBLANK and A2<>""?ISBLANK(A2) checks whether the cell itself has no content. A formula returning an empty string makes ISBLANK return FALSE. The comparison A2<>"" checks the evaluated result, so it returns FALSE for that empty-string formula.
Does COUNTA count formulas that return an empty string?
Yes. COUNTA counts cells containing formulas, even when their displayed result is "". If your goal is to count only results that are not empty strings, compare the range to "" and sum the TRUE/FALSE results instead.
Does Excel treat 0 as blank?
No. Zero is a numeric value, so it passes a not-empty test such as A2<>"" and is counted by COUNTA. If zero has a special meaning in your sheet, build that condition into a separate formula rather than treating it as an empty cell.
Related
- Excel File Could Be Corrupted or Unsafe? What to Do
- State Heat Map in Excel: Create, Format, and Troubleshoot
- Stop Flash Fill in Excel: Turn Off Automatic Filling
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel IFS List: Syntax, Examples, and Common FixesExcel’s IFS function checks conditions in order and returns the result paired with the first condition that is…
- IF Then Formula for Dates in Excel: Examples and FixesUse Excel’s IF function to test a date and return one result when the condition is true and…
- Excel Table Formulas Not Carrying Down? Fixes That WorkIf a formula in an Excel table is not carrying down, first confirm the data is an actual…
- HLOOKUP Examples for Practice: Formulas and Fixes in ExcelFor an exact-match HLOOKUP practice example, put product codes in A1:D1, prices in A2:D2, and stock counts in…
- Excel FIND Function: How to Find Text Within a StringIn Excel, use FIND to locate text inside a string and return the position where it begins. For…
- How to Copy a Formula in Excel to Other Cells SafelyTo copy a formula in Excel, select the cell that contains it, then drag its fill handle into…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.