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.
How to Ignore Blank Cells in Named Range in Excel: 5 Easy Methods
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:
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.
- Select New and a Pop Up window will appear like the following image. Type “|Sell” in the Name box and select OK.
- Named Range “Sell” creation is complete. A pop-up window will show the details of the Named Range. Select Close to proceed.
- 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:
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: Excel INDIRECT Function with Named Range
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:
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 Use Named Range in Excel VLOOKUP Function
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:
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.
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 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.
The final output contains all the non blank cells from the Named Range Sell and looks like this:
The final output contains all the non blank cells from the Named Range Sell and looks like this:
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:
- 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:
Download Practice Workbook
You can download the workbook from here and practice yourself.
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. Have a good day!
Related Articles
- How to Create Dynamic Named Range in Excel
- How to Use Dynamic Named Range in an Excel Chart
- Define Names with the Create from Selection Tool in Excel
- Excel Reference Named Range in Another Sheet
<< Go Back to Named Range | Excel Formulas | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
