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
The same spelling mistake is also done for the other 2 worksheets (2017 & 2018):
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:
Read the step-by-step tutorial below on how to group sheets in Excel and download the workbook to follow along:
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).
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.
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).
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.
All of these worksheets will be grouped together!
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.
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.
- If you have worksheets grouped, the word Group will be added to the name of the workbook.
Helpful Resource:
Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:
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.