If you frequently analyze data in Excel, you may come across empty cells before the actual values. Instead of removing these cells, you can get the position or even the value of the first non-blank cell with formulas. In this article, we are going to show you how to find the first non-blank cell in a range in Excel.

Download Workbook


Finding the position:      {=MATCH(FALSE,ISBLANK(range),0)}

Get the value:                    {=INDEX(range,MATCH(FALSE,ISBLANK(range),0))}

* range is the reference from the work range

How it works

Excel doesn’t have a built-in formula to find the first non-blank cell in a range. However, there is ISBLANK function which checks a cell and returns a Boolean value according to its content. The function returns TRUE if cell is blank, FALSE otherwise. Thus, finding the first FALSE value means to find the first non-blank cell.

MATCH function can help to locate FALSE value. Once the position is in hand, you can use it with INDEX function to get the result.

Although VLOOKUP function can be used with a wild card “*” character, it can return only text values. If you are not familiar with INDEX & MATCH functions, please visit our guide: INDEX & MATCH: A Better Way to Look Up Data

The problem is ISBLANK function only works with a single cell. You need to use a helper column to populate TRUE and FALSE values which doesn’t sound practical. Instead, you can use an array function and not need the helper column.

Use the reference of your range of values in the ISBLANK function. This action will return an array of Boolean values. The first FALSE value indicates the position of the first non-blank cell in the range. Wrap the function with either MATCH or INDEX-MATCH combo to get the position or the value respectively.

Use Ctrl + Shift + Enter key combination instead of regular Enter key to evaluate the formula as an array formula.

For more information about array formulas or Ctrl + Shift + Enter formulas, please visit Control Shift Enter Excel Shortcut (CSE) and Array Formulas