If you have a worksheet containing a large dataset and only want to print a specific portion(s) of the data, you can set the print area(s).
The print area is the particular range of cells that you designate to print when you send the worksheet to the printer.
Setting the print area in Excel enables you to print specific sections of your reports, saving paper and ink or toner.
In this tutorial, I will show you several ways to set single and multiple print areas in Excel and how to modify and clear them.
How to Set a Single Print Area in Excel
To set a single print area in Excel, you can use the Set Print Area command, Page Setup dialog box, Page Break Preview feature, Print Selection setting, and Excel VBA code, as described in this section.
Method #1.1: Set the Print Area Using the Set Print Area Command
The Set Print Area command on the Page Setup group of the Page Layout tab allows you to set a print area in Excel.
Suppose you have the report below running into several pages:
If you want to print only the first ten rows of the report, you can use the steps below to set the print area to the first ten rows:
- Select the first ten rows of the report.
- Click the Page Layout tab, open the Print Area drop-down on the Page Setup group, and click the Set Print Area option:
- Press CTRL + S to save the print area.
The steps above will set the print area to the first ten rows of the report, ensuring only those ten rows are printed when you send the worksheet to the printer.
However, viewing and verifying the print area is helpful before you send the worksheet to the printer.
Also read: How to Fit to Page in Excel (Print on One Sheet)
Method #1.2: Use the Page Setup Dialog Box to Set the Print Area in Excel
You can use the Page Setup dialog box to set the print area and customize it by adding features such as repeating rows and columns.
Suppose you have the report below that runs into several pages:
If you want to print only the first ten rows of the report, you can use the steps below to set the print area to the first ten rows:
- On the Page Layout tab, click the Page Setup dialog box launcher on the bottom left corner of the Page Setup group.
- On the Page Setup dialog box that appears, open the Sheet tab and click the range selector button at the end of the Print area text box.
The above step will collapse the dialog box so that you can see the cell range you want to select.
- Mouse-select the first ten rows of the dataset and click the range selector button again to expand the dialog box.
- Enter your desired customizations, if any, on the expanded Page Setup dialog box and then click OK.
- Press CTRL + S to save the print area.
The steps above will set the print area to the first ten rows of the report so that only the specified area is printed when you send the worksheet to the printer.
Before sending the worksheet to the printer, view and confirm the print area using any of the methods described in the section ‘How to View the Print Area.’
Also read: How to Print Gridlines in Excel?
Method #1.3: Use Print Selection Setting to Set Print Area in Excel
You can use the Print Selection setting to set a print area and instruct the printer to print only the worksheet’s selected area(s).
Suppose you have the multi-page report below:
You can use the steps below to set the print area to the first fifteen rows of the report:
- Select the first fifteen rows of the dataset.
- Click File to open the Backstage view.
- On the Backstage view, click the Print category on the left sidebar, open the first drop-down under the Settings area, and choose Print Selection.
On the Print Preview window, you can see what will be printed or the print area.
This method does not allow you to save the print area for future use. Consequently, you must repeat the process to set the print area in the future.
Method #1.4: Adjust Page Breaks to Set Print Area in Excel
Page breaks in Excel indicate where pages start and end when you print your worksheet. By adjusting the page breaks, you can set a print area.
Suppose you have the dataset below running into several pages:
You can use the steps below to set the first ten rows of the dataset as the print area:
- Open the View tab and click the Page Break button on the Workbook Views group.
- Drag the thick blue line at the bottom of the report to the bottom of the tenth row.
The results are shown below:
- Press CTRL + S to save the print area for future use.
When you send the worksheet to the printer, only the print area bounded by the thick blue lines will be printed.
Also read: How to Change Page Orientation in Excel (for Printing)
Method #1.5: Use VBA Code to Set Print Area in Excel
You can use Excel VBA code to automate setting the print area.
Suppose you have the multi-page report below on Sheet1 of the current workbook:
You can use the VBA code below to set the first ten rows of the dataset as the print area:
Sub SetPrintArea()
Dim ws As Worksheet
Dim printRange As String
Set ws = ThisWorkbook.Sheets("Sheet2")
printRange = "A1:E10"
ws.PageSetup.PrintArea = printRange
MsgBox "Print area set to " & printRange, vbInformation, "Print Area Set"
End SubWhen you execute the code, it sets the print area to the cell range A1:E10 and displays an appropriate message box.
Also read: How to Print Row Numbers in Excel?
How to Set Multiple Print Areas in Excel
In this section, I will show you how to set multiple print areas in Excel using the Set Print Area command on the Page Layout tab and VBA code.
Method #2.1: Use the Print Area Command to Set Multiple Print Areas in Excel
Suppose you have the dataset below running into many pages:
You want to set three print areas: A1:E5, A7:E11, and A13:E17.
You can use the steps below to achieve the goal:
- Select the cell range A1:E5, press and hold down the CTRL key, and select the cell ranges A7:E11 and A13:E17 as depicted below:
- Click the Page Layout tab, open the Print Area drop-down on the Page Setup group, and click the Set Print Area option:
- Press CTRL + S to save the print areas.
To view the three print areas you have set, open the View tab and click the Page Break Preview command button on the Workbook Views group.
You will see the three print areas, each bounded by solid blue lines and overlaid with the page number, as depicted below:
If you send the worksheet to the printer, each print area will be printed separately. However, in this case, the second and third print areas will not have column titles, which can confuse the reader.
You can do the following to ensure all the print areas have column titles:
- On the Page Layout tab, click the Page Setup dialog box launcher on the bottom left corner of the Page Setup group.
- On the Page Setup dialog box that appears, open the Sheet tab and click the range selector button on the Rows to repeat at top text box.
Notice that the above step collapses the dialog box so that you can see the range to select.
- Click the header row to enter its reference on the Rows to repeat at top text box, and click the range selector button to expand the dialog box.
- Click OK on the Page Setup dialog box.
When you send the worksheet to the printer, Excel will repeat the header row at the top of each printed page.
Method #2.2: Use VBA Code to Set Multiple Print Areas in Excel
You can use VBA code to automate the setting of multiple print areas in Excel.
Suppose you have the dataset below running into several pages on Sheet1 of the current workbook:
You want to set three print areas: A1:E5, A7:E11, and A13:E17.
You can use the VBA code below to accomplish the task:
Sub SetMultiplePrintAreas()
Dim ws As Worksheet
Dim printArea1 As Range
Dim printArea2 As Range
Dim printArea3 As Range
Dim multiplePrintAreas As String
Set ws = ThisWorkbook.Sheets("Sheet1")
Set printArea1 = ws.Range("A1:E5")
Set printArea2 = ws.Range("A7:E11")
Set printArea3 = ws.Range("A13:E17")
multiplePrintAreas = printArea1.Address & "," & printArea2.Address & "," & printArea3.Address
ws.PageSetup.PrintArea = multiplePrintAreas
MsgBox "Print areas set to " & multiplePrintAreas, vbInformation, "Print Areas Set"
End SubWhen you execute the code, it sets the three print areas on Sheet1 and displays an appropriate message box as depicted below:
How to View the Print Area
After setting the print area, you can use the following methods to view and confirm the print area:
Way #1: Check the Print Area Outline
After setting the print area, Excel highlights it with a grey boundary, as seen below:
Note: If you do not see the grey boundary around the print area, you can make it visible by doing the following:
- On the View tab, click the Page Layout button on the Workbook Views group.
- On the View tab, click the Normal button on the Workbook Views group.
The above steps will make the grey boundary around the print area visible.
Way #2: Use Print Preview
You can use the steps below to open the Excel Print Preview window to view the print area:
- Click the File tab to open the Backstage view.
- Click Print on the left sidebar to open the Print Preview window on the right to see what will be printed.
Way #3: Use Page Break Preview
You can switch to the Excel Page Break Preview to view the print area using the below steps:
- On the View tab, click the Page Break Preview command button on the Workbook Views group.
You will see the print area bounded by solid blue lines and overlaid with a page number, as shown below:
Way #4: Use the Name Box
Excel interprets a print area as a named range and lists it on the Name box. Therefore, you can view a print area on an Excel sheet using the steps below:
- Click the down arrow on the Name Box.
- Click Print_Area on the list that appears.
The print area will be selected as depicted below:
How to Modify the Print Area
After defining a print area, you may need to modify it by adding or removing cells. The techniques described below can help you achieve that.
Technique #1: Use the Add to Print Area Command to Modify the Print Area
The Add to Print Area command on the Page Setup group of the Page Layout tab allows you to add to a print area in Excel. Note that the Add to Print Area command only becomes available after setting a print area on the worksheet.
Suppose you have the print area below on a given worksheet:
You can use the steps below to add two more rows to the print area:
- Select the cells you want to add to the print area.
- Click the Page Layout tab, open the Print Area drop-down on the Page Setup group, and click the Add to Print Area option:
The highlighted cells are added to the print area as shown below:
Note: If the additional cells are adjacent to the print area, they will be merged into the original print area to create a larger print area to be printed on one page. However, if they are not directly next to it, they will form a new print area and be printed separately.
Technique #2: Use the Page Break Preview Feature to Modify the Print Area
You can adjust the solid blue page break lines to expand or contract a print area on a worksheet.
Suppose you have the print area below on a given worksheet:
You can use the steps below to remove the Brand column from the print area:
- Open the View tab and click the Page Break Preview button on the Workbook Views group.
- Drag the solid blue line on column E’s right edge to column D’s right edge.
The print area is adjusted accordingly.
Technique #3: Use the Name Manager Feature to Modify the Print Area
You can use Excel’s Name Manager feature to expand or contract a print area on a worksheet.
Suppose you have the print area below on Sheet2 of the current workbook:
You can use the steps below to remove the last five rows from the print area:
- On the Formulas tab, click the Name Manager option on the Defined Names group.
- Select the print area you want to modify on the Name Manager feature that appears and click the Edit button.
- On the Edit Name dialog box that appears, adjust the cell range on the Refers to text box to A1:E5 and click OK.
- Close the Name Manager feature.
The above steps will contract the print area accordingly.
Technique #4: Use the Page Setup Dialog Box to Modify a Print Area
You can use the Page Setup dialog box to modify the print area on a worksheet.
Suppose you have a print area on a worksheet and want to remove some cells from it. You can use the steps below:
- On the Page Layout tab, click the Page Setup dialog box launcher on the bottom left corner of the Page Setup group.
- On the Page Setup dialog box that appears, open the Sheet tab, enter a new print area on the Print area box, and click OK.
The above steps will modify the print area accordingly.
Technique #5: Modify the Target Print Range in the VBA Code
If you have used VBA code to set a print area in Excel, you can modify the target print range within the code and rerun it to either expand or contract the print area.
How to Clear the Print Area
If you initially set a specific print area but later decide you want to print the entire worksheet, you can clear the print area using the following approaches:
Approach #1: Use the Clear Print Area Command to Clear the Print Area
Here are the steps to clear the print area(s) using the Clear Print Area Command:
- Click the tab of the worksheet from which you want to clear the print areas.
- Click the Page Layout tab, open the Print Area drop-down on the Page Setup group, and click the Clear Print Area option:
The above steps will remove all print areas from the worksheet. You can use the Name Manager feature if you have set multiple print areas and only want to clear a few and leave others.
Approach #2: Use the Name Manager Feature to Clear the Print Area
Suppose you have the following two print areas on a given worksheet:
You can clear the second print area only using the steps below:
- On the Formulas tab, click the Name Manager option on the Defined Names group.
- On the Name Manager feature that appears, select the print area containing the print area you want to clear.
- On the Edit Name feature that appears, select the second print area (including the preceding comma) on the Refers to box, press Backspace to remove it, and press OK.
- Close the Name Manager feature.
The above steps will clear the second print area, leaving the first one intact.
Approach #3: Use VBA Code to Clear the Print Area
In VBA, you can assign an empty string to the Print Area property of the PageSetup object to clear the print area.
Suppose you have print areas on Sheet1 of the current workbook. You can use the following code snippet to clear print areas on the sheet:
Sub ClearPrintArea()
Worksheets("Sheet3").PageSetup.PrintArea = ""
End SubApproach #4: Ignore Print Area
The ‘Ignore Print Area’ setting in Excel allows you to print the entire worksheet, regardless of any previously defined print areas.
This setting can be helpful when you want to print everything on the sheet without clearing or modifying the existing print area settings.
You can use the steps below to enable the ‘Ignore Print Area’ setting:
- Click the File tab to open the Backstage view.
- On the Backstage view, click the Print category on the left sidebar, open the first drop-down under the Settings area on the right, and select the Ignore Print Area option.
Notice on the Print Preview window that any defined print area no longer appears.
In this tutorial, I showed you how to set, view, modify, and clear print area(s) in Excel. I hope you found the tutorial helpful.
Other Excel articles you may also like:
