Excel 2019 (Part 8): Working with Multiple Worksheets

In Microsoft Excel , each workbook contains at least one worksheet by default. When working with large amounts of data, you can create multiple worksheets to help organize your workbook and make searching for content easier. You can also group worksheets to quickly add information to multiple worksheets at once.

 

How to insert a new worksheet

1. Locate and select the New Sheet button near the bottom right corner of the Excel window.

2. A new blank spreadsheet will appear.

3. By default, any new workbook you create in Excel will contain one sheet, called Sheet1. To change the default number of sheets, navigate to Backstage view , click Options , and then select the desired number of sheets to include in each new workbook.

 

How to copy a spreadsheet

If you need to copy the contents of one worksheet to another, Excel allows you to copy the existing worksheet.

1. Right-click the worksheet you want to copy, then select Move or Copy from the worksheet menu.

2. The Move or Copy dialog box will appear. Select the position where the sheet will appear in the Before sheet: field. For example, select (move to end) to place the sheet to the right of the existing sheet.

3. Select the box next to Create a copy , then click OK.

4. The worksheet will be copied. It will have the same title as the original worksheet, as well as the version number. For example, the November worksheet has been copied, so the new worksheet will be named November (2). All content from the November worksheet has also been copied to the new worksheet.

 

You can also copy a worksheet to a completely different workbook. You can select any currently open workbook from the To book: drop-down menu.

How to rename a worksheet

1. Right-click the worksheet you want to rename, then select Rename from the worksheet menu.

2. Enter the desired name for the worksheet.

3. Click anywhere outside the worksheet tab or press Enter on your keyboard. The worksheet will be renamed.

How to move a worksheet

1. Click and drag the worksheet you want to move until a small black arrow appears above the desired location.

2. Release the mouse button. The worksheet will move.

 

How to change spreadsheet tab colors

1. Right-click on the desired worksheet tab and hover over the Color tab . The Color menu will appear.

2. Choose your desired color.

3. The spreadsheet tab color will be changed.

The tab color of a worksheet is significantly less noticeable when the worksheet is selected. Select a different worksheet to see how the color appears when the worksheet is not selected.

How to delete a worksheet

1. Right-click the worksheet you want to delete, then select Delete from the worksheet menu.

2. The worksheet will be deleted from your workbook.

If you want to prevent specific worksheets from being edited or deleted, you can protect them by right-clicking the desired worksheet and selecting Protect Sheet from the worksheet menu.

 

Switching between worksheets

If you want to view a different worksheet, you can simply click on the corresponding tab to switch to that worksheet. However, with larger workbooks, this can sometimes become tedious, as it may require scrolling through all the tabs to find the one you want. Instead, you can simply right-click on the scroll arrows in the bottom-left corner, as shown below.

A dialog box will appear with a list of all the worksheets in the workbook. You can then double-click the worksheet you want to go to.

Watch the video below to see how this shortcut works.

Group and ungroup worksheets

You can work with individual worksheets or multiple worksheets at once. Several worksheets can be grouped together. Any changes made to one worksheet in a group will be applied to all other worksheets in that group.

How to group worksheets

1. Select the first worksheet you want to include in the worksheet group.

2. Press and hold the Ctrl key on your keyboard. Select the next worksheet you want in the group.

3. Continue selecting until all the worksheets you want to group are selected, then release the Ctrl key. The worksheets are now grouped.

While the worksheets are grouped, you can navigate to any worksheet within the group. Any changes made to one worksheet will appear on all worksheets in the group.

How to ungroup worksheets

1. Right-click the sheet in the group, then select Ungroup Sheets from the sheet menu.

2. The worksheets will be ungrouped. You can also click on any worksheet that is not in the group to ungroup all worksheets.