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

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.

Table of Contents hide

Download Practice Workbook

2 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

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

Conclusion

Download Practice Workbook

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

Find External Links.xlsm

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.

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

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.

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

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

Step 3:

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

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.

Step 5:

Pick a colour and click on OK.

Step 6:

Finally, press Enter on click OK.

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

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

Step 1:

Go to the Data.
Click on the Edit Links.

Step 2:

Then, click on the Break Link.

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

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.

Step 1:

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

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

Step 3:

Select all the cells in the range B5:E11.

Step 4:

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

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.

Step 6:

Pick a colour and click OK.

Step 7:

Finally, press Enter or click OK.

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

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.