Google Sheets: Leave Blank If There Is No Value

To make a Google Sheets formula leave its result blank when an input is missing, wrap the calculation in an IF test, such as =IF(A2="","",A2*1.2). This returns an empty-looking result when A2 has no value and calculates normally when it does; change A2 and the calculation to match your sheet.

The basic IF formula

In Google Sheets, an IF formula checks a condition and returns one result when it is true and another when it is false. The general pattern is =IF(condition,value_if_true,value_if_false). To show nothing for a missing input, use an empty text string, written as two quotation marks with nothing between them: "".

For example, suppose A2 contains a quantity and you want B2 to show the quantity multiplied by 1.2. With the input in A2 and the output formula in B2, enter =IF(A2="","",A2*1.2). If A2 is empty, B2 appears blank. If A2 contains 50, B2 displays 60. If A2 contains 0, the calculation returns 0; zero is a value, not a missing input.

The formula belongs in the result cell, not in the cell where you expect someone to type the input. If you put a formula in A2 that also checks A2, it refers to itself and creates a circular dependency. Keep input cells and formula-result cells separate.

What “no value” means in Sheets

A cell that looks blank is not always truly empty. It might be untouched, contain a formula that returns an empty string, or contain spaces. These cases can behave differently in tests and when you count or process data.

Truly empty cell: No value or formula has been entered.
Empty-string result: A formula has returned "". The cell looks blank, but it still contains a formula.
Spaces: The cell contains one or more space characters. It may look empty, but it has content.
Zero: The cell contains the number 0. It is a real value, even though a particular spreadsheet design may choose to hide or ignore it.

The test A2="" is useful when you want a formula to respond to a blank input and to an input formula that returns an empty string. By contrast, ISBLANK(A2) checks whether the cell is genuinely empty. It returns FALSE for a cell containing a formula, even when that formula displays nothing. Choose the test based on what you mean by “no value.”

Choose the right condition for your data

For many sheets, checking one required input is enough. But a real worksheet may need to handle multiple required fields, intentional zeros, or entries that contain only spaces. Decide which cases should count as missing before building the formula.

One required cell: Use A2="" when A2 must be filled in before the result should appear.
Two required cells: Use OR(A2="",B2="") if either missing input should keep the result blank.
Zero is also considered missing: Add a check for zero only if that is genuinely appropriate for the data.
Spaces should count as missing: Trim the input before checking whether anything remains.

For example, with prices in A2 and quantities in B2, and a total in C2, enter =IF(OR(A2="",B2=""),"",A2*B2) in C2. The result stays blank until both inputs are present. If A2 is 8 and B2 is 3, C2 returns 24. If either input is missing, it returns an empty string instead.

To treat a zero in A2 as missing as well as a blank, use =IF(OR(A2="",A2=0),"",A2*1.2), assuming A2 is the input and the result is in another cell. This deliberately hides a valid numeric zero, so do not add that condition simply to make the sheet look cleaner. A zero may be an important result, such as zero items sold or no hours worked.

Ignore cells that contain only spaces

A cell containing spaces is not empty, so a basic blank check can allow the calculation to proceed. If people may accidentally type spaces, use TRIM to remove leading, trailing, and repeated ordinary spaces before checking the input. With the value in A2 and a dependent calculation in B2, one option is =IF(TRIM(A2&"")="","",A2*1.2). The &"" converts the input to text for the check; the calculation still uses A2.

TRIM is useful for ordinary spaces, but it is not a universal cleanup tool for every invisible character. Data copied from websites or other systems can include nonbreaking spaces or other characters that need separate cleaning. If a cell looks empty but the formula does not treat it that way, select the cell and inspect its contents in the formula bar, or test a cleaned copy of the data before changing the whole column.

Fill the formula down a column

For a typical row-by-row sheet, enter a formula in the first result row and copy it down. For example, with quantities in A2:A and rates in B2:B, put this formula in C2: =IF(OR(A2="",B2=""),"",A2*B2). It checks the inputs on row 2 and returns their product. When copied down, the references adjust to A3 and B3, then A4 and B4, and so on.

You can copy the cell using Ctrl+C and Ctrl+V on Windows or Command+C and Command+V on a Mac. You can also drag the small square at the lower-right corner of the selected cell down the column. In Google Sheets, double-clicking that square can fill the formula down alongside an adjacent continuous data range. Check the last rows afterward, especially if the neighboring column has gaps.

For a formula that automatically handles a column, Google Sheets has ARRAYFORMULA. With inputs in A2:A and B2:B, place this formula in C2 and leave the cells below C2 in column C available for its results: =ARRAYFORMULA(IF((A2:A="")+(B2:B=""),"",A2:A*B2:B)). It leaves a row blank if either input is blank and multiplies the two inputs when both are present. For example, 8 in A2 and 3 in B2 produce 24 in C2. Avoid placing other values in the output range, because they can block the array results. For a small, fixed table, copying a regular formula down is often easier to inspect.

A blank-looking result is not an empty cell

Returning "" hides the visible result; it does not remove the formula from the cell. That distinction matters when another formula checks the output. For example, ISBLANK will not identify a formula cell as genuinely empty, even if its displayed result is blank. A later formula can test the value with an empty-string comparison, or you can change the design so it checks the original input instead.

Counting functions also differ. COUNTBLANK counts empty cells and cells whose formulas return an empty string. COUNTA counts cells containing formulas, including formulas that display an empty string. This can surprise someone who sees a blank-looking column but gets a nonzero COUNTA result. If you are checking how many actual inputs were supplied, count the input range rather than the formula-output range, and consider whether spaces or zeros should count as entries.

A formula-generated blank is often useful for reports, but it is not the same as deleting a cell’s contents. If you need to remove the formula itself, clear the cell; if you need an automatically updated result, keep the formula and return "" when appropriate.

Blanks in charts, filters, and later formulas

Empty-string results help prevent unfinished rows from displaying errors or misleading zeros. They also make tables easier to scan. However, downstream behavior depends on the feature: a blank-looking formula result is still formula output, and other formulas may treat it differently from an untouched cell. When building a summary, test it with both a genuinely empty input and an input formula that returns "".

For charts, check the chart’s data range and how it handles empty cells. A formula returning an empty string may not be treated in every respect like a truly empty cell, and chart behavior can depend on the chart type and settings. If an unwanted point or gap appears, use the chart editor to review its data range and setup rather than assuming the formula has cleared the cell.

Filters can still show rows with formulas, even when those formulas display nothing. If you need to exclude unfinished rows, filter based on the input column or use a condition that reflects whether the required source data is present. This is usually more dependable than judging a row by how its calculated cells look.

Common problems and fixes

The formula shows 0 for a blank input. Wrap the calculation in an IF condition that returns "" when the required input is missing.
A zero disappears. Check whether the condition includes a test such as A2=0. Remove it if zero is a valid result.
A cell that looks empty does not pass the blank test. It may contain spaces or another character. Inspect it, or use TRIM for ordinary spaces.
The formula appears as text instead of calculating. Check that it begins with = and that the cell is not formatted as plain text. Re-entering the formula after correcting the format may be necessary.
The formula reports a parse error. Check quotation marks and parentheses. Spreadsheet locale settings can also affect formula separators; if Sheets expects semicolons in your locale, use the separator it accepts.
An array formula cannot expand. Clear any existing content in the cells where its results need to appear.

Google Sheets in a web browser and Excel use similar IF formulas for this task, but ARRAYFORMULA is specific to Google Sheets. Excel has its own array behavior, so do not assume that a Sheets ARRAYFORMULA can be pasted into Excel unchanged. In Sheets, formulas are entered in the grid; a formatting change alone does not make an output logically empty.

Frequently Asked Questions

How do I leave a cell blank if another cell is empty in Google Sheets?

Put an IF formula in the result cell. If A2 is the input and B2 is the result, use =IF(A2="","",your_calculation), replacing your_calculation with a valid expression. The result cell will look blank while A2 is empty.

Does IF(A2="") also detect a formula that returns blank?

Yes. An empty-string comparison generally detects both an untouched cell and a formula result of "". ISBLANK is different: it returns FALSE for a cell that contains a formula, even if the formula displays nothing.

How do I keep zero visible but hide blanks?

Use a blank check without a zero condition, such as =IF(A2="","",A2) when A2 is the input and another cell contains the formula. Since zero is not the same as an empty input, it remains visible as 0.

Can I make a formula cell truly empty?

No formula can both remain in a cell and make that cell genuinely empty. Returning "" creates a blank-looking formula result. To make the cell truly empty, remove its contents, which also removes the formula.

Why does COUNTA count a cell that looks blank?

COUNTA counts a cell containing a formula, even when the formula returns an empty string. If you want to count missing or blank-looking results, consider COUNTBLANK or check the original input range, depending on what you intend to measure.

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.