Your Excel sheets are stored in files, so understanding how to maintain them is crucial. The following articles explore everything from the basics of file naming to problems you may run into with file conversion and importing. Organize your Excel sheets into manageable files on your computer easily with these tips.

Tips, Tricks, and Answers

The following articles are available for the 'Files' topic. Click the article''s title (shown in bold) to see the associated article.

   Adding a File Path and Filename
If you need to stuff the current workbook's filename and path into a cell or a header or footer, you'll appreciate the formulas in this tip. They are quick and easy to use.

   Aligning Cells when Importing from CSV
When you import information from a CSV text file, Excel formats the data according to its default settings. Wouldn't it be great if you could control alignment of the incoming data? Here's why you can't do it and how you can work around it.

   Appending to a Non-Excel Text File
Does your macro need to add information to the end of a text file? This is called appending, and is done using the technique presented in this tip.

   Avoiding Scientific Notation on File Imports
When importing information from a CSV file, you may get unintended results from time to time. Here's how to force Excel to ask how information should be interpreted.

   Can't Open Multiple Workbooks from the Desktop
Having trouble opening a group of workbooks selected on your desktop? The reason is probably due to Windows, not Excel.

   Cannot Double-Click to Open a Workbook
When you double-click on a workbook in Windows, the Excel program should be started and the workbook loaded. When this doesn't happen, it means something has gone wrong and you need to try to track down the problem.

   Changing Link References
If you use UNC paths in your links to external information, those paths may need to be changed at some point. You can easily do this by using the standard find and replace tools in Excel.

   Checking for the Existence of a File
The data stored in a worksheet can often correspond to information external to that worksheet. For instance, you might collect data that represents filenames in a directory somewhere. If you want Excel to check whether those collected filenames exist, it's easy to do using a simple macro.

   Closing Multiple Files
When working with multiple workbooks open, you may want a way to close them all with a single command. Here's the secret.

   Comma-Delimited and MS-DOS CSV Variations
Excel provides different file formats you can use to export your worksheet information. One such file format is CSV, or comma separated values. Excel supports several CSV formats, two of which are addressed in this tip.

   Comma-Delimited Differences for PC and Mac
When you choose to save worksheet data in CSV format, Excel gives you three choices for file formats. Those choices are detailed in this tip.

   Correctly Saving Delimited Files
Delimited files are often created through Excel so that your data can be exported to other programs. If the delimited data generated by Excel isn't exactly to your liking or expectation, there are ways that you can "adjust" the output to get what you want.

   Crashing when Searching
If you use Excel's Open dialog box to search for files and you notice that doing so ends up crashing your system, you may naturally wonder what the heck is going on. Here are some ideas to help you track down the root cause of the problem.

   Creating a CSV File
Need to get your data into a format that can be easily read by other programs? Chances are good that a simple CSV file will fit the bill. Here's how to create one from within Excel.

   CSV File Opens with Data in a Single Column
When you import a CSV file into an Excel worksheet, you may be surprised at how the program allocates the information among columns. This tip explains how Excel figures out what goes where.

   Determining If a File Exists
Before you have your macro open and read a file from disk, you'll want to check to make sure it is really there. Here's how to do it.

   Determining the Length of a Text File
When processing plain text files in a macro, it is often helpful to know how much data the file contains. The normal way to accomplish this task is through the use of the LOF function, described in this tip.

   Displaying Path Names in the Menu Bar
Want a quick way to see the full path name associated with a workbook? You can add a tool to the menu bar that displays the information dynamically.

   Don't Update Links to Other Programs
If you have links in your workbook to data in other workbooks, you may want to control whether Excel updates those links or not. Here's how to find the settings that Excel uses for that purpose.

   Error Opening Second Workbook
If you try to open a second workbook and you see an error message, it could be because of the way you are opening the workbook. This tip explains what the problem is and how you can overcome it.

   Extracting File Names from a Path
If you have a full path designation for the location of a file on your hard drive, you may want a way for Excel to pull just the file's name from that path. There are a number of ways you can accomplish this task, using both formulas and macros.

   Faster Text File Conversions
Want to make your importing of text data faster than ever? Here are some ideas you can apply right away.

   File Formats that Include Field Formats
If you import data into Excel that is created by other programs, you know that it can be bothersome to get your data formatted correctly. This tip discusses text-only file formats (such as CSV) and whether you can specify individual field formatting in the file.

   Finding the Parent Folder
Do you need to figure out the name of the parent folder of whatever folder a worksheet is in? Believe it or not, this can be done with a worksheet formula.

   Finding the Size of a Workbook
Keeping tabs on the size of a workbook can be important when using Excel. You have a couple of options that will allow you to find this information quickly.

   Full Path Names in Excel
Need to know what the full path name is for the current workbook? With a simple macro you can display the full path name in the title bar of the workbook's window.

   Getting Input from a Text File
You can use a macro to read information from a text file. The steps are easy, and then you can use that information in any way you see fit.

   Getting Rid of "Copy of"
When you save a read-only workbook file under a new name, Excel automatically adds "copy of" to the beginning of that name. Here's a way to get rid of that verbiage and automate how you save the file.

   Getting Rid of Empty Rows after Importing
Import data into a worksheet (or paste it there) and you may find that you end up with a group of blank cells you need to get rid of. Here are some handy ideas on getting rid of those extra rows.

   Getting Rid of Extra Quote Marks in Exported Text Files
If you don't like the way that Excel exports information you intend to use with other programs, then your best bet is to create your own export routine. This tip shows how easy this task can be.

   How Excel Treats Disk Files
Workbooks are loaded from disk files, but workbooks aren't the only type of files that Excel can load. This tip provides a quick overview on how Excel deals with files you try to load.

   Importing Huge Data Files
Sometimes, when importing data created by other programs, you may find that there is too much for Excel to handle. Here's how you can still accomplish the task.

   Importing Many Files Into Excel
Importing a single file is easy. Importing a whole slew of files can be much more of a challenge.

   Importing Multiple Files to a Single Workbook
If you use Excel to work with data exported from another program, you might be interested in a way to import a large number of text files into an Excel workbook. This tip explains how you can use a macro to make short work of the files you need to import.

   Inconsistent Output for Empty Columns in a CSV File
When you create a CSV file in Excel, the information stored in the file may not contain all the fields that you think it should. This tip examines the reasons behind this behavior, along with several different ways to work around the problem.

   Jumping Around Folders
When you open a workbook in Excel, the Open dialog box always starts within the folder in which you were last working. You can use this to your advantage when working in multiple folders, as illustrated in this tip.

   Loading Unwanted Files at Startup
Imagine how painful it would be if every time you started Excel it tried to load all the files in your root directory? That is what was happening to Stephen, and here's how to fix it.

   Locked File Puzzle
What would you do if every time you opened a workbook Excel told you it was locked? Here's how you can try to recover from this type of error.

   Making Changes in a Group of Workbooks
If you need to change the same data in a large number of workbooks, the task can be daunting. Here are some ideas (and some macros) that can help make the task more manageable.

   Merging Many Workbooks
If you need to combine the contents of a bunch of workbooks into a single workbook, the process can get tedious. Here's a handy macro that will quickly do the workbook amalgamation easily.

   Odd Behavior when Opening a Shared File with a Shortcut
Tracking down a problem that occurs with a single workbook can be vexing. One such problem occurred with Chris, and these are some ideas for him to try.

   Opening a Workbook with Two Windows
If you open a workbook and notice that Excel displays two windows for it, this has to do with how the workbook was saved. Here's how to get back to a single workbook.

   Opening Non-Excel Files
Not all data is created in Excel. Indeed, you may have data in files created by many other types of programs. You might be able to open those files in Excel; just try!

   Personal.xls File Not Opening
The Personal.xls workbook is used primarily to store macros that you want available through all of your workbooks. Personal.xls is normally opened automatically when you start Excel. What happens if it doesn't load, however? This tip explains how you can get control back over this important workbook.

   Protecting an Entire Folder of Workbooks
Want to protect the Excel information stored in a particular folder on your system? There are a number of ways you can approach the task, as described in this tip.

   Protecting Excel Files from Word
Office easily allows you to use Word to open an Excel document. Doing so, however, can quickly result in and unusable workbook. Here's how to protect your data.

   Pulling Filenames into a Worksheet
You can use Excel for all types of data processing. You may want to work with filenames in a worksheet, but the first task is getting those names into the worksheet. Here are a couple of ways you can get filenames, parsed according to a delimiter, into a worksheet.

   Reducing File Size
As you work with a workbook (particularly one that contains macros) you may notice that the workbook size can become quite large. Here are some ideas on how to reduce the file size to something more appropriate.

   Reducing File Sizes for Workbooks with PivotTables
Need to reduce the size of your workbooks that contain PivotTables? Here's something you can try to minimize the behind-the-scenes data that Excel uses for those PivotTables.

   Saving a Workbook with a Preview
When you save your workbooks, Excel can also save a preview image (thumbnail) that can be displayed in the Open dialog box. Here's how to configure Excel to save that preview.

   Saving All Open Workbooks
Wouldn't it be nice to have a single command that would save each of you open workbooks, all at once? It's easy to do with the short macro presented in this tip.

   Saving in Multiple Locations
Need to save a workbook in more than one location? Here's a handy macro that can save your workbook in lots of different places.

   Saving in Two Locations
When you save a workbook to disk, you may want to automatically save a duplicate workbook in a separate location. This can be easily done using a macro.

   Saving Information in a Text File
The VBA programming language provide with Excel allows you to create and modify text files quite easily. Here's how to open a file, write information into it, and then close the file.

   Seeing Full File Names in the Files Menu
Wouldn't it be great if you could look at the files in the MRU list and see the full path and file names? Excel condenses the information for a reason, but there is a way to figure out the full path, as described in this tip.

   Selectively Importing Records
Want to easily control which records get imported from a text file into Excel? It's easy to do when you write the macro that handles the importing.

   Setting a Default File Format
Excel normally saves workbooks using a default file format that is peculiar to your version of the program. You can configure Excel to save workbooks in an entirely different format by using the information in this tip.

   Setting the AutoRecover Directory
Excel, by default, periodically writes information to AutoRecover files that can help protect your data in case Excel is ended abnormally. You can specify where you want these AutoRecover files stored by using the information in this tip.

   Short-Lived Book1
If you have a problem that crops up when you first start Excel, it can be a bear to track down the cause of the problem. Here's a problem scenario and what you can do about it.

   Sorting Files
The Open dialog box allows you to sort the files it presents to you. How you do the sorting depends on the version of Word and Windows you are using.

   Specifying a Delimiter when Saving a CSV File in a Macro
You can, within a macro, save a workbook in several different file formats that are understood by Excel. However, you may not always be able to specify a full range of options in your saving. This tip examines one such situation and describes ways you can deal with it.

   Speeding Up Opening and Saving a Workbook
Use a workbook for some time and you may find that it gets progressively slower to open and save. Here are some techniques you can use to speed up the use of the workbook.

   Stopping Date Parsing when Opening a CSV File
Excel tries to make sense out of any data that you import from a non-Excel file. Sometimes this can have unwanted results. Here's how to make sure that Excel interprets the imported data in the way you expect.

   Stopping Excel from Converting UNC Paths to Mapped Drives
Did you know that if you create a link that uses a UNC path, Excel could rewrite that path to something entirely different? This can cause huge problems, unless you understand what is going on from Excel's perspective.

   Sudden Increases in Workbook File Size
Workbooks can get rather large rather quickly. If you think your workbook has gotten too big too fast, here are some things you can check.

   Unwanted Read-Only Workbook Status
Once a workbook assumes a "read-only" status, it can be a real pain to get that status removed. This tip explains why and explains what you can do about it.

   Use Filenames That Sort Properly
When storing your Excel workbook, you need to specify a file name to be used for the workbook. Take a moment to consider how the file will be used, and then use a name that sorts properly when displayed in a dialog box.

   Who Has the File Open?
Open a workbook that someone else is working on, and you won't be able to save your changes back into the same file. Wouldn't it be nice to figure out who has a particular file open? It's not as easy to figure out as you may want.

   Working with Lotus 1-2-3 Spreadsheets
If you've got some older data around your office that started in an old Lotus 1-2-3 system, you may want to open it in Excel. How Excel works with that older data depends on a couple of program settings you make.