Excel Guides

Referencing the Last Cell in a Column in Excel

There are a few different ways that you can reference the last cell in a column in Excel. One way is to use the LOOKUP function. This function will return the value of a cell in a given row or column, depending on what you specify. For example, if you wanted to reference the last cell in column A, you could use the following formula:

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

Another way to reference the last cell in a column is to use the INDEX function. This function returns a value from a given array, based on a specified row and column number. For example, if you wanted to reference the last cell in column A, you could use the following formula:

=INDEX(A:A,COUNTA(A:A))

You could also use the COUNTA function by itself to return the number of cells in a given range that contain data. This can be useful if you want to reference a cell in another column that is a certain number of rows below the last cell in your original column. For example, if you wanted to reference the cell in column B that is two rows below the last cell in column A, you could use the following formula:

=B COUNTA(A:A)-2

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.