Joe writes in with a faster way to get an entire workbook into Group mode. This is a fast way to make changes to all of the sheets in a workbook. Episode 513 shows you how.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netcast I'm Bill Jelen.
Today we have a question and a tip sent in by Joe.
If you have a question for the podcast please feel free to get it to us either as a voicemail or drop me an email bill@MrExcel.com Now Joe had a question about group mode and let me basically cover group mode for you.
I tried to make our worksheet little bit smaller so you can see the whole thing at one time.
We have a workbook here with four different worksheets Q1 Q2 Q3 Q4 and we need to make some sort of a change to all 4 worksheets.
Now the way that we would normally do this is by going into group mode.
And there's three different ways to go into group mode.
If for some reason I needed to make a change.
So let's say Q1 and Q3 those are worksheets that are not adjacent.
I would select Q1 and then with the Ctrl key held on choose Q3 and you'll see that we've selected Q1 and Q3 there. If I would need to select worksheets that were adjacent, I might choose for example Q1 and then I could hold on the shift key and select Q3 that will select everything from Q1 to Q3, or if I needed to choose all of the worksheets in the workbook, Basically what we could do is right-click on any tab and from the drop down box that pops up. We'll choose "Select All Sheets" That'll put the entire workbook in group mode.
Here's how group mode works in addition to the title bar saying that we're in a group mode Basically anything that I would do to the visible worksheet So I'm seeing Q1 if we've now done a reorg and we now have the East region broken into Northeast and Southeast.
If I insert a new row here, and type Southeast and then rename the old East region to Northeast.
What's actually happening is excel has made that change to all of the sheets that were in the group.
So in that case I'd selected all sheets.
There I'm able to very quickly add a new row with Southeast and rename the East region to Northeast.
Group mode is very very powerful but I have to tell you that it's somewhat dangerous because I usually forget that I'm in group mode.
What will happen is I go into group mode make that change get a phone call and 15 minutes later I come back with a whole big list of to do's and I've forgotten that I'm in group mode, and I start working on Q1 typing numbers and don't realize that I'm going through and over writing numbers in Q2 Q3 and Q4, but now here's Joe's question.
Let's say that we needed to make some sort of a change to all the charts for example, we wanted to format the color of the first data point.
Normally we could right-click "Format Data Series" and choose a different color.
If we wanted to do that to all the charts at once.
When we try and do "Select all Sheets" We can no longer select any object on the sheet for example, a chart.
So group mode is great but it really seems to fall down as soon as you want it to change WordArt or a chart or clipart or anything like that that's embedded on the sheets.
Unfortunately the only solution I have for Joe is that we'd have to go in and write some VBA.
That would go through all the sheets and find the chart and change the chart.
How much ofcourse is beyond what we can do here on the podcast.
Now if you happen to know any good way around this to change all the charts, please feel free to let me know drop me a note bill@MrExcel.com so to get to Joe's question unfortunately it doesn't help.
I do want to thank Joe for passing along the great selection of using the right-click "Select all Sheets" to put the entire workbook in group mode.
That's one that I hadn't seen before.
I want to send Joe one of my excel master pins which of course is little consolation for the fact that we weren't able to solve his problem in the first place but group mode great option if you've never tried it.
Just make sure to always remember to get out of group mode before you start working on the visible worksheet.
Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
Today we have a question and a tip sent in by Joe.
If you have a question for the podcast please feel free to get it to us either as a voicemail or drop me an email bill@MrExcel.com Now Joe had a question about group mode and let me basically cover group mode for you.
I tried to make our worksheet little bit smaller so you can see the whole thing at one time.
We have a workbook here with four different worksheets Q1 Q2 Q3 Q4 and we need to make some sort of a change to all 4 worksheets.
Now the way that we would normally do this is by going into group mode.
And there's three different ways to go into group mode.
If for some reason I needed to make a change.
So let's say Q1 and Q3 those are worksheets that are not adjacent.
I would select Q1 and then with the Ctrl key held on choose Q3 and you'll see that we've selected Q1 and Q3 there. If I would need to select worksheets that were adjacent, I might choose for example Q1 and then I could hold on the shift key and select Q3 that will select everything from Q1 to Q3, or if I needed to choose all of the worksheets in the workbook, Basically what we could do is right-click on any tab and from the drop down box that pops up. We'll choose "Select All Sheets" That'll put the entire workbook in group mode.
Here's how group mode works in addition to the title bar saying that we're in a group mode Basically anything that I would do to the visible worksheet So I'm seeing Q1 if we've now done a reorg and we now have the East region broken into Northeast and Southeast.
If I insert a new row here, and type Southeast and then rename the old East region to Northeast.
What's actually happening is excel has made that change to all of the sheets that were in the group.
So in that case I'd selected all sheets.
There I'm able to very quickly add a new row with Southeast and rename the East region to Northeast.
Group mode is very very powerful but I have to tell you that it's somewhat dangerous because I usually forget that I'm in group mode.
What will happen is I go into group mode make that change get a phone call and 15 minutes later I come back with a whole big list of to do's and I've forgotten that I'm in group mode, and I start working on Q1 typing numbers and don't realize that I'm going through and over writing numbers in Q2 Q3 and Q4, but now here's Joe's question.
Let's say that we needed to make some sort of a change to all the charts for example, we wanted to format the color of the first data point.
Normally we could right-click "Format Data Series" and choose a different color.
If we wanted to do that to all the charts at once.
When we try and do "Select all Sheets" We can no longer select any object on the sheet for example, a chart.
So group mode is great but it really seems to fall down as soon as you want it to change WordArt or a chart or clipart or anything like that that's embedded on the sheets.
Unfortunately the only solution I have for Joe is that we'd have to go in and write some VBA.
That would go through all the sheets and find the chart and change the chart.
How much ofcourse is beyond what we can do here on the podcast.
Now if you happen to know any good way around this to change all the charts, please feel free to let me know drop me a note bill@MrExcel.com so to get to Joe's question unfortunately it doesn't help.
I do want to thank Joe for passing along the great selection of using the right-click "Select all Sheets" to put the entire workbook in group mode.
That's one that I hadn't seen before.
I want to send Joe one of my excel master pins which of course is little consolation for the fact that we weren't able to solve his problem in the first place but group mode great option if you've never tried it.
Just make sure to always remember to get out of group mode before you start working on the visible worksheet.
Thanks for stopping by. We'll see you next time for another netcast from MrExcel.
