Tips, Tricks, and Answers
The following articles are available for the 'Workbooks' topic. Click the article''s title (shown in bold) to see the associated article.
Accessing a Problem Shared Workbook
What are you to do is you share a workbook with others, and then suddenly the workbook won't open properly? Dealing with a corrupted workbook can be a bother, but there are a few things you can try.
If your worksheet is linked to data in other worksheets, you may need to change the link from time to time. Here's how to accomplish the task.
Closing a Read-Only Workbook
When you create a workbook that is read-only, users can still make changes to the workbook. When they exit, they are prompted to save their changes to another workbook (since yours is read-only). If you don't want users to be prompted to save their changes, you can use a simple macro that tricks Excel into thinking the workbook has already been saved.
Do you need to compare two workbooks to each other? While you can use specialized third-party software to do the comparisons, a simple formula or two might be all that you really need.
Creating a Workbook Clone
If you are afraid of messing up a workbook, consider doing your work on a clone of the workbook. Excel provides an easy way to create the clone.
Creating Default Formatting for Workbooks and Worksheets
Not satisfied with the way that default workbooks and worksheets look in Excel? You can easily create your own defaults for both elements.
Creating Individual Workbooks
Workbooks can contain many worksheets. If you want to pull a workbook apart and create a whole series of workbooks based on each worksheet, here’s the information you need.
Creating New Windows
If you need to look at different parts of the same worksheet at the same time, the answer is to create windows for your data. It is easy to do, as described in this tip.
Discovering Dependent Workbooks
When you starting linking information from one workbook to another, those workbooks become dependent on each other. Discovering which workbooks are dependent on the workbook you may have open can be difficult. Here's why.
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.
Excel Not Responding
Have you ever been working with data in Excel and experienced a "freeze" where the program stops responding? This can be frustrating. Fortunately, there are few things you can check out to solve the issue.
Forcing a Workbook to Close after Inactivity
Tired of your workbooks being left open on the screen where they can be seen by anyone passing by? Here's a way to have Excel automatically close a workbook after a given period of time.
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.
Noting the Workbook Creation Date
You may want to add, to your worksheet, the date on which a particular workbook was created. Excel doesn't provide a way to do this, but you can use one or two simple macros to insert the information you need.
Noting When a Workbook was Changed
Do you need to know when a workbook was last changed? There are a couple of ways you can go about keeping track of the change date, as discussed in this tip.
Open Workbooks Don't Display
Have you ever opened a workbook, only to have it not display your worksheet data? This can be very disconcerting, but it could be that your data is not really lost—just not visible.
Opening a Workbook as Read-Only
When you need to work on a workbook, you may want to do so without modifying the original contents of the workbook. This is where opening a workbook as read-only comes in handy.
Opening a Workbook to a Specific Worksheet
When you open a workbook, Excel displays the worksheet that was visible when the workbook was last saved. You may want, instead, for the workbook to always display a specific worksheet when it is opened. Here's how to do it.
Opening Multiple Workbooks at Once
Need to open a bunch of workbooks from within Excel? It's easy to do when you construct a selection set in the Open dialog box.
Opening Two Workbooks with the Same Name
If you have two workbooks that each have the same name, opening them at the same time in Excel could cause some problems. This tip examines why this is so and provides some ideas on how to get around the problems.
Printing Workbook Properties
Want to create a printed record of the properties associated with a workbook? There is no easy way to do it in Excel. Here's a macro, however, that you can use to create a worksheet that contains all your workbook properties.
Problems with Default Workbook and Worksheet Templates
You can create a default template for both your workbooks and worksheets. These should be placed in the XLSTART folder, but what if doing so causes problems when you start Excel?
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.
Remembering Commonly Used Workbooks
Want a quick way to access the workbooks you use most often? You can "pin" the workbooks so they can be opened with a click.
Remembering Workbook Position and Size
Want Excel to remember where your workbooks were located on the screen and then open them in the same position the next time you use them? This tip provides a few approaches that can help make this desire a reality.
Remembering Workbook Settings from Session to Session
You can spend a lot of time getting your workbook to look "just right." Wouldn't it be great if Excel was able to remember how you like your workbook to appear when you next open it? Here are a few ideas you can try.
Saving a Workbook in a Macro
Does your macro need to make sure that the workbook being processed is saved to disk? You can add the saving capability by using a single code line.
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.
Seeing a Worksheet Thumbnail in Windows
When you save a workbook, you have the opportunity to save a thumbnail image that can be displayed within Windows. Here's how to control the settings that handle the saving of the thumbnail image.
Seeing All Open Workbook Names
Ever want to see a list of all the workbooks that are open? If you open more than nine, Excel only displays the first nine without requiring you to go to a different menu to find the others. To make your job easier, this tip explains how to see every open workbook.
Sharing Your Workbook
Need to allow others to contribute to your Excel workbook? It's easy to do if you just share it. This tip provides an overview of how you can easily share with others.
Stopping a Workbook from Persistently Auto-Loading
Excel has the capability to automatically open workbooks when you first start the program. You may not want to have one (or more) of the workbooks opened, though. Here's how to find out all the settings that control what is being automatically loaded when Excel starts.
Strange Message about Others Making Changes in a Workbook
Have you ever tried to save a workbook, only to be notified that someone else has made changes in it? What if you are the only one with access to the workbook? This tip examines this scenario and presents some ideas on why it may be happening.
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.
Testing if a Workbook is Open
Your macros can easily open and manipulate other Excel workbooks. If a workbook you are trying to use is already in use by someone else, it may cause errors in your macro. Here is a quick discussion on how you can check to see if a workbook is already opened by someone else.
Turning Off Sharing
All good things must come to an end at some point. When you are done sharing your workbook with others, this is how you can turn off that feature.
Tying Workbooks Together
If you work with multiple workbooks at the same time, you might wonder how to tie them together so they open and close and the same time. Excel makes this easy. Here's how.
Using a Single Password for Multiple Workbooks
While password protecting a workbook does provide some security for the contents in the workbook, if you have several workbooks it can become cumbersome to access each file with a separate password. This tip explores the how you can get rid of some of the bother by using a single password for multiple workbooks.
Viewing Workbook Statistics
Excel keeps track of a range of stats about each workbook you use. If you want to take a look at those stats, it's easy; just display the Properties dialog box for the workbook.