←Module 3
Lesson

Working with Multiple Worksheets

Learn how to insert, rename, move, copy, group, and organize worksheets within a workbook.

By the end of this lesson you can

  • Add, rename, reorder, color, and delete worksheets
  • Reference a cell on another sheet in a formula
  • Group sheets to edit several at once
  • Structure a workbook so data, calculations, and output are separated

Video

Watch the lesson video, then complete the reading and challenge.

Lesson Notes

Read through the key concepts before you try the challenge.

One sheet per purpose

On the job

You rebuild the supply tracker at Lakeside Medical Associates.

The current workbook has everything on one sheet: raw orders, a summary, notes, and three abandoned attempts at a chart, side by side. Nobody can tell which region is authoritative, and every sort risks scrambling something unrelated.

Your task: Separate the workbook into sheets with distinct jobs, so each region has one purpose.

A durable convention is three kinds of sheet: raw data that is only ever added to, calculations that read from it, and output that people actually look at. Keeping them apart means a change to a formula cannot corrupt source data, and a person reading the summary never has to scroll past 900 rows to find it.

Cross-sheet references use the sheet name and an exclamation mark: =Data!C7, or ='Q3 Orders'!C7 when the name contains a space. Rather than typing them, start the formula, then click the other sheet's tab and the cell — Excel writes the reference correctly, including quoting names that need it.

Grouping sheets — clicking one tab, then Ctrl+clicking others — applies every edit to all of them at once. It is efficient for building twelve identical monthly sheets and dangerous afterwards, because it is easy to forget the group is active and overwrite eleven sheets with one change. Right-click and Ungroup Sheets the moment you are finished, and check the title bar, which says [Group] while grouping is on.
Right-click a tab to set a Tab Color. Coloring input sheets one color and output sheets another gives anyone opening the workbook an immediate sense of its structure, without a word of documentation.

Introduction

Every workbook contains at least one worksheet by default. When working with large amounts of data, you can create multiple worksheets to better organize your information and make it easier to manage.

You can also group worksheets together to make changes to multiple sheets at the same time.

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 worksheet will appear.
New worksheet button
New worksheet inserted

Copy a Worksheet

Excel allows you to duplicate an existing worksheet without copying and pasting content manually.

  1. Right-click the worksheet you want to copy.
  2. Select Move or Copy.
  3. Choose where the sheet should appear.
  4. Check Create a copy and click OK.
Right-click move or copy
Move or copy dialog box
Copied worksheet

Rename a Worksheet

  1. Right-click the worksheet tab.
  2. Select Rename.
  3. Type the desired name and press Enter.
Rename worksheet option
Worksheet renamed

Move a Worksheet

  1. Click and drag the worksheet tab.
  2. Release when the black arrow appears in the desired location.
Drag worksheet tab
Worksheet moved

Change Worksheet Tab Color

  1. Right-click the worksheet tab.
  2. Hover over Tab Color.
  3. Select the desired color.
Tab color menu

Delete a Worksheet

  1. Right-click the worksheet tab.
  2. Select Delete.
Delete worksheet option
Delete worksheet done

Switching Between Worksheets

You can click worksheet tabs to switch between sheets. For large workbooks, right-click the scroll arrows in the lower-left corner to see a list of all worksheets.

Worksheet navigation list
Worksheet navigation list

Grouping and Ungrouping Worksheets

Grouping worksheets allows you to make changes to multiple sheets at the same time.

  1. Hold the Ctrl key.
  2. Click each worksheet tab you want to group.
  3. Release Ctrl once selected.
Grouped worksheets selected
Grouped worksheets active

To ungroup worksheets, right-click any grouped tab and select Ungroup Sheets.

Ungroup sheets option

Knowledge Check

Check your understanding

How do you rename a worksheet tab in Excel?

Practice File

Download this file and follow along with the lesson.

Multiple Worksheets Practice File

Challenge

Apply what you've learned in this lesson.

  1. Insert a new worksheet and rename it Q1 Summary.
  2. Move the Expenses Summary worksheet to the far right.
  3. Move the Q1 Summary worksheet so it appears between March and April.
  4. Create a copy of the Expenses Summary worksheet using Move or Copy.
  5. Change the January tab color to blue and the February tab color to red.
  6. Group the worksheets September, October, and November.

When you're finished, your workbook should resemble the example below:

Completed worksheet layout

Finished this lesson?

Progress is saved in this browser only. It is not a grade — official progress lives in Brightspace.

Using Find & Replace →