Excel Null Values: Blanks, Formulas, and Power Query

Formulas & functions · Updated November 3, 2026

Microsoft Excel does not have a special worksheet value called NULL in the way a database does. What people call a “null value” in a worksheet is usually an empty cell, a formula that returns an empty string, the text “NULL,” or a value handled as null in Power Query; these are different and can produce different results.

To manage missing data reliably, first identify which kind you have. A genuinely empty cell can be tested with ISBLANK, while Power Query has its own null value and tools for filtering or transforming it.

What null means in Excel

In databases, NULL represents missing or unknown information and is not the same as zero or an empty text value. A normal Excel worksheet cell does not store that database-style NULL as a distinct value. If a database field containing NULL is brought into a worksheet, it will commonly appear as an empty cell, although the exact result depends on the import or connection.

Several different things can therefore look like “nothing” on screen:

  • A truly empty cell: the cell contains no value or formula.
  • An empty string: a formula returns zero characters, often written as "".
  • Text that says NULL: the cell contains ordinary text, such as a word entered by a person or imported from a file.
  • Zero: a numeric value, which may be hidden by formatting but is not missing data.
  • An error: a value such as #N/A or #VALUE!, which signals an error rather than a blank.

These distinctions matter when you count records, calculate averages, filter a list, or decide whether to replace missing information. A report that treats an unknown amount as zero, for example, may understate a total or distort an average.

Blank cells, empty strings, and zero

A genuinely empty cell has no contents. Pressing Delete with the cell selected removes its contents, though formatting can remain. By contrast, a formula such as one that returns "" leaves a formula in the cell. It looks blank but is not truly empty. The text “NULL” is different again: it is visible text and is counted as a non-empty entry by many functions.

Excel functions do not all treat these cases alike. ISBLANK returns TRUE only for a cell that is genuinely empty. It returns FALSE for a cell containing a formula, even if that formula displays nothing. COUNTBLANK counts genuinely empty cells and also cells whose formulas return an empty string. COUNTA counts cells containing formulas, including formulas that display an empty string. That difference is a frequent source of unexpected totals.

Zero is a number, not a missing value. It can be meaningful—for instance, a recorded sales amount of zero says something different from an amount that was never reported. If a worksheet hides zero values using number formatting or a display setting, check the cell’s underlying value before treating it as blank.

How to find and count missing-looking cells

For a quick visual selection of truly empty cells, select the relevant range and use Home > Find & Select > Go To Special > Blanks. Excel selects the empty cells within that selection. This is useful for reviewing a table or filling known gaps, but it will not select cells containing formulas that return an empty string, text such as “NULL,” or errors. On Excel for Mac, Go To Special is also available through the Find & Select controls, though ribbon placement can vary by build; Excel for the web does not offer every desktop Go To Special command.

With the data in A2:A10, enter this in another cell to test whether A2 is genuinely empty; the result is TRUE only if A2 has no contents:

=ISBLANK(A2)

With the range A2:A10, use this formula to count genuinely blank cells and cells whose formulas return an empty string; for example, if three cells meet either condition, the result is 3:

=COUNTBLANK(A2:A10)

With the range A2:A10, this formula also counts cells matching an empty-string criterion, including blank cells and formula results that display as empty; if two such cells are present, it returns 2:

=COUNTIF(A2:A10,"")

To find text entries that literally contain the word NULL, use Find (Ctrl+F on Windows; Command+F on Mac) and search for that text. Searching is not a substitute for checking cell values: a word might occur within a longer note, and a blank-looking formula will not contain the word.

Handling blanks in formulas

A formula can test for an empty-looking value and display a label instead. With the input in A2, enter the following in B2; if A2 is empty, the result is Missing, and otherwise Excel returns the value from A2:

=IF(A2="","Missing",A2)

This is useful for display, but it does not distinguish a truly empty cell from a formula that returns an empty string. If that distinction matters, use ISBLANK instead. Also consider how the formula should handle errors: an error in A2 can flow through a formula that simply tests or returns A2, and an error is not the same as missing data.

Many calculations ignore blank cells in some contexts, while an empty string, text entry, or zero may be handled differently. For example, averages normally exclude empty cells but include numeric zeros. That is often the desired behavior, but it can produce a misleading result if missing observations were converted to zero earlier. Decide what missing means in the context of the data before choosing a formula or replacing values.

If the goal is to flag rows with missing required information, test the relevant input cells rather than applying one blanket rule to an entire sheet. A blank date may need investigation, while a blank comment field may be perfectly acceptable. A good data check reflects the purpose of each column.

Null values in Power Query

Power Query, which is available through Excel’s Get & Transform Data tools, does use a distinct null value while data is being prepared. This is different from a worksheet cell containing the text “null” or a formula returning an empty string. When a query is loaded to a worksheet, a Power Query null commonly appears as a blank cell, so the distinction may be less visible after loading.

In Excel for Microsoft 365 on Windows, you can start a query from a table using Data > From Table/Range, or use Data > Get Data to connect to another source. In the Power Query Editor, inspect the column’s filter menu for a (null) entry. You can exclude those rows when they should not be included, or keep them and transform the column when missing values need a defined treatment. Menu labels and available connectors can differ by source and Excel build; Excel for Mac has fewer Power Query capabilities than Windows, and Excel for the web does not provide the full desktop editing experience.

Before replacing nulls in a query, ask what the missing field means. Replacing a missing quantity with zero may be appropriate if missing means “none”; it is not appropriate if it means “not reported.” For text fields, a label such as “Unknown” can make missingness visible, but it also becomes a real category in later grouping and analysis. Keep the original data or document the transformation so that the decision is clear to anyone using the workbook.

When to replace a null or blank

There is no universally correct replacement. The right choice depends on the data’s meaning and on what downstream formulas, charts, or reports expect. A practical decision process is:

  1. Confirm the source: determine whether the value was absent in the original data, lost during import, or created by a formula.
  2. Check the column’s meaning: distinguish “none” from “unknown,” “not applicable,” and “not yet entered.”
  3. Choose a treatment: leave it blank, label it, exclude the record from a specific calculation, or replace it with a justified value.
  4. Check the effect: recalculate totals, averages, counts, and summaries after the change.
  5. Keep the change traceable: preserve an original copy or record the transformation in the query steps or workbook notes.

For example, a blank quantity might mean a product was not sold, or it might mean the sales record is incomplete. Filling every blank with zero without checking could turn incomplete records into apparent zero sales. Conversely, leaving a confirmed “no sales” value blank may cause a report to omit information that should be counted as zero.

Common problems and misconceptions

“The cell looks blank, so ISBLANK should be TRUE.” Not necessarily. A formula can return an empty string while remaining in the cell. Check the formula bar or use the appropriate count function to identify the difference.

“NULL and zero mean the same thing.” They do not. Zero is a measured or assigned numeric value; null or blank generally indicates missing or absent information. Treating them as interchangeable can change averages, totals, and decisions based on the data.

“Find and Replace can safely clean every kind of blank.” A search for the text “NULL” targets text, not every genuinely empty cell or formula result. Go To Special selects true blanks, while Power Query handles nulls during query transformation. Choose the tool that matches the representation you actually have.

“A formula that displays nothing leaves an empty cell.” It does not. The formula remains, so functions such as ISBLANK and COUNTA can behave differently from what the display suggests. If you need a true empty cell, remove the formula or change the data-handling design; a worksheet formula cannot erase its own cell contents.

Finally, do not remove errors by treating them as nulls. Errors such as #N/A may intentionally identify a lookup miss or a failed calculation. Diagnose them separately, because hiding an error can conceal a problem that needs correction.

Frequently Asked Questions

Does Excel have a NULL function?
No. Standard worksheet formulas do not use a special NULL value or a NULL function. An empty cell, an empty string, the text “NULL,” and Power Query null are separate cases.

How do I check whether a cell is blank?
Use ISBLANK when you need to know whether a cell is truly empty. If a formula might return an empty string and should count as blank-looking, use COUNTBLANK for a range or test the displayed result separately.

Why does COUNTBLANK count a cell with a formula?
COUNTBLANK includes cells whose formulas return an empty string, even though those cells are not truly empty. ISBLANK returns FALSE for them because the formula is still present.

Should I replace blank cells with zero?
Only when zero accurately represents the missing value for that field. If the value is unknown, not applicable, or unreported, zero can misrepresent the data and affect calculations.

How do I filter nulls in Power Query?
Open the query in Power Query Editor and inspect the filter menu for the relevant column. The (null) entry can be excluded or retained according to your analysis; decide how to handle it before replacing or loading the data.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.