How to ignore blank cells in Excel.

When working with a large dataset in Excel, it is common to come across blank cells. These blank cells can be problematic when performing calculations or data analysis, as they can skew your results. Fortunately, Excel provides a number of ways to ignore blank cells in your calculations. 

Here we have a dataset, in this dataset, we have five columns containing the Date, Product, Selling Price, Original Price, and Profit. We wish to calculate the profit in Column E and we want to use a formula which will involve division by Column C entries. We can observe that Column C has some blank cells and if we use these cells as it is in the division formula, we’ll get errors. In this tutorial, we’ll walk you through how to ignore blank cells in Excel.

Method – 1 Using the IF Function

To ignore blanks, we need to implement a simple IF function to detect blank cells. When the blank cells will be detected, the calculations will not be performed. Follow the steps mentioned below to learn the method of implementation of IF function.

Step – 1 Select the cell to implement the IF fucntion

• Select the first cell in Column E, where you want to write the formula.
• The syntax of the formula will be

=IF(First_Cell = ” “, ” “, (First_Cell – Second_Cell) / First_Cell)

• In our case the actual formula will be

=IF(C2 = ” “, ” “, (C2 – D2) / C2)

Step – 2 Calculate the values and ignore blanks

• Press Enter and drag down the Fill Handle tool to the rest of the cells.
• The formula will be applied automatically.
• We can see that the formula was not applied to the cells where the Column C entries were blank, which means that we successfully ignored the blank cells and avoid errors.

Method – 2 Using the ISBLANK Function

Second method to detect blanks is the use of ISBLANK function of Excel. This is an easy method to detect whether a cell is blank or not, and by implementing this function we can avoid or ignore all blank cells during the calculations. 

Step – 1 Select the cell to implement the formula

• Select the cell where you want to write the formula.
• The syntax of the formula will be

=IF(ISBLANK(First_Cell), ” “, (First_Cell-Second_Cell) / First_Cell)

• In our case the actual formula will be

=IF(ISBLANK(C2),” “,(C2-D2)/C2)

Step – 2 Calculate the values and ignore blanks

• Press Enter and drag down the Fill Handle tool to the rest of the cells.
• The formula will be applied automatically to all the column and we can notice that the during the calculations the blank cells were ignored.