Hadi Ul Bashar
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.
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:
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: 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:
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
- [Fixed!] Name Manager Delete Option Greyed out in Excel (3 Reasons)
- How to Paste Range Names in Excel (7 Ways)
- Edit Defined Names in Excel ( with Easy Steps)
- How to Edit Named Range in Excel (With Easy Steps)
- Create Dynamic Named Range in Excel (2 Easy Ways)
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.
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 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:
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:
- 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:
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
- How to Use Named Range in Excel VLOOKUP Function
- Excel VBA to Delete Named Range (5 Suitable Examples)
- How to Check If Named Range Exists with Excel VBA
- Navigate to a Named Range in Excel (3 Easy Ways)
- How to Display Named Range Contents in Excel (4 Quick Ways)
- Copy a Sheet If Name Already Exists in Excel
- How to Rename Column in Excel (3 Easy Ways)
