What is the Page Setup Function in Excel & Google Sheets?

This tutorial will explain the Page Setup function in Excel & Google Sheets.

 

 

Page Setup allows you to set the appearance of the printed worksheet. This includes margins, page orientation, paper size, print areas, breaks, backgrounds, and printing titles. It also allows you to customize headers and footers and scale the document to size.

Accessing Page Setup

In the Ribbon, select Page Layout > Page Setup.

You can then select from the options that are available in the group by means of the drop-down list under most of the options, or by selecting the dialog box selector in the bottom right hand corner of the group. This opens the Page Setup dialog box.

 

 

Adjusting Margins

1. In the Ribbon, select Page Layout > Page Setup > Margins. Then select from the drop-down list.

 

 

If you select Custom Margins, the Page Setup dialog box will open with the Margins tab selected.

 

 

With this Dialog Box open, you can manually adjust the Top, Left, Bottom, and Right margins. You can also adjust the margins for the Header and the Footer.

2. To adjust any of these margins, use the toggle button to the right of the margin measurement. You can adjust the margins to be greater or smaller than the current value shown.

 

 

3. To view the worksheet’s margins, switch to Page Layout view by selecting the Page Layout view button in the bottom right hand corner of Excel.

 

 

Alternatively, in the Ribbon, select View > Workbook Views > Page Layout.

 

 

This view displays the worksheet as it would be shown when printed. A ruler is shown at the top of the screen above the columns and another ruler is shown down the left hand side of the screen to the left of the row numbers.

 

 

Another way of adjusting the margins in our worksheet is by using these rulers. To do this, click and drag in the required direction on the ruler.

1. Using the mouse, rest the cursor on the margin to be adjusted.

2. Then, drag the double-arrow to the left to decrease the size of the left margin.

3. Release the mouse button to see the effect of the amendment.

 

 

4. To return to Normal view in the worksheet, click on the Normal view button in the bottom right hand corner of Excel.

 

 

Adjusting Page Orientation

In the Ribbon, select Page Layout > Page Setup > Page Orientation and then select either Portrait or Landscape from the drop-down list.

 

 

Alternatively, in the Ribbon, select Page Layout > Page Setup and then select the dialog box selector in the bottom right hand corner of the group to show the Page Setup Dialog Box.

 

 

The Page Setup Dialog Box will open with the Page tab selected. In the Orientation group, select either Portrait or Landscape and then click OK.

 

 

Adjusting Paper Size

In the Ribbon, select Page Layout > Page Setup > Size and then select the required size from the drop-down list.

 

 

Alternatively, click on More Paper Sizes at the bottom of the list, or open the Page Setup Dialog box. The Page Setup Dialog Box will once again open with the Page tab selected.

From the Paper size drop-down list, select the required paper size, and then click on OK.

 

 

Setting the Print Area

Print Area is used to define a selected area of the worksheet as the printable area. This means only this area will print each time you print the worksheet.

1. First, select the range of cells for the print area.

 

 

2. Then, in the Ribbon, select Page Layout > Print Area > Set Print Area.

 

 

This creates a range name called Print_Area. Only this print area will print.

 

 

3. To view the print area for the worksheet, you can switch to Page Break Preview by selecting the Page Break Preview button in the bottom right hand corner of Excel.

 

 

The print area is displayed inside a blue border. Any rows and columns outside this border will not be printed or displayed in Print Preview.

 

 

4. To return to Normal view in the worksheet, click on the Normal view button in the bottom right hand corner of Excel.

Removing the Print Area

To remove the Print Area, in the Ribbon, select Page Layout > Print Area > Clear Print Area.

 

 

The excluded rows and columns are then added back into the Printed and Previewed data.

Inserting Page Breaks

Page breaks are inserted into the worksheet automatically if the data in that worksheet takes up more rows and columns than will fit onto the paper size that you’re using. However, you can manually insert page breaks into worksheets to put the page breaks at specific locations.

Both horizontal and vertical page breaks can be inserted into the worksheet.

Rules to Remember:

• A page break is inserted between the selected column and the column to the left.
• A page break is inserted between the selected row and the row above.

Horizontal Page Breaks

1. Select the row that will be the top row of the next page. Ensure your cell selector is in Column A.

2. In the Ribbon, select Page Layout > Page Setup > Breaks > Insert Page Break.

 

 

3. Switch to Page Break Preview by clicking on the Page Break Preview button in the bottom right hand corner of your screen, or in the Ribbon, select View > Page Break Preview.

 

 

This shows the page break across the screen as a blue line.

 

 

Vertical Page Breaks

Select the column that will be the left most column of the next page. Ensure the cell pointer is in Row 1 of the column you’re inserting the page break in.

In the Ribbon, select Page Layout > Page Setup > Breaks > Insert Page Break.

Adding Horizontal and Vertical Page Breaks at Once

To insert both vertical and horizontal page breaks at once, select the cell which will the top left hand cell of the new page.

In the Ribbon, select Page Layout > Page Setup > Breaks > Insert Page Break. Excel now inserts both page breaks at the cell pointer position.

Removing a Page Break

1. To remove a single page break, switch to Page Break Preview view by clicking on the Page Break Preview button in the bottom right hand corner of the screen, or in the Ribbon, select View > Page Break Preview. This enables you to see any previously-created page breaks.

 

 

2. Click in any cell directly under or to the right of the page break symbol on the screen.

3. In the Ribbon, select Page Layout > Page Setup > Breaks > Remove Page Break.

 

 

To remove all the page breaks in the worksheet, select Reset All Page Breaks.

Inserting a Background Into a Worksheet

Background allows you to add a background image to the sheet.

1. In the Ribbon, select Page Layout > Page Setup > Background.

 

 

You can select a picture from a file on your PC, search Bing for an image on the Internet, or select a picture from OneDrive.

 

 

2. For this example, click on Bing Image Search to search Bing.

 

 

3. Either type in a search term for the picture or click on one of the categories available.

 

 

4. Select the picture you want, and click Insert.

 

 

5. To remove the background image, in the Ribbon, go to Page Layout > Page Setup > Delete Background.

 

 

Print Titles

Print Titles are used when there are rows or columns that you want to print at the top or left side of every page in a worksheet. They are very helpful when you have a large worksheet that prints over several pages, and you want to be able to see the top or left most headings on each page.

1. In the Ribbon, select Page Layout > Page Setup > Print Titles.

 

 

The Page Setup dialog box is displayed with the Sheet tab selected.

 

 

You can have both rows and columns set as print titles.

2. In the Print Titles section, (1) click in the Rows to Repeat at Top box and then (2) click on the up arrow to the right of the Rows to Repeat at Top box.

 

 

3. Highlight the rows that need to be repeated on each page of the worksheet and then click the little down arrow to return to the Page Setup Dialog box.

 

 

4. Click OK. Excel will now repeat the selected lines at the top of every printed page.

To repeat columns, follow the same steps as above for setting rows but place your insertion point in the Columns to repeat at left box. Then in the worksheet click the column(s) that you want to repeat at the left of every page. You can select more than one column; however, all columns must be adjacent.

The Page Setup Dialog Box: Additional Functions

There are additional functions in each tab of the Page Setup dialog box that do not appear in the Page Setup group in the Ribbon.

In the Ribbon, select Page Layout > Page Setup and then select the dialog box selector in the bottom right hand corner of the group to show the Page Setup Dialog Box.

 

 

The Page Tab

Additional functions in the Page tab include Scaling, Print Quality, and First Page Number.

 

 

1. In the Ribbon, select Page Layout > Page Setup > Print Titles.

 

 

The Page Setup dialog box is displayed with the Sheet tab selected.

 

 

You can have both rows and columns set as print titles.

2. In the Print Titles section, (1) click in the Rows to Repeat at Top box and then (2) click on the up arrow to the right of the Rows to Repeat at Top box.

 

 

3. Highlight the rows that need to be repeated on each page of the worksheet and then click the little down arrow to return to the Page Setup Dialog box.

 

 

4. Click OK. Excel will now repeat the selected lines at the top of every printed page.

• Scaling sets the scale of the print size to either force the worksheet to print on a specified number of pages, or to increase/decrease the size of print as required.
• Print quality enables us to select a suitable print quality, i.e., DPI count.
• First page number allows us to set a starting page number other than the default setting of starting on Page 1.

The Margins Tab

The Margins tab allows us to center our worksheet in the middle of the printed page, both vertically and horizontally.

 

 

The Header / Footer Tab

The Header/Footer tab is the only tab where all of the functions displayed do not appear at all in the Page Setup group in the Ribbon. Instead, Header & Footer appears in the Ribbon on the Insert tab.

 

 

1. To access the Header/Footer tab in the Page Setup dialog box, in the Ribbon, go to Page Layout > Page Setup. This will open the Page Setup Dialog box. Click on the Header/Footer tab.

 

 

2. To insert a default header into the worksheet, click on the Header drop-down list to select from a preexisting headers list.

 

 

3. Repeat the process to insert a footer into the Worksheet by selecting the Footer drop-down list and selecting from the preexisting footers list.

For a Custom header or Footer, click Custom Header or Custom Footer.

You can insert information into the left, center, and right hand side of the header and footer. Custom Codes can be used to insert the file path and name, date, and time, and page numbers.

 

 

In addition, you can use the Headers & Footers tab to set odd and even pages, set a different first page, scale the font size with the document or align the headers and footers with the page margins.

The Sheet Tab

Additional functions in the Sheet tab include Print Options and Page Order.

 

 

Print options include the ability to print the gridlines, print in black and white, print draft quality, print the row and column headings if any. Any comments and notes that may be in the worksheet can be set to none, to be printed at the end of the workbook or to be printed as displayed in the workbook. Cells errors can be printed as displayed, as a blank, as “–“, or as N/A.

The page order can be adjusted to be Down, then over or Over, then down. The default is Down, then over.

From the Page Setup Dialog box, Print and Print Preview can be accessed as well as Printer Options.

What is the Page Setup Function in Google Sheets?

Page Setup in Google sheets is contained within the Print Functions.

In the File menu, select Print to show the Page Setup options available.

 

 

Paper Size

To amend the paper size, click on the drop-down list and select the required size, or select Custom and type in the required paper size.

 

 

Page Orientation

Select Landscape or Portrait to change the orientation.

Scale

To adjust the scaling, select from the list or select Custom number and type in the percentage scaling required.

 

 

Margins

Select from Normal, Narrow, or Wide – or select Custom. Custom Margins allow you to type the measurement required for each margin on the Google Sheet.

 

 

Custom Page Breaks

1. To insert custom page breaks, click on the Custom Page Breaks button. This will enable us to drag the page breaks to the positions you want them in the Google Sheet.

 

 

2. Drag the blue bars to the required positions and then click CONFIRM BREAKS in the right hand side of the sheet.

 

 

3. The Custom page breaks indicator will now be switched ON

 

 

4. To remove the custom breaks, slide the green slider across to the left.

Formatting

Click Formatting to show the formatting options available.

 

 

• Show Gridlines allows you to show or hide the gridlines when the sheet is printed.
• Show Notes enables the notes to be printed as they appear in the Google Sheet, or to be switched off instead.
• Page order (Over, then down OR Down, then over) allows you to dictate which page will come second (the one to the right or the one below the current page).
• Alignment allows you to change the horizontal and vertical alignment of the content in the Google sheet.

Headers and Footers

1. Click Headers & footers to show the header and footer options available.

 

 

Checking the options from the list (e.g., Page numbers, Workbook title, Sheet name, Current date and Current time) will automatically place the information into the header and footer of the Google sheets.

2. To have more control over where the information is placed, or to type in your own header and/or footer, click EDIT CUSTOM FIELDS.

 

 

5. To insert page numbers, select the first button on the toolbar and select the required page number format.

 

 

6. To insert the workbook name or sheet name, click on the second button on the toolbar and select the required field.

 

 

7. To insert the date, click on the third button on the toolbar and select the required date format from the date drop-down list.

 

 

8. Finally, to insert the time, click on the fourth button and select the required time format from the drop-down list.

 

 

9. Click on CONFIRM in the top right hand corner of the Sheet to confirm the amendments.

 

 

10. Click on Next to send the Google sheet to the printer.

 

 

HOT TIP: If you click CANCEL as this stage, all your sheet formatting will be lost. HOWEVER if you click NEXT and then in the Print screen, click CANCEL, the sheet formatting will remain!