Bhubon Costa
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.
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.
Read More: Excel Function: FIND vs SEARCH (A Comparative Analysis)
Similar Readings
- FIND Function Not Working in Excel (4 Reasons with Solutions)
- How to Use Formula to Find Bold Text in Excel
- [Solved!] CTRL+F Not Working in Excel (5 Fixes)
- [Fixed]: Can’t Find Project or Library Error in Excel (3 Solutions)
- How to Find * Character Not as Wildcard in Excel (2 Methods)
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.
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
- Excel Search for Text in Range (11 Quick Methods)
- How to Find If Cell Contains Specific Text in Excel
- How to Find from Right in Excel (6 Methods)
- Find Last Value in Column Greater than Zero in Excel (2 Easy Formulas)
- How to Find Lowest 3 Values in Excel (5 Easy Methods)
- Find First Occurrence of a Value in a Range in Excel (3 Ways)
