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 1st list is having main categories and 2nd 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.

image 1


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.

image 2


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

image 3


  • 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.


image 48


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



    • Hi Siahaan,

      This formula will work for 100 rows x 100 columns, just needs to change the cell reference.

      =INDEX ($A$1:$B$100, SMALL (IF ($A$1:$A$100=$D$1, ROW ($A$1:$A$100)), ROW (1:1)), 2)

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.