My Excel 2016 (2016)
3. Working with Sheets
In this chapter, you’ll discover what you can do with sheets, including the following:
→ Inserting additional sheets
→ Deleting sheets
→ Moving sheets to other workbooks
→ Selecting multiple sheets
A sheet, also known as a spreadsheet or worksheet, is where you enter all your data in Excel. A workbook can have multiple sheets—the number is limited only by the power of the computer opening the workbook.
Each sheet has a tab, visible above the status bar. The sheet with data that you’re looking at is considered the active sheet. To select another sheet, click its tab.
Adding and Deleting Sheets
You can add and delete sheets as needed, allowing you to separate your data, lookup tables, and reports on different sheets.
Add a New Sheet
By default, a new workbook has a single sheet, but you aren’t limited to using just that one sheet. When you add a new sheet, it becomes the active sheet.
1. Click the New Sheet icon to the right of the rightmost sheet tab. This inserts a new sheet to the right of the active sheet.
Delete a Sheet
If you no longer need a sheet, you can delete it, thus getting it out of your way and reducing the size of your workbook.
1. Right-click the sheet tab you want to delete and select Delete.
2. If data is on the sheet, a prompt appears to verify that this is the action you want to take. Click Delete to continue with the deletion.
It’s Not All Good: Deletion Is Permanent!
Be very careful when deleting a sheet because the Undo function will not work to recover it. Once you delete a sheet, it is gone.
Navigating and Selecting Sheets
Excel has a few tools to help navigate between sheets, which is useful if your workbook has a lot of sheets. And sometimes you might need to select multiple sheets at the same time—for example, if you want to print multiple sheets together.
Activate Another Sheet
To activate another sheet, click its tab along the bottom of the Excel window. You can also navigate from sheet to sheet using the keyboard. Ctrl+Page Up selects the sheet to the left; Ctrl+Page Down selects the sheet to the right.
If you have more sheets than can fit in the tab area, an ellipsis (three small dots) will appear on the left and right sides of the area, to show there are more sheets available to view in that direction. Clicking the three dots jumps you to the sheet closest to the dots. If you need to scroll one sheet at a time, click the left or right arrow located to the left of the tab area.
To scroll to the leftmost sheet, hold down Ctrl while clicking the left arrow. To scroll to the rightmost sheet, hold down Ctrl while clicking the right arrow. Note that scrolling the tab area doesn’t activate a different sheet; it just makes different sheet tabs visible.
If you right-click the left or right arrow instead, a list of all visible sheets in the workbook appears. You can then select a sheet, click OK, and you’ll go straight to that sheet.
Select Multiple Sheets
You can select multiple sheets at one time. This doesn’t activate them all at once. Instead, it groups them together so that an action on one, such as changing the tab color or typing in a cell, affects all of them.
To select multiple sheets, select the first one; then, while holding down the Ctrl key, select the tabs for the others. You can also hold down the Shift key and click on the first tab you want and the last one in a set, and Excel will select the entire contiguous range for you. As you select each sheet tab, the sheet name will become bold.
To ungroup the sheets, you can either select any sheet not in the group or right-click any tab and select Ungroup Sheets.
Moving or Copying Sheets
You might want to reorganize sheets in the current workbook to place similar sheets together. Or you might need to copy a sheet to run tests on the data but don’t want to change the original.
Move or Copy a Sheet in the Same Workbook
Copying or moving a sheet duplicates everything on the sheet, including formatting, links, and charts.
1. Right-click the sheet tab you want to move or copy.
2. Select Move or Copy.
Alternative Method
You can also open the Move or Copy dialog box by going to the Home tab, opening the Format drop-down and selecting Move or Copy Sheet.
3. Make sure the To Book field is the name of the current workbook.
4. If you’re making a copy of the sheet, select the Create a Copy box.
5. Select the sheet you want the active sheet to be placed in front of (that is, to the left of). You can also just move it to the end by selecting (Move to End).
6. Click OK.
Do It Quicker
You can also reorganize the sheets in a workbook by holding the mouse button down and dragging a sheet’s tab to a new location. If you want to copy the sheet, hold down the Ctrl key as you drag the sheet tab.
Move or Copy a Sheet Between Workbooks
Many database programs will export data to an Excel workbook or compatible file, but might not let you choose an existing workbook to export to. You can move sheets from one workbook to another. This is also useful when you need to combine the content from two or more workbooks.
1. Make sure the workbook into which you want to move or copy the sheet is open. Alternatively, you can transfer the sheet to a new, blank workbook by choosing it in step 5.
2. Right-click the sheet tab you want to move or copy.
3. Select Move or Copy.
4. Select the workbook to which you want to move or copy the sheet.
5. If you’re making a copy of the sheet, select the Create a Copy box.
6. Select the sheet you want the moved or copied sheet to be placed in front of (that is, to the left of) in the other workbook. You can place it at the end by selecting (Move to End).
7. Click OK.
Linked Formulas
When you move or copy a sheet from one workbook to another, any formulas on that sheet linked to another sheet in the original workbook will remain linked to the sheet in the original workbook unless you also include the linked sheet(s) in the move or copy procedure.
Sheet Names Are Unique
Sheet names must be unique. If you copy or move a sheet into a workbook that already has a sheet by that name, Excel will append a number to the end of the copied sheet’s name. For example, if you copy (or move) Sheet1 and there is already a Sheet1, Excel renames the new sheet to Sheet1 (2).
Renaming a Sheet
Excel’s sheet names (Sheet1, Sheet2, and so on) aren’t very descriptive. Sheets are easier to find when they have meaningful names.
Change a Sheet’s Name
1. Right-click the sheet tab you want to rename.
2. Select Rename.
3. Type a new name and press Enter.
Sheet Name Limitations
A sheet name is limited to 31 characters, and you can’t use some symbols, such as asterisks and slashes. Also, the renaming of a sheet cannot be undone.