# How to Ignore Blank Cells in Range in Excel (8 Ways) - ExcelDemy (2023)

If you are looking for some of the easiest ways to ignore blank cells in a range in Excel for getting perfect results with formulas, then you will find this article useful. So, let’s start our main article.

8 Ways to Ignore Blank Cells in Range in Excel

Method-1: Ignore Blank Cells in Range by Using the IF Function

Method-2: Using the ISBLANK Function to Ignore Blank Cells in Range in Excel

Method-3: Using the ISNUMBER Function

Method-4: Using the COUNT Function to Ignore Blank Cells in Range in Excel

Method-5: Ignore Blank Cells in Range by Using the COUNTA Function

Method-6: Using the COUNTBLANK Function to Ignore Blank Cells in Range

Method-7: Extracting a Range While Ignoring the Blank Cells

Method-8: Ignore Blank Cells in Range by Using the AVERAGE Function

Practice Section

Conclusion

Related Articles

Ignore Blank Cells.xlsx

(Video) How to hide cell contents in Excel spreadsheets.

## 8 Ways to Ignore Blank Cells in Range in Excel

Here, we have the following dataset containing the Selling Prices and Cost Prices of some products of a company. We will show the ways to ignore the blank cells in the range of the Selling Price column. We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.

### Method-1: Ignore Blank Cells in Range by Using the IF Function

Here, we will use the IF function to calculate the Profit Margin of the products ignoring the blank cells of the Selling Price column. If we don’t ignore these blank cells here then we will get errors and to avoid this error ignoring blank cells is essential. Let’s try not to ignore the blank cells at first for calculating Profit Margin.
➤ Enter the following formula in cell E4 and drag down the Fill Handle tool.

`=(C4-D4)/C4`

Here, C4 is the Selling Price, D4 is the Cost Price. So, we are having #DIV/0! error for the blank cells in the Selling Price column. To solve this problem, we will use the following formula to ignore the blank cells

`=IF(C4="","",(C4-D4)/C4)`

When C4 will be blank it will return TRUE, then IF will return a blank otherwise we will get the Profit Margin. ➤ Press ENTER and drag down the Fill Handle tool. Then, you will get the profit margins of the products ignoring the blank cells of the Selling Price column. To have a better visualization let’s format the Profit Margin column. After adding Percent Style, we are getting the following Profit Margins of the products. Read More: How to Remove Blank Cells in Excel (10 Easy Ways)

### Method-2: Using the ISBLANK Function to Ignore Blank Cells in Range in Excel

In this section, we will be using the ISBLANK function for ignoring the blank cells while calculating the Profit Margin of the Selling Price column. Steps:
➤ Type the following formula in cell E4.

`=IF(ISBLANK(C4),"",(C4-D4)/C4)`

• ISBLANK(C4) → returns TRUE for the blank cells and FALSE for the non-blank cells.
Output → FALSE
• (C4-D4)/C4 → gives the Profit Margin for Apple
Output → 0.439
• IF(ISBLANK(C4),””,(C4-D4)/C4) becomes
IF(FALSE,””,0.439) → returns the value 0.439 because the condition is giving FALSE
Output → 0.439
(Video) How to Create Macros in Excel Tutorial ➤ Press ENTER and drag down the Fill Handle tool. After that, you will get the Profit Margins for the products except for the products with blank selling prices. For having percentages in those fraction values apply the Percent Style to the Profit Margin column. Read More: Find If Cell is Blank in Excel (7 Methods)

### Method-3: Using the ISNUMBER Function

You can use the ISNUMBER function to calculate the Profit Margins for the products excluding the products having no Selling Prices. Steps:
➤ Type the following formula in cell E4.

`=IF(ISNUMBER(C4),(C4-D4)/C4,"")`

• ISNUMBER(C4) → returns TRUE for the numbers otherwise FALSE (for checking texts you can use the ISTEXT function similarly)
Output → TRUE
• (C4-D4)/C4 → gives the Profit Margin for Apple
Output → 0.439
• IF(ISNUMBER(C4),(C4-D4)/C4,””) becomes
IF(TRUE, 0.439,””) → returns the value 0.439 because the condition is giving TRUE
Output → 0.439 ➤ Press ENTER and drag down the Fill Handle tool. Then, you will get the Profit Margins for the products ignoring the blank cells of the Selling Price column. After applying the Percent Style we will get the Profit Margins as below. Read More: How to Remove Blank Cells Using Formula in Excel (7 Methods)

### Method-4: Using the COUNT Function to Ignore Blank Cells in Range in Excel

Here, we will use the COUNT function to calculate the Profit Margins of the products ignoring the blank cells of the Selling Price column. Steps:
➤ Type the following formula in cell E4.

`=IF(COUNT(C4)=1,(C4-D4)/C4,"")`

• COUNT(C4) → counts the number of cells containing numbers
Output → 1
• COUNT(C4)=1 becomes 1=1 and so returns TRUE.
• (C4-D4)/C4 → gives the Profit Margin for Apple
Output → 0.439
• IF(COUNT(C4)=1,(C4-D4)/C4,””) becomes
IF(TRUE,0.439,””) → returns the value 0.439 because the condition is giving TRUE
Output → 0.439 ➤ Press ENTER and drag down the Fill Handle tool. (Video) If Time is Within Range | If Time is Greater Than or Less Than, Return Value | Lookup Time in Range

Afterward, you will get the Profit Margins for the products except for the products with blank selling prices. For having percentages in those fraction values apply the Percent Style to the Profit Margin column. Read More: How to Remove Blank Cells from a Range in Excel (9 Methods)

• How to Delete Empty Cells in Excel (6 Methods)
• Remove Blank Lines in Excel (8 Easy Ways)
• Fill Blank Cells with Value Above in Excel (4 Methods)
• Excel VBA: Check If Multiple Cells Are Empty (9 Examples)
• How to Deal with Blank Cells That Are Not Really Blank in Excel (4 Ways)

### Method-5: Ignore Blank Cells in Range by Using the COUNTA Function

In this section, we will calculate the Profit Margins of the products using the COUNTA function only for the products having values in the Selling Price column. Steps:
➤ Type the following formula in cell E4.

`=IF(COUNTA(C4)=1,(C4-D4)/C4,"")`

• COUNTA(C4) → counts the number of cells containing numbers and texts
Output → 1
• COUNTA(C4)=1 → becomes 1=1 and so returns TRUE.
• (C4-D4)/C4 → gives the Profit Margin for Apple
Output → 0.439
• IF(COUNTA(C4)=1,(C4-D4)/C4,””) becomes
IF(TRUE,0.439,””) → returns the value 0.439 because the condition is giving TRUE
Output → 0.439 ➤ Press ENTER and drag down the Fill Handle tool. Then, you will get the profit margins of the products ignoring the blank cells of the Selling Price column. After adding Percent Style, we are getting the following Profit Margins of the products. Read More: How to Find Blank Cells Using VBA in Excel (6 Methods)

### Method-6: Using the COUNTBLANK Function to Ignore Blank Cells in Range

You can use the COUNTBLANK function also to calculate the Profit Margins ignoring the blank cells in the range of the Selling Price column. Steps:
➤ Type the following formula in cell E4.

`=IF(COUNTBLANK(C4)=0,(C4-D4)/C4,"")`

• COUNTBLANK(C4) → counts the number of blank cells
Output → 0
• COUNTBLANK(C4)=0 → becomes 0=0 and so returns TRUE.
• (C4-D4)/C4 → gives the Profit Margin for Apple
Output → 0.439
• IF(COUNTBLANK(C4)=0,(C4-D4)/C4,””) becomes
IF(TRUE,0.439,””) → returns the value 0.439 because the condition is giving TRUE
Output → 0.439 ➤ Press ENTER and drag down the Fill Handle tool. After that, you will get the Profit Margins for the products except for the products with blank selling prices. (Video) SURPRISING Advanced Filter TRICK in Excel (You've Never Heard Of!)

For having percentages in those fraction values apply the Percent Style to the Profit Margin column. Related Content: Formula to Return Blank Cell instead of Zero in Excel (With 5 Alternatives)

### Method-7: Extracting a Range While Ignoring the Blank Cells

Suppose, we want to extract the range of the Selling Price column excluding the blank cells and to do this here we will use the IFERROR function, INDEX function, SMALL function, IF function, ISNUMBER function, ROW function. So, we will extract the values from the left Selling Price column to the right Selling Price column. Steps:
➤ Type the following formula in cell F5.

`=IFERROR(INDEX(\$C\$4:\$C\$14,SMALL(IF(ISNUMBER(\$C\$4:\$C\$14),ROW(\$A\$1:\$A\$11),""),ROW(A1))),"")`

• `ISNUMBER(\$C\$4:\$C\$14)`returns TRUE for the numbers otherwise FALSE
Output →`{TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE}`
• `ROW(\$A\$1:\$A\$11)`returns the row numbers of this range
Output → `{1;2;3;4;5;6;7;8;9;10;11}`
• `ROW(A1)`returns the row number of this cell
Output → 1
• `IF(ISNUMBER(\$C\$4:\$C\$14),ROW(\$A\$1:\$A\$11),"") `becomes
`IF({TRUE;TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;TRUE;FALSE;TRUE},{1;2;3;4;5;6;7;8;9;10;11},"")`returns the row numbers for TRUE otherwise blank
Output → `{1;2; “”;4;5;6; “”;8;9; “”}`
• `SMALL(IF(ISNUMBER(\$C\$4:\$C\$14),ROW(\$A\$1:\$A\$11),""),ROW(A1))) `becomes
`SMALL(``{``1;2; “”;4;5;6; “”;8;9; “”},1)`returns the 1st smallest value of this range
Output → 1
• `INDEX(\$C\$4:\$C\$14,SMALL(IF(ISNUMBER(\$C\$4:\$C\$14),ROW(\$A\$1:\$A\$11),""),ROW(A1))) `becomes
`INDEX(\$C\$4:\$C\$14,1)`returns the 1st value of this range
Output → 3663
• `IFERROR(INDEX(\$C\$4:\$C\$14,SMALL(IF(ISNUMBER(\$C\$4:\$C\$14),ROW(\$A\$1:\$A\$11),""),ROW(A1))),"") `becomes
`IFERROR(3663,"")`returns blank for any error
Output → 3663 ➤ Press ENTER and drag down the Fill Handle tool. In this way, you will get the extracted values of the Selling Price column ignoring the blank cells. Read More: Excel VBA: Find the Next Empty Cell in Range (4 Examples)

### Method-8: Ignore Blank Cells in Range by Using the AVERAGE Function

The AVERAGE function counts the average of a range ignoring the blank cells by default, and so, here we will calculate the average of the selling prices excluding the blank cells using this function. Steps:
➤ Enter the following formula in cell E5.

`=AVERAGE(C4:C14)`

It will calculate the average of this range excluding the blank cells. Now, we can check if the AVERAGE function is actually calculating the average excluding the blank cells.

➤ Enter the following formula in cell E8.

`=(C4+C5+C7+C8+C9+C11+C12+C14)/8`

Here, C4, C5, C7, C8, C9, C11, C12, C14 are the non-blank selling prices.
So, we can see that the both average values of the selling prices are the same. Read More: How to Remove Blanks from List Using Formula in Excel (4 Methods)

## Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself. ## Conclusion

In this article, we tried to cover the ways to ignore blank cells in a range in Excel. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

(Video) UDF1: Concatenate a Range of cells with separator (excel VBA)

## Related Articles

• How to Set Cell to Blank in Formula in Excel (6 Ways)
• If Cell is Blank Then Show 0 in Excel (4 Ways)
• How to Calculate in Excel If Cells are Not Blank: 7 Exemplary Formulas
• Find, Count and Apply Formula If a Cell is Not Blank (With Examples)
• Null vs Blank in Excel

## FAQs

### How do I get Excel to ignore blank cells? ›

Do not calculate or ignore formula if cell is blank in Excel
1. =IF(Specific Cell<>"",Original Formula,"")
2. In our case discussed at the beginning, we need to enter =IF(B2<>"",(TODAY()-B2)/365.25,"") into Cell C2, and then drag the Fill Handle to the range you need.

How do you sum cells and ignore blanks? ›

Case # 1: SUMIF Not Blank

“<>” – > It shows the “NOT EQUAL TO” sign and it must be in the double quotation marks because the formula reads it as characters. Using this will let you sum up all the values that are not blank and you can ignore the blank cells entirely while summation process.

How do you ignore a cell without 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 ignore blank cells in index match? ›

Copy that cell (Ctrl+C), then select cells from C2 to C7 and use the 'Go to special' to select Blank cells only under Home tab, in Editing > Find & Select > Go To Special. Simply just Paste the formula to all the blank cells by Ctrl+V. You will get all the information in one row on the first row for each companies.

Can Vlookup ignore blank cells? ›

When VLOOKUP can't find a value in a lookup table, it returns the #N/A error. You can use the IFNA function or IFERROR function to trap this error. However, when the result in a lookup table is an empty cell, no error is thrown, VLOOKUP simply returns a zero.

What is ignore blank in data validation in Excel? ›

Ignore Blanks When Editing a Cell

If the user starts to edit the cell by typing into the cell, and does not enter a valid number (ie any number smaller than 1 or text), then an error will occur. If the user then backspaces to remove the incorrect number (and thereby leaving the cell blank), the error will still occur.

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.

Does Count ignore blank cells? ›

COUNTA ignores the blank values in D3, D4, D8, and D11, and counts only the cells containing values in column D.

How do you sum only active cells? ›

Just organize your data in table (Ctrl + T) or filter the data the way you want by clicking the Filter button. After that, select the cell immediately below the column you want to total, and click the AutoSum button on the ribbon. A SUBTOTAL formula will be inserted, summing only the visible cells in the column.

## Videos

1. Highlight Active Row and Column in Excel (Based on Cell Selection)
(TrumpExcel)
2. How To Show Or Unhide Multiple Rows And Columns At Once In Excel
(Excel 10 tutorial)
3. VLOOKUP COLUMN and ROW - Handle large data tables with ease [Advanced Excel]
(365 Data Science)
4. Short Excel 2: Named Ranges
5. Excel VBA Save Range to PDF
(The Excel Cave)
6. Excel - Remove Special Characters
(Hay Kel)
Top Articles
Latest Posts
Article information

Author: Delena Feil

Last Updated: 27/07/2023

Views: 6365

Rating: 4.4 / 5 (65 voted)

Author information

Name: Delena Feil

Birthday: 1998-08-29

Address: 747 Lubowitz Run, Sidmouth, HI 90646-5543

Phone: +99513241752844

Job: Design Supervisor

Hobby: Digital arts, Lacemaking, Air sports, Running, Scouting, Shooting, Puzzles

Introduction: My name is Delena Feil, I am a clean, splendid, calm, fancy, jolly, bright, faithful person who loves writing and wants to share my knowledge and understanding with you.