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.

Finding the Directory Name

by Allen Wyatt
(last updated December 7, 2013)

6

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:

=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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Closing Documents after a Hyperlink

When you click a hyperlink that takes you to another document, Word dutifully opens the new document in its own window. What ...

Discover More

Opening a Recently Used Workbook

Excel provides a special tool that can help you locate and open workbooks you've worked with recently. Here's how to use the ...

Discover More

Opening a Workbook as Read-Only

When you need to work on a workbook, you may want to do so without modifying the original contents of the workbook. This is ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Non-adjusting References in Formulas

Sometimes making sure that a reference in a formula doesn't get changed is not as simple as putting dollar signs in front of ...

Discover More

Counting Cells with Specific Characters

Excel is used by many people to hold all sorts of data, not just numbers. If you have cells that include meaningful leading ...

Discover More

Combinations for Members in Meetings

Got a large group of people listed in a worksheet and you want to make sure that each person has met with every other person ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 + 0?

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

----


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.