Excel allows you to change the order of worksheets within a workbook at any time. There are two ways of achieving this, the first of which is simply to drag the tabs representing each worksheet left or right. As well dragging individual tabs, it is also possible to highlight several tabs and drag them all at the same time.
As well as moving worksheets around within the same workbook, it is also possible to move sheets from one workbook to another. For example, let's say we 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 for "Jan", "Feb" and "Mar"; the second for "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 these new workbooks will have one sheet, which is the minimum that Excel will permit. We can activate each of these new workbooks by clicking on the View Tab and using the Switch Windows drop-down menu. The first way of moving worksheets from one workbook to another is the drag and drop technique. To use this method, we will need to see all of the workbooks simultaneously. Excel has a special command for making this happen. In the View Tab, simply click on the Arrange All button and choose "Tiled". Excel will then display each of the workbooks in a small window, allowing us to view all of the open workbooks simultaneously.
The next step is to highlight the three worksheets relating to the first quarter: we click on "Jan" (the first), hold down the Shift key and click on "Mar" (the last). We can then drag the selected sheets across to the window of one of our new workbooks. We can the simply repeat this procedure for the three remaining quarters.
As was mentioned earlier, the minimum number of sheets which you can have in a workbook is one. Therefore, when we have moved the final three sheets, the window of the original workbook will simply disappear. Naturally though, the last saved version of the document will still exist.
The final step would be to delete the unwanted sheet from each of the four new workbooks. Having done this, to leave the split screen view and return to normal mode, we simply maximise any of the windows.
Just for reference, the second way of copying sheets from one workbook to another is to use the Move or Copy Sheets command. This can be found in the Format drop-down menu in the Cells section of the Home Tab or by right-clicking on the selected sheet tabs. As well as moving sheets, this method also allows you to create a copy at another location.
As well as moving worksheets around within the same workbook, it is also possible to move sheets from one workbook to another. For example, let's say we 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 for "Jan", "Feb" and "Mar"; the second for "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 these new workbooks will have one sheet, which is the minimum that Excel will permit. We can activate each of these new workbooks by clicking on the View Tab and using the Switch Windows drop-down menu. The first way of moving worksheets from one workbook to another is the drag and drop technique. To use this method, we will need to see all of the workbooks simultaneously. Excel has a special command for making this happen. In the View Tab, simply click on the Arrange All button and choose "Tiled". Excel will then display each of the workbooks in a small window, allowing us to view all of the open workbooks simultaneously.
The next step is to highlight the three worksheets relating to the first quarter: we click on "Jan" (the first), hold down the Shift key and click on "Mar" (the last). We can then drag the selected sheets across to the window of one of our new workbooks. We can the simply repeat this procedure for the three remaining quarters.
As was mentioned earlier, the minimum number of sheets which you can have in a workbook is one. Therefore, when we have moved the final three sheets, the window of the original workbook will simply disappear. Naturally though, the last saved version of the document will still exist.
The final step would be to delete the unwanted sheet from each of the four new workbooks. Having done this, to leave the split screen view and return to normal mode, we simply maximise any of the windows.
Just for reference, the second way of copying sheets from one workbook to another is to use the Move or Copy Sheets command. This can be found in the Format drop-down menu in the Cells section of the Home Tab or by right-clicking on the selected sheet tabs. As well as moving sheets, this method also allows you to create a copy at another location.
About the Author:
Author is a developer and trainer with Macresource Computer Solutions, a UK IT training company offering Microsoft Excel 2007 training courses in London and throughout the UK.
Posts
No comments:
Post a Comment