Work with Worksheets

Delete Worksheet

Choose Home> Cells> Delete> Delete Sheet. If any cells in the selected sheet have data in them, a warning message appears.
A worksheet with no data in it will not display the warning message.
Click the Delete button.
Use caution when deleting worksheets. The Undo feature does not work with the Delete Sheet function.

To delete a bunch of worksheets from the workbook, select all the worksheets you want to delete and then click Home> Cells> Delete> Delete Sheet or press Alt+HDS or choose Delete from the tab's shortcut menu. Then, when you are sure that none of the worksheets will be missed, click the Delete button or press Enter when the alert dialog box appears.

If you find yourself constantly monkeying around with the number of worksheets in a workbook, either by adding a bunch of new worksheets or deleting all but one, you may want to think about changing the default number of worksheets in a workbook so that the next time you open a new workbook, you have a more realistic number of sheets on hand. To change the default number, click Office Button> Excel Options or press Alt+FI to open the Popular tab of the Excel Options dialog box and enter a new number between 1 and 255 in the Include This Many Sheets text box in the When Creating New Workbooks section of the tab or select a new number with the spinner buttons before you click OK.

Rename Worksheets

The sheet names that Excel comes up with for the tabs in a workbook (Sheet1 through Sheet3) are, to put it mildly, not very original and are certainly not descriptive of their function in life! You can easily rename a worksheet tab to whatever helps you remember what you put on the worksheet (provided that this descriptive name is no longer than 31 characters).

Click anywhere on the sheet you want to rename then choose Home> Cells> Format> Rename Sheet. The worksheet tab becomes highlighted. Leave it highlighted so that you can replace it with a new name.
Type a unique name for the worksheet. Remember that two worksheets in a single workbook cannot have the same exact name. Press Enter to accept the change. Be descriptive, but keep the name short. When you have lots of worksheets with long names, it can be more difficult to maneuver from one to the next.

Copy Worksheets

Click anywhere on the worksheet you want to duplicate then choose Home> Cells> Format> Move or Copy Sheet. The Move or Copy dialog box appears.
Check the Create a Copy box.
Select where in the order of the worksheets you want the duplicate sheet placed.
Click OK. Excel duplicates the sheet and gives it the same name as the copied sheet, plus numbers it sequentially.
To change the worksheet tab order, drag the worksheet tabs left or right.

Move or Copy Worksheets to a Different Workbook

Open the workbook to which you will move the worksheet then open the workbook that contains the worksheets you want to move.
Click anywhere on the worksheet you want to move. If you don't see the sheet you want, click the tab navigation buttons until you see it.
If you want to move or copy multiple worksheets, hold down the Ctrl key and click additional tabs. If you want to move or copy all the existing worksheets to another workbook, right-click a sheet tab and choose Select All Sheets. If you chose to copy a sheet to another workbook in which a sheet has the same name, Excel keeps the same name but adds a sequential number to the end. You cannot copy or move a worksheet created in an Excel 2007 workbook to one created in an earlier version of Excel.
Choose Home> Format> Move or Copy Sheet. The Move or Copy dialog box opens and then click the To Book drop-down list and select the workbook to which you want to move or copy the sheets.
If you want to duplicate the sheets to the other workbook, click the Create a Copy box.
Select where in the order of the existing worksheets you want the moved sheet placed then click OK. Excel moves or copies the worksheets to the other workbook.

Hide and Unhide Worksheets

Click anywhere on the worksheet that you want to hide and then choose Home> Cells> Format> Hide and Unhide> Hide Sheet. Excel hides the worksheet from view. All formula references to a hidden worksheet are still valid even when a worksheet is hidden. Optionally, right-click a worksheet tab and select Hide (or Unhide).
To unhide the worksheet, choose Choose Home> Cells> Format> Hide and Unhide> Unhide Sheet. A dialog box appears, listing all currently hidden worksheets in the active workbook and select the worksheet you want to unhide and click OK.

Change Worksheet Tab Colors

In Excel 2007, you can assign colors to the different worksheet tabs. This feature enables you to color-code different worksheets. For example, you could assign green to the tabs of those worksheets that need immediate checking and brown to the tabs of those sheets that you have already checked.

To recolor the tab of a worksheet, click anywhere in that worksheet.
Choose Home> Cells> Format> Tab Color. The Tab Color gallery appears and select a color. After you select a new color for a sheet tab, the name of the active sheet tab appears underlined in the color you just selected. When you make another sheet tab active, the entire tab takes on the assigned color (and the text of the tab name changes to white if the selected color is sufficiently dark that black lettering is impossible to read).

Select No Color to remove a tab color. When a worksheet with a colored tab is the current worksheet, Excel does not display the tab color in full. It only displays a colored line under the tab name. The tab becomes full color when the worksheet is not the active one.

Generate References to Other Worksheets

Select the cell into which you want to enter a reference and then perform one of the following actions:

To display a value located in another cell on the same worksheet, type the equal sign and then the cell address. For example, type =B45. If the value in B45 changes, the cell with the reference to B45 also changes.
To display a value located in a cell on a different worksheet but in the same workbook, type the equal sign. Next, click the worksheet tab containing the cell you want to reference and then click the actual cell you want to reference. Press the Enter key. Excel displays the equal sign, the worksheet name, an exclamation point, and the cell reference.
To include a cell located on a different worksheet but in the same workbook in a formula, begin to type the formula. In the place where you want to position the distant cell reference, click the worksheet containing the cell and then click the actual cell. Follow this with the remainder of the formula.

Formulas referencing other worksheets or other workbooks can also be compound formulas or used in a function.

Cross-Reference Other Workbooks

Open the workbook to which you will refer. For simplicity sake, call this Workbook2.
Click the desired cell in the workbook where you want to create a reference. Call this Workbook1. In Workbook1, begin the formula or reference with an equal sign.
If using a function or formula, enter any portion that you want to precede the cross reference and then click the cell that you want to reference from Workbook2.
Finish the remainder of the formula or press the Enter key. Excel displays the equal sign, an apostrophe, and then the Workbook2 filename in brackets followed by the worksheet name, a closing apostrophe, an exclamation point, and then, the absolute cell reference. For example, [Marketing.xls] July'!$E$10 refers to the value in cell E10 of the sheet January in the Excel file named Sales. Excel uses absolute references (with dollar signs) when referring to other workbooks.
When you open a workbook containing a cross reference, Excel displays a message prompting you with a security alert so it can determine whether to update the crossreferenced cell. Click Enable Contents if you want Excel to check the originating workbook for changes to the referenced cell. You may see another confirmation message.

[Previous] [Contents] [Next]