Today I´ll show you how to create a useful hyperlink in excel. If you click the link it will take you to the first empty row in a data set.

Use hyperlinks to move to last row in a data setff

Formula in cell C2:

=HYPERLINK("[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$"&MATCH(CHAR(255),B1:B100,1)+1,"Hyperlink")

Explaining the formula in cell C2

Step 1 - Find the last nonempty cell in cell range B1:B100

MATCH(CHAR(255),B1:B100,1)

becomes

MATCH(ÿ,B1:B100,1)

and returns 8. If your data set is bigger than row 100, expand cell range B1:B100 further down.

Step 2 - Join file name, sheet and cell address

"[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$"&MATCH(CHAR(255),B1:B100,1)+1

becomes

"[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$"&8+1

becomes

[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$9

Step 3 - Create the hyperlink

HYPERLINK("[Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$"&MATCH(CHAR(255),B1:B100,1)+1,"Hyperlink")

becomes

=HYPERLINK([Quickly jump to last row using hyperlinks.xlsx]Sheet1!$B$9,"Hyperlink")

You can also use this keyboard shorcut to find the next empty cell: Ctrl + Arrow

Download excel *.xlsx file

Quickly jump to last row using hyperlinks.xlsx

Functions in this post

MATCH(lookup_valuelookup_array[match_type])
Returns the relative position of an item in an array that matches a specified value in a specific order