Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Changing Link References.
Written by Allen Wyatt (last updated November 20, 2021)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and 2021
An ExcelTips reader wrote about a problem their company was having with links in their workbooks. It seems that their links were showing up as UNC paths to files instead of as mapped drive letters. This initially posed no problem, but when the files were moved to a new network server in preparation for changing the network configuration, the UNC paths no longer worked.
Fortunately, Excel has a very powerful search and replace feature. It is so powerful, it even works on OLE links in your worksheet. For instance, let's say your links in some cells looked like this:
='\\proserver\Excel\mystuff\[WordTips.xlsx]Sheet1!B25
If the server name is changing or the data is being moved to a different server, this can present a problem. And, unfortunately, the Edit Links tool (On the Data tab of the ribbon) is a real pain at times. You can quickly change all UNC references in the worksheet by searching for the UNC path (in this case, "\\proserver\Excel\mystuff\") and replacing it with your desired mapped drive letter (for instance, "q:\"). The result is links that all of a sudden point to the proper location.
There is one "gottcha" to be aware of. The UNC path is very specific, and will work regardless of what computer is connected to a network. However, it is possible for different computers to have different drive letters mapped for the same network destination. For instance, one workstation might have \\proserver\Excel\ mapped to drive S:, and another workstation may have it mapped to T:. If you change all the links to S:, then the workstations that don't use drive S: for mapping to that location will not be able to access the file.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10519) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and 2021. You can find a version of this tip for the older menu interface of Excel here: Changing Link References.
Dive Deep into Macros! Make Excel do things you thought were impossible, discover techniques you won't find anywhere else, and create powerful automated reports. Bill Jelen and Tracy Syrstad help you instantly visualize information to make it actionable. You’ll find step-by-step instructions, real-world case studies, and 50 workbooks packed with examples and solutions. Check out Microsoft Excel 2019 VBA and Macros today!
If you need to modify the URL used in a large number of hyperlinks, you can do so by using a macro and a little ...
Discover MoreCopying information from one place to another in a worksheet is easy. Copying hyperlinks may not seem that easy, but you ...
Discover MoreNeed to create links to many filenames on your system? Here are a couple of quick ways to get just the connection you need.
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-11-22 11:34:49
J. Woolley
You might also be interested in My Excel Toolbox's ListExLinks array function. The result of ListExLinks is similar to the Edit Links dialog minus the Update column, but ListExLinks adds a Reference column to identify cells that contain a formula referencing the link.
See https://sites.google.com/view/MyExcelToolbox/
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and 2021. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here:
Comments