How To Find The Position Of The First Blank Cell In A Range



Generic Formula


Note: This is an array formula. Do not type out the {} brackets. Hold Ctrl + Shift then press Enter while in Edit Mode to create an array formula.

Range – This is the range in which you want to find the position of the first blank cell.

What It Does

This formula will return the position of the first blank cell within a given range.

How It Works

First we create an array of Boolean values with ISBLANK(Range). The values in this array will either be TRUE if the corresponding cell in the Range is blank or FALSE if the corresponding cell in the Range is not blank. Then we use the MATCH function to find the first TRUE value in our Boolean array which corresponds to the first blank cell in the range.

In our example ISBLANK(Range) creates the following array.


MATCH(TRUE,{FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},0) then looks for the first exact match to a TRUE value within this array. This returns the value 3 since the first TRUE value is in the 3rd position and this is our first blank cell in the range.

About the Author

John MacDougall

John MacDougall

John is a Microsoft MVP and freelance consultant and trainer specializing in Excel, Power BI, Power Automate, Power Apps and SharePoint. You can find other interesting articles from John on his blog or YouTube channel.


Related Posts



Get the Latest Microsoft Excel Tips

Follow Us

Follow us on social media to stay up to date with the latest in Microsoft Excel!