ThisCalculator

Everyday

Last Non-Empty Cell in Excel: 4 Easy Ways

Updated · Standard formula, shown below

This guide shows you how to find the value of the last non-empty cell in a column or row in Excel, using formulas that work in current versions of Excel and Google Sheets. You will get the exact formula for each method, a plain-English explanation of how it works, and solutions for common problems like blank cells, errors, and text versus numbers.

Why You Need This Formula

Spreadsheets often have data that grows downward or to the right: daily sales, test scores, inventory counts, or a log of transactions. You frequently need the most recent entry, which is simply the last cell that contains something. Manually scrolling to find it is slow and error-prone, and a static reference like =A10 breaks as soon as you add a new row. A dynamic formula that always finds the last non-empty cell saves time and prevents mistakes, especially when you build dashboards or summary sheets that should update automatically.

Method 1: LOOKUP for the Last Number or Text

The most compact and widely used formula is built with LOOKUP. It works for both numbers and text, and it ignores empty cells in the middle of the range.

=LOOKUP(2, 1/(A:A<>""), A:A)

Here is what each part does:

  • A:A<>"" checks every cell in column A and returns TRUE if it is not empty, FALSE if it is empty.
  • 1/(...) converts TRUE to 1 and FALSE to a division-by-zero error (#DIV/0!).
  • LOOKUP(2, ...) searches for the number 2 in an array that only contains 1s and errors. Since 2 is larger than any value, LOOKUP finds the last 1, which corresponds to the last non-empty cell.
  • The third argument A:A tells LOOKUP to return the value from that same cell.

For a row instead of a column, replace A:A with 1:1:

=LOOKUP(2, 1/(1:1<>""), 1:1)

This formula is short, does not require array entry (no Ctrl+Shift+Enter), and works in Excel 365, Excel 2019, Excel 2016, and Google Sheets.

Method 2: INDEX and MATCH for More Control

If you need to find the last non-empty cell but also want to handle errors or use a specific match type, the INDEX/MATCH combination is more flexible.

=INDEX(A:A, MATCH(2, 1/(A:A<>""), 1))

This works similarly: 1/(A:A<>"") creates an array of 1s and errors. MATCH(2, ..., 1) finds the position of the largest value less than or equal to 2, which is the last 1. INDEX then returns the value at that row number.

The advantage of this method is that you can easily change the third argument of MATCH to 0 for an exact match, or wrap the whole thing in IFERROR to return a custom message when the column is completely empty:

=IFERROR(INDEX(A:A, MATCH(2, 1/(A:A<>""), 1)), "No data")

Method 3: XLOOKUP (Excel 365 and 2021)

If you use Excel 365 or Excel 2021, XLOOKUP is the cleanest and most readable option. It has a built-in “search mode” that starts from the last cell.

=XLOOKUP("*", A:A, A:A, "", 2, -1)

Breaking it down:

  • "*" is the wildcard that matches any text. For numbers, use a very large number like 9.99E+307.
  • A:A is the lookup array.
  • A:A is the return array (same column here).
  • "" is the value to return if nothing is found.
  • 2 means wildcard match.
  • -1 means search from last to first.

For a column that contains only numbers, use:

=XLOOKUP(9.99E+307, A:A, A:A, "", -1, -1)

The -1 in the fifth argument means exact match or next smaller, and the -1 in the sixth means search backwards. This is the easiest formula to read and maintain.

Method 4: FILTER (Excel 365) or QUERY (Google Sheets)

For a one-liner that returns the last non-empty cell and is easy to adapt, FILTER in Excel 365 works well:

=INDEX(FILTER(A:A, A:A<>""), COUNTA(A:A), 1)

This filters out all empty cells, then uses COUNTA to get the count of non-empty cells, and INDEX to pick the last one. In Google Sheets, the equivalent uses QUERY:

=INDEX(QUERY(A:A, "where A is not null", 0), COUNTA(A:A), 1)

Both are longer but very explicit about what they do, which can help when you revisit the spreadsheet months later.

Common Problems and How to Fix Them

These formulas fail or return unexpected results in a few common situations:

  • Empty cells in the middle: All methods above ignore blanks, so they work fine. The LOOKUP and INDEX/MATCH versions specifically skip errors and blanks.
  • Trailing spaces or non-printing characters: A cell that looks empty may contain a space. The formulas treat it as non-empty. Use =TRIM(A1) in a helper column to clean data first.
  • Formulas that return “”: If a cell contains a formula like =IF(...) that outputs an empty string, the cell is technically not empty. Use A:A<>"" to treat it as non-empty. If you want to ignore those, replace A:A<>"" with LEN(A:A)>0 (but that still counts spaces).
  • Errors in the data: If a cell contains #N/A or #DIV/0!, the LOOKUP method may return that error. Wrap the formula in IFERROR or use AGGREGATE to ignore errors.
  • Whole-column references: Using A:A is convenient but can slow down large sheets. Limit the range to A1:A10000 or a named range for better performance.

How to Choose the Right Method

Here is a quick decision guide:

  • Use LOOKUP for a short, no-frills formula that works everywhere.
  • Use INDEX/MATCH when you need to handle errors or return a custom message.
  • Use XLOOKUP if you are on Excel 365 or 2021 and want readability.
  • Use FILTER or QUERY when you are already comfortable with those functions and want a self-documenting formula.

All methods give the same result for a normal column of numbers or text. The choice is about your Excel version, your comfort level, and whether you need extra error handling.

Frequently Asked Questions

How do I get the last non-empty cell in a row instead of a column?

Replace the column reference with a row reference. For LOOKUP: =LOOKUP(2, 1/(1:1<>""), 1:1). For XLOOKUP: =XLOOKUP("*", 1:1, 1:1, "", 2, -1). The logic is identical; only the range changes.

Why does my formula return 0 instead of the last value?

This usually happens when the last non-empty cell contains a formula that returns an empty string (""). The cell is not truly empty, so A:A<>"" sees it as non-empty, and the value is an empty string, which displays as 0. Use =LOOKUP(2, 1/(LEN(A:A)>0), A:A) to ignore cells that are effectively empty.

Can I get the last non-empty cell in a range that contains errors?

Yes. Use AGGREGATE to ignore errors: =INDEX(A:A, AGGREGATE(14, 6, ROW(A:A)/(A:A<>""), 1)). This finds the largest row number where the cell is non-empty, ignoring errors. It requires array entry in older Excel versions.

Does this work in Google Sheets?

Yes. The LOOKUP and INDEX/MATCH formulas work exactly the same in Google Sheets. The XLOOKUP method is not available in Google Sheets, but you can use FILTER or QUERY instead.

What if the column is completely empty?

The LOOKUP formula returns #N/A. Wrap it in IFERROR to show a friendly message: =IFERROR(LOOKUP(2, 1/(A:A<>""), A:A), "No data").

Is there a way to get the last non-empty cell without using an array formula?

Yes. The LOOKUP and XLOOKUP methods do not require Ctrl+Shift+Enter. They work as normal formulas. The INDEX/MATCH version also works without array entry in most cases, but the AGGREGATE version may require it in older Excel.

Related

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.