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

 

Consider the following dataset containing the Selling Price and Cost Price of some products of a company. We will use it to demonstrate how you can ignore the blank cells in the Selling Price column while performing calculations.

Excel ignore blank cells in range


Method 1 – Ignore Blank Cells in a Range by Using the IF Function

We will use the IF function to calculate the Profit Margin of the products, ignoring the blank cells in the Selling Price column since they’ll result in an error.

Excel ignore blank cells in range

Here’s the general formula you could use to determine the Profit Margin for cell E4:

=(C4-D4)/C4

IF Function

This results in the #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 is 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

  • You will get the profit margins of the products while ignoring the blank cells of the Selling Price column.

Excel ignore blank cells in range

  • Put the column values in the Percent Style.

IF Function

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 a 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.

Excel ignore blank cells in range

Steps:

  • Use 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

  • You will get the Profit Margins for the products except for the products with blank selling prices.

ISBLANK function

  • Apply the Percent Style to the Profit Margin column.

Excel ignore blank cells in range


Method 3 – Using the ISNUMBER Function

You can use the ISNUMBER function to calculate the Profit Margins for the products in the same dataset.

ISNUMBER function

Steps:

  • Insert 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

  • You will get the Profit Margins for the products while ignoring the blank cells of the Selling Price column.

ISNUMBER function

  • After applying the Percent Style, we will get the following result.

Excel ignore blank cells in range


Method 4 – Using the COUNT Function to Ignore Blank Cells in a Range in Excel

We’ll use the same dataset as before.

Excel ignore blank cells in range

Steps:

  • Use 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

  • You will get the Profit Margins for the products except for the products with blank selling prices.

COUNT Function

  • Apply the Percent Style to the Profit Margin column.

Excel ignore blank cells in range


Method 5 – Ignore Blank Cells in a Range with the COUNTA Function

We’ll use the same dataset as before.

Excel ignore blank cells in range

Steps:

  • Use 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

  • 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


Method 6 – Using the COUNTBLANK Function to Ignore Blank Cells in a Range

We’ll use the same dataset as before.

Excel ignore blank cells in range

Steps:

  • Use 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

  • You will get the Profit Margins for the products except for the products with blank selling prices.

COUNTBLANK Function

  • Apply the Percent Style to the Profit Margin column.

Excel ignore blank cells in range


Method 7 – Extracting a Range While Ignoring the Blank Cells

We will extract the values from the left Selling Price column to the right Selling Price column.

Excel ignore blank cells in range

Steps:

  • Use 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

  • You will get the extracted values of the Selling Price column ignoring the blank cells.

Excel ignore blank cells in range

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


Method 8 – Ignore Blank Cells in a Range by Using the AVERAGE Function

Let’s get the average values while ignoring the blanks. We’ll insert a result cell, like below.

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. We can see that the average values of the selling prices are the same.

AVERAGE Function

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


Practice Section

We have provided a Practice section like below in a sheet named Practice.

practice


Download the Workbook


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo