Written by Allen Wyatt (last updated April 18, 2026)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365
In column A Georgi has several thousand values, with a mixture of numeric and text values. The column can contain blank cells, as well. He wonders if there is a formula he can use that will return the address of the first blank cell in the column.
There are many different formulas you can use to determine the first blank cell. Here are some that will do the trick, arranged from long to short:
=ADDRESS(MATCH(TRUE,INDEX(A:A="",0),0), COLUMN(A:A))
=ADDRESS(MATCH(TRUE,INDEX(LEN(A:A)=0,0),0),1)
=ADDRESS(MATCH(TRUE,INDEX(A:A="",0),0),1)
=CELL("address",XLOOKUP(TRUE,A:A="",A:A,,2))
=ADDRESS(MATCH(TRUE, EXACT("",A:A),0),1)
="$A$"&MATCH(TRUE,ISBLANK(A:A),0)
=ADDRESS(XMATCH(1,(N(A:A=""))),1)
=ADDRESS(MATCH(TRUE,A:A="",),1)
Each of these will return the exact same information—the address of the first blank cell in column A. Not all of the functions used in these formulas are available in all versions of Excel. The FILTER and XLOOKUP functions were introduced in Excel 2019, and XMATCH in Excel 2021.
If you would like the address of the first cell that appears blank, then you need to use a different formula. (A cell that appears blank may be the result of a formula returning an empty string.)
=ADDRESS(MATCH(TRUE, EXACT("", A:A), 0), 1)
="$A$"&@FILTER(ROW(A:.A),A:.A="")
=ADDRESS(XMATCH(1,(N(A:A=""))),1)
=ADDRESS(MATCH(TRUE,A:A="",),1)
Regardless of which formula you choose, remember that if your version of Excel predates Excel 2019, you'll need to enter the formula as an array formula by pressing Ctrl+Shift+Enter.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13976) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, 2024, and Excel in Microsoft 365.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
Excel works with decimal values very easily. It is more difficult for the program to work with non-decimal values, such ...
Discover MoreWhen converting between measurement systems, you might want to use two cells for each type of measurement. Make a change ...
Discover MoreFinding the maximum value in a range of cells is easy; finding the address of the cell containing that value is a ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2026-04-24 07:47:10
David Watssman
=HYPERLINK("#Sheet1!A"&MATCH(TRUE,ISBLANK(A:A),0),MATCH(TRUE,ISBLANK(A:A),0))
That will give you the row number in the cell; one that is also a hyperlink to the first blank cell.
2026-04-18 12:29:16
J. Woolley
The Tip's last formula (in both sections) illustrates an interesting issue related to Excel functions with optional arguments. These two versions of the formula will not produce the same result:
=ADDRESS(MATCH(TRUE, A:A="", ), 1)
=ADDRESS(MATCH(TRUE, A:A=""), 1)
The 3rd argument of MATCH is optional with a default value of 1; the second formula uses this default value but the first formula does not. The first formula's 3rd argument is a trailing comma followed by null which MATCH evaluates as 0, not 1; therefore, these two versions of the formula produce the same result:
=ADDRESS(MATCH(TRUE, A:A="", ), 1)
=ADDRESS(MATCH(TRUE, A:A="", 0), 1)
Notice the Tip's other formulas containing MATCH explicitly set its 3rd argument to 0 specifying an exact match.
Here's another example:
=IF(A1<>"", TRUE)
=IF(A1<>"", TRUE, )
When A1 is blank the first formula returns FALSE but the second returns 0.
And here's another example:
=LEFT("Hello")
=LEFT("Hello", )
The first formula returns "H" but the second returns "".
Lesson: If a function's last argument is optional, do not include a trailing comma; either omit the comma or follow it with a specific value.
2026-04-18 12:09:59
J. Woolley
Avoid using TRIMRANGE (currently in Excel 365) with column A in the Tip's formulas because the first blank cell might be outside the trimmed range. In particular, if leading blank rows are trimmed or the range is restricted like A3:A9999, then the reported address will be offset by that many rows.
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2026 Sharon Parq Associates, Inc.
Comments