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.

**Table of Contents**hide

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

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