## Navigate to first empty cell using a hyperlink formula

This article will demonstrate how to create a hyperlink that takes you to the first empty cell below data in a column. The way it works is that the Excel user press with left mouse button ons on the hyperlink and Excel instantly takes you to the first empty cell in a column.

The image above shows the formula in cell C2, it creates a hyperlink pointing to the first empty cell below the data set. This means that if more values are added to the data set the formula adapts and changes the destination cell making it dynamic without any user interaction.

This method can make the worksheet more user-friendly and easier to navigate, there is no VBA code or User Defined Functions in this workbook. The HYPERLINK function is a really versatile and interesting function that can perform many useful actions.

Formula in cell C2:

The hyperlink takes you to the first empty cell below the data set even if you have one or many blanks in the data set. The animated image above demonstrates this, the data set is not large in this example but you get the idea.

### Explaining the formula in cell C2

I recommend using the "Evaluate Formula" tool which is a feature built-in to Excel. It allows you to see the formula calculation step by step.

Go to tab "Formulas" on the ribbon, press with left mouse button on the "Evaluate Formula" button and a dialog box opens, see image above.

The step that is about to be calculated is underlined, when you press with left mouse button on the "Evaluate" button the underlined expression is calculated. The calculated step is in italic.

Use the scroll arrows if the formula is larger than the window, this lets you see the entire formula. I wish it was possible to make that evaluation window larger though.

Press with left mouse button on the "Evaluate" button to move to the next step in the calculation. Continue press with left mouse button oning the "Evaluate" button to see all calculation steps in the formula. Press with left mouse button on "Close" button to dismiss the dialog box.

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

The CHAR function converts a number to the corresponding character based on your computer's character set. The numbers can be from 1 to 255 and they represent a character.

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

becomes

MATCH(ÿ,B1:B100,1)

The MATCH function returns the relative position of a given value in a cell range. The third argument lets you choose how the function matches the value. Number 1 let you find the smallest value that is greater than or equal to the lookup value.

MATCH(*lookup_value*, *lookup_array*, *[match_type]*)

We are looking for the last character in your computer's character set and it will most likely not be found. This will match the last value in your lookup_array even if it is not sorted in ascending order.

If you know that your lookup_array contains this value then I recommend using multiple values concatenated like this CHAR(255)&CHAR(255).

MATCH(ÿ,B1:B100,1)

and returns 8 which is the last value in cell range B1:B100.

If your data set has more than 100 rows use a larger cell range. For example B1:B1000 or the entire column B:B, however, the formula will probably run slower.

#### Step 2 - Join file name, sheet and cell address

The HYPERLINK function requires the file name, worksheet name and a cell reference to work properly. Step 1 above calculated the row number of the last non-empty cell.

We need to add the file name, worksheet name and column letter, then concatenate this string with the row number. The workbook name has a beginning and ending bracket characters and the worksheet name ends with a ! (exclamation mark).

We need to reference the first empty cell below the last non-empty value, in order to do that we add 1 to the calculated row number.

"[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

and returns

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

#### Step 3 - Create the hyperlink

The HYPERLINK function lets you build a hyperlink in your worksheet using a formula. A formula can be made dynamic so if your data set changes the formula changes as well without any user interaction.

HYPERLINK(*link_location*, [*friendly_name*])

The HYPERLINK function has two arguments, the first one is the link location. We built the link location in step 1 and 2. The friendly name is optional, I chose the word "Hyperlink". However, I recommend you use something more descriptive.

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")

### Tip 1

You can also use this keyboard shortcut to find the last non-empty cell:

- Select any cell in the column.
- Ctrl + Arrow key down.

However, if there are many blanks in your column you must repeatedly press the short cut keys to reach the last one.

Go to a column that has no values, press CTRL + arrow key down. This takes you to the last cell in that column. Use the arrow keys or your mouse to select any cell in the column you want to find the last value in.

Now press CTRL + arrow key up and Excel takes you to the last non-empty cell in that column.

### Recommended reading

- Excel Hyperlinks and Hyperlink Function (Contextures)
- HYPERLINK function (Office support)
- Get workbook name (Formula)
- Get worksheet name (Formula)

The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]

Sean asks: Basically, when I do a refresh of the data in the "pivotdata" worksheet, I need it to recognise […]

Today I'll show you a formula that returns a hyperlink pointing to a location based on a lookup value. When […]

The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]

This article demonstrates a macro that automatically populates a worksheet with a Table of Contents, it contains hyperlinks to worksheets, […]

Table of Contents List files in a folder and create hyperlinks (VBA) How to navigate quickly in a complex workbook […]

The image above shows two hyperlinks, the first hyperlink lets you select a data set automatically based on a dynamic […]

Question: I have a workbook containing a lot of worksheets and data. I need a way to find certain data/sheets […]

### 9 Responses to “Navigate to first empty cell using a hyperlink formula”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Paste image link to your comment.

**Contact Oscar**

You can contact me through this contact form

Oscar, I played around with this type of MATCH formula long ago and found it to be a little buggy. Noenetheless, I have found that the most general form of this formula is:

=MAX(MATCH(0,A:A,-1),MATCH(CHAR(255),A:A,1))

David Hager,

You are right, I remember sam´s comment now:

https://www.get-digital-help.com/2011/04/28/create-a-dynamic-named-range-in-excel/#comment-24493

I am at a complete loss with this. This hyperlink function should work perfectly but it completely ignores the array that I program it to look within and returns me to a cell that is outside of the lookup array.

=HYPERLINK("[CompletePull.xlsx]'Sheet2'!$E$"&MATCH(410000,Sheet2!E3:E1002,1)+1,1)

I am attempting to match the entered date and return the cursor to the empty cell below on sheet two. My cursor returns to cell C1 instead. Would anyone tell me where I am going wrong here? My lookup array is clearly Sheet2 E3:E1002. Why would it even return my cursor to anything outside of that range?

=HYPERLINK("[CompletePull.xlsx]'Sheet2'!$E$"&MATCH(410000,Sheet2!E3:E1002,1)+3,1)

Okay, I have figured out how to get this syntax to work for dates. For some reason I had to tell the match formula to return the reference plus 3. I am having a hard time dissecting that but if anyone can help me with it, it would be much appreciated.

Thank you Oscar, I've been trying to figure this one out for a long time.

Randy,

MATCH(410000,Sheet2!E3:E1002,1)

Is your list sorted in an ascending order?

Read this:

Oscar, let's use CELL("filename") to make a filename-sheetname part dynamic, so, combined with David's suggestion, it will look like: HYPERLINK(REPLACE(CELL("filename"),1,FIND("[",CELL("filename")),"[")&"!B"&MAX(MATCH(0,B:B,-1),MATCH(CHAR(255),B:B,1))+1,"HYPERLINK")

Leonid,

great comment.

Thank you!

What is wrong with the below formula? When I press with left mouse button on the hyperlink, the cursor stops at A2270. Tried out various options, yet cursor stops at A2270.

`=HYPERLINK("[BANK STATEMENT.xlsx]ICICI$c$"&MATCH(CHAR(255),C2:C3000,1)+1,"BACK")`

Help !

Thanks,

`Martin`

Martin,

Perhaps there is a CHAR(255) character in C2270?

This article demonstrates a formula that extracts ANSI characters:

https://www.get-digital-help.com/2018/01/26/identify-all-characters-in-a-cell-value/