Excel 2020: Change All Sheets with Group Mode

February 05, 2020 - by Bill Jelen

Change All Sheets with Group Mode. Photo Credit: Yiran Ding at Unsplash.com

Any time your manager asks you for something, he or she comes back 15 minutes later and asks for an odd twist that wasn't specified the first time. Now that you can create worksheet copies really quickly, there is more of a chance that you will have to make changes to all 12 sheets instead of just 1 sheet when your manager comes back with a new request.

I will show you an amazingly powerful but incredibly dangerous tool called Group mode.

Right click on the January worksheet tab and choose Select All Sheets from the context menu.

Say that you have 12 worksheets that are mostly identical. You need to add totals to all 12 worksheets. To enter Group mode, right-click on any worksheet tab and choose Select All Sheets.

The name of the workbook in the title bar now indicates that you are in Group mode.

The title bar at the top of the Excel window shows the workbook name followed by the word Group enclosed in square brackets. [Group] is a very subtle indicator that the workbook is in group mode.

Anything you do to the January worksheet will now happen to all the sheets in the workbook.

Why is this dangerous? If you get distracted and forget that you are in Group mode, you might start entering January data and overwriting data on the 11 other worksheets!

When you are done adding totals, don’t forget to right-click a sheet tab and choose Ungroup Sheets.

Thanks to Olga Kryuchkova for the Group mode tip.

Title Photo: Yiran Ding at Unsplash.com

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.