11. Layout and Printing

Before printing a spreadsheet on paper, its layout must be defined. This involves defining:

The area of the sheet to be printed
The width of the margins
The orientation of the page
The size of the page
The reduction/enlargement from the normal size

The problem is that Excel's default view (i.e. display) does not show how the page will be printed, i.e. its layout. To see the layout of a spreadsheet, you need to switch to the Page Layout view or the Page Break Preview view. Another possibility is to display a Print Preview.

Let's expand on this using the example workbook that can be downloaded here.

You can switch to the Page Setup view or the Page Break view, using the corresponding buttons on the View tab of the Ribbon.

11.1. Page Layout and Page Break Preview view modes

You can switch to the Page Layout view or the Page Break Preview view, using the corresponding buttons on the View tab of the Ribbon.

These view mode buttons are also available at the bottom right of the Excel window without having to switch to the View tab of the Ribbon.

Open the example workbook and switch to the Page Layout view. Here is a preview:

The layout shown in this overview is not adequate for several reasons. At least notice that the data table does not fit horizontally on one page; how can I know the date of membership or the city of someone, unless I have to put sheets next to each other.

11.2. Print preview view

The layout can also be viewed using the Print Preview. To do this, click on the File tab of the ribbon:

Then click on the Print tab, the Print Preview is displayed on the right hand side of the screen. Note also the buttons at the bottom allowing you to scroll through the pages:

Let us now look at the layout tasks most commonly used by Excel users.

11.3. Setting the Print Area

By default, Excel takes the entire filled area of your sheet as the print area. However, the print area should always be explicitly defined. Either we want to print only a part of the sheet's content. Or the default print area does not correspond to what we want for various reasons...

To define the print area, we must first select the corresponding cell range. In our case, this is the entire contents of the sheet, i.e. the range A1:H1475. Then, in the Page Layout tab of the ribbon, click on the Print Area menu and then the Set Print Area command:

11.4. Definition of Margin Widths

These are the margins left empty on the right, left, top and bottom of the printed sheet. The Margins menu on the Page Layout tab of the Ribbon allows you to define the width of the margins. From the menu that appears, choose one of the 4 options or choose Custom Margins... to open the Page Setup dialog box on the Margins tab.

In the Page Setup dialog box, define the widths you want.

11.5. Page orientation

We can set the print orientation to Portrait or Landscape. To do this we use the Orientation menu on the Page Layout tab of the Ribbon.

11.6. Page Size

To set the page size, we use the Size menu on the Page Layout tab of the Ribbon. Mainly two sizes are used:

A4: sheet of 21 cm x 29.7 cm
A3: sheet of 29.7 cm x 42 cm

11.7. Header and footer

The header and footer is content that is repeated on each page at the top and bottom respectively. We put :

The page number
A logo
The title
Author's name
The date of printing. Etc...

Click on the Dialog box launchers in the Page Setup group of the Page Layout tab of the ribbon:

In the Page Setup dialog box, click on the Header/Footer tab. Then use the buttons Custom Header... and Custom Footer...

Let's add a logo and the print date to the page header in the form "On xx/xx/xxxx at xx:xx". To do this, click on the Custom Header... button. The Header dialog box is opened:

This dialog box shows 10 buttons that can be used to add content to the left, middle and right sections. By hovering the mouse pointer over each of these buttons, Excel displays a tooltip describing their purpose.

To add the logo and print date :

Click in the Left section field
Then enter "On ", click on the Insert Date button, enter " at " and click on the Insert Time button. The space character should be entered as shown after "On" and before and after "at".
Click on the Right section field
Then click on the Insert Picture button

The Insert Pictures dialog box is open. In this dialog box, click on the Browse button according to the location of your image file or use the Bing Image Search.

If you want to use my image file as a Logo, download it here. Otherwise, use any image file.

If the image file is saved on your computer, click on the Browse button for From a File. The Windows File Explorer is opened. Select your image file and click OK, to return to the Header dialog box.

Notice that when you click on the Right section field, where we have added the image, the Format Picture button becomes active

Click on the Format Picture button. The Format Picture dialog box appears. The image should be resized an appropriate size. In the following extract, I have reduced the size to 25%:

Confirm with the OK button. You return to the Header/Footer dialog box.

Now click on the Custom Footer... button to open the Footer dialog box. In the footer we will add the name of the sheet and the page number:

Click on the Left section field
Then click on the Insert Sheet Name button
Click in the Right section field
Then click on the Insert Page Number button, enter " / " and click on the Insert Number of Pages button. The space character should be entered as shown before and after "/".

Confirm with the OK button. Also confirm the Header/Footer dialog box with the OK button.

11.8. Line to be repeated at the top

If you switch to the Page Layout view or display a Print Preview, then you can see that the column headers are only present on the first page.

Here is an extract of the first page with the column headers present at the top:

Here is an extract from the second column: we can see the absence of column headers:

This is normal, since the column headers are entered in row 1 of the worksheet. But you can tell Excel to add this first row, when printing, to the beginning of each printed page. Here's how:

Click on the Dialog box launchers in the Page Setup group of the Page Layout tab of the ribbon:

In the Page Setup dialog box :

Click on the Sheet tab
Click on the Rows to repeat at top field
Select the row 1 in the worksheet. The address of row 1 is entered in the field Rows to repeat at top field
Confirm with the OK button.

11.9. Enlargement/Reduction

Let's go back to the Page Layout view. We still see that the data table does not fit horizontally on the same page.

This problem can be solved by enlarging the page, which can be done in one of the following ways:

Reducing the margins
Landscape orientation
A3 size

We have already seen how to apply these solutions. But another solution, which is often used in Excel, is the reduction or enlargement of the normal page size.

Proceed as follows:

Click on the Dialog box launchers in the Page Setup group of the Page Layout tab of the ribbon:

In the Page Setup dialog box :

Click on the Page tab
Enter 72 in the field Adjust to
Confirm with the OK button.

Switch to the Page Layout view to check.

<< 10. Generation of graphics