Display the File Name & File Path in Excel (PC & Mac)

Many a times, it is good to Display the location of the file in Excel.

Not just the file name, but the entire path of the file. It is a good reference to make sure you are working on the correct file, and is also good if you print out the Excel. With this option, the file path always gets printed on paper..

How to Write a Formula that will Display the File Path in Excel, automatically. 

One  useful formula that can assist us is the CELL formula. I bet you didn’t know about this in-built Excel Function…

The CELL function can help us to display the address, color, format, type or width of any cell.

The CELL Function takes a maximum of 2 arguments, and only the first one is mandatory.

=CELL(info_type, [reference])

So, if you write =CELL(“filename”) in any cell, you will get the Full Path name of this Excel file, along with the Sheet Name.

For example, I got the filename displayed as below:

D:\[email-blasting-migration.xlsx]Sheet1

According to the Excel Help, the CELL function  has the following arguments:

  • info_type Required. A text value that specifies what type of cell information you want to return. The following list shows some of the possible values of the info_type argument and the corresponding results.
    info_type Returns
    “address” Reference of the first cell in reference, as text.
    “col” Column number of the cell in reference.
    “color” The value 1 if the cell is formatted in color for negative values; otherwise returns 0 (zero).
    “contents” Value of the upper-left cell in reference; not a formula.
    “filename” Filename (including full path) of the file that contains reference, as text. Returns empty text (“”) if the worksheet that contains reference has not yet been saved.
    “format” Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns “-” at the end of the text value if the cell is formatted in color for negative values. Returns “()” at the end of the text value if the cell is formatted with parentheses for positive or all values.

Display only the File Name, without the Sheet Name

To only print the File name, you can use this long formula:

=MID(CELL("filename"),SEARCH("[",CELL("filename"))+1, SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)

Keep in mind that the file should be saved somewhere. If you just open a new workbook, and try these formulas, you will get a #VALUE ERROR.  This error will disappear automatically once the file is saved, and the error will be replaced with the correct full file path name, and file name.

This formula to display the filename in Excel also works on the Mac. Any Excel for Mac version, including Excel X for Mac, to Excel 2004, 2008, 2011 for Mac also work fine in displaying the filename.

For example, for a file on Mac with the path HD:Users:local:Desktop, the formula returns
HD:Users:local:Desktop:(filename)Sheet1

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Displaying the FilePath only

While writing macros in Excel, you may need the file’s path only, to pick up other files, or for any other reason. Just use this to get only the file’s path in Excel

=LEFT(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1),1)-1)

In this formula, we remove the Sheet Name, which starts with the [ character.

Displaying File name, File path in Excel Worksheet Header or Footer

If you wish to display the file name and/or file path in the Excel sheet’s header or footer, then it is extremely simple. Simply go to the Ribbon. Choose Insert > Header & Footer

Excel File Name, File Path

All you need to do is the click on the File Path, File Name icons.

ALternatively, if you want to type it yourself, simply type the following in the header or the footer in Excel:

&[Path]&[File]

Use &[Path] for only the File Path, and &[File] for only the File’s name.

Hope it helps you in your life with Excel.

Do let me know if you need any other functions in Excel, or if you are facing a problem in Excel. We can help you to solve everyday issues in Excel easily at ExcelChamp.Net.

And subscribe to the Excel Tips Newsletter for more such tips to improve productivity in Excel.

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!


Cheers,
Vinai Prakash

Vinai is the founder and principal trainer for Microsoft Excel Tips and Techniques seminars all over the world. To book Vinai to conduct an Excel training at your office for your company, do email to vinai@excelchamp.net.

Counting Text Columns like Employee Names in Excel

Excel is great and summing things up. And then count the number of rows easily. You can watch the step by step video on how to use simple functions in Excel.

In fact, most versions of Excel can even show the SUM, COUNT of a block of cells, in the bottom status bar automatically.

However, there is an issue, if you want to count number of employees, or number of departments, or number of students…

Text values, by default can not be summed by the SUM function, and can not be COUNTED by the Count function in Excel

That’s because Excel is good at adding and counting numbers, and not Text. If you try, you’ll get a ZERO.

Fortunately, the workaround is pretty simple, straight forward and easy to use in Microsoft Excel. And it counts text values with ease.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


Instead of COUNT, use the COUNTA function.

So to count the number of employees, you need to say =COUNTA(A5:A15).

And this nifty function will count the number of employees promptly. COUNTA is to count Alphanumeric values – text, strings… or even numbers.

Try it out, and let me know if this helps you… 🙂

You may find these resources useful:

Cheers,
Vinai Prakash
Founder of ExcelChamp.Net

 P.S. – Are you an Expert in Excel 2010? Try solving this – find a way to find duplicates in Excel 2010 quickly. And post your solution. We are looking for simple and innovative ways to do so…

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!

Excel Tutorial – Combine Multiple Text Columns Into a Single Column

Some of the attendees at my Excel Boot Camp trainings have been thrilled to find that there’s this nifty function, which can combine many text columns into a single column.

Let’s say you have a column with First Name, and a separate column for Last Name, and you want to combine them to form the full name. How would you do it?

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by Joining the
Online Training on Basic / Intermediate Excel.

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!


I have recorded a short video on how to achieve this, using 2 different methods. Check it out below:

Let me know if this works for you. And if you have any other method to achieve this, I’ll be glad to know it…

And check out my other Microsoft Excel Tips & Tricks videos on YouTube.

Cheers,
Vinai

Are you facing any problem in using Excel? Any Question?

You have come to the right place. Tell us your needs. We’ll be glad to help you!