Using the keyboard shortcut Ctrl+↓, we can jump to the cell just above the first blank cell in a column. To jump to the last cell with data in a column in Google Sheets, we can create a hyperlink.

The above shortcut is useful to make the jump to the last value cell in a column if you have no blank cells between the values.

In this Google Sheets tutorial, you can learn two formula tips. They are;

1. How to create a hyperlink to jump to the last cell in a column?
2. How to create a hyperlink to jump to the last row in a table?

Though the questions seem similar, there is a slight difference in the meaning. The former question is related to a single column, whereas the latter question is related to a multiple column table (data set).

I could see some people using Google Apps Script for the above two purposes. But we can do that with the built-in HYPERLINK function in Google Sheets.

Please follow the steps below.

Hyperlink to Jump to the Last Cell with Data in a Column

First, I am starting with creating a hyperlink button or link to jump to the last non-blank cell in a column.

Steps:-

1. In the sheet in question, click any cell. For example, click on cell E3. Then right-click and select “Get a link to this cell”.

You will see a notification saying, “Link copied to the clipboard”. That means the link of the cell E3 is in your computer memory.

2. Again right-click on cell E3 and select “Paste”.

3. Remove the characters (cell ID) E3 from the end of the pasted link.

4. We must now find the cell ID of the last non-blank cell in column A.

For that, we can use the following formula in cell E4 as detailed in my tutorial titled Address of the Last Non-Empty Cell Ignoring Blanks in a Column in Excel (applicable to Sheets also).

=ArrayFormula("A"&MATCH(2,1/(A1:A<>""),1))

5. Combine the URL in E3 with the formula in E4 as below (we can do that in the cell E3 itself).

https://docs.google.com/spreadsheets/d/1YE3upO7UlY69r1wisVdD7eJjVt2UvIzqtYI1CCNAnns/edit#gid=1225485770&range=
&ArrayFormula("A"&MATCH(2,1/(A1:A<>""),1))

The above formula would be the formula in cell E3 now. After this, you can now safely remove the E4 formula.

Note:- Do not copy the above URL. It’s the URL of my sheet. You must use your own Sheet’s URL as instructed above.

6. It’s time to create the hyperlink to jump to the last cell with data in column A. For that, edit the E3 formula as below.

=hyperlink(
     "https://docs.google.com/spreadsheets/d/1YE3upO7UlY69r1wisVdD7eJjVt2UvIzqtYI1CCNAnns/edit#gid=1225485770&range="
     &ArrayFormula("A"&MATCH(2,1/(A1:A<>""),1)),
     "Jump"
)

7. It will create the hyperlink to jump to the last non-empty cell in column A in Google Sheets.

You can use the formula in any column other than column A. In column A, it would return the circular dependency error.

If you want to place the Hyperlink button to jump to the last cell with data in column A in cell A1 itself, you must avoid using the cell reference A1 in the formula.

What changes should we make to the Hyperlink formula then?

Replace ArrayFormula("A"&MATCH(2,1/(A1:A<>""),1)) with ArrayFormula("A"&MATCH(2,1/(A2:A<>""),1)+1).

The changes are just minimal. We have replaced A1:A with A2:A and to cover the difference of one row added 1 to the MATCH result.

Then cut the formula from cell E3 (right-click and select Cut) and past it in cell A1 (right-click and click Paste). That’s all!

Note:- Feel free to change the hyperlink text “Jump” in the formula with any other text or button characters such as ⬛, 🟧, etc.

For example;

=hyperlink(
     "https://docs.google.com/spreadsheets/d/1YE3upO7UlY69r1wisVdD7eJjVt2UvIzqtYI1CCNAnns/edit#gid=1225485770&range="
     &ArrayFormula("A"&MATCH(2,1/(A2:A<>""),1)+1),
     "🟧"
)

If you use square buttons or any other character, to remove the underline (hyperlink places an underline with the link label character/text), use the shortcut Ctrl+U in cell A1.

Hyperlink to Jump to the Last Row of a Table

We have learned to write the formula to jump to the last cell with data in Google Sheets. What about the last row in a table?

In the above table, assume the cells A16 and A17 are blank. Upon click, the above formula takes you to A15.

It’s OK if you consider the last nonblank cell in column A. But if you are considering the table in the range A2:B17, it’s not OK.

What’s the solution to this ‘problem’?

The solution is to tweak the formula a little bit to consider both the columns, and it’s not a difficult task to do.

To jump to the last row of a table using a hyperlink, tweak the formula as below.

=hyperlink(
     "https://docs.google.com/spreadsheets/d/1YE3upO7UlY69r1wisVdD7eJjVt2UvIzqtYI1CCNAnns/edit#gid=1225485770&range="
     &ArrayFormula("A"&MATCH(2,1/(A2:A&B2:B<>""),1)+1),
     "Jump"
)

I have just changed A2:A in our earlier formula to A2:A&B2:B.

I have more than two columns in my table. What should I do?

No issue. Combine the columns as above using the ampersand sign. For example, if you have four columns (A2:D), combine them as A2:A&B2:B&C2:C&D2:D.

That’s all about how to jump to the last cell/row with data in a column/table in Google Sheets.

Thanks for the stay, enjoy!

Resources:

• Search Value and Hyperlink Cell Found in Google Sheets.
• Create Hyperlink to Vlookup Output Cell in Google Sheets.
• Hyperlink Max and Min Values in Column or Row in Google Sheets.
• Hyperlink to Index-Match Output in Google Sheets.
• Two Ways to Hyperlink to an Email Address in Google Sheets.
• Inserting Multiple Hyperlinks within a Cell in Google Sheets.
• Hyperlink to Jump to Current Date Cell in Google Sheets.