Excel worksheet formulas do not have a special NULL value: to test whether a cell is not blank, a common formula is =A2<>"". It returns TRUE when A2 contains a value or text, and FALSE when A2 is empty or contains a formula that returns an empty string.
What null means in Excel
“Null” usually means a missing or unknown value in a database or programming language. Excel’s worksheet grid does not use NULL as a built-in value you can compare with a cell. Instead, a cell may be truly empty, contain a formula whose result looks empty, contain a space, contain the text NULL, or contain an error. Those cases can look similar on screen but behave differently in formulas.
If you enter =A2<>NULL and there is no defined name called NULL in the workbook, Excel will generally return a #NAME? error. Excel interprets an unquoted word in a formula as a name, not as a special null constant. If the cell literally contains the four letters NULL, compare it with the text value in quotes instead. That tests for the word, not for a missing value.
The important first decision is what you mean by “null.” If you mean “nothing visible,” a comparison with an empty string is often appropriate. If you mean “the cell has never been populated,” use ISBLANK. If the data is in Power Query, its null values are a separate concept and should be handled there.
How the not-equal-to-blank test works
In Excel, <> is the not-equal-to operator. Two double quotation marks with nothing between them represent a zero-length text string. With data in A2, the formula =A2<>"" asks whether A2 is different from that empty string. This is useful for checks, conditional calculations, and filtering rows that have a value.
For example, if A2 contains the name Maya, the formula returns TRUE. If A2 is genuinely empty, it returns FALSE. If A2 contains a formula such as =IF(B2=0,"",B2) and that formula currently displays nothing, the comparison also returns FALSE. In contrast, a cell containing a space is not empty: the comparison returns TRUE even though the space may be hard to see.
To mark each row as populated or blank, enter a formula in B2 and copy it down. With the value being checked in A2, use =IF(A2<>"","Not blank","Blank"). A2 containing 25 produces “Not blank”; an empty A2 produces “Blank.” The formula tests the cell’s value, not whether someone has ever typed into it or whether it has formatting.
Choose the right test for your goal
There is no single best blank test for every workbook. Match the formula to the distinction your task needs:
A2<>""is a practical test for a value that is not empty, including text and numbers. It treats a truly empty cell and a formula result of""as empty.ISBLANK(A2)is TRUE only when the referenced cell is genuinely empty. A formula in A2 makes it FALSE, even if that formula displays nothing.COUNTBLANK(A2)counts a genuinely empty cell and a formula result of""as blank. It is useful when counting rather than testing one cell.COUNTA(A2)counts a cell containing a formula, text, number, or error as occupied. A formula returning an empty string is still counted by COUNTA, which can surprise people.
For a single-cell test, with the candidate cell in A2, =ISBLANK(A2) returns TRUE for a cell with no contents and FALSE for a formula that returns an empty string. With the same layout, =COUNTBLANK(A2) returns 1 for either a genuinely empty cell or a formula result of "", and 0 for ordinary text or numbers. Both functions are available in current Excel versions.
Check for the literal text NULL
Sometimes a file uses the word NULL as a placeholder. That is ordinary text, not Excel’s representation of an empty cell. If A2 contains the data and you want to identify rows whose text is not the word NULL, use =A2<>"NULL". This returns FALSE when A2 contains NULL and TRUE for other values, including a blank. It does not, by itself, tell you whether the cell is blank.
To identify the literal word, reverse the comparison: =A2="NULL". Excel’s ordinary text comparisons are generally not case-sensitive, so this will match variations such as null or Null as well. If case matters, the EXACT function can make a case-sensitive text comparison. Be careful not to treat placeholder text as the same thing as missing data unless that is how the source system defines it.
Handle spaces, zeroes, and errors
A visible blank may actually contain one or more spaces. The basic not-empty comparison treats spaces as content, and ISBLANK also returns FALSE. If you want to treat cells containing only ordinary spaces as blank, and the value in A2 is text, use =LEN(TRIM(A2))=0. It returns TRUE for an empty cell, an empty string, or text made only of ordinary spaces; it returns FALSE for text such as “North.”
TRIM removes leading and trailing ordinary spaces and reduces repeated internal ordinary spaces to one. It does not remove every character that can look like a space. Data copied from web pages may include a nonbreaking space, for example. With text in A2, this alternative also removes character 160 before testing: =LEN(TRIM(SUBSTITUTE(A2,CHAR(160),"")))=0. Use it when imported text appears blank but is still being treated as content. This formula is intended for text values; errors in the source can still cause a formula error.
Zero is not the same as blank. A cell containing the number 0 is a value, so A2<>"" returns TRUE. If zero means “no data” in a particular dataset, include that rule explicitly rather than relying on a blank test. Likewise, a source cell containing an error such as #N/A can make a comparison return an error. Decide whether errors should be reported, excluded, or handled separately instead of silently treating them as blanks.
Use blank tests in practical formulas
A blank check can prevent a calculation from displaying an unwanted zero when its inputs are missing. Suppose A2 contains a quantity and B2 contains a price, and C2 should show their product only when both inputs are present. With the inputs in A2 and B2 and the result formula in C2, enter =IF(OR(A2="",B2=""),"",A2*B2). If A2 is 3 and B2 is 12, C2 returns 36. If either input is blank or returns an empty string, C2 displays nothing.
That example intentionally treats zero as a supplied value: zero is not equal to an empty string, so a zero quantity can produce a numeric result of 0. If zero should also suppress the result, add that as a separate condition. Making the rule explicit helps prevent a formula from confusing a legitimate zero with missing information.
For a quick visual review, you can also filter a column to show blanks or nonblanks. In Excel for Microsoft 365 on Windows, select the data and choose Data > Filter, then use the column’s filter menu to select or clear the blanks option. Filtering changes which rows are displayed, not the underlying values. For more complex checks, a helper formula can make the definition of “missing” visible and easier for another person to audit.
Worksheet blanks versus Power Query null
Power Query, Excel’s data import and transformation tool, does represent missing values as null. That is different from a worksheet formula treating a cell as blank. When cleaning data in Power Query, you can filter a column to remove or retain null values, or use the column’s replacement options where appropriate. This is often the best place to handle nulls when the data is still being transformed before it reaches the worksheet.
In Microsoft 365 for Windows, open a query through Data > Queries & Connections, then open the query for editing. In the Power Query Editor, use the filter menu on the relevant column to select or exclude null entries. The exact choices depend on the column’s type and the values present. After loading the result, a Power Query null commonly appears as an empty cell in the worksheet, so the grid alone may not preserve the distinction you saw during transformation.
In Excel for the web, access to Power Query editing and available actions can differ from the Windows desktop app and may depend on the account and workbook. Mac capabilities can also differ by Excel release. If the distinction between null and an empty text value matters, verify it in the query before loading, rather than assuming that the worksheet display retains every source-data distinction.
Frequently Asked Questions
What is the Excel formula for not equal to blank?
With the cell to test in A2, use =A2<>"". It returns TRUE for ordinary text or numbers, and FALSE for an empty cell or a formula result of an empty string.
Is NULL the same as a blank cell in Excel?
No. NULL is not a built-in worksheet value. A blank cell, a formula returning "", text that says NULL, and a Power Query null are different cases.
What is the difference between ISBLANK and A2 equals empty text?ISBLANK(A2) is TRUE only when A2 has no contents. A comparison with "" also treats a formula result of an empty string as blank, so it answers a broader question.
Why does Excel say a cell is not blank when it looks empty?
The cell may contain spaces, a nonprinting character, or a formula that returns an empty string. The formula result looks empty, but ISBLANK still returns FALSE because the cell contains a formula. Use a whitespace-cleaning test when imported spaces are the issue.
Does not equal blank treat zero as blank?
No. A numeric zero is a value, so =A2<>"" returns TRUE when A2 contains 0. Add a separate zero condition only if your data rules say that zero should count as missing.
Related
- Analysis Group in Excel: Where It Is and How to Use It
- Excel Analysis ToolPak: Setup, Uses, and Limitations
- Excel Circle Bullets: How to Add, Format, and Align Them
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- How to Use Mail Merge in Microsoft Word Step by StepTo use mail merge in Microsoft Word, prepare a recipient list, connect it to a Word document through…
- Excel Conditional Formatting by Date: Rules and ExamplesTo conditionally format dates in Excel for Microsoft 365, select the date cells, then choose Home > Conditional…
- How to Insert a Checkbox in Word Online: Clickable or NotIn Word for the web, select the lines you want to turn into a list, then choose Home…
- How to Make a Brochure in Microsoft Word Step by StepTo make a tri-fold brochure in Microsoft Word, set the page to landscape, divide each of two pages…
- Project Timeline Excel Template: Build One Step by Step GuideTo make a project timeline in Microsoft Excel, list each task, owner, start date, and duration; calculate finish…
- How to Join Cells in Google Sheets: Merge or CombineTo join cells in Google Sheets by merging them, select the adjacent cells, then choose Format > Merge…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.