How to Keep Excel Hyperlinks Clickable in Power Query
Question details
The user needs to maintain clickable hyperlinks in their data after processing it through Power Query, as the links currently convert to unclickable plain text.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Loading a source data table containing hyperlinks into Power Query and outputting the processed data.
- Observed behavior
- Hyperlinks function correctly in the source table but lose their formatting and become plain display text after being processed and loaded by Power Query.
Ensure your source data uses the HYPERLINK formula rather than right-click 'Insert Link' formatting, as Power Query cannot read standard embedded cell link metadata.
Convert Hyperlink Formulas to Text Before Power Query Processing
Force the hyperlink to import as a text string into Power Query, then convert it back to a formula in the output worksheet.
Power Query reads raw data values and drops Excel cell formatting, including embedded hyperlinks. By forcing the HYPERLINK formula to act as text in the source data, it passes safely through Power Query. Once the data is loaded back into Excel, you can use the Find and Replace tool to turn the text back into a working formula.
In your source data table, locate the cells containing your =HYPERLINK() formulas. Add an apostrophe (') directly in front of the equals sign (e.g., '=HYPERLINK(...)) so Excel treats the formula as plain text.
Open the Data tab and click 'Refresh All' to load the new text strings through Power Query and into your output table.
In the final Power Query output table in Excel, select the entire column that contains the imported '=HYPERLINK text.
Press Ctrl + H to open the Find and Replace dialog. In the 'Find what' box, type '= (apostrophe and equals sign). In the 'Replace with' box, type = (just the equals sign). Click 'Replace All' to reactivate the clickable links.
Try WPS Office for Seamless Spreadsheet Management
Struggling with complex data queries and lost formatting in Excel? WPS Office offers a free, highly compatible, and lightweight alternative with robust spreadsheet features. Enjoy a familiar interface and comprehensive data analysis tools without the steep learning curve.
- 1. Download and Install WPS Office: Visit the official WPS Office website to download the free installation package for your operating system.
- 2. Open your Excel File: Launch WPS Spreadsheet and open your existing .xlsx files directly to continue working with zero compatibility issues.
Frequently Asked Questions
Why does Power Query remove my hyperlinks by default?
Power Query is designed strictly to extract, transform, and load raw data values. It does not import presentation layers such as font colors, borders, or embedded cell hyperlinks, leaving you with only the raw display text.
Can I create clickable hyperlinks directly inside the Power Query Editor?
No, Power Query's M code does not have a native function to generate clickable Excel links. You must output the URL strings and construct the HYPERLINK formula in the final Excel worksheet.
What if my source links were created using 'Insert Link' instead of a formula?
If your links were added via the standard 'Insert Link' feature, you will first need to extract the underlying URL using a VBA macro or Excel's FORMULATEXT function before importing the table into Power Query.
