Excel print area: how to set, change and clear

In this tutorial, you will learn how to select print area in Excel manually and how to set print ranges for multiple sheets by using macros.

When you hit the Print button in Excel, the entire spreadsheet is printed by default, which often takes multiple pages. But what if you don't really need all the content of a huge worksheet on paper? Luckily, Excel provides the ability define the parts for printing. This feature is known as Print Area.

A print area is a range of cells to be included in the final printout. In case you don't want to print the entire spreadsheet, set a print area that includes only your selection.

When you press Ctrl + P or click the Print button on a sheet that has a defined print area, only that area will be printed.

You can select multiple print areas in a single worksheet, and each area will print on a separate page. Saving the workbook also saves the print area. If you change your mind at a later point, you can clear the print area or change it.

Defining a print area gives you more control over what each printed page looks like and, ideally, you should always set a print area before sending a worksheet to the printer. Without it, you may end up with messy, hard to read pages where some important rows and columns are cut off, especially if your worksheet is bigger than the paper you are using.

How to set the print area in Excel

To instruct Excel which section of your data should appear in a printed copy, proceed in one of the following ways.

Fastest way to set print area in Excel

The quickest way to set a constant print range is this:

  1. Select the part of the worksheet that you want to print.
  2. On the Page Layout tab, in the Page Setup group, click Print Area > Set Print Area.

Fastest way to set print area in Excel

A faint gray line will appear denoting the print area.
Excel print area

More informative way to define print area in Excel

Want to visually see all your settings? Here's a more transparent approach to defining a print area:

  1. On the Page Layout tab, in the Page Setup group, click the dialog launcher Dialog launcher. This will open the Page Setup dialog box.
  2. On the Sheet tab, put the cursor in the Print area field, and select one or more ranges in your worksheet. To select multiple ranges, please remember to hold the Ctrl key.
  3. Click OK.

More informative way to define print area in Excel

Tips and notes:

  • When you save the workbook, the print area is also saved. Whenever you send the worksheet to the printer, only that area will be printed.
  • To make sure the defined areas are the ones you really want, press Ctrl + P and go through each page preview.
  • To quickly print a certain part of your data without setting a print area, select the desired range(s), press Ctrl + P and choose Print Selection in the drop-down list right under Settings. For more information, please see How to print selection, sheet or entire workbook.

How to set multiple print areas in Excel

To print a few different parts of a worksheet, you can select multiple print areas in this way:

  1. Select the first range, hold down the Ctrl key and select other ranges.
  2. On the Page Layout tab, in the Page Setup group, click Print Area > Set Print Area.

Done! Multiple print areas are created, each one representing its own page.

Note. This only works for non-contiguous ranges. Adjacent ranges, even selected separately, will be included in a single print area.

How to force Excel to ignore print area

When you want a hard copy of a whole sheet or entire workbook but do not want to bother clearing all the print areas, just tell Excel to ignore them:

  1. Click File > Print or press Ctrl + P.
  2. Under Settings, click the arrow next to Print Active Sheets and select Ignore Print Area.

Ignore print area in Excel.

The ability to print multiple areas per sheet of paper is controlled by a printer model, not by Excel. To check if this option is available to you, press Ctrl + P, click the Printer Properties link, and then switch through the available tabs of the Printer Properties dialog box searching for the Pages per Sheet option.
Printer Properties

If your printer has such an option, lucky you are :) If there is no such option, then the only way I can think of is copying the print ranges to a new sheet. With the help of the Paste Special feature, you can link the copied ranges to the original data in this way:

  1. Select the first print area and press Ctrl + C to copy it.
  2. On a new sheet, right click any blank cell and choose Paste Special > Linked Picture.
  3. Repeat steps 1 and 2 for other print areas.
  4. In the new sheet, press Ctrl + P to print all the copied print areas on one page.

Paste the copied range as liked picture.

In case you have a lot of worksheets with exactly the same structure, you will obviously want to output the same rage on paper. The problem is that selecting several sheets disables the Print Area button on the ribbon. Luckily, there is an easy workaround described in How to print the same range in multiple sheets.

If you have to print the same area on multiple sheets regularly, the use of VBA can speed up things.

Set print area in selected sheets as on the active sheet

This macro automatically sets the print area(s) for all selected worksheets the same as on the active sheet. When multiple sheets are selected, the active sheet is the one which is visible when you run the macro.

Sub SetPrintAreaSelectedSheets() Dim CurrentPrintArea As String Dim Sheet As Worksheet CurrentPrintArea = ActiveSheet.PageSetup.PrintArea For Each Sheet In ActiveWindow.SelectedSheets Sheet.PageSetup.PrintArea = CurrentPrintArea span style="color: #8D38C9;">Next End Sub

Set print range in all worksheets as on the active sheet

No matter how many sheets you have, this code defines the print range in a whole workbook in one go. Simply, set the desired print area(s) on the active sheet and run the macro:

Sub SetPrintAreaAllSheets() Dim CurrentPrintArea As String Dim Sheet As Worksheet CurrentPrintArea = ActiveSheet.PageSetup.PrintArea For Each Sheet In ActiveWorkbook.Sheets If Sheet.Name <> ActiveSheet.Name Then Sheet.PageSetup.PrintArea = CurrentPrintArea End If Next End Sub

Set the specified print area in multiple sheets

When working with different workbooks, you may find it convenient if the macro prompts you to select a range.

Here's how it works: you select all the target worksheets, run the macro, select one or more ranges when prompted (to select multiple ranges, hold the Ctrl key), and click OK.
Macro to set print area for multiple sheets

Sub SetPrintAreaMultipleSheets() Dim SelectedPrintAreaRange As Range Dim SelectedPrintAreaRangeAddress As String Dim Sheet As Worksheet On Error Resume Next Set SelectedPrintAreaRange = Application.InputBox("Please select the print area range", "Set Print Area in Multiple Sheets", Type:=8) If Not SelectedPrintAreaRange Is Nothing Then SelectedPrintAreaRangeAddress = SelectedPrintAreaRange.Address(True, True, xlA1, False) For Each Sheet In ActiveWindow.SelectedSheets Sheet.PageSetup.PrintArea = SelectedPrintAreaRangeAddress Next End If Set SelectedPrintAreaRange = Nothing End Sub

How to use the macros

The easiest way is to download our sample workbook with Print Area Macros and run a macro directly from that workbook. Here's how:

  1. Open the downloaded workbook and enable the macros if prompted.
  2. Open your own workbook.
  3. In your workbook, press Alt + F8, select the macro of interest, and click Run.

The sample workbook contains the following macros:

  • SetPrintAreaSelectedSheets - sets the print area in the selected sheets as on the active sheet.
  • SetPrintAreaAllSheets – sets the print area in all sheets of the current workbook as on the active sheet.
  • SetPrintAreaMultipleSheets - sets the specified print area in all the selected worksheets.

Alternatively, you can save your file as a macro-enabled workbook (.xlsm) and add a macro to it. For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.

How to change print area in Excel

Accidentally included irrelevant data or missed selecting a few important cells? No problem, there are 3 easy ways to edit print area in Excel.

How to expand print area in Excel

To add more cells to the existing print area, just do the following:

  1. Select the cells that you'd like to add.
  2. On the Page Layout tab, in the Page Setup group, click Print Area > Add to Print Area.

Expanding the print area in Excel

This is of course the fastest way to modify print area, but not transparent. To get it right, here are a few important things to remember:

  • The Add to Print Area option appears only when the worksheet already has at least one print area.
  • If the cells you are adding are not adjacent to the existing print area, a new print area is created, and it will print as a different page.
  • If the new cells are adjacent to the existing print area, they will be included in the same area and printed on the same page.

Edit print area in Excel by using Name Manager

Every time you set a print area in Excel, a defined range named Print_Area is created, and there is nothing that would prevent you from modifying that range directly. Here's how:

  1. On the Formulas tab, in the Defined Names group, click Name Manager or press Ctrl + F3 shortcut.
  2. In the Name Manager dialog box, select the range you want to change and click the Edit button.

Editing the print area

Change print area via Page Setup dialog box

Another quick way to adjust print area in Excel is to use the Page Setup dialog box. The best thing about this method is that it lets you make any changes you want – modify the print area, delete or add a new one.

  1. On the Page Layout tab, in the Page Setup group, click the dialog launcher (a small arrow in the lower-right corner).
  2. On the Sheet tab of the Page Setup dialog box, you will see the Print area box and can do your edits right there:
    • To modify the existing print area, delete and type the correct references manually.
    • To replace the existing area, put the cursor in the Print area box and select a new range on the sheet. This will remove all the existing print areas so only the selected one is set.
    • To add a new area, press and hold the Ctrl key while selecting a new range. This will set a new print area in addition to the existing one(s).

Changing print area in Excel

How to clear print area in Excel

Clearing the print area is as easy as setting it :)

  1. Open the worksheet of interest.
  2. Switch to the Page Layout tab > Page Setup group and click the Clear Print Area button.

Clear print area in Excel.

Note. If a worksheet contains multiple print areas, all of them will be removed.

How to lock print area in Excel

If you frequently share your workbooks with other people, you may want to protect the print area so that no one could mess up your printouts. Regrettably, there is no direct way to lock the print area in Excel even by protecting a worksheet or workbook.

The only working solution to protect print area in Excel is with VBA. For this, you add the Workbook_BeforePrint event handler that silently forces the specified print area just before printing.

A simpler way would be to set the event handler for the active sheet, but this works with the following caveats:

  • All your worksheets should have the same print rage(s).
  • You will need to select all the target sheet tabs before printing.
Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.PrintArea = "A1:D10" End Sub

If different sheets have different structure, then specify the print area for each sheet individually.

Private Sub Workbook_BeforePrint(Cancel As Boolean) Worksheets("Sheet1").PageSetup.PrintArea = "A1:D10" Worksheets("Sheet2").PageSetup.PrintArea = "A1:F10" End Sub

The above macro sets the print area to A1:D10 for Sheet1 and to A1:F10 for Sheet2. You are free to change these as desired as well as to add more sheets.

To add the event handler in your workbook, carry out these steps:

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. In the Project Explorer window on the left, expand the target workbook's node and double-click ThisWorkbook.
  3. In ThisWorkbook Code window, paste the code.

Lock print area in Excel

Note. For this approach to work, the file needs to be saved as a macro-enabled workbook (.xlsm) and the macro should be enabled on opening the workbook.

Most printing problems in Excel usually relate to the printer settings rather than print area. Nevertheless, the following troubleshooting tips may come in helpful when Excel is not printing the correct data.

Cannot set print area in Excel

Problem: You can't get Excel to accept the print area that you define. The Print Area field shows some odd ranges, but not the ones you have entered.

Solution: Try to clear print area completely, and then select it anew.

Not all columns are printed

Problem: You have selected a certain number of columns for the print area, but not all of them are printed.

Solution: Most likely, the column width exceeds the paper size. Try making the margins narrower or adjust scaling – choose Fit All Columns on One Page.

The print area prints on several pages

Problem: You want the one-page printout, but it prints on several pages.

Solution: Non-adjacent rages are printed on individual pages by design. If you selected just one range but it gets split to several pages, then most probably it is bigger than the paper size. To fix this, try setting all margins close to 0 or choose Fit Sheet on One Page. For more details, please see How to print Excel spreadsheet on one page.

That's how you set, change and clear print area in Excel. I thank you for reading and hope to see you on our blog next week!


  1. Amazing! Thank you very much!

  2. How do you set the print area in MS Excel for android?

    Where are all the help files for Excel for android?

  3. I have multiple sheets with multiple ranges on each that I am exporting to a single pdf. My VBA code is working except I can't find a way to set the print titles on multiple ranges that appear on the same sheet. For example the PDFControls below include a row "Assets Inventory" which contains two ranges to print ("Assets_Crystal_And_Keepsakes, Assets_Electronics"). I'm unable to see how I can change the Titles for each range using my code.

    Any thoughts or ideas would be gratefully welcome.

    Here is a subset of the code and the range containing my print controls:

    For Each R In Range("PDF_Controls").rows
    SheetName = R.Cells(, 1).Value
    SheetPrintRange = R.Cells(, 2).Value
    Sheets(SheetName).PageSetup.PrintArea = SheetPrintRange
    SheetPrintTitle = R.Cells(, 3).Value
    Sheets(SheetName).PageSetup.PrintTitleRows = SheetPrintTitle
    ReDim Preserve pdfSheets(i)
    pdfSheets(i) = R.Cells(, 1).Value
    i = i + 1

    With ActiveSheet.PageSetup
    .LeftHeader = "&""-,Bold""&K0070C0" & Chr(10) & "Personal Information"
    .CenterHeader = _
    "&""-,Bold""&14&K08-024Estate Criteria for Executor" & Chr(10) & "Vital Information and Inventories"
    .RightHeader = "&P"
    .FitToPagesWide = 1
    .FitToPagesTall = False

    End With

    RangeName "PDF_Controls" =

    Sheet Name Print Range Print Title Rows

    Personal Information PI_Personal_Information, PI_Military_Information
    Financial Information Banking_Accounts, Home_Mortgage, Credit_Cards
    Assets Inventory Assets_Crystal_And_Keepsakes, Assets_Electronics CrystalTitles

    Thanks again

    • Sorry about the previous message the PDF Controls did not align.
      "Assets Inventory" is the SheetName
      "Assets_Crystal_And_Keepsakes, Assets_Electronics" are the print ranges
      "CrystalTitles" is one of the range's title rows

  4. I understand how to create more than 1 print area on a single worksheet.

    Is there a way to have each a different page size for each print area be set at the same time (preferably without using VBA)?

  5. I would like to print all the Payslips by "Set Print Area" that are generated into Single Excel Files.

    Is there any VBA code that would allow me to Macro this?

  6. I wish to have a constant number of lines printed in a multi-page worksheet, without having to manually adjust.
    The worksheet is under constant change with lines inserted and deleted but I wish to be able to print the same number of lines per page regardless. eg 35lines each page

    The worksheet has a fixed number of columns and that does not vary - only the addition/deletion of lines.

Post a comment

Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)