Excel: How to Keep Cell Blank Until Data is Entered


Often you may want to keep a cell blank in Excel until data is entered into it.

You can use the following syntax to do so:

=IF(OR(ISBLANK(B2),ISBLANK(C2)), "", B2*C2)

This particular example multiplies the values in cells B2 and C2 only if both cells have data entered into them.

If either cell contains no data, then the formula simply returns a blank.

The following example shows how to use this formula in practice.

Example: How to Keep Cell Blank Until Data is Entered in Excel

Suppose we have the following dataset in Excel that shows the number of units sold and the price of various products at some store:

Suppose we would like to calculate the Revenue for each product by multiplying the value in the Units Sold column by the value in the Price column.

However, suppose we want the Revenue column to remain blank until values have been entered into both the Units Sold and Price columns.

We can type the following formula into cell D2 to perform this calculation:

=IF(OR(ISBLANK(B2),ISBLANK(C2)), "", B2*C2)

We can then click and drag this formula down to each remaining cell in column D:

Excel keep cell blank until data is entered in

The Revenue column now displays the result of multiplying the value in the Units Sold column by the value in the Price column, only if both cells contain data.

For example:

  • Product A has a total revenue of 10*2 = 20
  • Product B does not have data in the Price column so the Revenue column remains blank.
  • Product C has a total revenue of 14*4 = 56
  • Product D does not have data in the Units Sold column so the Revenue column remains blank.

And so on.

How This Formula Works

Recall the formula that we used to multiply the value in cell B2 by the value in cell C2:

=IF(OR(ISBLANK(B2),ISBLANK(C2)), "", B2*C2)

Here is how this formula works:

First, we use OR(ISBLANK(B2), ISBLANK(C2)) to check if either cell B2 or C2 is blank.

If either of these cells are blank, then the expression returns TRUE and the IF function returns a blank value.

If both cells are not blank, then the expression returns FALSE and the IF function simply returns the result of B2*C2.

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel Formula: If False Then Blank
Excel: How to Calculate Only If Not Blank
Excel: If Cell is Blank then Skip to Next Cell

Featured Posts

Leave a Reply

Your email address will not be published. Required fields are marked *