Formatting Margins in multiple sheets (XP)

Topic

New Reply

Before I print my spreadsheets I go into Print Preview and adjust the margins to where I need them, particularly the distance from the top of the page for the header and the first line of the spreadsheet, as well as centering horizontally. Is there any way to have the changes I make apply to all the sheets? I tried cntrl-clicking to select all the sheets before I made the changes, but it did not work. I need all the sheets to look uniform, and it is too hard to get all of them exactly the same.
Thanks

Reply | Quote

Replies

You should try again, for changes in Page Setup should apply to all selected sheets.

Reply | Quote

You should try again, for changes in Page Setup should apply to all selected sheets.

Reply | Quote

Page Setup changes should operate on all sheets which have been Activated using either Shift-Click (for a contiguous selection), or Ctrl-Click just as you did.

Be aware however that if a user selects a subset or a partially overlapping group of sheets and makes any changes in the Page Setup dialog, all attributes selected in the dialog will be applied to all the selected Active sheets, possibly changing settings he or she did not change nor intended to change.

Reply | Quote

I tried it again, something strange is going on. I decided to test using something simple, I chose Center Horizontally. When I started, sheet A and sheet B were both left justified when viewed in Print Preview. I select both sheets by cntrl clicking on their tabs, they both turned white. I went into Print preview/setup/margins for sheet A and checked Center Horizontally. Print Preview now showed both sheets centered. Then I clicked on the tab for another sheet and therefore de-selected A and B as a Group. When I went and looked again at Print Preview for A and B, Sheet A was still centered, but Sheet B went back to being left justified. The change only seemed to affect both sheets as long as they remained both selected, as a Group.

Reply | Quote

This is not as it should be. Could you try this in a blank new workbook? Just enter a dummy value in cell A1 in each worksheet, then repeat the page setup as you described here. It works fine in Excel 2002 (SP-2) for me – the Center Horizontally “sticks” after deselecting the group, and also after saving, closing and reopening the workbook.

Reply | Quote

I did as you suggested, created a new workbook. Same thing. The formatting changes to the rest of the sheets in the Group are lost as soon as I de-select the Group.

Reply | Quote

There are only three settings that can’t be set for multiple sheets at a time: Print Area, Rows to Repeat at Top, and Columns to Repeat at Left.

I can’t find any mention of this problem in the Knowledge Base or in the newsgroups, so I fear there is something wrong with your Excel installation. Take a look at Jan karel Pieterse’s Systematic Approach to Behavioral Problems in XL, and try Help | Detect and Repair…

Reply | Quote

OK will do. I’ll report back if I find the problem.
Thanks for the help.

Reply | Quote

After hearing the comments here, I decided to look carefully to see if there was a user error. And it seems like there is. I did not realize that when you select multiple sheets and thereby create a group, that when you go to Print Preview for ANY of the sheets, you are actually still only formating for page 1 of the group. So when I was going to each tab of the group and saw that the format changes were there, I was actually looking at the first page every time, and the other pages were seen (unless I clicked the Next button) and not affected. A good clue is that at the bottom it says Page 1 of 4.
So my question now is how do I really select all sheets in order to make the formatting changes.

Reply | Quote

Select multiple sheet tabs, either by clicking on one tab then Shift+click on another tab (also selecting all tabs in between), or by Ctrl+clicking individual tabs.
Then select File | Page Setup…
Set the margins, layout etc. the way you want, then click OK.
Do NOT select Print Preview first, and click Setup from the preview window – see XL2000: Page Setup in Print Preview Does Not Apply to All Grouped Sheets. Although this MSKB article is for Excel 2000, it holds for Excel 2002 too.

Reply | Quote

OK I got it now. Thanks for your help.

Reply | Quote

OK I got it now. Thanks for your help.

Reply | Quote

Select multiple sheet tabs, either by clicking on one tab then Shift+click on another tab (also selecting all tabs in between), or by Ctrl+clicking individual tabs.
Then select File | Page Setup…
Set the margins, layout etc. the way you want, then click OK.
Do NOT select Print Preview first, and click Setup from the preview window – see XL2000: Page Setup in Print Preview Does Not Apply to All Grouped Sheets. Although this MSKB article is for Excel 2000, it holds for Excel 2002 too.

Reply | Quote

OK I see that going to the Print Preview button may be the problem. If I select all the sheets and instead go to File/Page Setup my changes are universal on all pages. The only problem is that I do not get to see the image and drag the margins where I want them, I have to enter them numerically. Or am I still missing something?

Reply | Quote

You can do it like this:
1. Select the leftmost sheet you want to apply the settings to.
2. Activate Print Preview.
3. Click Margins and drag the margins where you want them.
4. Click Setup and apply other settings you like.
5. Close Print Preview.
6. Use Shift+click or Ctrl+click to select the other sheets you want to apply the same page setup to.
7. Select File | Page Setup.
8. Activate each tab. Added later: this step is not necessary, see below.
9. Click OK.

Reply | Quote

In step 8, Activate each tab, how do I do that? Once the Page Setup dialog comes up, clicking on the tabs has no effect.

Reply | Quote

Wait! it seems like your procedure did work. But I don’t think step 8 is necessary. Just cntrl-clicking the sheets, going to File/page setup and clicking OK seems to be enough.

Reply | Quote

Yes, you’re right. I thought I had read somewhere that you had to activate each tab of the Page Setup dialog (not each worksheet tab) but that turns out not to be necessary. It’s even easier then

Reply | Quote

Yes, you’re right. I thought I had read somewhere that you had to activate each tab of the Page Setup dialog (not each worksheet tab) but that turns out not to be necessary. It’s even easier then

Reply | Quote

Wait! it seems like your procedure did work. But I don’t think step 8 is necessary. Just cntrl-clicking the sheets, going to File/page setup and clicking OK seems to be enough.

Reply | Quote

Hans is saying to select each “tab” of the page setup dialog box so that all of the current settings will apply to all the selected sheets (you select the sheets after you close print preview and before you open page etup dialog)

Steve

Reply | Quote

OK I get it now. I misunderstood, thought he meant the tabs for each sheet.
Thanks for all the help.

Reply | Quote

OK I get it now. I misunderstood, thought he meant the tabs for each sheet.
Thanks for all the help.

Reply | Quote

Hans is saying to select each “tab” of the page setup dialog box so that all of the current settings will apply to all the selected sheets (you select the sheets after you close print preview and before you open page etup dialog)

Steve

Reply | Quote

In step 8, Activate each tab, how do I do that? Once the Page Setup dialog comes up, clicking on the tabs has no effect.

Reply | Quote

You can do it like this:
1. Select the leftmost sheet you want to apply the settings to.
2. Activate Print Preview.
3. Click Margins and drag the margins where you want them.
4. Click Setup and apply other settings you like.
5. Close Print Preview.
6. Use Shift+click or Ctrl+click to select the other sheets you want to apply the same page setup to.
7. Select File | Page Setup.
8. Activate each tab. Added later: this step is not necessary, see below.
9. Click OK.

Reply | Quote

OK I see that going to the Print Preview button may be the problem. If I select all the sheets and instead go to File/Page Setup my changes are universal on all pages. The only problem is that I do not get to see the image and drag the margins where I want them, I have to enter them numerically. Or am I still missing something?

Reply | Quote

After hearing the comments here, I decided to look carefully to see if there was a user error. And it seems like there is. I did not realize that when you select multiple sheets and thereby create a group, that when you go to Print Preview for ANY of the sheets, you are actually still only formating for page 1 of the group. So when I was going to each tab of the group and saw that the format changes were there, I was actually looking at the first page every time, and the other pages were seen (unless I clicked the Next button) and not affected. A good clue is that at the bottom it says Page 1 of 4.
So my question now is how do I really select all sheets in order to make the formatting changes.

Reply | Quote

OK will do. I’ll report back if I find the problem.
Thanks for the help.

Reply | Quote

There are only three settings that can’t be set for multiple sheets at a time: Print Area, Rows to Repeat at Top, and Columns to Repeat at Left.

I can’t find any mention of this problem in the Knowledge Base or in the newsgroups, so I fear there is something wrong with your Excel installation. Take a look at Jan karel Pieterse’s Systematic Approach to Behavioral Problems in XL, and try Help | Detect and Repair…

Reply | Quote

I did as you suggested, created a new workbook. Same thing. The formatting changes to the rest of the sheets in the Group are lost as soon as I de-select the Group.

Reply | Quote

This is not as it should be. Could you try this in a blank new workbook? Just enter a dummy value in cell A1 in each worksheet, then repeat the page setup as you described here. It works fine in Excel 2002 (SP-2) for me – the Center Horizontally “sticks” after deselecting the group, and also after saving, closing and reopening the workbook.

Reply | Quote

I tried it again, something strange is going on. I decided to test using something simple, I chose Center Horizontally. When I started, sheet A and sheet B were both left justified when viewed in Print Preview. I select both sheets by cntrl clicking on their tabs, they both turned white. I went into Print preview/setup/margins for sheet A and checked Center Horizontally. Print Preview now showed both sheets centered. Then I clicked on the tab for another sheet and therefore de-selected A and B as a Group. When I went and looked again at Print Preview for A and B, Sheet A was still centered, but Sheet B went back to being left justified. The change only seemed to affect both sheets as long as they remained both selected, as a Group.

Reply | Quote

Page Setup changes should operate on all sheets which have been Activated using either Shift-Click (for a contiguous selection), or Ctrl-Click just as you did.

Be aware however that if a user selects a subset or a partially overlapping group of sheets and makes any changes in the Page Setup dialog, all attributes selected in the dialog will be applied to all the selected Active sheets, possibly changing settings he or she did not change nor intended to change.

Reply | Quote