How to Find Duplicate Hyperlinks Using Excel Conditional Formatting & VBA
Question details
Identify and highlight duplicate hyperlink addresses in a spreadsheet, even when their display texts are different.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Managing a spreadsheet with multiple links where standard conditional formatting fails because it only compares display text, not the underlying URL.
- Observed behavior
- Standard conditional formatting ignores the actual hyperlink address and only checks visible text, making it impossible to find duplicate URLs using native formatting alone.
Ensure that your workbook is saved as a Macro-Enabled Workbook (.xlsm), as you will need to use a VBA script to extract the underlying hyperlink addresses before applying conditional formatting.
Extract and Highlight Duplicate Hyperlinks Using a VBA Helper Function
Create a custom VBA function to extract the actual URL from the hyperlink, then use conditional formatting to highlight duplicates based on the extracted URLs.
Since Excel's native conditional formatting cannot read the hidden URL of a hyperlink, we must first extract the URL into a helper column. A short VBA script makes this extraction automatic.
Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor. Go to Insert > Module in the top menu to create a new module.
Copy and paste the following code into the blank module window: Function ShowHyperlink(ByVal Where As Range) As String ShowHyperlink = Where.Hyperlinks(1).Address End Function. Close the VBA editor when done.
In an empty column adjacent to your hyperlinks, type the formula =ShowHyperlink(A2) (assuming your first hyperlink is in cell A2). Press Enter, then drag the fill handle down to extract all URLs in your list.
Select the new column containing the extracted URLs. Go to the Home tab, click Conditional Formatting, and choose New Rule.
Select 'Use a formula to determine which cells to format'. Enter a COUNTIF formula such as =COUNTIF($B:$B,$B2)>1 (assuming your helper column is B). Click Format to choose a fill color, then click OK to highlight all duplicate links.
Easily Manage Hyperlinks and Macros with WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros, custom functions, and advanced conditional formatting. You can extract and highlight duplicate hyperlinks seamlessly without altering your normal workflow.
- 1. Open Workbook: Open your macro-enabled workbook in WPS Spreadsheet.
- 2. Access VBA Editor: Go to the Developer tab and click on 'Macros' or press Alt + F11 to open the VBA Editor.
- 3. Add Function: Insert the ShowHyperlink function into a new module.
- 4. Use Helper Formula: Use the helper formula =ShowHyperlink() to pull URLs into a new column.
- 5. Format Duplicates: Navigate to Home > Conditional Formatting to set your COUNTIF rule for highlighting duplicates.
Frequently Asked Questions
Why doesn't standard conditional formatting find duplicate hyperlinks?
Standard conditional formatting in Excel only evaluates the visible text in a cell. If two identical URLs have different display text (e.g., 'Click Here' vs 'Website'), Excel treats them as unique values and will not flag them as duplicates.
Can I find duplicate hyperlinks without using VBA?
Without VBA, you would need to manually edit and copy each hyperlink address into a helper column, or use third-party add-ins. This is generally too time-consuming for large datasets, making the VBA helper function the most efficient approach.
What does the COUNTIF formula do in the conditional formatting rule?
The COUNTIF formula counts how many times a specific URL appears in the selected column. If the count is greater than 1, it means the URL is duplicated, and the rule applies the highlight formatting to those specific cells.
