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

Get FREE Advanced Excel Exercises with Solutions!

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


How to Ignore Blank Cells in Range in Excel: 8 Ways

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

Excel ignore blank cells in range

We have used Microsoft Excel 365 version here, you can use any other versions 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.

Excel ignore blank cells in range

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.

=(C4-D4)/C4

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

IF Function

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

Excel ignore blank cells in range

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

=IF(C4="","",(C4-D4)/C4)

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

IF Function

➤ Press ENTER and drag down the Fill Handle tool.

IF Function

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

Excel ignore blank cells in range

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.

IF Function

Read More: How to Remove Blank Cells 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 for ignoring the blank cells while calculating the Profit Margin of the Selling Price column.

Excel ignore blank cells in range

Steps:
➤ Type the following formula in cell E4.

=IF(ISBLANK(C4),"",(C4-D4)/C4)
  • ISBLANK(C4) → returns TRUE for the blank cells and FALSE for the non-blank cells.
    Output → FALSE
  • (C4-D4)/C4 → gives the Profit Margin for Apple
    Output → 0.439
  • IF(ISBLANK(C4),””,(C4-D4)/C4) becomes
    IF(FALSE,””,0.439) → returns the value 0.439 because the condition is giving FALSE
    Output → 0.439

ISBLANK function

➤ Press ENTER and drag down the Fill Handle tool.

ISBLANK function

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

ISBLANK function

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

Excel ignore blank cells in range

Read More: Find If Cell is Blank in Excel 


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.

ISNUMBER function

Steps:
➤ Type the following formula in cell E4.

=IF(ISNUMBER(C4),(C4-D4)/C4,"")
  • ISNUMBER(C4) → returns TRUE for the numbers otherwise FALSE (for checking texts you can use the ISTEXT function similarly)
    Output → TRUE
  • (C4-D4)/C4 → gives the Profit Margin for Apple
    Output → 0.439
  • IF(ISNUMBER(C4),(C4-D4)/C4,””) becomes
    IF(TRUE, 0.439,””) → returns the value 0.439 because the condition is giving TRUE
    Output → 0.439

ISNUMBER function

➤ Press ENTER and drag down the Fill Handle tool.

ISNUMBER function

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

ISNUMBER function

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

Excel ignore blank cells in range


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.

Excel ignore blank cells in range

Steps:
➤ Type the following formula in cell E4.

=IF(COUNT(C4)=1,(C4-D4)/C4,"")
  • COUNT(C4) → counts the number of cells containing numbers
    Output → 1
  • COUNT(C4)=1 becomes 1=1 and so returns TRUE.
  • (C4-D4)/C4 → gives the Profit Margin for Apple
    Output → 0.439
  • IF(COUNT(C4)=1,(C4-D4)/C4,””) becomes
    IF(TRUE,0.439,””) → returns the value 0.439 because the condition is giving TRUE
    Output → 0.439

COUNT Function

➤ Press ENTER and drag down the Fill Handle tool.

COUNT Function

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

COUNT Function

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

Excel ignore blank cells in range

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


Similar Readings


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.

Excel ignore blank cells in range

Steps:
➤ Type the following formula in cell E4.

=IF(COUNTA(C4)=1,(C4-D4)/C4,"")
  • COUNTA(C4) → counts the number of cells containing numbers and texts
    Output → 1
  • COUNTA(C4)=1 → becomes 1=1 and so returns TRUE.
  • (C4-D4)/C4 → gives the Profit Margin for Apple
    Output → 0.439
  • IF(COUNTA(C4)=1,(C4-D4)/C4,””) becomes
    IF(TRUE,0.439,””) → returns the value 0.439 because the condition is giving TRUE
    Output → 0.439

COUNTA Function

➤ Press ENTER and drag down the Fill Handle tool.

COUNTA Function

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

COUNTA Function

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

Excel ignore blank cells in range

Read More: How to Find Blank Cells Using VBA in Excel 


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.

Excel ignore blank cells in range

Steps:
➤ Type the following formula in cell E4.

=IF(COUNTBLANK(C4)=0,(C4-D4)/C4,"")
  • COUNTBLANK(C4) → counts the number of blank cells
    Output → 0
  • COUNTBLANK(C4)=0 → becomes 0=0 and so returns TRUE.
  • (C4-D4)/C4 → gives the Profit Margin for Apple
    Output → 0.439
  • IF(COUNTBLANK(C4)=0,(C4-D4)/C4,””) becomes
    IF(TRUE,0.439,””) → returns the value 0.439 because the condition is giving TRUE
    Output → 0.439

COUNTBLANK Function

➤ Press ENTER and drag down the Fill Handle tool.

COUNTBLANK Function

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

COUNTBLANK Function

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

Excel ignore blank cells in range

Related Content: Formula to Return Blank Cell instead of Zero in Excel 


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 function, INDEX function, SMALL function, IF function, ISNUMBER function, ROW function. So, we will extract the values from the left Selling Price column to the right Selling Price column.

Excel ignore blank cells in range

Steps:
➤ Type the following formula in cell F5.

=IFERROR(INDEX($C$4:$C$14,SMALL(IF(ISNUMBER($C$4:$C$14),ROW($A$1:$A$11),""),ROW(A1))),"")
  • ISNUMBER($C$4:$C$14)returns TRUE for the numbers otherwise FALSE
    Output  →{TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}
  • ROW($A$1:$A$11)returns the row numbers of this range
    Output → {1;2;3;4;5;6;7;8;9;10;11}
  • ROW(A1)returns the row number of this cell
    Output → 1
  • IF(ISNUMBER($C$4:$C$14),ROW($A$1:$A$11),"") becomes
    IF({TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE},{1;2;3;4;5;6;7;8;9;10;11},"")returns the row numbers for TRUE otherwise blank
    Output → {1;2; “”;4;5;6; “”;8;9; “”}
  • SMALL(IF(ISNUMBER($C$4:$C$14),ROW($A$1:$A$11),""),ROW(A1))) becomes
    SMALL({1;2; “”;4;5;6; “”;8;9; “”},1)returns the 1st smallest value of this range
    Output → 1
  • INDEX($C$4:$C$14,SMALL(IF(ISNUMBER($C$4:$C$14),ROW($A$1:$A$11),""),ROW(A1))) becomes
    INDEX($C$4:$C$14,1)returns the 1st value of this range
    Output → 3663
  • IFERROR(INDEX($C$4:$C$14,SMALL(IF(ISNUMBER($C$4:$C$14),ROW($A$1:$A$11),""),ROW(A1))),"") becomes
    IFERROR(3663,"")returns blank for any error
    Output → 3663

extracting a range

➤ Press ENTER and drag down the Fill Handle tool.

extracting a range

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

Excel ignore blank cells in range

Read More: Excel VBA: Find the Next Empty Cell in Range 


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.

Excel ignore blank cells in range

Steps:
➤ Enter the following formula in cell E5.

=AVERAGE(C4:C14)

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

AVERAGE Function

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

➤ Enter the following formula in cell E8.

=(C4+C5+C7+C8+C9+C11+C12+C14)/8

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.

AVERAGE Function


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.

practice


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

Tanjima Hossain
Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo