Excel Formula to Return a Blank When No Value

Formulas & functions · Updated November 18, 2026

Yes. In Excel for Microsoft 365 on Windows, use an IF formula that returns an empty text string when the source cell has no value. With the source in A2 and a result you want to calculate from it, use =IF(A2="","",A2*2). If A2 is empty, the cell looks blank; if A2 contains 5, the result is 10.

The basic IF formula

The general pattern is =IF(test,"",value_if_not_blank): Excel checks a condition, displays an empty string when the condition is true, and otherwise calculates or returns the result. The two quotation marks contain no characters. They are not spaces, and they tell Excel to return zero-length text.

For example, with a name in A2 and a greeting in B2, enter this in B2:

With the name in A2, this formula in B2 returns a blank if A2 has no value, or a greeting if it does: =IF(A2="","","Hello, "&A2).

The formula does not remove the formula from B2. B2 still contains a formula, and the formula returns text that looks empty. This distinction matters for some counts, checks, and other formulas, so it helps to know what Excel considers blank before building a larger worksheet.

What Excel means by no value

A cell can look blank without being truly empty. It may be genuinely unused, contain a formula that returns an empty string, contain one or more spaces, or display a number in a way that makes it hard to see. Those cases can behave differently in tests.

  • Truly empty cell: Nothing has been entered in the cell. The ISBLANK function returns TRUE for this case.
  • Formula returning an empty string: The cell contains a formula, but displays nothing. A test such as A2=”” treats this as blank, while ISBLANK does not.
  • Space or other text: The cell contains characters, even if they are hard to see. A simple blank check may treat it as a value.
  • Zero: Zero is a numeric value, not an empty cell. A formula checking for an empty string normally lets zero through.

With a value or formula in A2, =ISBLANK(A2) returns TRUE only when A2 is truly empty. If A2 contains a formula that displays nothing, the result is FALSE. With A2 empty or containing a formula that returns an empty string, =A2="" returns TRUE. The second test is usually more useful when the goal is to hide an output until the source has something to show.

Hide a calculation until its inputs exist

A common situation is a row with two required inputs, such as quantity and unit price. If either input is missing, showing a partial result or a zero may be confusing. With quantity in A2, unit price in B2, and the formula in C2, use:

=IF(OR(A2="",B2=""),"",A2*B2)

If A2 is 3 and B2 is 12, C2 returns 36. If either input is empty, C2 displays blank. OR checks whether at least one of the two tests is true; if both inputs must be present, that is the condition you want. If only one input is required, test just that cell instead.

For a longer calculation, put the desired calculation in the final part of IF. With a date in A2 and a payment amount in B2, for example, you could return the payment only after both are entered. Keep the condition aligned with the actual business rule: checking one field when two are required can allow an incomplete row to display a misleading result.

Blank is different from zero

Returning an empty string is not the same as returning 0. A zero communicates a numeric result; a blank communicates that there is no result to display yet. Avoid a test that treats zero as missing if zero is a valid quantity, score, balance, or measurement.

For example, with a value in A2 and a doubled result in B2, =IF(A2="","",A2*2) leaves B2 blank when A2 is empty but displays 0 when A2 contains 0. That is often the intended behavior. By contrast, a condition such as A2=0 would suppress both a genuine zero and, depending on the comparison, an empty input. Choose the test based on what the data means, not just on what the cell looks like.

Excel also has a worksheet setting that can hide displayed zero values, but that changes how zeros appear rather than changing a formula result into a blank. A hidden zero can still affect calculations. If the distinction between missing and zero matters to the worksheet, make it explicit in the formula rather than relying on display settings.

Handle cells that contain spaces

A cell containing a space is not empty. Therefore, A2=”” will not identify a space-only entry as blank. This commonly happens when data is pasted from another system, or when someone types a space by mistake. A formula can trim ordinary leading and trailing spaces before checking the value.

With text input in A2 and a calculation based on B2 in C2, this formula treats A2 as blank if it contains only ordinary spaces: =IF(TRIM(A2)="","",B2*2). TRIM removes leading and trailing ordinary spaces and reduces repeated spaces between words to one. If A2 contains text with accidental extra spaces, TRIM also cleans it for the test.

TRIM does not remove every invisible character that can arrive in imported data. For example, nonbreaking spaces may need separate cleanup. If a formula still reports a value after applying TRIM, inspect the source cell or clean the imported text rather than assuming the cell is truly empty. Be careful not to trim meaningful text in the output when spacing is intentional.

Return blank when a calculation would show an error

A missing input is not the only reason a result may be undesirable. A division by zero or another calculation error can be handled with IFERROR, which returns a chosen value when the calculation produces an error. Use it when hiding the error is appropriate; do not use it to conceal a problem that readers need to investigate.

With a required status in A2 and a division of B2 by C2 in D2, use: =IF(A2="","",IFERROR(B2/C2,"")). D2 stays blank if A2 is empty, and it also stays blank if the division produces an error. When A2 is filled and B2 divided by C2 is valid, D2 shows the quotient. If the error should be explained instead, replace the empty-string result with a clear message such as text indicating that the inputs need checking.

IFERROR catches any error in the calculation it wraps, not just division by zero. A missing reference, invalid value, or other formula problem could therefore also be hidden. Test the workbook with representative inputs and decide whether a blank, a message, or a visible error is the most helpful response.

Fill the formula down a column

After entering a formula in the first result row, you can copy it to the rows below. With source values in A2:A20 and results starting in B2, enter =IF(A2="","",A2*2) in B2. Select B2 and use the fill handle at the lower-right corner of the selection to copy the formula down. Excel adjusts A2 to A3, A4, and so on, so each row checks its own input.

On Windows, Ctrl+D fills the selected range downward from the top cell. On Mac, the equivalent shortcut is usually Control+D. You can also copy and paste with Ctrl+C and Ctrl+V on Windows, or Command+C and Command+V on Mac. When copying a formula, confirm the references point to the intended row; dollar signs make a reference absolute if you need it to stay fixed.

In an Excel table, entering a formula in a calculated column generally fills that formula through the table column. The visible result can still be blank for rows with missing inputs. Tables are useful when records are added over time because the calculated column can extend with new rows, but verify the formula after changing the table structure.

How blank-looking results affect other formulas

A formula result of “” is text, not a genuinely empty cell. This is the main limitation of the blank-returning approach. Some functions treat it as blank for particular purposes, while others recognize that a formula is present. For example, COUNTA counts cells containing formulas, including formulas that return empty text. COUNT counts numeric values and does not count an empty-text result as a number.

With results in D2:D20, =COUNTA(D2:D20) can count cells with formulas even when those cells display nothing. If the goal is to count numeric results only, =COUNT(D2:D20) is more appropriate, provided the results are numbers. COUNTBLANK can count genuinely empty cells and cells whose formulas return empty text. Check the function that matches the question you are asking rather than assuming every blank-looking cell is identical.

A blank-looking formula result can also affect downstream logic. If another formula should treat it as missing, a comparison such as D2=”” may work. If the next step needs a numeric value, make sure the blank case is handled before performing arithmetic. A formula that concatenates text, a chart, or a data export may also treat empty text differently from a truly unused cell. Check the result in the context where it will actually be used.

Common problems and how to fix them

  • The cell displays 0: Confirm the blank test checks the correct input cell and that the calculation is in the false branch of IF. Also check whether the input contains a zero, which is a value, rather than being empty.
  • The cell looks empty but the check says it has a value: The input may contain spaces or an invisible character. Try TRIM for ordinary spaces and inspect or clean imported data.
  • ISBLANK returns FALSE: The tested cell may contain a formula, even if that formula displays nothing. Use a comparison with an empty string when that is the case you want to recognize.
  • The formula shows as text instead of calculating: Check that the cell is not formatted as Text and that the entry begins with an equals sign. Changing the format to General and re-entering the formula can help.
  • The output is blank but totals or counts seem wrong: Remember that the output cell still contains a formula. Use a counting function suited to numbers, blank-looking results, or all populated cells.

When checking a formula, test at least three cases: an empty input, a normal input, and a valid zero if zero is possible. If multiple inputs are required, test each missing-input combination. This small check helps catch formulas that hide useful zeros or reveal incomplete results.

Frequently Asked Questions

How do I make an Excel formula show nothing if a cell is blank?
With the input in A2 and a doubled result in B2, enter =IF(A2="","",A2*2) in B2. It returns an empty string when A2 is empty and otherwise returns twice the input.

Does an empty-string formula make a cell truly blank?
No. The cell still contains a formula; it only displays no characters. That can matter for ISBLANK, COUNTA, and processes that distinguish a formula cell from a truly unused cell.

How do I keep a formula from showing zero?
First decide whether zero means a valid result or missing data. If an input is genuinely absent, test that input and return an empty string in the missing case. Do not hide zero values if users need to see them.

Why does my blank check fail when the cell looks empty?
The cell may contain a formula returning empty text, ordinary spaces, or other invisible characters. ISBLANK only returns TRUE for a truly empty cell. A comparison with an empty string can recognize formula results that display blank, while TRIM can help with ordinary spaces.

Can I use this formula in Excel for the web or on Mac?
IF and the empty-string pattern are available in Excel for Microsoft 365 on Windows, Excel for Mac, and Excel for the web. The formula syntax is the same; keyboard shortcuts and some interface details may differ by platform.

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.