How to Ignore Blank Cells in Range in Excel (8 Ways)

If you are looking for some of the easiest ways to ignore blank cells in a range in Excel to get perfect results with formulas, then you will find this article useful. So, let’s start our main article.

How to Ignore Blank Cells in Excel Range : 8 Ways

Here, we have the following dataset containing the Selling Price and Cost Price of some products of a company. We will show the ways to ignore the blank cells in the range of the Selling Price column.

We have used the Microsoft Excel 365 version here, you can use any other version according to your convenience.

Method-1: Ignore Blank Cells in Range by Using the IF Function

Here, we will use the IF function to calculate the Profit Margin of the products ignoring the blank cells of the Selling Price column. If we don’t ignore these blank cells here then we will get errors and to avoid this error ignoring blank cells is essential.

Let’s try not to ignore the blank cells at first for calculating Profit Margin.
➤ Enter the following formula in cell E4 and drag down the Fill Handle tool.

Here, C4 is the Selling Price, D4 is the Cost Price.

So, we are having #DIV/0! error for the blank cells in the Selling Price column.

To solve this problem, we will use the following formula to ignore the blank cells

When C4 is blank it will return TRUE, then IF will return a blank otherwise we will get the Profit Margin.

➤ Press ENTER and drag down the Fill Handle tool.

Then, you will get the profit margins of the products ignoring the blank cells of the Selling Price column.

To have a better visualization let’s format the Profit Margin column. After adding Percent Style, we are getting the following Profit Margins of the products.

Read More: How to Leave Cell Blank If There Is No Data in Excel

Method-2: Using the ISBLANK Function to Ignore Blank Cells in Range in Excel

In this section, we will be using the ISBLANK function to ignore the blank cells while calculating the Profit Margin of the Selling Price column.

Steps:
➤ Type the following formula in cell E4.

➤ Press ENTER and drag down the Fill Handle tool.

After that, you will get the Profit Margins for the products except for the products with blank selling prices.

For having percentages in those fraction values apply the Percent Style to the Profit Margin column.

Method-3: Using the ISNUMBER Function

You can use the ISNUMBER function to calculate the Profit Margins for the products excluding the products having no Selling Prices.

Steps:
➤ Type the following formula in cell E4.

➤ Press ENTER and drag down the Fill Handle tool.

Then, you will get the Profit Margins for the products ignoring the blank cells of the Selling Price column.

After applying the Percent Style we will get the Profit Margins as below.

Method-4: Using the COUNT Function to Ignore Blank Cells in Range in Excel

Here, we will use the COUNT function to calculate the Profit Margins of the products ignoring the blank cells of the Selling Price column.

Steps:
➤ Type the following formula in cell E4.

➤ Press ENTER and drag down the Fill Handle tool.

Afterward, you will get the Profit Margins for the products except for the products with blank selling prices.

For having percentages in those fraction values apply the Percent Style to the Profit Margin column.

Method-5: Ignore Blank Cells in Range by Using the COUNTA Function

In this section, we will calculate the Profit Margins of the products using the COUNTA function only for the products having values in the Selling Price column.

Steps:
➤ Type the following formula in cell E4.

➤ Press ENTER and drag down the Fill Handle tool.

Then, you will get the profit margins of the products ignoring the blank cells of the Selling Price column.

After adding Percent Style, we are getting the following Profit Margins of the products.

Method-6: Using the COUNTBLANK Function to Ignore Blank Cells in Range

You can use the COUNTBLANK function also to calculate the Profit Margins ignoring the blank cells in the range of the Selling Price column.

Steps:
➤ Type the following formula in cell E4.

➤ Press ENTER and drag down the Fill Handle tool.

After that, you will get the Profit Margins for the products except for the products with blank selling prices.

For having percentages in those fraction values apply the Percent Style to the Profit Margin column.

Method-7: Extracting a Range While Ignoring the Blank Cells

Suppose, we want to extract the range of the Selling Price column excluding the blank cells, and to do this here we will use the IFERROR, INDEX, SMALL, IF, ISNUMBER, and ROW Functions. So, we will extract the values from the left Selling Price column to the right Selling Price column.

Steps:
➤ Type the following formula in cell F5.

➤ Press ENTER and drag down the Fill Handle tool.

In this way, you will get the extracted values of the Selling Price column ignoring the blank cells.

Read More: How to Return Non Blank Cells from a Range in Excel

Method-8: Ignore Blank Cells in Range by Using the AVERAGE Function

The AVERAGE function counts the average of a range ignoring the blank cells by default, and so, here we will calculate the average of the selling prices excluding the blank cells using this function.

Steps:
➤ Enter the following formula in cell E5.

It will calculate the average of this range excluding the blank cells.

Now, we can check if the AVERAGE function is actually calculating the average excluding the blank cells.

➤ Enter the following formula in cell E8.

Here, C4, C5, C7, C8, C9, C11, C12, C14 are the non-blank selling prices.
So, we can see that the both average values of the selling prices are the same.

Read More: How to Remove Blank Cells from a Range in Excel

Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Download Workbook

Conclusion

In this article, we tried to cover the ways to ignore blank cells in a range in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Related Articles

• How to Delete Blank Cells and Shift Data Up in Excel
• How to Delete Blank Cells and Shift Data Left in Excel
• How to Remove Unused Cells in Excel
• How to Remove Blank Cells in Excel
• How to Remove Blank Lines in Excel

<< Go Back to Blank Cells | Excel Cells | Learn Excel