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.


Download Practice Workbook

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


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

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
  • Then, select the Find

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

Read More: Excel Function: FIND vs SEARCH (A Comparative Analysis)


Similar Readings


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
'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

Read More: Find External Links in Excel (6 Quick Methods)


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.

The Exceldemy team will respond as quickly as possible.

Stay with us and keep learning.


Related Articles

Tags:

Bhubon Costa
Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo