Whether you're preparing reports, data summaries, or financial statements, understanding how to effectively set, adjust, and manage print areas can significantly enhance the presentation of your work. In this comprehensive guide, we will delve into the essentials of Excel's print area, covering everything from defining your first print area to advanced techniques like setting consistent print areas across multiple sheets and employing VBA for customization. By mastering these skills, you'll ensure that your Excel documents print exactly as needed, every time.

• Common method to set print area
• Visually define print area in Page Setup dialog box

• Print the same selection in multiple sheets
• Easily set same print area for multiple worksheets with Kutools
• Set same print area to multiple worksheets with VBA

• Add cells to an existing print area
• Add or remove adjacent cells from an existing print area
• Directly edit the current print area
• Replace the current print area with a new one

• Ignore print area
• Lock/protect print area
• Preview the print area

What is print area in Excel?

The print area in Excel is a designated range of cells that you specify to be printed, as opposed to printing an entire worksheet. This feature is incredibly useful for focusing on specific data or sections of your spreadsheet that you want to share or present, ensuring that only relevant information is printed and unnecessary data is excluded from the printout.

When you specify a print area (marked by a blue box as the following screenshot shown), Excel will only print the contents within that designated area. If no print area is set, Excel defaults to printing the entire worksheet.

Set one or more print areas in a sheet

This section will illustrate two methods to help you set one or more print areas in a sheet in Excel.

Common method to set print area

Excel offers a built-in feature “Set Print Area” for setting print areas, commonly used by many users. In this section, we'll guide you on how to apply this feature to set one or more print areas in an Excel worksheet.

1. In a worksheet, select the range of cells you wish to set as a print area. In this case, I select the range A1:C7.
   Tip: To set multiple ranges as separate print areas simultaneously, hold the Ctrl key and select each range one after another.
2. Go to the Page Layout tab, click Print Area > Set Print Area. See screenshot:
   
3. Now, a dark gray border appears around the print area. See screenshot:
   

After designating the selected range as a print area, only this area will be printed by default when you print the worksheet.

Tip: When setting multiple print areas in an Excel sheet, they will be printed on separate pages.

Visually define print area in Page Setup dialog box

You can also define the print area visually in the Page Setup dialog box, which offers a more interactive approach to setting your desired range. Here is how you can do:

1. Go to the Page Layout tab, click the dialog box launcher button in the Page Setup group.
   
2. In the Page Setup dialog box, you need to configure as follows.
   1. Go to the Sheet tab.
   2. Click on the Print area address box, then select the range you want to set as a print area.
      Tip: To set multiple separate print areas at once, hold the Ctrl key and sequentially select each range. The selected ranges will appear in the Print area address box, separated by commas.
   3. Click the OK button.
      

The specified range is now set as a print area. After that, only this area will be printed by default when you print the worksheet.

Tip: When setting multiple print areas in an Excel sheet, they will be printed on separate pages.

Set same print area for multiple sheets

For some worksheets with the same data structure, you may need to set the same print area for these sheets. This section will provide three methods to help you accomplish this task.

Print the same selection in multiple sheets

To print identical areas across multiple sheets, please do as follows.

1. On the current worksheet, select the range you wish to print across multiple sheets.
2. Holding the Ctrl key, click the sheet tabs to select the sheets you want to apply the same print area as the active sheet.
   
3. Click File > Print, change the print Settings to Print Selection, and then click the Print button to start printing.
   

Easily set same print area for multiple worksheets with Kutools

The above method can be used to temporarily print the same area in multiple worksheets. If you often need to print the same area in multiple worksheets, this method is more time-consuming, and Kutools for Excel's Copy Page Setup feature helps you quickly and accurately copy the print area or other page setup configurations from the active worksheet to multiple other worksheets, ensuring uniformity and efficiency with minimal effort.

After installing Kutools for Excel, go to the Kutools Plus tab, select Printing > Copy Page Setup to open the dialog box. Then configure as follows.

1. In the Copy to section, all worksheets (except the active one) are listed. You simply need to check the sheets to which you want to apply the same print area as the active worksheet.
2. In the Options section, check only the Print area option.
   Tip: By default, all options are checked. You can uncheck the Page setup checkbox to uncheck all options and then check only the option you need.
3. Click OK. See screenshot:
   

The print area of the current worksheet is now applied to the specified worksheets. When printing these areas from the worksheets, please click File > Print, in the Settings section, select Print Entire Workbook, then click Print button to start printing.

Set same print area to multiple worksheets with VBA

For advanced users, VBA scripting can automate the process of setting the same print area across multiple sheets. Please do as follows.

1. In any of the worksheets, press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In the Microsoft Visual Basic for Applications window, click Insert > Module, and then insert any of the following VBA code you need to the Module window.
   
   VBA code 1: Set the same print area for all worksheets in the current workbook
   This code will set a specified print area (e.g., "A1:C7") on each worksheet in the workbook.

   Sub SetPrintAreaAllSheets()
   'Updated by Extendoffice 20240205
       Dim ws As Worksheet
       Dim printArea As String
   
       ' Define the print area
       printArea = "A1:C7"
   
       For Each ws In ThisWorkbook.Worksheets
           ws.PageSetup.printArea = printArea
       Next ws
   End Sub
   

   VBA code 2: Set the same print area for specified worksheets in the current workbook
   This code will set the same print area on a specified list of worksheets (e.g., "Sheet1", "Sheet3", "Sheet5").

   Sub SetPrintAreaOnSheets()
   'Updated by Extendoffice 20240205
       Dim ws As Worksheet
       Dim printArea As String
       Dim sheetNames As Variant
       Dim i As Long
   
       ' Define the print area
       printArea = "A1:C7"
   
       ' Array of specific worksheet names to change print area
       sheetNames = Array("Sheet1", "Sheet3", "Sheet5")
   
       ' Loop through all sheet names in the array
       For i = LBound(sheetNames) To UBound(sheetNames)
           ' Check if the sheet exists in the workbook
           If SheetExists(CStr(sheetNames(i))) Then
               ' Set print area on the specified sheet
               Set ws = ThisWorkbook.Worksheets(sheetNames(i))
               ws.PageSetup.printArea = printArea
           End If
       Next i
   End Sub
   
   ' Helper function to check if a sheet exists
   Function SheetExists(sheetName As String) As Boolean
       Dim sheet As Object
       On Error Resume Next
       Set sheet = ThisWorkbook.Sheets(sheetName)
       On Error GoTo 0
       SheetExists = Not sheet Is Nothing
   End Function

3. Press the F5 key to run the code.
4. When printing these areas from the specified sheets, please click File > Print, in the Settings section, select Print Entire Workbook, then click Print button to start printing.
   

Adjust the print area

This section will show you how to adjust the existing print area in an Excel workbook.

Add cells to an existing print area

You can expand the print area by adding more cells to the existing print area. For example, to expand a print area from the range A1:C5 to A1:C7, you can do as follows.

1. Select the cell range A6:C7 that you want to add to the existing print area A1:C5.
   
2. Go to the Page Layout tab, click Print Area > Add to Print Area.
   

The existing print area is now expanded from A1:C5 to A1:C7.

Add or remove adjacent cells from an existing print area

You can add or remove adjacent cells from an existing print area by moving the page breaking line in your worksheet, tailoring the printout to your specific needs.

1. After setting the print area, go to the View tab, select Page Break Preview.
   
2. You will see blue lines indicating the page breaks. The range within the boundary is the print area. To adjust the print area, hover your mouse cursor over the page break line until it turns into a two-sided arrow. Click and drag the page break line to include or exclude cells in the print area.
   

Directly edit the current print area

You can directly modify the print area by visually adjusting the cell reference in the Page Setup dialog box.

1. Go to the Page Layout tab, click the dialog box launcher button (the small arrow at the bottom right corner of the group) to open the Page Layout dialog box.
   
2. In the Page Setup dialog box, navigate to the Sheet tab. Here, the cell references of the print areas of the current worksheet are displayed in the Print area box. You can directly edit the cell references of the print area in this box. And finally click OK to save the changes.
   

Replace the current print area with a new one

If you need to update the print area in your worksheet, follow these steps to replace the existing print area with a new one quickly.

1. Select the cell range you want to set as the new print area.
2. Go to the Page Layout tab, click on Print Area > Set Print Area.
   

This action will replace any existing print area(s) with the newly selected range.

Clear print area

To clear the set print area in the current worksheet, please do as follows.

1. Under the Page Layout tab, click on Print Area > Clear Print Area.
   

Then all print areas in the current worksheet are cleared immediately.

More tips for print area

This section lists some tips and tricks that you may need when working with print areas in Excel.

Ignore print area

If you've set a print area in a worksheet but occasionally need to print the entire sheet, you can enable the Ignore Print Area option in the print settings. This feature overrides the set print area, allowing the entire sheet to be printed as needed.

1. Click File > Print to open the Print section.
2. In the Print section, click to open the Settings drop-down list, and then select the Ignore Print Area option in the menu. A check mark will appear before the option once it's enabled.
   

After clicking the Print button, the entire worksheet will be printed, ignoring the set print area.

Lock/protect print area

By default in Excel, protecting a worksheet does not directly protect the print area settings. This means that even though the worksheet is protected, the user with the workbook can still change or clear the print area. If you often share workbooks and want to protect the designated print area, the VBA code in this section can help you.

1. In the workbook you want to protect the print area in a certain sheet, press the Alt + F11 keys to open the Microsoft Visual Basic for Applications window.
2. In this opening window, double click on ThisWorkbook project in the left pane, and then enter the following VBA code to the Code window.
   
   VBA code: Lock or protect the print area

   Private Sub Workbook_Open()
   'Updated by Extendoffice 20210206
       SetPrintArea "commission  IFS", "A1:C7" ' Specify the sheet name and the print area
   End Sub
   Sub SetPrintArea(sheetName As String, printArea As String)
       Dim ws As Worksheet
       Set ws = ThisWorkbook.Worksheets(sheetName)
       ws.PageSetup.printArea = printArea
   End Sub
   

   Note: In this VBA code, replace “commission IFS” with the name of your worksheet and "A1:C7" with the print area you wish to set.
3. Save the code and press the Alt + Q keys to return to the worksheet, and then click File > Save as > Browse.
4. In the opening Save As dialog box, choose a destination folder, rename the file as you need, select Excel Macro-Enabled Workbook from the Save as type drop-down list, and finally click the Save button.
   

Each time the workbook is opened, this code will ensure that the print area is set to your specified area. It's not a perfect solution, as it cannot stop users from changing the print area during a session, but it does ensure that the correct print area is used every time the workbook is opened.

Preview the print area

In this section, I'll show you how to preview the print area once it's been set up, ensuring your document is ready for printing exactly as intended.

After setting up the print area, go to the View tab, click Page Break Preview in the Workbook Views group.

Result

The worksheet will switch to the Page Break Preview view, where you can see the print area delineated by the blue page break boundaries. Refer to the screenshot below for a visual representation.

In conclusion, mastering the print area in Excel is a crucial skill for effectively managing how your data is presented in printed form. With the skills in this comprehensive guide, you can ensure that your Excel sheets are always print-ready, exactly as you intend. For those eager to delve deeper into Excel's capabilities, our website boasts a wealth of tutorials. Discover more Excel tips and tricks here.

Related Articles