So, hello Excellers, welcome my first #FormulaFriday #Excel blog post in my 2020 series. Welcome if you are new to my blog and welcome back if you are a regular reader. Today let’s look at a request I have had from a reader. The question is to insert the file path and name of the file in a cell on the front of a workbook (or the first sheet). There is a solution. I will use Excel CELL Formula.
This actually is a really useful piece of information, to know exactly where your file is saved either locally or in a network location. In particular when sharing the workbook with colleagues. I also use this a lot as would have a need to know if my files are locally stored or on a shared drive. I use multiple files and folders in shared and local locations all day long.
Displaying The Excel File Path And Name.
So, how do we easily get the file name and path? We can use the little-used (in my experience) CELL formula. The syntax of the CELL formula is
=CELL(info_type, [reference])
Where
info_type – This argument is required and is a text value that specifies what type of cell information you want to return. The following list shows the possible values of the Info_type argument and the corresponding results.
If you select the “format” value to be returned then the following then the values will be returned as follows:-
[reference] This optional. The cell that you want information about. If omitted, the information specified in the Info_type argument is returned for the last cell that was changed. If the reference argument is a range of cells, the CELL function returns the information for only the upper-left cell of the range.
The following list describes the text values that the CELL function returns when the Info_type argument is “format” and the reference argument is a cell that is formatted with a built-in number format
So in this example, we are using the info_type “filename” and type it into cell A1 of my workbook. It returns the full file path right down to the current worksheet. I have this workbook saved on my desktop and the current worksheet is call CELL FORMULA.
If you use CELL formula in a workbook that has not yet been saved then the formula returns a blank result ” “. Below is a new unsaved Excel workbook.
Now It’s Your Turn!
Have you ever used this function at all, do you think it would be useful?.
If you want more Excel and VBA tips then sign up to my Monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Formula Friday series click on the link below.
How To Excel At Excel – Formula Friday Blog Posts.
