More Topics

The following are additional topics related to the category listed above. A bracketed number after the topic indicates how many articles are related to that subject.

• AutoSave [1]
• MRU List [4]

Tips, Tricks, and Answers

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

Adding a File Path and Filename Do you need to add a files’ path and name to your workbook? There are a couple of ways you can go about adding the information, either to an existing cell or to a header/footer. This tip explains all the approaches you can use. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Aligning Cells when Importing from CSV When importing values from a CSV text file, you may want to include some sort of way to automatically align the information that is imported. Excel doesn’t provide such a feature, but you can create a macro that will do the alignment for you, based on indicators you include in the CSV file. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Appending to a Non-Document Text File Do you need to write information out to a text file so it can be used by other programs? You can easily accomplish this task using a macro, as described here. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Avoiding Scientific Notation on File Imports Excel can import some types of files automatically, and other types require some intervention on your part. If you are not getting the results you want through automatic imports, then you’ll appreciate the information in this tip, which explains how to force a manual import so you can get just want you need. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Can't Open Multiple Workbooks from the Desktop Do you want to open multiple workbooks, all at the same time, from your Windows desktop? The trick is in what you do with the workbooks once they are selected. This tip explains that the secret is in how you click. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Closing Multiple Files Do you work with lots of workbooks open at the same time? Excel provides a simple way to close all those workbooks at once, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Converting from SuperCalc The scrapheap of computing history is littered with programs that had their moment in the sun and then fell by the wayside. One such program is SuperCalc, a popular early spreadsheet program. This tip explains different ways you can retrieve data stored in old SuperCalc files. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Crashing when Searching Excel allows you to search for workbooks based on different criteria you specify. If your copy of Excel crashes when doing such searches, it will take some detective work—as outlined in this tip—to uncover and correct the problem. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Creating a CSV File Need to share information with another program? Chances are good that you can do it if you save your data in CSV (comma separated values) format. It’s easy to do, as this tip shows. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Delays in Saving Workbooks A few reasons why it may take awhile to save an Excel workbook. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Determining If a File Exists Macros can be used to access and manipulate data files on your hard drive. It is a good idea for the macro to determine if the file exists before actually trying to do anything with the file. This tip shows a way you can determine if the needed file is actually on the hard drive. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Determining the Length of a Text File When creating a macro to process a text file, you may need to know the length of that file. You can figure this out by using the LOF function, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Displaying Path Names in the Menu Bar Want to know the full path name for a particular workbook while you have it open? It’s easy to add a tool to your menu bar that will provide the desired information. This tip explains how. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Don’t Update Links to Other Programs Excel allows you to link to information stored in or created by other programs. Normally the links are updated automatically. This tip explains how to turn off the automatic link updating without modifying the individual link settings. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Duplicate Workbooks Opening If you ever open a workbook and always see two workbooks instead of one, chances are good the reason is because of the number of windows you’ve saved in the workbook. Here’s how to get back to only a single window being visible. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Error Opening Second Workbook, Take Two In addition to a previous tip, this tip will bypass the error message you receive when opening a second Excel workbook. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Faster Text File Conversions When importing a text file into Excel, you have the opportunity to define the characteristics of the data being imported. If the data has quite a few fields in it, specifying the characteristics of each field can be tedious. This tip explains a couple of ways you can make faster work of this importing process. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

File Format Not Valid Sometimes Excel can give some very cryptic errors. Such is the case with Bob, who ran into two “File Format is not Valid” errors. This tip discusses what could be a possible cause of the errors and how to fix them. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Finding the Parent Folder Do you need to know, in a formula, the name of the folder in which the current workbook is stored? This tip provides a formula you can use right away. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Full Path Names in Excel If you want to see the full path name for a particular workbook, you’ll like the macro in this tip. It allows you to display the path on Excel’s status bar. Assign the macro to a toolbar button, and your desired information is only a quick click away. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Getting Rid of "Copy of" When you open a read-only workbook, and then display the Save As dialog box, the words “Copy of” are added as a prefix to the suggested file name. This tip discusses how you can get rid of these added words so you can save quicker and more efficiently. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Getting Rid of Extra Quote Marks in Exported Text Files In an effort to be helpful, Excel may add extra quote marks around text that it exports to a text file. If you don’t like the format in which text is exported, then the solution is to create your own export capability with a macro, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

How Excel Treats Disk Files Excel doesn’t just limit you to opening workbook files. If you try to open a different type of file, Excel tries to load the data from the file and place it in a worksheet. This tip explains, in general terms, how Excel treats different disk files when you try to open them. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Importing Many Files Into Excel Excel includes import filters that allow you to load data from non-Excel formatted files into a worksheet. (The CSV import filter is an example.) These filters are designed to allow you to import one file at a time. If you have many files, you may wonder if you can set your import specifications once and have Excel process all the files. This tip answers this question. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Jumping Around Folders A handy tip to find your way back to a folder without traversing through all of the directory paths. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Making Changes in a Group of Workbooks Making changes in a workbook is easy. Making changes in hundreds of workbooks is not easy. This tip explains a variety of ways in which you can make quick work making changes to a group of workbooks. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Merging Many Workbooks Got a whole slew of workbooks that you need to merge together? You can do it manually, but it could take you all day. It’s much better to use a macro to do the merging, and you can be done in a few minutes. This tip explains how you can develop such a macro and how it could save you time. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Odd Behavior when Opening a Shared File with a Shortcut Troubleshooting Excel when a shortcut no longer works on just one computer. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Opening a Workbook as Read-Only Want to work on a workbook but not accidentally change the original workbook file? The best way to protect yourself is to open the workbook as read-only. You can then make all the changes you want and save them using a different name. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Opening a Workbook with Two Windows If you open a workbook and you notice that it shows two windows for the data, you may wonder how to get rid of one of those windows. Follow the steps in this tip, and you won’t be bothered by those extra windows any more. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Opening Non-Excel Files Using Excel to open non-Excel files. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Other "Phantom" Users Microsoft has a fix for an Excel 2002 glitch. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Problems Saving in Older File Format Saving files in an older Excel format may cause a few problems that need troubleshooting with this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Protecting an Entire Folder of Workbooks If you have a lot of Excel workbooks in a folder on your hard drive, you may wonder if there is a way to protect them all by simply protecting the folder. Excel doesn’t provide a way, but that doesn’t mean you can’t protect your work. This tip examines four different ways you can achieve the desired result. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Protecting Excel Files from Word Turning off the file converter to protect your Excel files. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Reducing File Size Adding macros to a workbook can make the file larger that contains that workbook. This tip explains why this happens and some ways you can make the workbook file smaller if you later remove the macros. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Reducing File Sizes for Workbooks with PivotTables Adding PivotTables to a workbook can balloon the size of the workbook file dramatically. There are ways to reduce the file size, and you should take advantage of them when creating the tables. This tip explains the different techniques you can use. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Saving in Multiple Locations Got a workbook that you need to save copies of in several locations? Going through the manual process of saving the same file in five locations can be tiresome. Doing the same thing using the macro in this tip can be a breeze. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Saving Information in a Text File When writing macros, VBA includes quite a few different commands you can use to manipulate text files. This tip shows how easy it is to use some of those commands to save information into a text file. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Saving Worksheets in Lotus 1-2-3 Format You can export an Excel worksheet in a variety of formats. One of those formats is Lotus 1-2-3, which is available as an export format on many versions of Excel. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Seeing Full File Names in the Files Menu The MRU (most recently used) file list shows the workbook files you’ve most recently worked with. If the file name is too long, then an abbreviated version of the file name is shown in the list. This tip explains how you can see the full path to the file, even if it is too long for listing fully in the MRU. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Selectively Importing Records Got a huge amount of data you need to import into Excel? Sometimes the best way is through the use of a macro, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

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. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Specifying a Delimiter when Saving a CSV File in a Macro When you create a CSV file from within Excel, you can do some things (like specify delimiters) that you cannot do from within a macro. The only way around this shortcoming is to change how you create the output file, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Speeding Up Opening and Saving a Workbook When workbooks get very large, opening and saving them can seem to take forever. Here are a number of ideas on how you can make your large workbooks open and save faster. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Stopping Date Parsing when Opening a CSV File You can double-click on a CSV (comma separated values) file in Windows, and Excel obediently opens and loads the file. You may not get the results you expect, however, as Excel may parse some of the values in the file incorrectly. In that case, you need to take greater control over how Excel interprets that data. Here’s how to do it. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Sudden Increases in Workbook File Size As you add information to a workbook, a little at a time, you’ll notice that the size of the workbook file slowly increases. If you notice a huge jump in the size of a workbook, you may be perplexed at what causes such a dramatic change. There are any number of reasons, as described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Trouble Opening Network Workbooks Troubleshooting the reasons why Excel would flag a workbook as "in use" when it hasn't been opened in weeks. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Unwanted Read-Only Workbook Status If you have a workbook on a floppy disk, you might run into a problem where the workbook suddenly becomes “read-only.” This is typically caused by removing the workbook’s disk prematurely from the disk drive. This tip discusses why removing early isn’t a good idea, plus how you can again make the workbook modifiable. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Use Filenames That Sort Properly How you name your workbook files can be an important decision. This tip explains one way you can name your files so that they sort in a desired manner when displayed in dialog boxes and in Windows. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Using Your Own File Extensions Excel uses the familiar XLS filename extension for its workbooks. If you want to use a different extension, you can use the file naming techniques described in this tip. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Who Has the File Open? If a workbook is shared on a network server, it is possible that someone could have it open and prohibit others from opening it. This tip discusses ways you can identify who has a particular workbook open. Microsoft Excel versions: 97 | 2000 | 2002 | 2003 | 2007

Working with Lotus 1-2-3 Spreadsheets Lotus 1-2-3, a spreadsheet program popular before Excel came on the scene, treats data in a worksheet differently than Excel does. This tip provides information on how you can configure Excel to better approximate the way that Lotus 1-2-3 handles data. Microsoft Excel versions: 97 | 2000 | 2002 | 2003

Helpful Links

Ask an Excel Question
Make a Comment

Tips.Net Home

ExcelTips FAQ
ExcelTips Premium

Learn Access Now

Beauty Tips
Bugs and Pests Tips
Car Tips
Cleaning Tips
College Tips
Cooking Tips
Excel2007 Tips
ExcelTips
Family Tips
Gardening Tips
Health Tips
Home Tips
Money Tips
Organizing Tips
Pet Tips
Word2007 Tips
WordTips

Advertise on the
ExcelTips Site

 

RSS Feeds

 Daily Nuggets (an ExcelTip every day)

 Allen Wyatt's ExcelTips (the weekly classic)