How to find the first blank cell in a column in Excel
Formula (Excel and Google Sheets)
=XLOOKUP(TRUE,B2:B9="",A2:A9,"Full")Look for the first TRUE in a blank test: =XLOOKUP(TRUE,B2:B9="",A2:A9,"Full") returns the slot next to the first empty cell.
- Result on the example
- Tue 8 AM in
E1 - Works in
- Excel 2021, 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
B2:B9="" is TRUE for every empty volunteer cell. XLOOKUP returns the slot beside the first TRUE, Tue 8 AM. When all slots are filled, it shows the 4th argument, "Full".
=XLOOKUP(TRUE,B2:B9="",A2:A9,"Full")Result"Tue 8 AM"
B2:B98 rows × 1 col: "Maya", "Jon", "Ava", (empty), …A range: 8 cells in one column.
B2:B9=""{8 rows × 1 col} FALSE, FALSE, FALSE, TRUE, …The = operator tests whether the two sides are equal.
A2:A98 rows × 1 col: "Mon 8 AM", "Mon 10 AM", "Mon 1 PM", "Tue 8 AM", …A range: 8 cells in one column.
XLOOKUP(TRUE,B2:B9="",A2:A9,"Full")"Tue 8 AM"XLOOKUP: Searches one range and returns the matching item from another.- lookup_value
TRUE - lookup_array
B2:B9=""{8 rows × 1 col} FALSE, FALSE, FALSE, TRUE, … - return_array
A2:A98 rows × 1 col: "Mon 8 AM", "Mon 10 AM", "Mon 1 PM", "Tue 8 AM", … - if_not_found
"Full"
- lookup_value
In Google Sheets
The same formula works in Google Sheets: =XLOOKUP(TRUE,B2:B9="",A2:A9,"Full"). We ran it in Google Sheets mode on the same data and it returns Tue 8 AM.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =INDEX(A2:A9,MATCH(TRUE,INDEX(B2:B9="",0),0)) | Tue 8 AM | Every Excel version, without Ctrl+Shift+Enter. |
| =XMATCH(TRUE,B2:B9="")+ROW(B2)-1 | 5Different result | Returns the sheet row of the first blank cell instead. The row number, 5. |
Common mistakes
- A formula returning "" counts as blank in this test; a cell containing a space doesn't.
- ISBLANK is stricter: it is FALSE for a formula that returns "". Use
=""unless you need that.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Find the first blank cell in a column
E1, return the first slot that has no volunteer yet, or "Full" if every slot is taken.Questions
How do I find the first blank row in a column with no end?
Use a generous range: =XMATCH(TRUE,B2:B1000="")+1 gives the first empty row below the header.
How do I count the open slots?
Use =COUNTBLANK(B2:B9).
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.