ThisCalculator

Everyday

Last Cell in Range: Excel Address Formula

Updated · Standard formula, shown below

The calculator for this page is switched off while it is checked. The formula is below, and the worked example in the article shows how to use it by hand.

This calculator finds the address of the last cell in a range you specify. Enter the range (like A1:C10) and the calculator returns the cell address of the last cell in that range, based on the order rows and columns appear in the range. It works for any rectangular range in a spreadsheet, giving you the bottom-right cell by default.

Formula

The last cell in a range is the cell at the intersection of the last row and the last column of that range. In spreadsheet terms, if your range is TopLeft:BottomRight, the last cell is simply BottomRight. For example, in the range A1:C10, the last cell is C10.

If you need the address as a text string (like “C10”), the formula is:

ADDRESS(ROW(BottomRight), COLUMN(BottomRight))

where ROW() gives the row number of the bottom-right cell and COLUMN() gives its column number. The ADDRESS function then converts those numbers into a standard cell reference.

Worked Example

Using the default range A1:C10:

  1. The top-left cell is A1 (row 1, column 1).
  2. The bottom-right cell is C10 (row 10, column 3).
  3. So the last cell is C10.

The calculator returns C10.

When to Use This

Knowing the last cell of a range is useful when you want to:

  • Create dynamic named ranges that automatically expand as you add data.
  • Find the end of a data table to append new rows.
  • Set the print area to include all your data.
  • Write formulas that reference the entire data set without hardcoding the range size.

For example, if you have data in A1:C10 and you add a new row, the last cell becomes C11. Using a formula that finds the last cell ensures your references update automatically.

How to Read the Result

The result is a standard cell address, like C10. It tells you the exact cell at the bottom-right corner of your range. If your range is a single column (e.g., A1:A5), the last cell is the bottom cell (A5). If it’s a single row (A1:C1), the last cell is the rightmost cell (C1).

Common Mistakes

  • Assuming the last cell is always the bottom-right. That’s true for a rectangular range, but if you use a non-contiguous range (like A1:B2,C3:D4), the “last” cell depends on how the range is defined. This calculator assumes a single rectangular range.
  • Confusing row and column. The last row is the highest row number, and the last column is the highest column letter. For A1:C10, the last row is 10, not 1.
  • Using the wrong range syntax. Some spreadsheets use a comma to separate the top-left and bottom-right cells (e.g., A1,C10) instead of a colon. This calculator expects the colon format.

Limits of the Formula

This formula works for any rectangular range, but it does not handle:

  • Non-rectangular ranges (like A1:B2,C3:D4).
  • Ranges defined by named ranges that are not rectangular.
  • Entire rows or columns (like A:A or 1:1). For those, the last cell is the bottom-right of the entire sheet, which is typically XFD1048576 in modern Excel.

Also, if your range is empty, the concept of “last cell” is ambiguous. The calculator will still return the bottom-right cell of the range you specify.

Frequently Asked Questions

How do I find the last cell in a column in Excel?

To find the last cell in a column (e.g., the last non-empty cell), you can use a formula like INDEX(A:A, COUNTA(A:A)) if there are no blank cells in the middle. For a more robust approach, use LOOKUP(2,1/(A:A<>""),A:A) to get the last non-empty value.

What is the difference between the last cell and the last used cell?

The last cell of a range is simply the bottom-right corner of the range you define. The last used cell on a worksheet is the cell at the intersection of the last row and last column that contains any data or formatting. They can be different if your data doesn’t fill the entire rectangular area.

Can I use this to find the last cell in a named range?

Yes, if the named range is rectangular, you can use the same logic. For example, if you have a named range “Data”, the last cell is ADDRESS(ROW(Data)+ROWS(Data)-1, COLUMN(Data)+COLUMNS(Data)-1).

Why does my formula return a cell like $C$10?

That’s because the ADDRESS function returns an absolute reference by default. To get a relative reference, use ADDRESS(row, col, 4) where 4 means relative row and column.

Does this work in Google Sheets?

Yes, the same functions (ADDRESS, ROW, COLUMN) work in Google Sheets. The formula logic is identical.

Related

Formula used
in r | Range | - | A1:C10 | 0
out addr | Last cell address | - | last_cell(r) | 0

Each line is one input (in, sel, list) or one result (out: label, unit, formula, decimals). How the calculators work

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