by Allen Wyatt
(last updated December 17, 2016)
Becky recently updated to Excel 2016. In previous versions of the program she could display the File tab of the ribbon and then click Info to see the complete path and filename of the workbook. This was very useful for copying the information to send to a co-worker. In Excel 2016 the information isn't there any more. She wonders if there is another place to get this useful information with a single copy/paste.
Actually, the information is there, but Microsoft (as it is wont to do) changed how you access the information. Plus, they changed the precise nature of what is returned. Here's how to do it:
Figure 1. Where Excel 2016 stores location information.
If you are using a worksheet that is brand new (it hasn't been saved yet), then the above steps won't work because there is no file name or location to note.
When you paste the information from the Clipboard, you may be in for a little bit of a surprise. What you'll find is that the location is formatted as a URL. For instance, here is what I found when I copied the location of a workbook (Master Email List.xlsx) stored on my desktop:
You can, of course, remove the encoding characters in the URL and the protocol information to render a full path to the workbook. There are other ways to get the full path, however.
One alternative is to use a formula in a cell to get the desired information. Provided the workbook has been saved, this will return the desired information:
When I used this in a cell on a worksheet named "Original," here is what was returned:
C:\Users\Administrator\Desktop\[Master Email List.xlsx]Original
Obviously this returns a bit more information than just the full path name, so you'll need to (again) make some changes to the information.
Another alternative is to try this little trick:
You can then paste the location wherever you want, but note that it is simply the path to the folder containing the workbook; it does not include the workbook name.
Another alternative is to add a tool to the Quick Access Toolbar:
Figure 2. The Quick Access Toolbar area of the Excel Options dialog box.
Now, your QAT includes a text box that contains the full path for the current workbook. You can click once in the text box, press Ctrl+A to select the path, and press Ctrl+C to copy it to the Clipboard.
There is one caveat to using the Document Location tool. If you save a new workbook or use Save As to save the current workbook to a new name, you'll need to make an application other than Excel active for a moment (just click the other application's window to give it focus) and then come back to Excel. This causes Excel to update whatever is in the Document Location text box to the correct location.
Finally, if you are comfortable with using the Visual Basic Editor, you could follow these steps:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13504) applies to Microsoft Excel 2016.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Need to get your data into a format that can be easily read by other programs? Chances are good that a simple CSV file ...Discover More
When processing plain text files in a macro, it is often helpful to know how much data the file contains. The normal way ...Discover More
Keeping tabs on the size of a workbook can be important when using Excel. You have a couple of options that will allow ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
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.