How to Find External Links in Conditional Formatting in Excel (2 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Assume your worksheet contains a large number of external links, and you now have to dynamically identify the cells that contain the links and references. In this tutorial, we will show you how to find external links in our worksheets and mark them with Conditional Formatting in Excel.


Find External Links in Conditional Formatting in Excel: 2 Effective Ways

In the image below, we have provided a data set containing an external link from another workbook ‘Previous Data’. Now, we will apply the Find & Select option to find the external links and then apply Conditional Formatting. Later on, we will run a VBA code to build a function to automatically marked by Conditional Formatting.

Effective Ways to Find External Links in Conditional Formatting in Excel

1. Apply the Find & Select Option to Find External Links and Results in Conditional Formatting in Excel

To apply the Find & Select option, follow the outlined procedures below.

Step 1:

  • Go to the Home
  • Click on the Find & Select drop-down list.
  • Then, select the Find option.

Effective Ways to Find External Links in Conditional Formatting in Excel

Step 2:

  • Type the file extension to find particular files. We type (.xl) to find the external excel files.
  • Then, click on the Find All.

Effective Ways to Find External Links in Conditional Formatting in Excel

  • Therefore, you will get all the external links that exist in your workbook.

Effective Ways to Find External Links in Conditional Formatting in Excel

  • Notice that, you will get the cell references that contain the links. Now, we will apply Conditional Formatting.

Effective Ways to Find External Links in Conditional Formatting in Excel

Step 3:

  • Firstly, click on the Conditional Formatting.
  • Then, select the New Rule.

Effective Ways to Find External Links in Conditional Formatting in Excel

Step 4:

  • Select the option Use a formula to determine which cells to format.
  • In the Format values where this formula is true box, type a cell with absolute reference that contains external links ($E$5).
  • Then, click on the Format option to select a colour.

Effective Ways to Find External Links in Conditional Formatting in Excel

Step 5:

  • Pick a colour and click on OK.

Effective Ways to Find External Links in Conditional Formatting in Excel

Step 6:

  • Finally, press Enter on click OK.

Effective Ways to Find External Links in Conditional Formatting in Excel

  • As a result, you will get the cell formatted with Conditional Formatting.

Effective Ways to Find External Links in Conditional Formatting in Excel

Notes.  For any reason, if you want to delete all the external links, follow the steps below.

Effective Ways to Find External Links in Conditional Formatting in Excel

Step 1:

  • Go to the Data.
  • Click on the Edit Links.

Effective Ways to Find External Links in Conditional Formatting in Excel

Step 2:

  • Then, click on the Break Link.

Effective Ways to Find External Links in Conditional Formatting in Excel

  • Therefore, all the links associated with the worksheet will be removed.

Effective Ways to Find External Links in Conditional Formatting in Excel


2. Run a VBA Code to Find External Links in Conditional Formatting in Excel

By applying a VBA code, we can automate the process. Follow the instructions below to do so.

Sample Data

Step 1:

  • Press Alt + F11 to open the VBA Macro.
  • From the Insert tab, select the Module.

Sample Data

Step 2:

  • Paste the following VBA code.
'Give your function a name (ExtraLink)
Function ExtraLink(Linked_Cell As Range) As Boolean
'Apply the If condition
If Linked_Cell.HasFormula Then
    ExtraLink = VBA.InStr(1, Linked_Cell.Formula, "[") > 0
End If
End Function
Sub Find_Link()
End Function

Sample Data

Step 3:

  • Select all the cells in the range B5:E11.

Sample Data

Step 4:

  • Then, click on the Conditional Formatting.
  • Select the New Rule.

Sample Data

Step 5:

  • At first, click on the Use a formula to determine which cells to format.
  • In the box, type the newly created function extralink.
  • Type the first cell reference (B5) of your range inside the extralink.
  • Finally, click on the Format to add colour.

Sample Data

Step 6:

  • Pick a colour and click OK.

Sample Data

Step 7:

  • Finally, press Enter or click OK.

Sample Data

  • Consequently, all the cells containing external links are highlighted with the desired color.

Sample Data


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


Conclusion

Finally, I hope you now know how to find external links in conditional formatting in Excel. All of these tactics should be performed while your data is being educated and practiced. Look over the practice book and put what you’ve learned to use. Because of your generous support, we are motivated to continue delivering initiatives like these. Please do not hesitate to contact us if you have any questions. Please let us know what you think in the comments area below.


Related Articles

<< Go Back to Conditional Formatting | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Bhubon Costa
Bhubon Costa

Bhubon Costa, B.Sc. in Naval Architecture & Marine Engineering from Bangladesh University of Engineering & Technology, has worked with the ExcelDemy since 2021. Currently, he has been working as a reviewer. Notably, he has written over 90 articles and led several VBA content development teams. He has a great passion for the fields of data analytics and data science. His areas of expertise include Excel VBA, Power Query, Pivot Table, Power BI, MySQL, PostgreSQL, machine learning, and Python... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo