Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

How to Ignore Blank Cells in Named Range in Excel

You may have seen many Excel Sheets with blank cells. Some of these blank cells may even exist under a Named Range. Excel in general doesn’t do anything on its own regarding the blank cells. So, users need to specify what to do with these blank cells. In this article, I’m going to share some useful examples of how we can proceed so that Excel ignore blank cells in named range and explain them so that you may use them whenever necessary.

Before going to the next phase, let’s have a look at the final output of what you are going to get.

Ignoring Blank Cells in Named Range in Excel


Download Practice Workbook

You can download the workbook from here and practice yourself.


5 Easy Methods to Ignore Blank Cells in Named Range in Excel

The example below comes up with the “Buying Price” and “Selling Price” sections in column D and column E respectively. We intend to find out the “Buy – Sell Ratio” values. This is basically the ratio of the Buy and Sell values of a certain item and we need to do it for all the items present in the data set. But some cells are blank under the scope of Selling Price”. So, the ratio will show an error in those cases. We need to ignore the blank cells in the named range. Our data set looks like this:

Data Set for how to ignore blank cells in named range in Excel

I’ve used Microsoft Office 365 for this demonstration. I’ve used Buy and Sell names for cell C5:C13 and D5:D13 respectively. You may check how to Name a Range if necessary.


1. Use of IF Function to Ignore Blank Cells in Named Range

In our first method, we’ll use the IF function to ignore the blank cells in the named range. We’ll use the basic logical approach of the IF function, that is, when a if the value under the Sell name is not blank, then calculate the “Buy – Sell Ratio”, and ignore the corresponding row otherwise.

Steps:  

  • First, to create Named Range, first select all the necessary cells under the Header “Selling Price” and navigate to Formulas > Name Manager > New.

Named Range Creation

  • Select New and a Pop Up window will appear like the following image. Type “|Sell” in the Name box and select OK.

Typing the name

  • Named Range “Sell” creation is complete. A pop-up window will show the details of the Named Range. Select Close to proceed.

Named Range Creation Complete

  • Similarly, create a Named Range “Buy”. We’ll use Named Range “Buy” and “Sell” for all the methods demonstrated in this article.
  • Afterward, type the following formula in cell F5.
=IF(Sell="","",Buy/Sell)
  • Next, hit Enter.
  • Therefore, we’ll get the following output:

Use of IF function to ignore blank cells in named range in Excel

Formula Breakdown:

  •   =IF(Sell=””,””,Buy/Sell): IF statement first checks whether the value in the Sell named range is blank or not. If the value is blank, the IF function ignores that cell and calculates Buy/Sell otherwise and returns those values in corresponding Buy – Sell Ratio cells.

Read More: How to Edit Name Box in Excel (Edit, Change Range and Delete)


2. Use of ISBLANK Function 

In this method, we’ll use the ISBLANK function to ignore the blank cells in the named range. We’ll use ISBLANK to check which cells are blank and then use the basic logical approach of the IF function, that is, when a if the value under the Sell name is not blank, then calculate the “Buy – Sell Ratio”, ignore the corresponding row otherwise.Note that, we’ve created Named Range “Buy” and “Sell” as mentioned earlier in Method 1.

Steps:  

  • First, type the following formula in cell F5.
=IF(ISBLANK(Sell),"",Sell/Buy)
  • Next, hit Enter.
  • Consequently, we’ll get the following output:

Use of ISBLANK to ignore blank cells in named range in Excel

Formula Breakdown:

  • ISBLANK(Sell): ISBLANK first checks whether the value in the Sell named range is blank or not. If the value is blank, the IF function ignores that cell and calculates Buy/Sell otherwise and IF returns those values in corresponding Buy – Sell Ratio cells.

Read More: How to Name a Cell in Excel (4 Easy Ways)


Similar Readings


3. Use of ISNUMBER Function to Ignore Blank Cells in Named Range

Now, we’ll use the ISNUMBER function to ignore the blank cells in the named range. The logic of using IF function remains the same as described in first two methods. Note that, we’ve created Named Range “Buy” and “Sell” as mentioned earlier in Method 1.

Steps:  

  • First, Type the following formula in cell F5.
=IF(ISNUMBER(Sell),Buy/Sell,"")
  • Then, Hit Enter.
  • As a result, we’ll get the following output:

Use of ISNUMBER to ignore blank cells in named range in Excel

Formula Breakdown:

  • ISNUMBER(Sell): ISNUMBER first checks whether the value in the Sell named range is a numeric value or not. If the value is numeric, the ISNUMBER function calculates Buy/Sell, ignores that cell otherwise. Finally, IF function returns those values in corresponding Buy – Sell Ratio cells.

Read More: How to Name a Column in Excel (3 Easy and Effective Ways)


4. Extract Cells Data from Named Range Ignoring Blank Cells

Now if we want to extract cells’ data from the Named Range but need to ignore the blank cells, then we can use a formula for this purpose. For example, we want all the “Selling Price” data ignoring the blank cells. Now to extract this data, we’ll use a combination of ROW, ISNUMBER, SMALL, INDEX, IF and IFERROR functions. Note that, we’ve created Named Range “Buy” and “Sell” as mentioned earlier in Method 1.

Steps:  

  • First, type the following formula in cell G5.
=IFERROR(INDEX(Sell,SMALL(IF(ISNUMBER($E$5:$E$13),ROW($A$1:$A$9),""),ROW(A1))),"")
  • Afterward, hit Enter.
  • As a consequence, we’ll get the following output:

Formula for Extraction

Formula Breakdown:

  • ISNUMBER($E$5:$E$13): Like we’ve just mentioned the details of how ISNUMBER works, ISNUMBER returns TRUE for the numbers, FALSE otherwise. So, the output will be TRUE for the non blank cells and FALSE for the blank cells.

Output: {TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE}

  • ROW($A$1:$A$9): This returns the selected range’s row numbers.

Output: {1; 2; 3; 4; 5; 6; 7; 8; 9}

  • IF(ISNUMBER($E$5:$E$13),ROW($A$1:$A$9),””) is now simplified as

IF({TRUE; TRUE; FALSE; TRUE; TRUE; FALSE; TRUE; FALSE; TRUE},{1; 2; 3; 4; 5; 6; 7; 8; 9},””). This returns the corresponding row numbers for TRUE and blank otherwise.

Output: {1;2; “” ; 4; 5; “”; 7; “”; 9}

  • ROW(A1): This returns the row no of the cell in argument.

Output: 1

  • SMALL(IF(ISNUMBER($E$5:$E$13),ROW($A$1:$A$9),””),ROW(A1))) is now simplified as

SMALL({1;2; “” ; 4; 5; “”; 7; “”; 9},1) and returns the 1st smallest value from the mentioned range

Output: 1

  • INDEX(Sell,SMALL(IF(ISNUMBER($E$5:$E$13),ROW($A$1:$A$9),””),ROW(A1))): now looks like

INDEX(Sell,1) and this returns the 1st non blank value from this range.

Output: 45860

  • IFERROR(INDEX(Sell,SMALL(IF(ISNUMBER($E$5:$E$13),ROW($A$1:$A$9),””),ROW(A1))),””) now resembles

IFERROR(45860, “”) and this returns 45860 as the final output, blank if any error happens

Output: 45860

  • Drag the AutoFill icon to the end of the list.
  • AutoFill for the remaining cellsThe final output contains all the non blank cells from the Named Range Sell and looks like this:

AutoFill Output

Read More: How to Name a Group of Cells in Excel (3 Easy Methods)


5. Ignore Blank Cells in Named Range in Excel by Using AVERAGE Function

The AVERAGE function returns the average of selected cells or cell range and ignores blank cell(s) by default. We’ll use this function to calculate the average of our Named Range Sell and then verify the result by getting the average by means of hardcode. Note that, we’ve created Named Range “Buy” and “Sell” as mentioned earlier in Method 1.

Steps:  

  • First. type the following formula in cell C15.
=AVERAGE(Sell)
  • Next, hit Enter.
  • We’ll get the following output:

Use of AVERAGE Function

  • Now to verify the output of AVERAGE function, type the following formula in cell C17.
=(E5+E6+E8+E9+E11+E13)/6 
  • Hit Enter.
  • The output matches with our intension. We can see from the image that, both outputs are same:

Getting Average by Hard Coding

Read More: Excel VBA to Create Named Range from Selection (5 Examples)


Conclusion

If you’re at this segment, I thank you for your interest in this content. I’ve demonstrated 5 ways to ignore blank cells in the named range. I hope you get the necessary solution. Being said that, if you face any problem regarding this article or have any queries, please feel free to leave a comment in the comment box below, Exceldemy team will try to solve that for you at. Have a good day!


Related Articles

Hadi Ul Bashar

Hadi Ul Bashar

I'm Hadi Ul Bashar, an Engineering aspirant. I always look forward to keeping myself up to date in my area of interest. Currently, I'm happy to work at Exceldemy as an Excel & VBA Content Developer. My goal here is to provide an easy and detailed solution to whatever problem you may face in Excel. I hope I will make your Excel life easy as I'm always open to solve new problem!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo