Change All Sheets

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!