Excel permits you to change the order of worksheets within a workbook at any time. There are two ways of doing this, the first of which is simply to drag the tabs that represent each worksheet. As well dragging a single tab, it is also possible to highlight several tabs and drag them all at the same time.
Not only can we move worksheets around within the same workbook, it is also possible to move worksheets from one workbook to another. For example, suppose you have a workbook containing a worksheet for each month of the year ("Jan", "Feb", etc.) and that we now want to split this into four smaller workbooks, one for each quarter: the first containing "Jan", "Feb" and "Mar"; the second containing "Apr", "May" and "Jun"; and so forth.
To keep the number of sheets we will end up with in each workbook to a minimum, we could begin by reducing the default number of worksheets Excel will give us in each new workbook. To do this, we click on the Office Button and then choose Excel Options. In the section labelled "When creating new workbooks Include This Many Sheets", we reduce the number to one. We can now create four sheets by clicking four times on the new sheet icon on the Quick Access Toolbar.
Each of our new workbooks has one sheet, which is the minimum that Excel will allow. We can access these new workbooks by clicking on the View Tab and accessing the Switch Windows drop-down menu. The first method of moving worksheets from one workbook to another is to drag and drop. To do this, we will need to see all the workbooks simultaneously. Excel has a special command for doing this. In the View Tab, click on the Arrange All button and choose "Tiled". Excel will then present each of the workbooks in a miniature window, allowing us to see all of the open workbooks simultaneously.
The next step would be to select the three sheets pertaining to the first quarter: we click on "Jan", hold down the Shift key and click on "Mar". We can then drag the selected sheets across to the window of any of the new workbooks. We can then repeat this exact same procedure for the other quarters.
As we saw earlier, one is the minimum number of sheets which you can have in a workbook. Therefore, when we have moved the last three sheets from the original workbook, it will have no worksheets left and will therefore simply disappear. Naturally, however, the last saved version of the Excel document will still exist on disk.
The final step would be to delete the unwanted (original) sheet from each of our four new workbooks. Having done this, to exit the split screen view and return to normal mode, we simply click on the maximise button in any of the tiled windows.
As a matter of interest, the second way of copying sheets from one workbook to another is to use the Move or Copy Sheets command. This is available in the Format drop-down menu in the Cells section of the Home Tab or by right-clicking on the selected sheet tabs. As the name implies, as well is moving sheets, this method allows you to create a copy at another location.
Not only can we move worksheets around within the same workbook, it is also possible to move worksheets from one workbook to another. For example, suppose you have a workbook containing a worksheet for each month of the year ("Jan", "Feb", etc.) and that we now want to split this into four smaller workbooks, one for each quarter: the first containing "Jan", "Feb" and "Mar"; the second containing "Apr", "May" and "Jun"; and so forth.
To keep the number of sheets we will end up with in each workbook to a minimum, we could begin by reducing the default number of worksheets Excel will give us in each new workbook. To do this, we click on the Office Button and then choose Excel Options. In the section labelled "When creating new workbooks Include This Many Sheets", we reduce the number to one. We can now create four sheets by clicking four times on the new sheet icon on the Quick Access Toolbar.
Each of our new workbooks has one sheet, which is the minimum that Excel will allow. We can access these new workbooks by clicking on the View Tab and accessing the Switch Windows drop-down menu. The first method of moving worksheets from one workbook to another is to drag and drop. To do this, we will need to see all the workbooks simultaneously. Excel has a special command for doing this. In the View Tab, click on the Arrange All button and choose "Tiled". Excel will then present each of the workbooks in a miniature window, allowing us to see all of the open workbooks simultaneously.
The next step would be to select the three sheets pertaining to the first quarter: we click on "Jan", hold down the Shift key and click on "Mar". We can then drag the selected sheets across to the window of any of the new workbooks. We can then repeat this exact same procedure for the other quarters.
As we saw earlier, one is the minimum number of sheets which you can have in a workbook. Therefore, when we have moved the last three sheets from the original workbook, it will have no worksheets left and will therefore simply disappear. Naturally, however, the last saved version of the Excel document will still exist on disk.
The final step would be to delete the unwanted (original) sheet from each of our four new workbooks. Having done this, to exit the split screen view and return to normal mode, we simply click on the maximise button in any of the tiled windows.
As a matter of interest, the second way of copying sheets from one workbook to another is to use the Move or Copy Sheets command. This is available in the Format drop-down menu in the Cells section of the Home Tab or by right-clicking on the selected sheet tabs. As the name implies, as well is moving sheets, this method allows you to create a copy at another location.
About the Author:
The author is a training consultant with Macresource Computer Solutions, a UK IT training company offering Microsoft Excel 2007 Classes at their central London training centre.
No comments:
Post a Comment