Are you frustrated that as you print each worksheet in a workbook, the page numbers constantly start over at page 1? Use Group mode to have your page numbers appear correctly when printing many sheets. Episode 354 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 podcast, I'm Bill Jelen.
Many of you know that you can go into File, Page Setup, and setup page numbers for your workbooks.
So you go to File, Page Setup, and then in the Footer drop-down you can choose to say page 1 of ? , and the question mark will tell you the total number of pages in the printout.
Now if you have your data on multiple worksheets, and you print Sheet1 and then Sheet2 and then Sheet3 and then Sheet4, that might be a total of 4 pages.
But all 4 sheets are going to say that this is page 1 of 1, because it didn't understand that you were about to print all of the sheets.
A great trick is to put the workbook in Group mode before you print.
So, let's say, for example, I wanted to print Sheet1, you would then hold down the Shift key while you chose the last sheet tab.
Now, when you click the Print icon, it will automatically print all of the selected sheets, and instead of saying 1 of 1, 1 of 1, 1 of 1, and 1 of 1, you'll have 1 of 4, 2 of 4, 3 of 4, and 4 of 4.
Now Group mode is very, very cool, there’s a couple of things that it's great for, but one caution, you have to remember to turn it off.
The first thing that is great for is, if you need to make Page Setup changes to all of the sheets.
You can put the sheets in Group mode, select the first sheet, Shift, click the last sheet.
And then anything you do to file Page Setup will happen to all the sheets at once, and we don't have to go through and change the Footer again and again and again.
It's also interesting that when the worksheet is in Group mode, you're seeing the top worksheet, but any changes that you make to this worksheet will happen to all 4 worksheets.
So if you need to insert some rows everywhere in your book, you can put the sheets in Group mode, and make the changes to this top sheet, they’ll happen to all of the sheets.
Couple of tricks, one is if you don't want to select all the sheets, you can select the first sheet, and then Ctrl-click all of the other sheets.
The gotcha, though - you have to remember to take it out of Group mode.
If you forget that it's in Group mode, and you start working on the top sheet, all of the numbers that you type will happen to all 4 sheets.
There's a little reminder of here on the Title bar that says you're in Group mode, but I tend to forget about that and keep working, and just basically wreak havoc on the worksheet.
If there's one sheet that's not selected, you can click on that tab to exit Group mode, the best way, though, is to right-click any tab and choose Ungroup Sheets.
Hey, there you have it, using Group mode to be able to improve the page number footer in your printouts, and also to make changes to all sheets at once.
Thanks for stopping by, we'll see you next time for another podcast from MrExcel!
Many of you know that you can go into File, Page Setup, and setup page numbers for your workbooks.
So you go to File, Page Setup, and then in the Footer drop-down you can choose to say page 1 of ? , and the question mark will tell you the total number of pages in the printout.
Now if you have your data on multiple worksheets, and you print Sheet1 and then Sheet2 and then Sheet3 and then Sheet4, that might be a total of 4 pages.
But all 4 sheets are going to say that this is page 1 of 1, because it didn't understand that you were about to print all of the sheets.
A great trick is to put the workbook in Group mode before you print.
So, let's say, for example, I wanted to print Sheet1, you would then hold down the Shift key while you chose the last sheet tab.
Now, when you click the Print icon, it will automatically print all of the selected sheets, and instead of saying 1 of 1, 1 of 1, 1 of 1, and 1 of 1, you'll have 1 of 4, 2 of 4, 3 of 4, and 4 of 4.
Now Group mode is very, very cool, there’s a couple of things that it's great for, but one caution, you have to remember to turn it off.
The first thing that is great for is, if you need to make Page Setup changes to all of the sheets.
You can put the sheets in Group mode, select the first sheet, Shift, click the last sheet.
And then anything you do to file Page Setup will happen to all the sheets at once, and we don't have to go through and change the Footer again and again and again.
It's also interesting that when the worksheet is in Group mode, you're seeing the top worksheet, but any changes that you make to this worksheet will happen to all 4 worksheets.
So if you need to insert some rows everywhere in your book, you can put the sheets in Group mode, and make the changes to this top sheet, they’ll happen to all of the sheets.
Couple of tricks, one is if you don't want to select all the sheets, you can select the first sheet, and then Ctrl-click all of the other sheets.
The gotcha, though - you have to remember to take it out of Group mode.
If you forget that it's in Group mode, and you start working on the top sheet, all of the numbers that you type will happen to all 4 sheets.
There's a little reminder of here on the Title bar that says you're in Group mode, but I tend to forget about that and keep working, and just basically wreak havoc on the worksheet.
If there's one sheet that's not selected, you can click on that tab to exit Group mode, the best way, though, is to right-click any tab and choose Ungroup Sheets.
Hey, there you have it, using Group mode to be able to improve the page number footer in your printouts, and also to make changes to all sheets at once.
Thanks for stopping by, we'll see you next time for another podcast from MrExcel!
