How to Copy Excel Hyperlinks to Another Sheet Without Losing Links
Question details
The user wants to copy cells containing hyperlinks to another worksheet without losing the clickable link metadata.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Referencing cells across different worksheets where a simple cell reference fails to retain the hyperlink functionality.
- Observed behavior
- Using a standard cell reference formula like =Publishers!B12 transfers the text value but strips the hyperlink metadata, resulting in unclickable text.
Verify whether your source hyperlinks point to internal workbook locations or external web addresses, as this determines the exact syntax needed for your formula.
Use the HYPERLINK Function to Retain Clickable Links
Recreate the hyperlink manually using Excel's built-in function to pull both the link destination and the displayed text dynamically.
A standard cell reference cannot transfer hyperlink metadata. The most reliable method to mirror a hyperlink dynamically across sheets is to wrap your cell reference inside the HYPERLINK function.
Click on the cell in the new worksheet where you want the copied hyperlink to appear.
If linking to another sheet in the same workbook, type a formula like =HYPERLINK("#Publishers!B12", Publishers!B12). Replace 'Publishers!B12' with your actual source sheet name and cell reference.
If the original cell links to an external website, type =HYPERLINK("https://example.com", Publishers!B12).
Press Enter to execute the formula, then click and drag the fill handle at the bottom-right corner of the cell to copy the formula down the column.
Use a VBA Macro for Manually Inserted Hyperlinks
When dealing with various manually inserted links that a formula cannot retrieve, use a VBA script to copy the original Hyperlinks collection.
Easily Manage Hyperlinks and Formulas with WPS Spreadsheet
WPS Spreadsheet fully supports the HYPERLINK function and advanced cell referencing, allowing you to seamlessly manage linked data across multiple sheets. It offers a highly compatible and lightweight environment for all your data management needs.
- 1. Open your workbook: Launch WPS Spreadsheet and open the file containing your hyperlinks.
- 2. Select your target cell: Navigate to the new sheet and click the cell where the link should be displayed.
- 3. Apply the formula: Type =HYPERLINK("#SheetName!Cell", SheetName!Cell) and press Enter to generate the clickable link.
- 4. Fill the column: Drag the fill handle downward to copy the dynamic links to other rows.
Frequently Asked Questions
Why doesn't a standard copy-paste or cell reference keep my Excel hyperlink?
A standard dynamic cell reference (like =A1) only pulls the text or numerical value of the source cell. It intentionally ignores underlying metadata, cell formatting, and hyperlink properties.
Can I copy a range of hyperlinks without formulas?
Yes. If you simply use the standard Copy (Ctrl+C) and Paste (Ctrl+V) commands instead of referencing cells with an equals sign, the static text and its hyperlink will transfer properly to the new location.
How do I remove a hyperlink without deleting the text?
Right-click the cell containing the hyperlink and select 'Remove Hyperlink' from the context menu. This action eliminates the clickable link property while leaving your displayed text completely intact.
Does the HYPERLINK function update automatically if the original URL changes?
The displayed text will update automatically if it's referenced dynamically (e.g., Publishers!B12). However, the hardcoded destination string inside the HYPERLINK formula must be updated manually unless it points to a cell that contains the actual URL text.
