Function Problems

Convert Excel Cell Addresses into Hyperlinks with Dynamic Array Formulas

Bushra ParveenBushra Parveen Nov 10, 2026 869 views

Question details

The user needs to convert a dynamic, spilled range of cell addresses into clickable internal hyperlinks.

Convert Excel Cell Addresses into Hyperlinks with Dynamic Array Formulas
Product
Spreadsheet
Device & OS
not provided
Scenario
Managing dynamic data arrays where a list of cell references generated by a formula needs to be transformed into active links for easy navigation.
Observed behavior
The goal is to flatten a dynamic array of text addresses and output them as functional internal hyperlinks pointing to specific locations within the same workbook.
Before you start

Ensure your spreadsheet software is up to date and supports modern dynamic array functions such as TOCOL and LET, and verify that the source text strings are valid cell references.

Solution 1Recommended

Use the LET, TOCOL, and HYPERLINK Formula Combination

Apply a nested dynamic array formula to flatten the range into a single column and prepend the '#' symbol required for internal workbook navigation.

The HYPERLINK function requires a '#' symbol before the address text to recognize it as an internal location within the same workbook.

By leveraging the TOCOL function, you can transform a 2D spilled array (like A1#) into a clean 1D column. The LET function simplifies the formula by defining this flattened array as a variable, making the formula easier to read and compute.

1
Select the destination cell

Click on the blank cell where you want the new column of hyperlinks to begin.

2
Enter the nested formula

Type the formula =LET(addresses, TOCOL(A1#), HYPERLINK("#"&addresses)). Replace A1# with the actual reference to your spilled array.

3
Apply and test

Press Enter. The results will dynamically spill down the column. Click on any of the generated links to verify it jumps to the correct cell address.

Use the LET, TOCOL, and HYPERLINK Formula Combination
Formula Breakdown: In this formula, 'addresses' acts as a variable storing the 1D array created by TOCOL. The HYPERLINK function then iterates through this array, automatically appending the '#' symbol to each address.
Advanced Spreadsheet Capabilities

Handle Dynamic Array Formulas Seamlessly in WPS Spreadsheet

WPS Office provides robust native support for modern dynamic array formulas, including LET, TOCOL, and HYPERLINK. You can effortlessly manage complex data workflows and array calculations with high efficiency.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your cell address text data.
  2. 2. Input the dynamic array formula: Select the target cell and enter =LET(addresses,TOCOL(A1#),HYPERLINK("#"&addresses)).
  3. 3. Execute the formula: Press Enter to let the array spill automatically, converting the text into clickable links.
  4. 4. Navigate the spreadsheet: Click the newly generated hyperlinks to instantly jump to the referenced cells across your workbook.
Seamlessly executes dynamic array formulas to create automated hyperlinksHighly compatible with Microsoft Excel file formats (.xlsx) and functionsFree, lightweight, and features a familiar tabbed user interface
microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'Reference isn't valid' error when clicking the hyperlink?

This error typically occurs if the '#' symbol is missing before the cell address in your HYPERLINK formula. It can also happen if the text refers to a worksheet that has been renamed or deleted.

Can I display a friendly, readable name instead of the raw cell address?

Yes. You can add a second argument to the HYPERLINK function to display a custom name. For example, use =LET(addresses,TOCOL(A1#),HYPERLINK("#"&addresses, "Jump to Cell")) to make all links display as 'Jump to Cell'.

Will TOCOL work properly if my spilled range contains empty cells?

By default, TOCOL includes blank cells and returns a 0 for them. To ignore empty cells in your dynamic array, you can add the ignore parameter to the function like this: TOCOL(A1#, 1).