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

Video Transcript

The MrExcel podcast is sponsored by the book: "MrExcel XL"!

Learn Excel from MrExcel podcast, episode 1983 - Change All Sheets with Group Mode!

Way back in episode 1981, I showed you how to very quickly create sheets, Remember CTRL drag and so, while we build a report here for January, we're able to make copies incredibly quickly. But unfortunately now, we're violating a business rule that you may have heard off, called the "Manager 15 Minute Rule". Well actually, you may not have learned this in business school, because I think I made it up, and it goes like this: it says if your manager comes in, and asked you to do something for a task, it usually is about 15 minutes later, the manager comes back and says: "You know, hey, I changed my mind, I needed to do something different." But you know, now, the problem is because we can make sheets so quickly, that instead of having to make one change, we would have to make in this case 12 changes, January through December. I'm going to show you a trick that is incredibly powerful, but incredibly, incredibly dangerous.

So I start here on the January sheet, and I'm going to shift click on December. Alright, and what that has done, you can see up here, now we say that we are in Group mode, so. Even though we're looking at January, any changes that we make, are happening to all of the sheets in the group. So maybe the manager says "Look, I need the totals highlighted in bold." So I do CTRL+B there, CTRL+B, and, we need to edit this January report, I want to call it "Sales Report for January". So I go into Edit mode, and up here say: "Sales Report", actually let's scream in all caps, "FOR" &, and then I don't need the "Report" at the end, just UPPER there. That looks good. And let's set that in some other color. Maybe a nice deep blue. Alright. So, there we've made the change for January, but it has actually done the same change in all of the sheets in the group.

Alright now, the reason that this is so dangerous, is that every time that I go into group mode, my telephone rings, and it's not a short call, someone with some complicated thing, or maybe even my manager, you know, who gives me, 15 minutes worth of things to do. And, I come back from that call, and I sit here, and I realize that I'm looking at the sales report for January, and I started changing numbers in January. And the problem with that is, although I think I'm working on the January report, I'm actually screwing up all of the numbers on all of the other reports in the group! I mean, let's face it, it's really subtle, really subtle, the sheet tabs down here aren't that different in color, and really small up there, really out of your sight, that you're in group mode, so. My actual real-life tip here, is a tip that really doesn't have to do anything with Excel, it's just that I've been burned by this so many times, every time that I go into group mode, I have a sticky note on my wall. I stick that sticky note in the monitor, it says "Bill... You are in Group Mode!" Alright, just because once you've been burned by this, it is a painful, painful thing.

Now, the question, how do you get out of Group mode, alright? There's a couple of different ways, if all of the sheets are not selected, then you can just click any other sheet in the workbook, and it will get you out of Group mode. But if all the sheets are selected, so I'm going to select all sheets, then you just have to choose something that's not the active sheet, so like, if I click on February, that would get us out. However that doesn't work in the case we're not in all of the sheets. So like right, on January through December, if I click on February, that just makes that the active sheet. You have to click outside. So, the way that works reliably all the time, is to right-click any sheet tab, and choose "Ungroup Sheets". I realize it's out of your view there. But that works all the time.

Just a bonus tip here, if you needed to select not every sheet, you could choose January, and then CTRL-click on March and May and July and September, and put those in Group mode, like that.

Hey, if you're enjoying these tips go subscribe to the MrExcel XL playlist, I'll be podcasting all of the 40 tips in the MrExcel XL book. Or hey, you can save time and buy the whole book now, there's a link up there in the "i" up in the top-right hand corner. You can buy the whole book now, it's 25$, it's cheap, all these great Excel tips, great reference guide to all of this series of podcasts we're doing here.

Alright, Episode Recap: You learned how to make copies of a sheet quickly, back in podcast 1981, the Manager 15 Minute Rule, my creation says: every time a manager asks for something, they change their mind 15 minutes later, but now we have to make changes to 12 copies instead of just one. So you select the January sheet, Shift-click on December to begin Group mode, changes all the sheets in the group. But it's easy to get distracted and forget you're in Group mode. The phone rings, get called to a staff meeting, you come back and you see that you're in January, start over writing things. There's three different ways to exit group mode: If all the sheets aren't selected, select the sheet that's not in the group. If all the sheets are grouped, then select the sheet that's not the active sheet. Or reliably, no matter how many sheets are selected, right-click the sheet tab, and choose Ungroup Sheets.

Hey, a lot of the ideas in the book were crowdsourced, so Olga K sent in Group mode, it's one of her idea so thanks to her. And I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!

Download File

Download the sample file here: Podcast1983.xlsx

Title Photo: PublicDomainPictures / pixabay

Bill Jelen is the author / co-author of
MrExcel LIVe

A book for people who use Excel 40+ hours per week. Illustrated in full color.