How to break Excel formula links—this feature uses the “Edit Links” command to break links between different Excel files. If you frequently use the VLOOKUP function, you can use this feature to convert linked data to values in one step.
How to Break VLOOKUP Function Links
Use the feature for breaking links between Excel files.
DATA > EDIT LINKS > BREAK LINKS
DATA > EDIT LINKS > BREAK
Steps for Editing External Data Links Between Excel Files
As shown below, there is an “A file” on the left and a “B file” on the right.
Use one of the multiple-criteria VLOOKUP methods to retrieve the values in column C of file A into column C of file B.
Learn About VLOOKUP with Multiple Criteria
The VLOOKUP formula used in column C of “B file” is shown below. (For details, please refer to the link above.)
C2 cell formula
= VLOOKUP(A2&B2,'[A file.xlsx]sheet1′!$C$1:$D$11,2,0)
After retrieving the desired values with the VLOOKUP function, use “Break Links” to convert the data to values as shown below.
Once all external data links in the Excel file have been removed, the “Edit Links” command is disabled.
Conclusion
We reviewed how to remove links from values retrieved from external files in Excel formulas and convert them to values all at once. Simply breaking the external data links after using VLOOKUP preserves the correct values and helps you manage accurate data. We recommend adding this to your frequently used Excel “keyboard shortcuts”.