If you wish to print selective areas from your worksheet to save paper sheets or hide sensitive information, you should read this quick Excel tutorial.
Here, I’m explaining to you several methods for setting a print area in Excel with steps and illustrations.
What Is an Excel Print Area Setting?
Often, you might need to print a workbook or worksheet in Excel. If you hit the Ctrl + P keys on your worksheet and click on the Print button, Excel prints all the worksheet’s content on multiple pages. Also, the output might not always be organized and easily readable.
To help you avoid such situations, Microsoft added the Print Area command for setting a print area in Excel, expanding the print area, or clearing the print area selection.
However, the Print Area command isn’t the only method to set print areas in Excel. You can also use the Page Break tool or special printing properties in your printer app.
Set Print Area in Excel Using Print Area Command
The Print Area button in the Page Setup block of the Page Layout tab is the default tool for setting a print area in Excel.
Highlight a cell range you want to print exclusively on the paper sheet. Now, press Alt + P + R + S to create the print area.
Hit the Ctrl + P keys to bring up the Print dialog. There, you should see a print preview of the cell range you’ve selected on your worksheet. Now you can print the page if you like.
Don’t forget to click the Save button on the worksheet to save the print area for the future.
Set Print Area in Excel Using Page Setup Dialog
The Print Area command doesn’t allow you to customize the area selection for printing. You might want more options when setting up a print area. In this scenario, you can use the Page Setup dialog.
So, head over to the worksheet from which you want to print a part of its content. Now, press Alt + P + SP to launch the Page Setup dialog.
On the Page Setup dialog, go to the Sheet tab to find various customizations for print area setup.
Here, you can do the following:
- By clicking the up arrow on the Print area field, you can choose a new cell range.
- If you need to print titles, you can utilize the up arrows of the fields like Rows to repeat at top and Columns to repeat at left.
- Suppose, the page you’re printing contains multiple tables or cell ranges from different parts of the worksheet, you can set up the order of the content by choosing a Page order between Down, then over and Over and then down options.
- You’ll find more customizations like Gridlines, Black and white, Row and column headings, etc.
Set Print Area in Excel Using Print Selection
If you’ve forgotten to select a print area in Excel and are on the Print preview window, no worries. You can use the Print Selection feature of the Excel Printing Settings. However, you must have selected the target cell range, object, visualization, etc., before you can use this feature.
On your worksheet, highlight an area or object for printing.
Click the Ctrl + P buttons to bring up the Print dialog.
There. click on the Settings drop-down menu and choose the Print Selection option.
The Print preview should show the content you’ve selected on your worksheet.
If the preview looks good, you can continue with the printing process.
Like the Print Area command, this method doesn’t save the selected cell range for future printing.
Set Print Area in Excel Using Page Break
Another easy way to print a specific area from your worksheet is the Page Break feature of Microsoft Excel.
On your worksheet, go to the View tab and click on the Page Break Preview button inside the Workbook Views block.
You should see page-wise segmentation of your worksheet with thick blue borders.
Drag the side and bottom thick lines in or out to choose a print area on your worksheet.
If you go to Print preview now, you shall see that Excel is printing the content within the Page Break Preview borders.
How to Add Print Area to Existing Selection
Suppose, you had selected a print area in your worksheet in the past. Recently, have updated the worksheet and need to amend the print area so that the updated part also prints with the existing content of your worksheet. In this scenario, you can utilize the Add to Print Area option of the Print Area tool.
Go to your Excel spreadsheet and highlight the new cell ranges to extend the existing print area.
Click on the Add to Print Area command inside the Print Area dialog of the Excel Page Layout tab.
There’s a catch though! If you select cell ranges that are adjacent to the current print area, Excel will include those in the same paper sheet.
However, if you select cell ranges that aren’t continuously adjacent to the existing print area, Excel will print the cell ranges on different paper sheets.
Setting Multiple Print Areas
Suppose you want to create multiple print areas on your worksheet for quick printing on different sheets, you can do that using the Ctrl-click on Excel.
So, go to your worksheet and highlight the first cell range to be included in the print area.
Now, press the Ctrl key and highlight as many objects and cell ranges as you want.
Create your print areas using the Page Layout > Print Area > Set Print Area button.
That’s it! You can now check the print preview of the worksheet. You’ll see that Excel has organized the selected sections on different pages for printing.
📒 Read More: Best Ways to Select Multiple Cells in Excel
Setting a Print Area in Excel Using Excel VBA
If you’re a bit reluctant to set up print areas manually, you can use Excel VBA to automate the task. VBA scripts are available to select print areas in different ways, like for the selected range, on multiple sheets, multiple print areas on the same sheet, and so on.
If you need a VBA script for a different scenario or print area selection, don’t hesitate to write a few lines in the comment box.
You might ask how to create a VBA macro using the given scripts. Don’t worry! Read this article:
📒 Read More: How To Use The VBA Code You Find Online
Find below a set of three different scripts for the most commonly used scenarios of print area selection:
Set Print Area For Selected Range in Active Worksheet
Sub PrintAreaSelectedRange()
Dim selectedRange As Range
' Check if any range is selected
If TypeName(Selection) = "Range" Then
Set selectedRange = Selection
' Set the print area for the entire selected range
ActiveSheet.PageSetup.PrintArea = selectedRange.Address
MsgBox "Print area set successfully for the entire selected range.", vbInformation
Else
MsgBox "No range selected. Please highlight a range before running this script.", vbExclamation
End If
End Sub
Before executing this VBA script, select the object or cell range for inclusion in the print area. A dialog box will appear if the process is successful.
Set Print Area for Selected Range in All Worksheets
Sub PrintAreaSelectedRangeMultipleWorksheets()
Dim selectedRange As Range
Dim ws As Worksheet
' Check if any range is selected
If TypeName(Selection) = "Range" Then
Set selectedRange = Selection
' Loop through all worksheets in the workbook
For Each ws In ThisWorkbook.Sheets
' Set the print area for the entire selected range on each worksheet
ws.PageSetup.PrintArea = selectedRange.Address
Next ws
MsgBox "Print area set successfully for the entire selected range on all worksheets.", vbInformation
Else
MsgBox "No range selected. Please highlight a range before running this script.", vbExclamation
End If
End Sub
This script is almost the same as the previous one. However, this time you select a print area for a similar cell range or object in all the worksheets.
Set print Area for Multiple Cell Ranges on Active Worksheet
Sub MultiplePrintArea()
Dim printArea As String
Dim ws As Worksheet
Dim selectedRange As Range
' Prompt the user to select the print area using an input box
On Error Resume Next
Set selectedRange = Application.InputBox("Select the print area for multiple worksheets.", Type:=8)
On Error GoTo 0
' Check if the user selected a range
If Not selectedRange Is Nothing Then
' Convert the selected range to an address string
printArea = selectedRange.Address
' Loop through all worksheets in the workbook
For Each ws In ThisWorkbook.Sheets
' Set the print area for each worksheet
ws.PageSetup.PrintArea = printArea
Next ws
MsgBox "Print area set successfully for multiple worksheets.", vbInformation
Else
MsgBox "No print area selected. Please try again.", vbExclamation
End If
End Sub
After executing this macro, you’ll see an input box. Highlight the first cell range or object on the worksheet, put a comma, and go on adding the next cell range for the print area. Repeat the steps for as many cell ranges or objects as you need.
Setting a Print Area in Excel Using Office Scripts
The Excel VBA-based automation only works on the desktop Excel app. If you plan to share the workbook online and offline, you can choose Office Scripts to automate the printing area setup.
Provided that you’ve got Microsoft 365 Business Standard or a better subscription, open your Excel worksheet and click on the Automate tab. There, click on the New Script button inside the Scripting Tools command block.
A code editor will appear on the right side of the Excel app. There, copy and paste the following script:
function main(workbook: ExcelScript.Workbook) {
// Get the current worksheet and the selected range.
let selectedSheet = workbook.getActiveWorksheet();
let selectedRange = workbook.getSelectedRange();
// Set the print area to the selected range.
selectedSheet.getPageLayout().setPrintArea(selectedRange.getAddress());
}
Click the Save script button to save it for future use. Now, click the Run button to execute the Office Scripts code. Don’t forget to highlight the target cell range on the worksheet for print area selection.
This script will help you in setting a print area in Excel only for the selected range.
Expert Tips on Excel Print Area
How to Clear Print Area
Go to Page Layout > Page Setup and click on the Print Area button. There, you should see the Clear Print Area option. Click on it to remove all existing print areas from your worksheet.
How to Ignore Print Area
Suppose you’ve strategically created print areas on your worksheets. However, you must take a printout of the whole worksheet. In this scenario, you don’t need to clear these print areas. You can use the Ignore Print Area option on Print Settings.
Open a worksheet that has print areas. Press Ctrl + P to bring up the Print preview dialog.
Click on the Settings drop-down and choose the Ignore Print Area command from the drop-down list.
Editing a Print Area in Excel
If you wish to edit the cell ranges in a print area, go to Formulas > Defined Names. There, click on the Name Manager button.
The Name Manager dialog will open. Under the Name column, find the Print Area item and double-click.
The Edit Name dialog will open. On this dialog, click the up arrow of the Refers to field to open the range selection box.
Clear the existing selections and choose new ranges or objects separated by commas. Click on the down arrow to go back to the Edit Name dialog.
Click OK to save the changes you’ve made.
Conclusions
So, these are all tried and tested methods to print one or multiple selections in Excel to save paper, organize printing, or hide sensitive information from the worksheet.
Use the method that suits your Excel usage style and expertise. You might want to give the Excel VBA-based method a try because it enables you to automate the process for several types of Excel print area settings.
If the article helped you or you want to add a method you think is more efficient, comment below.
0 Comments