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
5 Easy Methods to Ignore Blank Cells in Named Range in Excel
1. Use of IF Function to Ignore Blank Cells in Named Range
2. Use of ISBLANK Function
3. Use of ISNUMBER Function to Ignore Blank Cells in Named Range
4. Extract Cells Data from Named Range Ignoring Blank Cells
5. Ignore Blank Cells in Named Range in Excel by Using AVERAGE Function
Conclusion
Related Articles
Download Practice Workbook
You can download the workbook from here and practice yourself.
Ignore Blank Cells in Named Range.xlsx
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 functionto 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 functionto 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 functionto 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 functionreturns 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)
FAQs
How do I remove blank cells from a named range in Excel? ›
- Select the range where you want to remove blanks. ...
- Press F5 and click Special… . ...
- In the Go To Special dialog box, select Blanks and click OK. ...
- Right-click any of the selected blanks, and choose Delete… from the context menu:
- Select all columns that hold your data range.
- Go to Ribbon > Data tab > Sort & Filter Group > Filter.
- Move across the columns. Click the Filter dropdown for each column. Uncheck Select All then check Blanks for rows that have only some blank cells.
Select the cells where the data validation is set, and then in the Ribbon, select Data > Data Tools > Data Validation. This option is available with any validation criteria be it whole number, decimal, list, date, time, text length or a custom format. Make sure Ignore Blank is checked and then click OK.
How do you ignore a cell with no value? ›Using the empty text string method will return TRUE (therefore ignore the formula) if the cell is empty or contains an empty text string. Using the ISBLANK method will return TRUE (therefore ignore the formula) only if the cell is empty.
How do I remove blank cells from a range? ›Right-click one of the selected cells and then click Delete. In the window that opens, select either Shift cells up or Entire row. Shifting cells up will just remove the blanks from the range that you selected.
How do you count cells in a range that are not blank? ›Use the COUNTA function to count only cells in a range that contain values. When you count cells, sometimes you want to ignore any blank cells because only cells with values are meaningful to you. For example, you want to count the total number of salespeople who made a sale (column D).
How do I get Sumproduct to ignore blank cells? ›Using a comma "," instead of Multiply "*" can solve the SUMPRODUCT blank cells problem.
How do I bypass Data Validation restrictions in Excel? ›- Select the cell(s) with data validation.
- On the Data tab, click the Data Validation button.
- On the Settings tab, click the Clear All button, and then click OK.
Hi, STDEV will ignore blank cells but in this ARRAY format it wont so we need an additional check for blanks. you must enter it again with CTRL+Shift+Enter.
How do I ignore Data Validation in Excel? ›To quickly remove data validation for a cell, select it, and then go to Data > Data Tools > Data Validation > Settings > Clear All.
What is the difference between IsBlank and IsEmpty in Excel? ›
The IsBlank() function is used to check if a value is Blank. The IsEmpty() function is used to check if a table contains any records, in other words, the IsEmpty() function is used to detect if there is no records within a Table. The "" (empty string) is a string text value, but there is no character within it.
How do I conditional Format non blank cells in Excel? ›Highlight row if cell is not blank
On the Home tab, click Conditional formatting > New Rule > Use a formula to determine which cells to format. In the Format values where this formula is true box, enter one of these formulas: To highlight non-empty cells that contain anything: value, formula, empty string, etc.
We can use the ISBLANK coupled with conditional formatting. For example, suppose we want to highlight the blank cells in the range A2:F9, we select the range and use a conditional formatting rule with the following formula: =ISBLANK(A2:F9).
What is a quick way of finding all blank cells in a range? ›On the Home tab, in the Editing group, click Find & Select > Go to Special. Or press F5 and click Special… . In the Go To Special dialog box, select Blanks and click OK. This will select all empty cells in the range.
How do I remove blank cells from multiple columns? ›Select all blank columns - click on the first column letter, press Shift, and then click the letter of the last blank column. Right-click the selected columns and choose Delete from the pop-up menu.
Does the count function ignore blank cells? ›The COUNTA function does not count empty cells. If you do not need to count logical values, text, or error values (in other words, if you want to count only cells that contain numbers), use the COUNT function.
Why are my blank cells being counted in Excel? ›Formulas that return empty text (“”) are considered blank and will be counted. So, if a cell contains an empty text string or a formula that returns an empty text string, the cell is counted as blank by the COUNTBLANK function.
Which function is used to counts the number of blank cells in a range? ›Use the COUNTBLANK function, one of the Statistical functions, to count the number of empty cells in a range of cells.
Can you use wildcards in SUMPRODUCT? ›SUMPRODUCT doesn't accept wildcards. Note that case is not factor.
Does SUMPRODUCT include blanks? ›We can use the SUMPRODUCT function in Excel to check or test if all cells in a range are blank. If all cells in the specified range are empty, the function will return TRUE. But if not all cells are empty, then it will return a FALSE.
How do I force Excel to allow content? ›
Click the File tab > Info. In the Security Warning area, click Enable Content > Advanced Options. In the Microsoft Office Security Options dialog box, select Enable content for this session, and click OK.
Can validation rules be bypassed? ›Bypass Validation Rules
To set up a Validation Rule bypass, the process also starts by creating a new field on your User object. This field is a checkbox and can be named “Bypass VR”. This value must then be entered on all validation rules for all objects.
Select the cells which contain the drop down lists you have already created, and then click Data > Data Validation > Data Validation. See screenshot: 2. In the Data Validation dialog box, uncheck the Show error alert after invalid data is entered box under Error Alert tab, then click the OK button.
Should I use STDEV s or STDEV P in Excel? ›The STDEV. S function is used when calculating the standard deviation for an entire population. If we are calculating the standard deviation of an entire population, we need to use the STDEV. P function.
How do you ignore blanks in unique functions? ›Get unique values in Excel ignoring blanks
To get a list of unique values without blanks, this is what you need to do: Filter out blank cells and empty strings by using the FILTER function.
If data represents a sample, use the STDEV function. Note: STDEVP has been replaced with a newer function called STDEV. P, which has identical behavior. Although STDEVP still exists for backwards compatibility, Microsoft recommends that people use the newer STDEV.
How do you ignore validation? ›You can set the ignore property in the config object for the validation. $("form[name='editPost']"). validate({ ignore: ".
How do I bypass a validation rule for a particular user? ›- Author: Rishabh Dubey.
- Step 1: Create a Custom permission.
- Step 2: Create A permission Set and Assign the custom permission to the permission set.
- Step 3: Assign this permission set to the desired users.
- Step 4: Update your Validation Rule.
Skip cells when dragging formula with OFFSET and ROW functions. In Excel, with the combination of the OFFSET and ROW functions, you can create a formula to easily skip cells when dragging it in a column.
Which is better isEmpty or isBlank? ›Practically speaking, you should generally use String. isBlank if you expect potentially whitespace strings (e.g. from user input that may be all whitespace), and String. isEmpty when checking fields from the database, which will never contain a string of just whitespace. Save this answer.
What is the difference between isEmpty and null? ›
An empty string is a string instance of zero length, whereas a null string has no value at all. An empty string is represented as "" . It is a character sequence of zero characters. A null string is represented by null .
What is _ isEmpty? ›isEmpty() function: It is used to check whether a list, array, string, object etc is empty or not.
How do you use an IF function for a blank cell? ›Sometimes you need to check if a cell is blank, generally because you might not want a formula to display a result without input. In this case we're using IF with the ISBLANK function: =IF(ISBLANK(D2),"Blank","Not Blank")
Can Xlookup ignore blank cells? ›Explanation. When XLOOKUP can't find a value in a lookup array, it returns an #N/A error. You can use the IFNA function or IFERROR function to trap this error and return a different result. However, when the result is an empty cell, XLOOKUP does not throw an error.
How do you make a cell untouchable in Excel? ›On the worksheet, select just the cells that you want to lock. Bring up the Format Cells popup window again (Ctrl+Shift+F). This time, on the Protection tab, check the Locked box and then click OK. On the Review tab, click Protect Sheet.
How do I hide blank cells in a drop down list? ›Select the cell that contains a data validation list. Choose Data|Validation. On the Settings tab, remove the check mark from the Ignore blank box. Click OK.
How do you find a blank cell in a range? ›Use the COUNTBLANK function, one of the Statistical functions, to count the number of empty cells in a range of cells.
Can you do Isblank on a range? ›We can use the ISBLANK coupled with conditional formatting. For example, suppose we want to highlight the blank cells in the range A2:F9, we select the range and use a conditional formatting rule with the following formula: =ISBLANK(A2:F9).
How do I remove blanks from a cell in Excel? ›You can also remove spaces using the Find/Replace feature in Excel. Click CTRL+F to open the Find dialog box, then click the Replace tab. Enter one space ” ” in the Find what: field and leave the Replace with: field empty to remove all spaces.
How do I create a dynamic list in Excel? ›- Select a cell where you want to create the drop down list (cell C2 in this example).
- Go to Data –> Data Tools –> Data Validation.
- In the Data Validation dialogue box, within the Settings tab, select List as the Validation criteria.
How do I create a conditional list in Excel? ›
Select the cell where you want the Dependent/Conditional Drop Down list (E3 in this example). Go to Data –> Data Validation. In the Data Validation dialog box, within the setting tab, make sure List in selected. In the Source field, enter the formula =INDIRECT(D3).
What is the difference between Isblank and IsEmpty in Excel? ›The IsBlank() function is used to check if a value is Blank. The IsEmpty() function is used to check if a table contains any records, in other words, the IsEmpty() function is used to detect if there is no records within a Table. The "" (empty string) is a string text value, but there is no character within it.
How do you check if a range contains a blank cell in Excel? ›Excel ISBLANK function
The ISBLANK function in Excel checks whether a cell is blank or not. Like other IS functions, it always returns a Boolean value as the result: TRUE if a cell is empty and FALSE if a cell is not empty. Where value is a reference to the cell you want to test.
Sometimes you need to check if a cell is blank, generally because you might not want a formula to display a result without input. In this case we're using IF with the ISBLANK function: =IF(ISBLANK(D2),"Blank","Not Blank")
What is the difference between Isblank and is null? ›The isNull operator checks a string and returns a boolean value: true if the string is null, or false if the string is not null. The isEmpty operator checks if a string contains no characters and is only whitespace. The isBlank operator checks if a string contains no characters, is only whitespace, and is null.
What is the formula for not blank in Excel? ›To evaluate the cells are Not Blank you need to use either the logical expression Not Equal to Blank (<>””) of ISBLANK function in logical_test argument of IF formula. In case of logical expression Not Equal to Blank (<>””) logical_test argument returns TRUE if the cell is Not Blank, otherwise, it returns FALSE.