When we work with a table that has many rows, there is a need to quickly jump to the first blank cell.
Let’s imagine that we are managing the production of steel prefabricated construction elements. We have a worker that is responsible for data entry on received materials. He has a simple table that needs to fill with data on daily basis.
Here is the desired look – CELL with a hyperlink that leads to the first blank cell and easy navigation to bring us back to the first and the last row in the table:
How to achieve this?
There are many ways and a lot of formula combinations that can be used while tackling this problem. In this post we will look closely at how to do this with HYPERLINK function.
Applying HYPERLINK function directly to cell
When I have to “jump” / “navigate” from one cell to another first that comes to my mind is HYPERLINK function. Let’s see the official explanation:
HYPERLINK function
This article describes the formula syntax and usage of the HYPERLINK function in Microsoft Excel.
Description
The HYPERLINK function creates a shortcut that jumps to another location in the current workbook, or opens a document stored on a network server, an intranet, or the Internet. When you click a cell that contains a HYPERLINK function, Excel jumps to the location listed, or opens the document you specified.
Syntax
HYPERLINK(link_location, [friendly_name])
The HYPERLINK function syntax has the following arguments:
Link_location Required. The path and file name to the document to be opened. Link_location can refer to a place in a document — such as a specific cell or named range in an Excel worksheet or workbook, or to a bookmark in a Microsoft Word document. The path can be to a file that is stored on a hard disk drive. The path can also be a universal naming convention (UNC) path on a server (in Microsoft Excel for Windows) or a Uniform Resource Locator (URL) path on the Internet or an intranet.
Note Excel for the web the HYPERLINK function is valid for web addresses (URLs) only. Link_location can be a text string enclosed in quotation marks or a reference to a cell that contains the link as a text string.
If the jump specified in link_location does not exist or cannot be navigated, an error appears when you click the cell.Friendly_name Optional. The jump text or numeric value that is displayed in the cell. Friendly_name is displayed in blue and is underlined. If friendly_name is omitted, the cell displays the link_location as the jump text.
Friendly_name can be a value, a text string, a name, or a cell that contains the jump text or value.
If friendly_name returns an error value (for example, #VALUE!), the cell displays the error instead of the jump text.
Remark
In the Excel desktop application, to select a cell that contains a hyperlink without jumping to the hyperlink destination, click the cell and hold the mouse button until the pointer becomes a cross 
, then release the mouse button. In Excel for the web, select a cell by clicking it when the pointer is an arrow; jump to the hyperlink destination by clicking when the pointer is a pointing hand.
Examples
Example | Result |
|---|---|
=HYPERLINK(“http://example.microsoft.com/report/budget report.xlsx”, “Click for report”) | Opens a workbook saved at http://example.microsoft.com/report. The cell displays “Click for report” as its jump text. |
=HYPERLINK(“[http://example.microsoft.com/report/budget report.xlsx]Annual!F10”, D1) | Creates a hyperlink to cell F10 on the Annual worksheet in the workbook saved at http://example.microsoft.com/report. The cell on the worksheet that contains the hyperlink displays the contents of cell D1 as its jump text. |
=HYPERLINK(“[http://example.microsoft.com/report/budget report.xlsx]’First Quarter’!DeptTotal”, “Click to see First Quarter Department Total”) | Creates a hyperlink to the range named DeptTotal on the First Quarter worksheet in the workbook saved at http://example.microsoft.com/report. The cell on the worksheet that contains the hyperlink displays “Click to see First Quarter Department Total” as its jump text. |
=HYPERLINK(“http://example.microsoft.com/Annual Report.docx]QrtlyProfits”, “Quarterly Profit Report”) | To create a hyperlink to a specific location in a Word file, you use a bookmark to define the location you want to jump to in the file. This example creates a hyperlink to the bookmark QrtlyProfits in the file Annual Report.doc saved at http://example.microsoft.com. |
=HYPERLINK(“\\FINANCE\Statements\1stqtr.xlsx”, D5) | Displays the contents of cell D5 as the jump text in the cell and opens the workbook saved on the FINANCE server in the Statements share. This example uses a UNC path. |
=HYPERLINK(“D:\FINANCE\1stqtr.xlsx”, H10) | Opens the workbook 1stqtr.xlsx that is stored in the Finance directory on drive D, and displays the numeric value that is stored in cell H10. |
=HYPERLINK(“[C:\My Documents\Mybook.xlsx]Totals”) | Creates a hyperlink to the Totals area in another (external) workbook, Mybook.xlsx. |
=HYPERLINK(“[Book1.xlsx]Sheet1!A10″,”Go to Sheet1 > A10”) | To jump to a different location in the current worksheet, include both the workbook name, and worksheet name like this, where Sheet1 is the current worksheet. |
=HYPERLINK(“[Book1.xlsx]January!A10″,”Go to January > A10”) | To jump to a different location in the current worksheet, include both the workbook name, and worksheet name like this, where January is another worksheet in the workbook. |
=HYPERLINK(CELL(“address”,January!A1),”Go to January > A1″) | To jump to a different location in the current worksheet without using the fully qualified worksheet reference ([Book1.xlsx]), you can use this, where CELL(“address”) returns the current workbook name. |
=HYPERLINK($Z$1) | To quickly update all formulas in a worksheet that use a HYPERLINK function with the same arguments, you can place the link target in another cell on the same or another worksheet, and then use an absolute reference to that cell as the link_location in the HYPERLINK formulas. Changes that you make to the link target are immediately reflected in the HYPERLINK formulas. |
If we use Excel native option, right-click on a cell and click LINK from the menu, this is how it looks like:
So, Excel is showing: DataEntryCell!A1, and on hover, it gives note with the full path to the linked cell: file:///D:\OneDrive\…\ – DataEntryCell!A1
If we want to produce this with HYPERLINK function we have to write:
=HYPERLINK(“#A1″;”#A1”)
I found out while copying sheets, moving around workbook, changing names, and distributing to other colleges that sometimes we have problems with links created if we refer to cell only, so I am always adding sheet name to it:
=HYPERLINK(“#DataEntryCell!A1″;”#DataEntryCell!A11”) just like Excel shows it if we use LINK option.
And here, it is very important to remember to always add the hash sign “#” if we are referring to the cell from the active sheet of the same book.
We have two challenges. First to find link location (sheet name and column letter) and second to find row number of a first blank cell.
1. The first part of the problem is to make dynamic and independent sheet names and cell references.
=CELL(“filename”;A1)
Returns the full path of this active workbook:
D:\OneDrive\!analyst2become\!Blog\!Excel Jump to the first blank cell\[!Jump to first blank cell.xlsx]DataEntryCell
Using FIND function to locate square bracket “]”.
=FIND(“]”;CELL(“filename”;A1))
Using LEN function to get string length.
=LEN(CELL(“filename”;A1))
Wrapping FIND and LEN into RIGHT function to always extract sheet name.
=RIGHT(CELL(“filename”;A1);LEN(CELL(“filename”;A1))-FIND(“]”;CELL(“filename”;A1)))
After this, the next step is to fix the DATE column (column C) in the table TableMaterials, by concatenating it with the ampersand symbol “&”.
=….&”!C”&…
This is how we can make sheet and cell references independent, regardless of changing workbook and sheet name.
2. The second part of the problem is how to locate the first blank cell – row number?
Let’s break this linking into smaller steps:
We need to find the first blank cell in column C (It’s the DATE column from table TableMaterials)
=ISBLANK(TableMaterials[DATE])
Dynamic Arrays are great. Excel SPILL the result into the rows below. We can see how it evaluates row by row and writes Boolean TRUE and FALSE for every row evaluated. We need the exact position of the first TRUE.
=MATCH(TRUE;ISBLANK(TableMaterials[DATE]);0)
The result is 1064. This represents the 1064th row in a column DATE in the table TableMaterials. Our array started at the beginning of the table, so its counting position from that point and not from excel row 1. The first row in excel where dates are starting is ROW 8 this is ROW 1 of the table, and the first blank row is 1071 this is ROW 1064 of the table.
I want to make this dynamic and independent.
Often, there is a need to add rows above tables, to point out something, to show SUM or AVERAGE or something else. We need to know how many rows are above our table.
I am fixing column C, DATE column, or column B, and I want to check where is the first number starting. Why number? Because sometimes we need to write text above table.
=ISNUMBER(C1:C20);0)
Setting this to the first 20 rows as a backup. It’s enough if I need to add more rows.
Excel evaluates row by row, and again I need first TRUE.
=MATCH(TRUE;ISNUMBER(C1:C20);0)
The result is 8. So, in the 8th row is the first number (date). From this we need to subtract 1, because above this first row with the number (date) is the table header.
And here we finished the second part of the problem, we know how to locate absolute row number where the first blank cell occurs. Independently of the number of rows above the table.
Here is our big formula:
=HYPERLINK("#"&RIGHT(CELL("filename";A1);LEN(CELL("filename";A1))-FIND("]";CELL("filename";A1)))&"!C"&MATCH(TRUE;ISBLANK(TableMaterials[DATE]);0)+MATCH(TRUE;ISNUMBER(C1:C20);0)-1;"Next entry")
Let’s try to make it more readable as real programmers do:
=HYPERLINK(
"#" &
RIGHT(
CELL(
"filename";
A1
);
LEN(
CELL(
"filename";
A1
)
) -
FIND(
"]";
CELL(
"filename";
A1
)
)
) & "!C" &
MATCH(
TRUE;
ISBLANK(
TableMaterials[DATE]
);
0
) +
MATCH(
TRUE;
ISNUMBER(
C1:C20
);
0
) - 1;
"Next entry"
)
RECAPITULATION:
Excel basic LINKing:
Realisation:
1st part:
2nd part:
3rd part:
4th part:
All together:
Final touches:
After clicking cell with a hyperlink, cell format changes, and It is becoming well known excel blue colour:
The next step is to create a new Hyperlink cell style to match the desired colour theme.
On the HOME tab, Styles card, Cell Styles option, right-click on Hyperlink option then Duplicate option. After that in the formating pop-up, we can customize every part of the cell style.
To sum up:
- We can always pull full file location
- We can extract the desired part of the location
- We can find the first blank cell in a table
- We can find blank cells above the table
With a great formula, combo using RIGHT, LEN, FIND, MATCH, ISNUMBER, ISBLANK and using boolean logic in them we assure dynamic hyperlinking while changing the name of the file, sheet and adding rows.
Link to excel file: Click on File, Save as, Download a Copy.
This is tested and working on Excel 365.
Great resources to follow up:
Hyperlinks
- https://www.contextures.com/excelhyperlinkfunction.html
- https://www.automateexcel.com/functions/hyperlink-formula-excel/
Excel formula beautifier
Screen video to gif
If you notice anything strange, incorrect or if you experience errors, please, feel free to leave a comment.
