Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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: Finding the Directory Name.
If you have a need to find out the directory in which your workbook is saved, you may be tempted to use a macro to figure out the answer. While this is a valid approach (and relatively easy), some people are intimidated by macros or don't want to use them within the workbooks. The following worksheet formula will return the directory in which the workbook is stored:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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: 
=LEFT(CELL("Filename",$A$1),FIND("[",CELL("Filename",$A$1))-1)
If you use this formula in a workbook that is brand new—one that has yet to be saved—then it will return a #VALUE! error. This happens because the filename has not yet been set, and the LEFT function cannot return a portion of something that is not there. To avoid the error, simply encase the formula in the IFERROR function, as follows:
IFERROR(LEFT(CELL("Filename",$A$1),FIND("[",CELL("Filename",$A$1))-1),"")
In this variation the CELL function is used to extract and return the directory name, but only if it has been set. If it has not been set, then an error occurs and IFERROR returns an empty string.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9455) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Finding the Directory Name.
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!
You can use the naming capabilities of Excel to name both ranges and formulas. Accessing that named information in a workbook ...
Discover MoreNeed a bit of help in figuring out how Excel is evaluating a particular formula? It's easy to figure out if you use the ...
Discover MoreWant to create a sequential pattern using formulas? It's easy to do if you take a look at how your data repeats. This tip ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2014-05-21 08:34:20
Natashia
Really Scott? Because I think it's kind of underhanded to do that. Flow has nothing to do with citing a worthy comment, as seen in scholarly articles anywhere. It sounds like that's how you meant it to be though, Allen. Especially since you deleted Bryan's comment showing the information he gave you. Your articles are informative, but I'm not sure I trust your credentials or morals if this is how you treat your tips.net comment section. I hope that articles I read here in the future are original or at least give credit where credit is due. It's not shameful to take constructive criticism and suggestions from others. People would respect you more for it in the end.
But that's all I'll say on the matter. Thank you for the tips and I hope to read more in the future.
2013-12-16 20:52:51
Scott Renz
Hi Allen,
I like the style of your articles and the way that they flow.
To me it would be annoying if you broke up that flow by pausing to give credit information.
I don't assume that everything you write all came out of your own head. I would pressume that you might use information people kindly added as comments to your
articles.
If I give such comments, I expect no credit for them. I am happy to see them passed along and being useful to people.
2013-12-10 18:42:41
awyatt
Wow. All I can say is wow.
Guess I'll write up a "terms of use" document, then. Never had a call for it in 16 years of doing this, but I guess I need to write one up now.
Sheesh.
Oh, and by the way: Your math is quite a bit off, on the high side. As in more than an order of magnitude. (I *wish* the reality were close to your numbers. I wish.)
-Allen
2013-12-10 15:47:54
Bryan
You have mentioned this once before, but that doesn't stop me from getting upset about it. I can't find any sort of policy that states that comments are yours to do with what you please, and you didn't ask if you could use the comment in the article, so I'm having a hard time seeing how it's not stolen. It was my idea that you are presenting as your own.
You may well have given credit in the premium newsletter, but all that means is only the people who paid 19-24 cents for this tip and happened to be subscribed this week got to see the attribution. Anyone who found the tip through the website, or who finds it in the future, will just see a clean slate article and have no way to know the entire thing isn't your idea.
The brass tax of the situation is this: I don't have any sort of legal case here -- IFERROR isn't exactly a novel concept and you can argue you would have come up with it eventually. And you might have. But the fact remains that since this was a premium tip, you sold my revision to other people, and I didn't get so much as a (public) mention. If conservatively 1% of your 43,000 subscribers are premium subscribers, then this tip netted you somewhere between $82 and $103. Considering you had 4 tips this week, surely ~$350 (plus advertising plus book sales) is enough to write a comment policy.
And, ironically, I'm not comfortable giving you my full name or email address. Don't need the bosses figuring out what I do at work all day :)
2013-12-09 13:53:39
awyatt
Bryan,
It wasn't stolen. I felt your comment was good enough to be incorporated into the actual tip. When I revisit tips for republishing them in the newsletters, I often do that. (We've had this discussion before, IIRC.)
Credit is always given *in the newsletter*. This particular tip ended up this week in ExcelTips Premium.
By the way, it would be very nice to have your full name (first and last) so that I can always credit you correctly. Please, if you don't mind, send it to me: allen-at-sharonparq.com.
-Allen
2013-12-09 12:15:36
Bryan
Credit goes to me for simplifying the second formula (in a comment since removed). I guess maybe I should stop giving advice if it's going to be stolen without recognition.
There were a couple other good recommendations as well:
----
Paul Kinnecom 17 Sep 2013, 01:14
If I want to know the directory where my spreadsheet is saved, I do this:
Click on the File tab (or Office Button)
Click on "Save As"
Read the directory path, and
Click on Cancel
----
Marc 13 Sep 2013, 14:14
You can also customize the Quick Access Toolbar adding the 'Document Location' to display the complete path of any excel you are working with. This way, you can copy the path from that window to quickly create a hyperlink in an email or anywhere else. Very handy.
----
Bryan 13 Sep 2013, 09:11
In Excel 2007 they introduced a new formula IFERROR. You can use it to reduce your second formula to =IFERROR(LEFT(CELL("Filename",$A$1),
FIND("[",CELL("Filename",$A$1))-1),"")
See also: http://excelribbon.tips.net/T011356_Adding_a_File_Path_and_Filename.html
----
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 © 2017 Sharon Parq Associates, Inc.
Comments