Author: Oscar Cronquist Article last updated on November 01, 2018

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.

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