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

Serial for cs2 photoshop mac. “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.

Read More, general data analysis How to Use an Excel Pivot Table for Data Analysis How to Use an Excel Pivot Table for Data Analysis The pivot table is one of the single most powerful tools in the Excel 2013 repertoire. It is frequently used for large data analysis.

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. Mac app store update for el capitan. 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 To Get Most out of Excel, Learn the 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.