# Return Non Blank Cells from a Range in Excel (3 Suitable Ways)

The following dataset consists of two columns labeled Area and Sales Rep. It has several records that are missing important information. We are going to utilize each of the three methods to return the cells that have data from a range, and then we are going to collect that data in a new column that is going to be called Sales Rep List.

### Method 1 – Utilize the FILTER Function to Display Non-Blank Cells from a Field in Excel

Steps:

• Create another column D titled Sales Rep List.
• Select the D5Â cell.
• Copy the following formula in the Formula Bar.

`=FILTER(\$C\$5:\$C\$10,\$C\$5:\$C\$10 <> "")`

• Hit the Enter or TabÂ key.

### Method 2 – Combine IFERROR, INDEX, SMALL, and ROW Functions to Get Non-Empty Cells from a Range

Steps:

• Make another column DÂ called Sales Rep List.
• Choose the D5Â cell.
• Copy the following formula in the cell.

`=IFERROR(INDEX(\$C\$5:\$C\$10,SMALL(IF(\$C\$5:\$C\$10<>"",ROW(\$C\$5:\$C\$10)-ROW(\$C\$5)+1),ROWS(\$C\$5:\$C5))),"")`

• Hit Enter or Tab.

• We will get the intended outcome for D5.
• Use the AutoFill Handle icon and drag it into cell D10.

How Does the Formula Work?

=IFERROR(INDEX(\$C\$5:\$C\$10,SMALL(IF(\$C\$5:\$C\$10<>””,ROW(\$C\$5:\$C\$10)-ROW(\$C\$5)+1),ROWS(\$C\$5:\$C5))),””)

For this formula to make sense, you need to know how to use the following Excel functions:

IFERROR, INDEX, SMALL, ROW and Rows Functions

• ROW(\$C\$5:\$C\$10)-ROW(\$C\$5)+1)

The ROW function within Excel provides the cell’s row number. By involving the Row function in the combination, we find- {1;2;3;4;5;6}.

• IF(\$C\$5:\$C\$10<>””,ROW(\$C\$5:\$C\$10)-ROW(\$C\$5)+1)

The IF function compares any value in the range \$C\$5:\$C\$10 and the null value. Utilizing the IF function, we get – {1;2;FALSE;4;FALSE;6}

• ROWS(\$C\$5:\$C5))

The ROWS function will retrieve the total number of rows included when passing an Excel range. In this demo, it will produce 1 for the first row.

• SMALL(IF(\$C\$5:\$C\$10<>””,ROW(\$C\$5:\$C\$10)-ROW(\$C\$5)+1),ROWS(\$C\$5:\$C5))

The SMALL function provides ranked numerical values according to their location in a given list. It finds the k-smallest item in a particular dataset and delivers the deals. The SMALL function produces 1 as an outcome for the first row.

• INDEX(\$C\$5:\$C\$10,SMALL(IF(\$C\$5:\$C\$10<>””,ROW(\$C\$5:\$C\$10)-ROW(\$C\$5)+1),ROWS(\$C\$5:\$C5)))

To get data from a specific location in a column or range, use the INDEX function. Olivia will appear at the top of the list in this case.

• IFERROR(INDEX(\$C\$5:\$C\$10,SMALL(IF(\$C\$5:\$C\$10<>””,ROW(\$C\$5:\$C\$10)-ROW(\$C\$5)+1),ROWS(\$C\$5:\$C5))),””)

The ISERROR is a function that tells whether the cells or formulas contain calculation mistakes. We will get Errors when we try to work with empty cells, and we will use the ISERROR function to avoid these errors.

### Method 3 – Run Excel VBA Code to Return Non-Blank Cells from a Range

Steps:

• Choose the intended sheet as the ActiveÂ sheet.
• Create another column D.
• Navigate to the Developer tab and click the Visual BasicÂ icon.

• Click on Insert and Module

• Input the following code in the ModuleÂ box.
``````Sub ReturnNonBlankCells()
Â Â Â  Range("D5").Select
Â Â Â  ActiveCell.Formula2R1C1 = "=FILTER(R5C3:R10C3," & Chr(10) & "R5C3:R10C3 <> """")"
End Sub``````
• PressÂ F5 or the SaveÂ button.

• This will provide the desired result like the one below.

## Related Articles

<< Go Back to Excel Cells | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Lutfor Rahman Shimanto

Lutfor Rahman Shimanto, BSc, Information Technology, Jahangirnagar University, Bangladesh, has worked with the ExcelDemy project for over a year. He has written 50+ articles and provided solutions of 100+ comments for ExcelDemy. Currently, he works as an Excel & VBA Developer and provides support and solutions in the ExcelDemy Forum. He has solved 100+ ExcelDemy Forum problems. His work and learning interests are in developing various Excel & VBA and Desktop applications. Outside of work, he enjoys Chess... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF