Manipulating sheets in Excel

You've seen those little tabs at the bottom of the Excel spreadsheet screen – Sheet1, Sheet2, Sheet3 – how would you like to learn some tricks for manipulating those sheets and making them work for you?!

When you open a new Excel workbook file, Sheet1 is the active spreadsheet. The active sheet is the one that is displayed on your screen. You can also tell which sheet is active by looking at the Sheet tabs at the bottom of the screen. The tab that is white is the active sheet.

Move from one sheet to another by clicking on the various sheet tabs. Clicking on the Sheet2 tab places Sheet2 on the top of your pile of sheets and turns the Sheet2 tab white.

Switch rapidly between sheets by either clicking on the sheet tabs at the bottom left of your Excel screen or by pressing CONTROL PAGE DOWN to advance to the next, higher=numbered sheet and CONTROL PAGE UP to go to the prior, lower-numbered sheet.


NOW PLAYING:
Manipulating Sheets In Excel

or right-click HERE to download
audio file for mp3 players
Need help?

Visit archives here

If you have more than a few sheets in your workbook, you can quickly display your sheet tabs by clicking the arrow keys at the left of your sheet tabs. The arrow on the left displays the leftmost sheet tab, the second arrow displays tabs to the left, one sheet tab at a time, the third arrow displays tabs to the right, one sheet tab at a time, and the rightmost arrow displays the rightmost sheet tab. Note that clicking on these arrows changes the display of sheet tabs at the bottom of the screen, but it doesn't change the active sheet, the one that is displayed on top of your screen.

You can right-click on these little arrows and choose the particular sheet you want to display from the pop-up menu that appears. Choosing a sheet in this manner changes the active sheet to the one you have chosen.

By default, Excel provides you with three sheet tabs on each new workbook (old-timer Excel users will remember when the program started you out with 16 sheets in a workbook!). You are entitled to increase the number of sheets in your workbook. In Excel 2007, the only limit to the number of sheets you can add to your workbook is a limitation based on the memory in your computer. You can choose to increase the number of sheets in a particular workbook, or you can choose to increase the default number of sheets in all workbooks.

Using Excel 2007, add one sheet to your current workbook by choosing Home, Insert, Insert Sheet. A new sheet will appear before the current worksheet, numbered chronologically with the next highest number available. For example, if there are three sheets in your workbook, and Sheet2 is the active sheet, choosing Insert Sheet will add a Sheet4, and the new sheet will appear between sheets 1 and 2. Alternatively, click the Insert Worksheet button that appears to the right of the sheet tabs, or press Shift F11 to quickly insert a new worksheet.

You can add multiple sheets to your workbook (up to the number of sheets already in existence in your workbook) in one easy step. Hold down SHIFT while you click on as many worksheet tabs as you wish to add new worksheets, and then choose Home, Insert, Insert Sheet. The same number of sheets on which you clicked will be added. For example, if you wish to add three sheets to your workbook and Sheet1 is the active sheet, hold down the Shift key and click the Sheet3 tab, selecting the tabs for Sheets 1 through 3. When you choose Insert Sheet, three new sheets will be added. The new sheets will be inserted to the left of the active sheet.

If you find you are frequently increasing the number of sheets in your workbooks, consider changing the default number from three to something larger. Adjust the number of sheets that will appear in new workbooks by clicking the Office button in the top left corner of the Excel 2007 screen, then click the Excel Options button. On the Popular screen that appears, in the section labeled When creating new workbooks, change the option to Include this many sheets from 3 to any number up to 255, keeping in mind that, although you can add as many sheets as you like to your workbook, 255 is the highest number of sheets you can use when opening new workbooks. Click OK to save your changes. From this point forward, each time you open a new workbook you will see tabs for the newly designated number of sheets.

It's easy for the sheet tab numbers to get out of chronological order when you are adding new sheets. You can rearrange your sheets by dragging the sheet tabs to the location where they belong. For example, if your sheets are numbered from the left, Sheet1, Sheet4, Sheet2, Sheet3, and you wish to present them in ascending order, drag the Sheet4 tab to the right of Sheet3. A tiny arrow appears above the tabs, indicating the location where the sheet will appear when you release your mouse button.

The dragging method works well if you don't have a lot of sheets in your workbook. When the quantity of sheets gets large, it is sometimes easier to move a sheet to a new location by using the menu options for relocating. Select the sheet you want to move by clicking on the sheet tab. Right-click, and pick Move or Copy from the pop-up menu. In the window that appears, click on the sheet before which you want to move the selected sheet, then click OK. Your sheet will move to its new location, with all of its data intact.

Sometimes you can overdo it and add too many sheets to a workbook. No harm is done if you leave the extra sheets in the file, but if you're pressed for memory, you might want to remove the empty sheets because they do take up room in your file. You might also want to remove the sheets so that others who might look at your workbook won't get confused. To remove an unwanted sheet, right-click on the tab of the sheet you don't need, and choose Delete. If the sheet is empty, it will be removed immediately. If there is data on the sheet, you will be asked if you're sure you want to remove the sheet and the data that is on it.

Warning! Deleting a sheet from a workbook is not an undoable procedure! You cannot click the Undo button to reinstate the sheet, so be certain you no longer want this sheet before deleting it. The only way to recover a deleted sheet is if the file was saved previously with this sheet intact. If you have not resaved the file since you deleted the sheet, you can close the file without saving, then reopen the file. The previously saved version of the file will appear.

You can remove several sheets at once by holding down the Shift key and clicking the first and last sheet tab to select a group of adjacent sheets, or holding down the Control key and clicking on each sheet tab you want to delete, then right-clicking and choosing Delete.


Already a member? log in here.

Editor's Choice