There are many ways to work with filenames in Excel. You might need to make changes to the worksheet name or format, or want to name or rename other files using Excel. This piece will give you some guidelines and shortcuts for common tasks that involve Excel filenames.
Changing Excel Filename
If you need to rename your Excel file, there are several ways to do this. If the file is open, you can save a copy of it with a new name using the “Save As” function. But, you will still have the original, which you could delete once you close the file.
The best way to rename an Excel file is through File Explorer in Windows. Find the file, highlight it, Right-click and select Rename (or press F2 on your keyboard), and then type your new filename. Hit Enter.
Learn how to troubleshoot Excel filename issues.
How to Change an Excel File Extension
Excel allows for a variety of file extensions: XLS, XLSX, CSV, XML, HTML, XLTX, and XLTM. Which one you choose depends on the type of data you have and some other factors. But changing extensions is simple. You follow the same instructions as above (Changing Excel Filename).
In short, you can either save a copy with the new extension or rename the file through the File Explorer with your new extension. If your new extension is not compatible with your current data, Excel will give you an error message.
Setting Mime Type for Excel Filename
Microsoft Excel has the following observable MIME types:
- application/msexcel
- application/x-msexcel
- application/vnd.ms-excel (official)
- application/x-ms-excel
- application/xls
- application/x-xls
- application/xlsx
- application/x-excel
- application/x-dos_ms_excel
- application/vnd.opensmlformats-officedocument.spreadsheetml.sheet (xlsx)
While the standard MIME type for Excel is application/vnd.ms-excel, experts recommend that you also set the following header in the response:
header(‘Content-Disposition: attachment; filename=”name_of_excel_file.xls”‘);
Also, if the Excel file is in OpenXML SDK format, you’ll want to use the below MIME type:
vnd.openxmlformats-officedocument.spreadsheet.sheet
Get List of Filenames from a Folder in Excel
Assuming you need to extract a large list of filenames from folders, you can do this using Excel. However, this method only pulls filenames from a single folder and not subfolders.
There is an older function in Excel called FILES that still works when used in named ranges. Let’s assume you have a folder on your computer called “Baseball,” and you want to get a list of the filenames in this folder. Here are the steps to do this:
1. In any cell (let’s assume A1), enter the address of the folder on your computer, followed by an *. For example:
C:\Baseball\*
2. Go the Formulas tab in Excel, and click on “Define Name.”
3. In the Dialogue box that opens, choose any name, such as ListofFileNames, Scope: Workbook, and Refers to: =FILES(Sheet1!$A$1)
4. Now go to any cell where you want you filename list to start and enter this formula:
=IFERROR(INDEX(ListofFileNames,Row()-2),””)
5. Drag down this formula, and it will provide you with a list of all the filenames in that folder.
If you want to extract filenames of just a single extension, you can change the * to *xls* or *doc* or anything else.
For example, C:\Baseball\*xls*
Return Filename without Extension in Excel
If you need to insert your workbook’s filename somewhere in your spreadsheet, but want to omit the extension (such as xls or xlsm), you can accomplish this with two formulas. Both are rather long, but either will get the job done.
1. In any blank cell, you can enter the following formula to return just the filename (no extension):
=MID(CELL(“filename”,A1),SEARCH(“[“,CELL(“filename”,A1))+1,SEARCH(“.”,CELL(“filename”,A1))-1-SEARCH(“[“,CELL(“filename”,A1)))
Hit Enter.
2. Again, enter this formula in any blank cell and hit Enter to get the filename without the extension:
=TRIM(LEFT(SUBSTITUTE(MID(CELL(“filename”,A1),FIND(“[“,CELL(“filename”,A1))+1,255),”.xl”,REPT(” “,255)),255))
When Excel Filenames Are Hidden
If you have a list of filenames in Microsoft Explorer, it might be more helpful if you could see the entire filename. This doesn’t always happen. If Excel decides to “hide” them by inserting a “~$” before part of the filename, you’ll need to figure out whether to keep these files or not.
First, this generally occurs when your computer crashes unexpectedly. This is Office’s way of creating backup copies of certain files for you when the main file wasn’t closed properly. It can also act as a singleton lock, meaning there are other instances of the file that are opened by another user. If you do need the backup, you can open the file and then “Save As” the original filename that you want to keep.
In some cases, you can just delete these files because you have the originals that you need. But, if you want to hide these, you can do this in Microsoft Explorer settings. From the Control Panel, go to File Explorer Options, View, and select “Don’t show hidden files, folders, or drives.”
Create Links to Other Documents in an Excel File
When you have Excel and other Office filenames in a spreadsheet, you might want these to be clickable links. For example, you might want to refer to another Excel spreadsheet, presentation, or Word doc. This is simple enough to set up.
When you’re ready to do this, just click on the hyperlink button or select Insert – Hyperlink. A box will pop up, and you’ll want an “Existing File or Web Page,” and then you can browse to the location of your desired file using the file explorer button. Once you’ve found this, make sure that the “Text to Display” is correct for your link and then hit Ok.
This is a brief look at working with filenames in Excel, but there are sure to be more problem-specific issues that you might run across when dealing with a project or assignment. Whether you are just learning Excel or have run into a roadblock that you need help solving fast, Excelchat can provide lightning fast and friendly assistance.
Let us know how we can help. Your first session is always free.
Leave a Comment