Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. 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.
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 Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Changing Link References.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
When you establish links between data on a target worksheet and data on a source worksheet, those links are typically ...
Discover MoreIf you have a whole slew of hyperlinks in a worksheet and you want to get rid of them, it's easier than you think. This ...
Discover MoreNeed to change the various targets of a group of hyperlinks? Getting at the underlying link can seem challenging, but it ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-03-20 13:07:19
Lucio
Nice Tips,
but what if I open two Excel files on a mapped network drive in different folders and I link a Cell of one file to Cell of the second?
Excel, while the second file is open, will show the referense as: ='[FILE.xlxs]Sheet1'!A1 and when the second file is closed the reference became ='X:\folder\[FILE.xlxs]Sheet1'!A1 and not as ='\\server\folder\[FILE.xlxs]Sheet1'!A1
2019-05-29 10:32:09
Don
Excel 2010, tried find/replace, simply did not change a single link.
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 © 2020 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. 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