Pinterest Pixel

How to Group Sheets in Excel: A Step-by-Step Tutorial

Bryan
Ever had a time when you needed to modify data across multiple worksheets?  It is very easy to do this using the Excel group sheets feature!
How to Group Sheets in Excel: A Step-by-Step Tutorial | MyExcelOnline How to Group Sheets in Excel: A Step-by-Step Tutorial | MyExcelOnline

This is only possible when you have multiple worksheets with different data but the same layout.

Once you Excel group sheets and make an edit in one cell, it will be reflected in the same cell for other worksheets as well.

In this article, you will be provided a detailed guide on how to group worksheets in Excel:

Let’s look into each one of these!

 

Group selected worksheets

Let us say we have this same mistake on multiple worksheets –  see the Dvv typo in the screenshot below.  We want to change this to Dec

How to Group Sheets in Excel: A Step-by-Step Tutorial

The same spelling mistake is also done for the other 2 worksheets (2017 & 2018):

How to Group Sheets in Excel: A Step-by-Step Tutorial

How to Group Sheets in Excel: A Step-by-Step Tutorial

Instead of making the change in each worksheet separately, you can easily group these worksheets and make changes in one of them only. This will save you a lot of time.

Watch this video on how you can Excel group sheets:

How to Group Sheets in Excel: A Step-by-Step Tutorial | MyExcelOnline

Read the step-by-step tutorial below on how to group sheets in Excel and download the workbook to follow along:

download excel workbookHow-to-Group-Worksheets-in-Excel.xlsx

 

STEP 1: Hold the CTRL button and select the worksheets with the left mouse button that need editing. This step will Excel group sheets.

For our example, we need to select 2016, 2017, and 2018 worksheets while holding the CTRL button (this will turn each selected sheet to white color).

How to Group Sheets in Excel: A Step-by-Step Tutorial

 

STEP 2: Edit the cell in any one of the worksheets.  Let us change the Dvv to Dec and press ENTER.

This will change all of the worksheet values to reflect the same change.

 

How to Group Sheets in Excel: A Step-by-Step Tutorial

How to Group Sheets in Excel: A Step-by-Step Tutorial

How to Group Sheets in Excel: A Step-by-Step Tutorial

 

Ungroup selected worksheets

To ungroup the worksheets, right-click on the worksheet tabs and select Ungroup Sheets (super important to do this when you finish making your changes).

How to Group Sheets in Excel: A Step-by-Step Tutorial

Or, you can simply click on any worksheet outside the group.

 

Group all worksheets

In the previous example, you had to group only three worksheets. What if there are more worksheets that you need to group?

Grouping each of them would be impractical and takes a lot of time! There is a simple solution to this.

Right-click on any one of the worksheets listed at the bottom of the Excel window and click Select all Sheets.

How to Group Sheets in Excel: A Step-by-Step Tutorial

All of these worksheets will be grouped together!

How to Group Sheets in Excel: A Step-by-Step Tutorial

 

Ungroup all worksheets

To ungroup all worksheets together, simply right-click a selected worksheet at the bottom of the Excel window, and then click Ungroup Sheets.

How to Group Sheets in Excel: A Step-by-Step Tutorial

It is very easy to group worksheets in Excel and make multiple changes in one go.

But, it is possible that you may forget to ungroup the worksheet and continue to make edits in the file. In this case, the changes will be reflected in all of the grouped worksheets.

There are a few things that will help you notice when you have group sheets in Excel:

  • The tabs that have a white background are grouped together; whereas the tabs that have grey background are outside the group.

How to Group Sheets in Excel: A Step-by-Step Tutorial

  • If you have worksheets grouped, the word Group will be added to the name of the workbook.

How to Group Sheets in Excel: A Step-by-Step Tutorial

 

Helpful Resource:

728x90

Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:

Microsoft Excel Training

 

If you like this Excel tip, please share it
How to Group Sheets in Excel: A Step-by-Step Tutorial | MyExcelOnline
Bryan

Bryan Hong is an IT Software Developer for more than 10 years and has the following certifications: Microsoft Certified Professional Developer (MCPD): Web Developer, Microsoft Certified Technology Specialist (MCTS): Windows Applications, Microsoft Certified Systems Engineer (MCSE) and Microsoft Certified Systems Administrator (MCSA).

He is also an Amazon #1 bestselling author of 4 Microsoft Excel books and a teacher of Microsoft Excel & Office at the MyExecelOnline Academy Online Course.

See also  Filter a Pivot Table by Dates

Get Video Training

Advance your Microsoft Excel & Office Skills with the MyExcelOnline Academy!

Dramatically Reduce Repetition, Stress, and Overtime!
Exponentially Increase Your Chances of a Promotion, Pay Raise or New Job!

Learn in as little as 5 minutes a day or on your schedule.

Learn More!

Share to...