Find the Cell Address of a Last Used Cell in Google Sheets

Are you looking for a way to easily find the cell address of the last used cell in Google Sheets? Unfortunately, there are no built-in functions for this task, but fear not! I have two custom formulas that will come to your rescue. However, it’s important to note that both formulas may not provide accurate results if there are any blank cells in the primary column of your data range. But don’t worry, I’ll explain how to overcome this limitation.

Primary Column

In your data range, which can consist of multiple columns and infinite rows, you can designate one column as the primary column for the formulas. For example, you can choose Column A as your primary column. It’s crucial to select a column that doesn’t contain any blank cells within your range. Blank cells in the primary column can lead to incorrect results. So choose wisely!

Custom Formula 1: The INDEX Formula

To find the cell address of the last used cell in a range, we’ll utilize three functions: CELL, INDEX, and COUNTA. Among these, the INDEX function plays the main role. Let me walk you through the formula step by step:

1. First, we need to define the reference range in the INDEX function. For example, A1:G represents the range we want to work with.
2. Next, we want to determine the last row number in our data range. To achieve this, we use the COUNTA function on the primary column (Column A in our case).
3. To find the column number, we can simply use the Cell function to return the column number of the last column in our data range (column G).
4. Finally, we combine the INDEX and CELL functions to get the last cell value. The formula will look something like this: =INDEX(A1:G,COUNTA(A1:A),CELL("col",G1))

Now, to obtain the cell address of the last used cell, we can use the Cell function with the “address” info type. Here’s the formula: =CELL("ADDRESS",INDEX(A1:G,COUNTA(A1:A),CELL("col",G1))). Voila! You’ll get the cell address as the result.

Custom Formula 2: A Different Approach

Here’s an alternate formula to find the cell address of the last used cell in Google Sheets. This approach involves the ADDRESS, ARRAYFORMULA, MIN, IF, and ROW functions. Let’s break it down:

1. First, designate your primary column (in this example, column G).
2. Use the ARRAYFORMULA function to apply the formula to the entire range.
3. The IF function checks if each cell in the primary column is blank. If it is, the formula returns the corresponding row number minus the starting row number plus 1.
4. The MIN function then identifies the minimum value from the resulting array, which gives us the last used row number.
5. Lastly, we combine the ADDRESS function with the row and column numbers to obtain the cell address. The formula will look like this: =ADDRESS(ArrayFormula(MIN(IF(G2:G="",ROW(G2:G)-ROW(G2)+1))),7)

That’s it! With this formula, you’ll get the cell address of the last used cell in your Google Sheets.

I hope these custom formulas help you in your Google Sheets adventures. If you’re hungry for more tips and tricks, don’t forget to visit Crawlan.com for all your Google Sheets needs. Happy crunching!