Easily select data using hyperlinks
The image above shows two hyperlinks, the first hyperlink lets you select a data set automatically based on a dynamic formula. The second hyperlink takes you to a specific Excel Table and automatically selecting all data as well.
The technique demonstrated in this article will be interesting for you if you often copy data sets or Excel defined tables. Adding more rows or columns to the data set makes no difference, both the first formula and the Excel defined Table formula are dynamic meaning they adapt when lists grow or shrink. This may be a time-saver for you making some actions less tedious.
Formula in cell B3:
Explaining formula in cell B3
I recommend that you use the "Evaluate Formula" tool built-in to Excel. Go to tab "Formulas" on the ribbon and press with left mouse button on the "Evaluate Formula" button, a dialog box appears. Press with mouse on the "Evaluate" button to see calculations step by step, see image above.
The underlined expression tells you which part of the formula that will be calculated in the next step if you press the "evaluate" button. The result is in italic making it easier for you to understand and learn how a formula works. Press with left mouse button on "Close" button to dismiss the dialog box.
Step 1 - Find last row in the data set
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), Sheet2!B1:B1000, 1)
becomes
MATCH("ÿ", Sheet2!B1:B1000, 1)
The MATCH function returns a number representing 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 lets you find the smallest value that is greater than or equal to the lookup value.
MATCH(lookup_value, lookup_array, [match_type])
The formula is 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. It is not required to have the column sorted in ascending order.
I recommend using multiple values concatenated like this CHAR(255)&CHAR(255) if you know that your lookup_array contains CHAR(255) .
MATCH("ÿ", Sheet2!B1:B1000, 1)
an returns 102.
Change cell range B1:B1000 if the data set has more than a thousand rows or if the data set location requires you to change the cell reference.
Step 2 - Find the rightmost column in the data set
The MATCH function returns the relative position of a given value in a cell range. It lets you choose how the function matches the value, use 1 in the third argument. It allows you to 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.
MATCH(CHAR(255), Sheet2!A2:CW2, 1)
returns 5.
This number represents the rightmost column in the data set. Change cell range A2:CW2 if the data set has more than a hundred columns.
Step 3 - Create cell reference
The ADDRESS function returns the address of a specific cell, based on a row and column number. We calculated the row and column numbers in steps 1 and 2.
ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])
[abs_num], [a1], and [sheet_text] are optional arguments that we don't need to use in this tutorial.
ADDRESS(MATCH(CHAR(255), Sheet2!B1:B1000, 1), MATCH(CHAR(255), Sheet2!A2:CW2, 1))
becomes
ADDRESS(102, 5)
returns cell reference $E$102.
Step 4 - Add file and sheet name
The HYPERLINK function requires the workbook name and worksheet name to operate properly. The ampersand character lets you concatenate two strings, in this case, the workbook name, the worksheet name, and the cell reference.
The workbook name must have a beginning and ending bracket and ther worksheet name must end with an exclamation mark.
"[Quickly select a data set or an excel defined table.xlsx]Sheet2!$B$2:"&ADDRESS(MATCH(CHAR(255), Sheet2!B1:B1000, 1), MATCH(CHAR(255), Sheet2!A2:CW2, 1))
becomes
"[Quickly select a data set or an excel defined table.xlsx]Sheet2!$B$2:"&$E$102)
becomes
[Quickly select a data set or an excel defined table.xlsx]Sheet2!$B$2:$E$102
Step 5 - 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])
HYPERLINK("[Quickly select a data set or an excel defined table.xlsx]Sheet2!$B$2:"&ADDRESS(MATCH(CHAR(255), Sheet2!B1:B1000, 1), MATCH(CHAR(255), Sheet2!A2:CW2,1)), "sheet2")
becomes
HYPERLINK([Quickly select a data set or an excel defined table.xlsx]Sheet2!$B$2:$E$102, "sheet2")
Formula in cell B4:
Select Excel defined Table using a hyperlink
The Excel Table is great a built-in tool that has many advantages. This tutorial demonstrates how Excel uses cell references to Excel Tables. They are called "structured references" and work differently than regular cell references.
A structure reference begins with the Excel Table name, press with left mouse button on any cell in the Excel Table. Press with mouse on tab "Table Design" on the ribbon, the tab appears if you have a cell in the Excel Table selected. You can find the Table name on the ribbon, it also allows you to change the Table name if you want to.
The name box shown in the image above lets you try different "structured references" and see what they do. The table below shows different "structured references", type them in the name box and check out what Excel selects.
For example, you will select data in the Excel Table if you only reference the Excel Table name. Structured references allow you to also use square brackets as well to further specify data you want to reference in the Excel Table.
| Structured reference | Description |
| Table1[#All] | Selects everything, headers, and data. |
| Table1 | Selects data. |
| Table1[[#All],[Country]] | Selects data and column header "Country". |
| Table1[Country] | Selects data in column Country |
The animated gif shows two hyperlinks, the first one selects a data set, the second one selects an excel defined table.
Recommended reading
- Excel Hyperlinks and Hyperlink Function (Contextures)
- HYPERLINK function (Office support)
- Get workbook name (Formula)
- Get worksheet name (Formula)
Hyperlinks category
The macro demonstrated above creates hyperlinks to all worksheets in the current worksheet. You will then be able to quickly […]
This article will demonstrate how to create a hyperlink that takes you to the first empty cell below data in […]
The macro in this blog post lists all hyperlinks in a worksheet. It also lists all words beginning with "http" […]
Excel categories
2 Responses to “Easily select data using hyperlinks”
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.
The file (Quickly select a data set or an excel defined table.xlsx) where you try to explain , not working.
Yoram,
The filename was wrong, windows seems to change blanks to - when ytansfering a file, like this:
Quickly select a data set or an excel defined table.xlsx
to
Quickly-select-a-data-set-or-an-excel-defined-table.xlsx
It works now, try it again.