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

Microsoft Excel is, in fact, a beneficial program. We can do many things with a given dataset using Excel’s tools and features. In Excel, we often need to return all of the cells that are not blank from a range. This article discusses three good ways to get non-empty Excel cells. So, look at these 3 suitable ways to Return Non Blank Cells from a Range in Excel.

How to Return Non Blank Cells from a Range in Excel: 3 Suitable Ways

Let’s use a sample dataset to illustrate this point with an example. The following dataset, for instance, consists of two columns labeled Area and Sales Rep. This specific dataset has several records that are missing important information. In Excel, 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. In addition, I have yet to mention that I have used the Microsoft Excel 365 edition for this essay. You are, however, at liberty to utilize any other edition that affords you the most significant degree of commoditization in the manner that best suits your needs.

excel return non blank cells from a range

1. Utilize FILTER Function to Display Non Blank Cells from a Field in Excel

The FILTER function can filter a range of information based on the conditions you define in Excel. The function can retrieve contents from a range quickly and easily. In this scenario, we will make use of the FILTER function to extract all of the cells from a field that is not blank. To finish the work, please follow the directions that are stated below.


  • To begin, take another column titled Sales Rep List throughout the column.
  • Second, select the D5 cell.
  • Third, type the following formula in the Formula Bar.

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

  • After that, hit the Enter or Tab key.
  • Consequently, it will produce the desired output like the below one.

Utilize FILTER Function to Display Non Blank Cells from a Field in Excel

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

The ROW function can be employed to determine a cell’s row number and return that value. However, supplying an Excel range to the ROWS function will return the entire number of rows in that range. The SMALL function orders a set of numbers according to their position in a supplied list. Using the INDEX function, we can get any value or reference to a value inside a table or range. Lastly, the ISERROR function is a rational function that indicates whether the cells referred to are wrong. Throughout this demonstration, we will build a formula combining the abovementioned functions to display non-blank cells from a range. To finish the assignment, follow these steps.


  • To start, make another column in the D column called Sales Rep List.
  • Secondly, choose the D5 cell.
  • After that, type the following formula in the cell.


  • Next, hit Enter or Tab.

Combine IFERROR, INDEX, SMALL, and ROW Functions to Get Non-Empty Cells From a Range

  • Subsequently, we will get the intended outcome for D5.
  • Currently, we have to use the same formula for other cells.
  • To achieve this, use the AutoFill Handle icon and drag it into cell D10.
  • As a result, we will have the desired output as the following one.

Output of Combining IFERROR, INDEX, SMALL, and ROW Functions to Get Non-Empty Cells From a Range

How Does the Formula Work?


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. Then, 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 enables us to compare 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 rational function that tells whether the cells being kept referring to have a mistake. In our demonstration, we will get Errors when we try to work with empty cells, and we will use the ISERROR function to avoid these errors.

3. Run Excel VBA Code to Return Non Blank Cells from a Range

Visual Basic for Application is what VBA stands for. VBA is a language for programming that Microsoft made. Users can use the VBA programming language to access functions that Excel doesn’t support. We will use VBA to get the non-empty cells from an Excel field in this part. Please do the task right by following the instructions below.


  • First, choose the intended sheet as the Active sheet.
  • Likewise, create another column throughout the column.
  • Second, navigate to the Developer tab and click the Visual Basic icon.

Run Excel VBA Code to Return Non Blank Cells From a Range

  • Later, click on,

Insert Module

  • Next, input the following code in the Module box.
Sub ReturnNonBlankCells()
    ActiveCell.Formula2R1C1 = "=FILTER(R5C3:R10C3," & Chr(10) & "R5C3:R10C3 <> """")"
End Sub
  • After that, press  F5  or tap the Save button.

  • Consequently, it will provide the desired result like the one below.

Read More: Formula to Return Blank Cell instead of Zero in Excel

Download Practice Workbook

Downloading the example workbook that was utilized during the presentation is entirely free of charge and is available to anybody interested.


You can Return Non-Blank Cells from a Range in Excel using the steps we just discussed from now on. On the ExcelDemy Website, you can find many articles like this one. Keep using those, and let us know if you have any other ways to get the work done or if you have any new ideas. Remember to post questions, comments, or suggestions in the section below.

Related Articles

<< Go Back to Blank Cells | Excel Cells | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Lutfor Rahman Shimanto
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

Leave a reply

Advanced Excel Exercises with Solutions PDF