Pinterest Pixel

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

Ever had a time when you needed to modify data across multiple worksheets?  It is very easy... read more

Download Excel Workbook
Bryan
Posted on

Overview

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

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!

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.

See also  Text to Columns - Emails

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.

See also  How to Convert Text to Numbers in Microsoft Excel

 

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.
See also  Useful Excel Solver Functions for Optimal Data Analysis

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 itEmail this to someone

email

Pin on Pinterest

Pinterest

Share on Facebook

Facebook

Tweet about this on Twitter

Twitter

Share on LinkedIn

Linkedin

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

Bryan is a best-selling book author of the 101 Excel Series paperback books.

See also  Group Sales with a Pivot Table

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!