See all How-To Articles

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

This tutorial explores the Page Setup options in Excel and Google Sheets.

PageSetup DlgBox

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

 

Access Page Setup

  1. In the Ribbon, go to Page Layout > Page Setup.
  2. Then choose from the options that are available in the group by means of their individual drop downs.
    Or click the selector in the bottom-right corner of the group. This opens the Page Setup dialog box (pictured above).

PageSetup Ribbon

Adjust Margins

  1. In the Ribbon, go to Page Layout > Page Setup > Margins. Then choose from the drop down.

PageSetup MarginDropDown

If you click Custom Margins…, the Page Setup dialog box opens to the Margins tab.

PageSetup MarginsTab

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

  1. To adjust any of these margins, use the arrows to the right of each measurement. You can adjust the margins to be greater or smaller than the current value shown.

PageSetup MarginsAdjust

  1. To view the worksheet’s margins, switch to Page Layout view by clicking the Page Layout view button in the bottom-right corner of Excel.

PageSetup PageLayoutView

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

PageSetup View Ribbon

Page Layout 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 side of the screen to the left of the row numbers.

PageSetup Margins PageSetup

Rulers to Adjust Margins

Using rulers is another way to adjust margins. Click and drag in either direction along 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 change.

PageSetup MarginAdjust

  1. To return to Normal view in the worksheet, click the normal view button in the bottom-right corner of Excel.

PageSetup NormalView

Adjust Page Orientation

In the Ribbon, go to Page Layout > Page Setup > Orientation, and then choose either Portrait or Landscape from the drop down.

PageSetup OrientationDropDown

OR

  1. Alternatively, in the Ribbon, go to Page Layout > Page Setup and click the dialog box selector in the bottom-right corner of the group to show the Page Setup dialog box.

PageSetup Ribbon

  1. The Page Setup dialog box opens to the Page tab. For Orientation, choose either Portrait or Landscape, and then click OK.

PageSetup OrientationPageSetup

Adjust Paper Size

  1. In the Ribbon, go to Page Layout > Page Setup > Size, and then choose the size you need from the drop down.

PageSetup Size

Alternatively, click More Paper Sizes at the bottom of the list, or open the Page Setup dialog box (to the Page tab).

  1. From the Paper size drop down, choose the paper size, and then click OK.

PageSetup Paper-Size

Set Print Area

A print area defines what area of the worksheet is intended for printing. This means only this area prints each time you print the worksheet.

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

PageSetup PrintArea Select

  1. Then, in the Ribbon, go to Page Layout > Print Area > Set Print Area.

PageSetup PrintArea Ribbon

This creates a range name called Print_Area. Anything outside the print area doesn’t print.

PageSetup PrintArea RangeName

  1. To view the print area for the worksheet, you can switch to Page Break Preview by clicking the page break preview button in the bottom-right corner of Excel

PageSetup-PrintArea PageBreakPreview

The print area is displayed inside a blue border. Any rows and columns outside this border won’t print or show up in Print Preview.

PageSetup-PrintArea View PrintArea

  1. To return to Normal view in the worksheet, click the normal view button in the bottom-right corner of Excel. (See “Rulers to Adjust Margins,” Step 4 above.)

Easy Excel Automation

Add Excel automation to your workbook with just a few clicks.

Learn More

Remove Print Area

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

PageSetup PrintArea ClearPrintArea

The previously excluded rows and columns are now added back into the print and preview ranges.

Insert Page Breaks

Page breaks are inserted into the worksheet automatically if the data in that worksheet takes up more rows and columns than fit onto the paper size you’re using. However, you can manually insert page breaks into worksheets to break 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. In Column A, select the row you want as the top row of the next page.
  2. In the Ribbon, go to Page Layout > Page Setup > Breaks > Insert Page Break.

PageSetup PrintArea Ribbon PageBreak

  1. Switch to Page Break Preview by clicking on the Page Break Preview button in the bottom-right corner of your screen, or in the Ribbon, go to View > Page Break Preview.

PageSetup PrintArea Ribbon PageBreakPreview

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

PageSetup PrintArea Ribbon-Pages

Vertical Page Breaks

  1. Select the column that you want as the left-most column of the next page.
  2. Ensure the pointer is in Row 1 of the column you’re inserting the page break in.
  3. In the Ribbon, go to Page Layout > Page Setup > Breaks > Insert Page Break.

Add Horizontal and Vertical Page Breaks at Once

  1. To insert both vertical and horizontal page breaks at once, select the cell you want as the top-left cell of the new page.
  2. In the Ribbon, go to Page Layout > Page Setup > Breaks > Insert Page Break.

Excel inserts both page breaks at the selected cell.

Remove Page Break

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

PageSetup PageBreaks ViewBreaks

  1. Click in any cell directly under or to the right of the page break line on the screen.
  2. In the Ribbon, go to Page Layout > Page Setup > Breaks > Remove Page Break.
    (To remove all the page breaks in the worksheet, click Reset All Page Breaks.)

PageSetup PageBreaks RemoveBreak

Insert a Background

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

  1. In the Ribbon, go to Page Layout > Page Setup > Background.

PageSetup Background Ribbon

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

PageSetup Background InsertPicture

  1. For this example, click Bing Image Search.

PageSetup Background SearchBing

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

PageSetup Background Money

  1. Choosethe picture you want and click Insert.

PageSetup Background Money Sheet

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

PageSetup Background DeleteBackground

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, go to Page Layout > Page Setup > Print Titles.

PageSetup PrintTitles Ribbon

The Page Setup dialog box opens to the Sheet tab.

PageSetup PrintTitles SheetTab

  1. You can have both rows and columns set as print titles. In the Print titles section, click in the Rows to repeat at top box and then click the up arrow to the right.

PageSetup PrintTitles RowsToRepeat

  1. Select 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.

PageSetup-PrintTitles Select RowsToRepeat

  1. Click OK. Excel now repeats the selected lines at the top of every printed page.
  2. 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, columns must be adjacent.

AutoMacro | Excel Automation Made Easy | Free Trial

The Page Setup Dialog Box: Additional Functions

There are more 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, go to Page Layout > Page Setup and then click the selector in the bottom-right corner of the group to show the Page Setup dialog box.

PageSetup Ribbon

Page Setup: Page

Additional functions in the Page tab include Scaling, Print quality, and First page number.

PageSetup Dialog Print tab

  • 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 needed.
  • Print quality lets you choose a suitable print quality, i.e., DPI count/resolution.
  • First page number lets you set a starting page number, rather than the default setting of starting on page 1.

Page Setup: Margins

The Margins tab lets you center a worksheet in the middle of the printed page, both vertically and horizontally.

PageSetup Dialog Margins tab

Page Setup: Header / Footer

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

PageSetup Insert Ribbon

  1. In the Ribbon, go to Page Layout > Page Setup. This opens the Page Setup dialog box. Click the Header/Footer tab.

PageSetup-Dialog HeaderFooter tab

  1. To insert a default header into the worksheet, click the Header drop down to choose from a preexisting headers list.

PageSetup Insert Header

  1. Repeat the process to insert a footer into the worksheet by choosing from the preexisting footers list in the Footer drop down.

Custom

  1. For a custom header or footer, click Custom Header… or Custom Footer
  2. You can insert information in the left, center, and right sections of the header and footer. Custom Codes can be used to insert the file path and name, date, and time, and page numbers.

PageSetup Custom Header

  1. In addition, you can use the headers and 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.

AutoMacro | Excel Automation Made Easy | Free Trial

Page Setup: Sheet

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

PageSetup Sheet tab

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 not to print (None), to be printed at the end of the workbook, or to be printed as displayed in the workbook. Cell 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.

Page Setup in Google Sheets

Page Setup in Google Sheets is contained within the print functions.

In the File menu, go to Print to show the page setup options available.

PageSetup GS Menu

Paper Size

To change the paper size, click the drop down and choose a size, or choose Custom size and type in the paper size you need.

PageSetup GS PaperSize

Page Orientation

Choose Landscape or Portrait to change the orientation.

AutoMacro | Excel Automation Made Easy | Free Trial

Scale

To adjust the scaling, choose an option from the list.

pagesetup gs scale

If you choose Custom number, you can type in the percentage scaling.

pagesetup gs scale percent

Margins

You can choose Normal, Narrow, Wide, or Custom numbers.

page setup gs set margins

If you choose Custom numbers, you can type in the numbers for your top, bottom, left, and right margins.

page setup gs set custom margins

Custom Page Breaks

  1. To insert custom page breaks, click the SET CUSTOM PAGE BREAKS.

pagesetup gs custom page breaks

  1. To insert a row page break, drag the dotted blue line going across your sheet up or down your sheet.

pagesetup gs drag page breaks rows

  1. To insert a column page break, drag the dotted blue line going down the page across to the left or right.

pagesetup gs drag page breaks columns

  1. Click CONFIRM BREAKS to return to Print Preview.

PageSetup GS CustomPageBreaks Confirm

  1. The Custom page breaks indicator is now switched on.

pagesetup gs custom page breaks

  1. You can edit the custom breaks by clicking EDIT.
    Alternatively, you can remove all custom breaks by clicking the Custom page breaks toggle button to off.

Formatting

Click Formatting to show the formatting options available.

pagesetup gs format

  1. Show Gridlines allows you to show or hide gridlines when the sheet is printed.
  2. Show Notes enables the notes to be printed as they appear in the Google Sheet, or to be switched off instead.
  3. Page order (Over, then down or Down, then over) allows you to dictate which page comes second (the one to the right or the one below the current page).
  4. Alignment allows you to change the horizontal and vertical alignment of the content in the 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) automatically places the information into the header and footer of the sheets.

pagesetup gs footerheader

  1. To have more control over where the information is placed, or to type in your own header and/or footer, click EDIT CUSTOM FIELDS. This enables you to type in the header and footer areas of your Google sheet.

pagesetup gs custom headerfooter

  1. To insert page numbers, click in one of the available areas and then, using the first button on the toolbar, choose a page number format.

PageSetup GS PageNumbers

  1. To insert the workbook name or sheet name, click the second button on the toolbar and choose the field you want.

PageSetup GS titles

  1. To insert the date, click the third button on the toolbar and choose the date format from the drop down.

PageSetup GS Dates

  1. Finally, to insert the time, click the fourth button and choose the time format from the drop down.

PageSetup GS Times

  1. Click CONFIRM in the top-right corner of the sheet.

PageSetup GS Header Final

  1. Click NEXT to send the Google sheet to the printer.

PageSetup GS Next

Tip: If you click CANCEL as this stage, you lose all sheet formatting. However, if you click NEXT, and then in the Print menu click CANCEL, the sheet formatting remains!

See all How-To Articles