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:

**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**