MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Change All Sheets

July 14, 2017 - by Bill Jelen

Change All Sheets

Learned how to make copies of sheets quickly in podcast 1981.

You've heard of the Manager's 15 minute rule, right? Any time your manager asks you for something, he or she comes back 15 minutes later and asks for an odd twist that he or she did not specify 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 one sheet, when your manager comes back.

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

At this point, you have 12 worksheets that are mostly identical. You need to add totals to all 12 worksheets. To enter Group mode, right-click on the January tab and choose Select All Sheets.

Select All Sheets
Select All Sheets

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

Group Mode
Group Mode

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

Why is this dangerous? Because if you get distracted and you 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.

Ungroup Sheets
Ungroup Sheets

Watch Video

  • Learned how to make copies of sheets quickly in podcast 1981
  • Manager 15 minute rule says your manager will change their mind 15 minutes after a request
  • Select Jan sheet. Shift-click on Dec sheet to begin group mode
  • Any change to Jan is happening to all sheets in the group
  • Easy to forget that you are in group mode
  • Every time I am in group mode, the phone rings
  • Three ways to exit group mode
  • Select a sheet not in the group.
  • If all sheets are grouped, select any sheet other than the active sheet
  • Right-click sheet tab and choose ungroup sheets

Download File

Download the sample file here: Podcast1983.xlsx

Title Photo: PublicDomainPictures / pixabay

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point

The new Dynamic Array Functions are just one side-effect of an effort to completely rewrite the Calculation Engine in Excel. Joe McDaid and the rest of the CALC team have the laid the groundwork for all future functions in Excel. Yes, the first crop of SORT, SORTBY, FILTER, UNIQUE, SEQUENCE and RANDARRAY are awesome and powerful, but they are just the first of many new functions that will come to Office 365 over the coming years.