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.
Automatically Hiding the Personal Workbook
If you leave your Personal.xls workbook visible from one Excel session to another, you may find that you unwittingly make changes you never intended to make. Here's some ideas on how to protect the workbook or at least stop yourself from making those unwanted changes.
Calculating Only the Active Workbook
When you enter information into a workbook, Excel automatically recalculates every worksheet in every open workbook on your system. If you want to limit what is recalculated, you'll need some of the techniques described in this tip.
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.
Closing All Open Workbooks
Excel provides a handy (but little-known) shortcut for closing all the workbooks you have open. This tip explains how easy it is to use this shortcut.
Combining Worksheets from Many Workbooks
Do you need to pull a particular worksheet out of a group of workbooks and combine those worksheets into a different workbook? You can make the task a breeze with the macros presented in this tip.
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 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.
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.
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.
Hanging When Opening a Workbook
If you are opening a workbook and Excel seems to hang without ever fully loading, it could be due to a number of different causes. This tip provides some things you can check to get at your data.
Invalid Names when Opening Workbook
Don't you hate opening a workbook and seeing error messages? If you see a message that some "invalid names" were detected in your workbook, this tip provides some ideas on how you can track down and solve the problem.
Limiting Who Can Delete Data
Excel allows you to protect your worksheet data in several different ways. Unfortunately, it doesn't allow you to protect the data for some people but not for others. There are some ways you can approach a solution to such a problem, as described in this tip.
Losing Data in a Shared Workbook
When you create a shared workbook, you run the risk of losing some of the data in that workbook. Here's a discussion about why this occurs and what you can do about it.
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.
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 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 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 a List of Named Ranges
You already know that you can define names that apply to different ranges of cells and other elements such as formulas. Here's how to print out a reference list of those ranges.
Printing Workbooks in a Folder
This tip presents two techniques you can use to print multiple workbooks all at the same time. Both techniques involve creating selection sets and then acting on those sets.
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?
Renaming a Workbook
Renaming a workbook from within Excel can seem daunting, but it is actually quite easy. All you need to do is use the Open dialog box as described in this tip.
Saving Changes in the Personal Workbook
The Personal workbook is a special place used to store information and macros that you can access from all the other workbooks on which you work. If you are confused as to why Excel continually asks whether you want to save this special workbook, here are some ideas on why it may be happening.
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.
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.
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.
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.
Updating Links in Copied Files
When you copy workbooks that contain links, you may be at a loss as to how to update those links. There are a couple of ways you can approach the task, as discussed in this tip.
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.
Working With Multiple Workbooks
Need to do work in more than one workbook at a time? For many, this ability is a necessity. Excel allows you to easily work with multiple workbooks, as described in this tip.