By Bob Umlas, Excel MVP

For the final part of the Page Layout tab series we’ll complete the discussion of what’s available on the tab and a few other items. The Header/Footer tab of the page Setup dialog is where we left off. Let’s look at this again. Click the dialog launcher below the Print Titles icon and then click the 3rd tab, and you see:


The main buttons here are the Custom Header and Custom Footer buttons. Either one gives the identical dialog aside from the results being shown in the Header or the Footer. The dialog looks like this:


Before we go into all these buttons, notice the tab, “Header” at the top of the dialog. If, in the previous dialog, the box “Different odd and even pages” was checked, then when the Custom Header button is clicked you’d see these tabs at the top:


and if the “Different first page” was checked, then you’d see these tabs at the top:


and if both were checked, you’d see these tabs:


The icons in the middle of the dialog are these:


If you type “My left header” in the left section, select that text, then click the Format Text icon (pointed to by the arrow), you will get the Font dialog as shown below. This is where you can format the selected text. What’s shown here is the effect of doing exactly that, then issuing the command again where you can see the result of the text’s formatting:


In the next dialog, the cursor was placed in the Center section, the page number icon was selected, the word “of” was typed (surrounded by a space), and then the number of pages icon was selected:


The &[Page] and &[Pages] are “codes” for Excel to insert the current page and total number of pages. For example, the following preview would show if this were done to the center section of the footer on a document with 19 pages and we were looking at page 2:


Some of the other commands are pretty self-explanatory. Here the Insert Picture icon was selected (an Excel logo was chosen), then the Format picture icon was chosen where the image was set to be 1” x 1”:


The result:


If the margins are also set, then this would show as:


Where can you set the margins in the Print preview? Scroll down a bit and you’ll see 2 icons at the bottom right, quite hidden from the user (!):


The left one is the toggle for the margins, which you can drag around, and right one is to zoom in.

The last icons on the Page Layout tab we’re discussing are these:


The # of pages wide or tall can be set, or the scale can be set. For example, if you set both Width and Height to 1 page, then the Scale would automatically be set (and no longer enabled), something like this:


The last 4 checkboxes are used to display and/or print the gridlines or headings (headings being the row numbers and column letters).

Suppose you make all your page settings as you like, and then realize you have other sheets to which you’d like to make the same settings! Do you have to repeat all the steps for each sheet? No! You can ctrl/click or shift/click the other worksheet tabs to put them into a group. Here, Sheet4 is active but Sheet3 is now part of the group: plt-16.

The top of the screen shows this: plt-17. The word [group] indicates more than one sheet is included in the group. Now all you need to do is bring up the page setup dialog and simply click OK! The settings for the active sheet are copied to all the other sheets in the group!

If you click on the Page Break Preview icon at the bottom right of the screen,


you would see an editable preview of the printout:


The dotted lines indicate where the page breaks will automatically occur. You can actually click and drag these to a new location. Dragging the vertical break to the right would be inserting a manual page break. Here’s the same data with the vertical break dragged all the way to the right:


The icon to the left of the Page Break preview at the bottom of the Excel screen is the Page Layout tab. This gives the following view:


Notice you can see the header at the top, rulers are present above the columns headings and to the left of the row headings, the page break between columns J & K are shown, and you can still edit the workbook in this view. You can also click where it says “Add Header” (or “Add Footer” if you scroll down) and you’ll see this:


Notice the new ribbon tab “Design” in the contextual tab “Header & Footer Tools” which gives you access to the items as icons we saw previously! Here are the definitions of the right-most checkboxes in the above illustration:


These different views of the spreadsheet are also visible from the ribbon’s View tab in the very left section, as seen here:





Join Bob at IIL’s IPM Day virtual conference on November 3rd, where he’ll be presenting on “The Magic of Macros.” Learn how Excel’s Macros (a.k.a. VBA) can create incredible results in the blink of an eye!

Conference Overview: 

Register with promo code BLOG for 10% off!

Bob Umlas
has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic!, Excel Outside the Box, and More Excel Outside the Box.