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.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel Data Analysis and Business Modeling today!
Formulas are the heart of using Excel, and formulas often refer to ranges of cells. How you insert cells into the ...
Discover MoreInsert a row at the top of a range of cells, and the effects within your formulas may not match your expectations. This ...
Discover MoreGot a large group of people listed in a worksheet and you want to make sure that each person has met with every other ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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