Function Problems

How to Create a Dynamic Excel Hyperlink to a PDF

Maira Mehtab Sep 22, 2026 868 views

Question details

The user wants to build an Excel HYPERLINK formula that dynamically opens a PDF file by extracting a file identifier, such as an order number, from a specific cell and appending it to a OneDrive folder path.

Product: Excel
Device & OS: not provided
Scenario: Creating automated, clickable links to local or OneDrive-synced PDF files based on changing spreadsheet cell data.
Observed behavior: The user needs the exact formula syntax to successfully concatenate a static directory path, a dynamic cell value, and the .pdf extension into a working clickable hyperlink.

Before you start

Ensure the target PDF files are saved in the intended directory and identify the exact absolute folder path from your file explorer before attempting to build the formula.

Solution 1Recommended

Use the HYPERLINK and TRIM Functions to Construct the URL

Use Excel's built-in HYPERLINK function alongside text concatenation (&) to dynamically combine a static folder path with a changing cell value.

When dealing with local or OneDrive-synced files on a Windows system, spaces in the folder path do not need URL encoding (such as %20). You simply enclose the static directory path in quotation marks and use the ampersand (&) to join it with your cell references.

Identify the Base File Path

Find the local path of your target folder in File Explorer. For example: C:\Users\Name\OneDrive\Folder\. Make sure to keep the trailing backslash at the end of the path.

Format the Formula Elements

Identify the cell containing your file identifier (e.g., cell E16). To prevent hidden spaces from breaking the link, wrap the cell reference in the TRIM function, making it TRIM(E16).

Enter the Complete Formula

Click the cell where you want the link to appear and enter the concatenated formula. Using the example path, it would look like this: =HYPERLINK("C:\Users\Name\OneDrive\Folder\"&TRIM(E16)&".pdf","Open PDF")

Test the Hyperlink

Press Enter to save the formula. The text "Open PDF" will appear as a clickable link. Click it to verify it correctly fetches and opens the corresponding PDF document.

Exact Matching Required: Confirm that the local folder path and file names match exactly. A missing backslash before the file name or a typo in the directory structure will result in a broken link.

Easily Manage Dynamic Hyperlinks with WPS Spreadsheet

WPS Spreadsheet fully supports advanced formula creation, including HYPERLINK, CONCATENATE, and TRIM, making it easy to build dynamic document references just as you would in Microsoft Excel.

1. Open WPS Spreadsheet: Launch WPS Office and open your spreadsheet document containing the order numbers.
2. Input the HYPERLINK Formula: Select your target cell and type your `=HYPERLINK(...)` formula with concatenation, applying the exact same syntax used in Excel.
3. Save for Cross-Platform Use: Save your document in .xlsx format to ensure the dynamic links remain fully functional when shared with colleagues using other spreadsheet software.

Fully compatible with Microsoft Excel (.xlsx, .xls) files and formulas.Process thousands of dynamic hyperlinks quickly without lag.Free to use with an intuitive, familiar user interface.

microsoft office alternative - wps office

Frequently Asked Questions

Why is my dynamic Excel hyperlink returning a 'Cannot open the specified file' error?

This error usually occurs if the concatenated file path is incorrect, the referenced cell contains unexpected spaces, or the target file does not exist in that specific directory. Using the TRIM() function around your cell reference (e.g., TRIM(E16)) ensures trailing spaces do not break the path.

Do I need to replace spaces in the file path with '%20' in the formula?

No. When linking to a local Windows file path (including a locally synced OneDrive folder), standard spaces function correctly. You only need to use '%20' or URL encoding when linking to web-based HTTP/HTTPS addresses.

Can I dynamically link to a PDF stored on a shared network drive?

Yes, you can replace the local C: drive path with your network's UNC path (e.g., \\ServerName\SharedFolder\). Just ensure that anyone clicking the hyperlink has the appropriate network permissions to access that drive.

Excel & Data Problems