Creating a List of All Non-Blank Cells in a Column in Microsoft Excel

In this article, you’ll learn how to create a list of non-blank cells in a column in Microsoft Excel. To solve this problem, we will use IF, INDEX, SMALL and ROW function in Microsoft Excel.

INDEX function will help to return a value from the intersection between the row number and the column number in an Array

SMALL function will be used to retrieve the smallest value from the data set

Row function will be used to define the sequence number

IF function will be used to give the condition.

Let’s take an example to understand how to create a list of all non-blank cells in a column

Example 1: In the below image, you can see we have 2 lists, in which 1^st list is having main categories and 2^nd list is having sub categories. But, we do not have few main categories. So, we want to retrieve all those sub categories for which we do not have the main category.

Follow below given steps:-

• Enter the formula in cell C2
• =INDEX($A$1:$B$8,SMALL(IF($A$1:$A$8=$A$10,ROW($A$1:$A$8)),ROW(1:1)),2)
• Press Ctrl+Shift+Enter on your keyboard.

• Copy the same formula below in the range till #NUM! Error is not returned

• List of all the sub categories  will appear which are not having main category

In this way, we can create a list of all blank cells in a column in Microsoft Excel by combining the functions.

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com