In Excel, you can organize multiple worksheets into what is called a workbook. Eventually, your workbooks can become quite large and managing them may be confusing. Check out the following articles to tackle any task involving your workbooks in Excel.
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.
Comparing Workbooks for Differences
When working with copies of workbooks--particularly copies derived from a common ancestor workbook--you may be interested in determining what differences exist between those workbooks. This tip provides plenty of ways that you can make that determination.
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.
Creating Workbooks for Individual Worksheets
If you have a workbook with lots of worksheets, you may want those worksheets to be saved off in individual workbooks. Doing it manually is easy, unless you have lots of those worksheets. Here are some ideas on how to make the workbook saving more automatic.
Determining If a Workbook is Referenced by Another Workbook
It is a difficult task, in Excel, to determine if a workbook is the target of any external links. This tip examines some ways you might be able to track down those external links.
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.
Incorrect In-Use Message
If you try to open a workbook that someone else has open, Excel lets you know of the conflict. What if Excel tells you, though, that you are the one with the workbook open, and you know you aren't? Here's what may be going on.
Limiting Where a Workbook is Used
Want to limit who can use your workbook and on what system? Locking down a workbook can be trickier than you might imagine.
Links between Two Desktop Workbooks
If you have formulas that reference values in an external workbook and that workbook is closed, Excel may generate errors. This can be frustrating, especially since Excel is not consistent in how such external references are handled.
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.
Making Data Universally Accessible to Workbooks
If you are using Excel as a repository for data used in your business, you may want to figure out a way to make that information available to other Excel workbooks. This tip provides a high-level look at what is necessary to make that a reality.
Making Ribbon Customizations Apply to All Workbooks
Excel allows you to easily make changes to the ribbon, as well as to the Quick Access Toolbar, which is near the ribbon. If you want your customizations to be available whenever you are using Excel (regardless of the workbook on which you are working), there are a couple of things you need to check when doing your customizations.
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.
Needing to Save a Workbook Twice
If you save a workbook and then immediately close the workbook, Excel may ask you to save again. This tip looks at possible reasons why this request to double-save a workbook may occur.
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.
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 at Once
Do you work with a group of workbooks all the time in Excel? Windows and Excel both provide a plethora of ways you can open both workbooks at the same time.
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 your 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.
Shrinking Workbook Size
As you work with a workbook over time, it is possible for the workbook to grow to a huge size. If you want to shrink the size of your workbooks, you'll appreciate the ideas discussed in this tip.
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.
Stopping Excel from Deleting Macros from a Workbook
When working with very large workbooks, it is possible for Excel to behave erratically. This tip looks at ways you can protect your macros in very large workbooks.
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 at the same time. Excel makes this easy. Here's how.
Undeleting a Worksheet
Have you ever deleted a worksheet by mistake? Once you get over the sick feeling in your stomach, you will start casting about for ways to undelete the worksheet. Excel (and Windows) provide different ways you can get back the information you need.
Unresponsive Excel when Double-Clicking a Workbook
There are two ways you can load a workbook in Excel, and each approach should result in the workbook being loaded relatively quickly. If double-clicking a workbook within Windows results in much slower opening of the workbook, the ideas in this tip may be helpful.
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 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.
When Excel Starts, Nothing Shows
When you start up Excel, you should eventually see a blank workbook, ready for you to work with. If you don't, if you see nothing, then you've got some configuration sleuthing to do to correct the situation.
Who Has the Workbook Open?
When you are working with workbooks to which multiple people have access, it can be helpful to know who has a particular workbook open. Excel should inform you of this automatically, but if it doesn't you'll appreciate the ideas presented in this tip.
Workbook Loses All Conditional Formatting
Workbooks created in very old versions of Excel can, at times, have issues when opened in later versions of the program. This tip looks at a couple of ways that you can bring the workbook out of the past and into a format that can be used with more recent versions.
Workbook not Saving
Having trouble saving a workbook? It could have to do with the age, size, and complexity of that workbook. This tip offers some ideas of how you can handle the situation.
Workbooks Opening as Read-Only
If Excel thinks a workbook should not be changed, there is a good chance it will be opened as read-only. This tip looks at why that read-only status might occur and what you can do about it.