This article explains how to find the last non-empty row in a mixed dataset that contains no blank cells, covering the most reliable formulas for Excel and Google Sheets. You will learn the exact functions to use, how they work, and how to adapt them when your data includes numbers, text, or dates.
Why the Simple LOOKUP Fails with Mixed Data
Many people try =LOOKUP(2,1/(A:A<>“”),ROW(A:A)) or =MATCH(REPT(“z”,255),A:A). These work only for text or numbers, not both. The LOOKUP trick with 1/(condition) returns the position of the last number or text, but it fails when your column contains a mix of types because it relies on the lookup vector being sorted or on the data type being consistent. The IF/MAX array formula handles all types equally because it simply checks whether each cell is empty, regardless of content.
How to Use It in Excel and Google Sheets
In Excel 365 and Google Sheets, you can enter the formula normally and it will spill or work as an array. In older Excel (2019 and earlier), you must press Ctrl+Shift+Enter to make it an array formula. If you see curly braces around the formula, it worked. For a column that starts at row 1, use =MAX(IF(A:A<>“”,ROW(A:A))). If your data starts at row 5, use =MAX(IF(A5:A100<>“”,ROW(A5:A100)-4)) to get a position relative to the start of the range.
Common Mistakes and How to Avoid Them
- Forgetting to press Ctrl+Shift+Enter in older Excel – the formula returns 0 or a #VALUE! error.
- Using whole-column references in old Excel – A:A can slow down the calculation; limit to a realistic range like A1:A10000.
- Counting blank cells as data – if a cell contains a space character or a formula that returns “”, it is not truly blank. Use =MAX(IF(TRIM(A:A)<>“/”,ROW(A:A))) to ignore spaces, but be aware this treats “0” as data.
- Mixing data types and using MATCH with “zzz” – that only finds text, not numbers or dates.
Alternatives for Specific Data Types
If you know your column contains only numbers, =MATCH(1E+306,A:A) returns the last row with a number. For only text, =MATCH(REPT(“z”,255),A:A) works. For dates, you can use =MAX(IF(ISNUMBER(A:A),ROW(A:A))) because dates are stored as numbers. But for a true mixed column with no blanks, the IF/MAX formula is the only one that handles all types in one shot.
When Your Data Has Blanks
If your mixed data has blank cells in the middle, the formula still returns the last row with any data, which is what you want. If you need the last row of a contiguous block (ignoring trailing blanks), you can use =MAX(IF((A:A<>“”)*(ROW(A:A)<=MATCH(1E+306,A:A)),ROW(A:A))) but that is rarely necessary. The simple IF/MAX already skips blanks and finds the last non-empty cell anywhere in the range.
Frequently Asked Questions
Why does my formula return 0?
This usually happens when you did not press Ctrl+Shift+Enter in older Excel, or when the range is entirely empty. Check that the range actually contains data and that the formula is entered as an array.
Can I use this formula for a row instead of a column?
Yes. Replace ROW with COLUMN. For example, =MAX(IF(1:1<>“”,COLUMN(1:1))) returns the last column with data in row 1.
Does it work if my data has formulas that return blank?
No, because a formula that returns “” is not empty. Use =MAX(IF(A:A<>“”,ROW(A:A))) but note that a cell with a formula returning “” is considered non-empty. To ignore those, you would need to check for length: =MAX(IF(LEN(A:A)>0,ROW(A:A))).
What is the difference between this and COUNTA?
COUNTA counts non-empty cells, but it does not tell you the position of the last one. The formula here returns the row number, which is what you need for dynamic ranges or offsetting.
Can I use this to build a dynamic named range?
Yes. Define a name like LastRow with the formula, then use =OFFSET($A$1,0,0,LastRow,1) as your dynamic range. This is a common technique for charts and pivot tables.
Related
Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.
More cards in this drawer
- How to Choose an Amplifier for Magico A3 Speakers
- How to Ban a Player from Your FiveM Server
- Pokemon Go Friend Codes: How to Add & Use Them33.33 XP/day
- Google Algorithm Change: Original Reporting Boost
- Create Folder for Selected Item on Mac: Full Guide
- Data Center Location Questions for AI Workloads