How do I lock the Print area in Excel?

Before you print, learn these tricks

Table of Contents Show

Before you print, learn these tricks
What to Know
Set One or More Excel Print Areas
How to Add Cells to an Excel Print Area
How to Clear a Print Area in Excel
View Your Excel Print Areas
Reasons to Set Print Areas in Excel
1. Worksheet Protection to limit the screen scroll area
2. Turning off Vertical and Horizontal scroll bars in Excel Options
3. Locking the scroll with VBA
How do I lock the print in Excel?
How do you set a permanent print area in Excel?
How do you lock cells in Excel so they print on every page?
Why is Excel print area not working?

What to Know

Set print area: Select cells > go to Page Layout tab > Page Setup > Print Area > select Set Print Area.
Set multiple: Hold Ctrl while selecting areas > Page Layout > Page Setup > Print Area > select Set Print Area.
Add cells to area: Select cells to add > Page Layout > Page Setup > Print Area > Add to Print Area.

This article explains how to set the print area for standard paper sizes in Excel 2019, 2016, 2013, 2010, 2007, and Excel for Microsoft 365.

Set One or More Excel Print Areas

Open a worksheet and select the cells that you want to be part of the print area.
To set more than one print area, hold down the Ctrl key and select the areas you want to print. Each print area gets a separate page.
Go to the Page Layout tab.
In the Page Setup group, click Print Area and select Set Print Area from the drop-down menu.
When you save your workbook, it also retains the print areas.

How to Add Cells to an Excel Print Area

Once you’ve set a print area, you can add adjacent cells to it, if you made a mistake the first time or input additional data.

If you try to add cells that are not adjacent to your print area, Excel creates a new one for those cells.

On your worksheet, select the adjacent cells you want to add to the existing print area.
Go to the Page Layout tab of the Ribbon.
In the Page Setup section, click Print Area > Add to Print Area.

How to Clear a Print Area in Excel

You can also change print areas that you no longer need or created by mistake.

Click a cell in a print area that you want to remove.
Go to the Page Layout tab.
In the Page Setup section, click Print Area > Clear Print Area.

View Your Excel Print Areas

You can see your print areas and preview them before printing your spreadsheet.

To see your print areas:

Go to the View tab.
In the Workbook Views section, click Page Break Preview.
Click Normal in the Workbook Views section to return to the spreadsheet.
When you’re ready to print, click File > Print.
To the right of the print options, you can scroll through each printable page in the document.

Reasons to Set Print Areas in Excel

If you print out a large spreadsheet without setting print areas, you run the risk of outputting hard to read, unformatted pages. If the sheet is wider or longer than the paper your printer is using, you’ll end up cutting off rows and columns. It won’t look pretty. Setting print areas gives you control over what each page looks like, so it’s easy to read and understand.

Thanks for letting us know!

Get the Latest Tech News Delivered Every Day

Often there are times when a spreadsheet developer has the need to set the screen scroll lock area in Excel restricting where the user can scroll and has visibility. A perfect example would be Excel Dashboards.

****Scroll to the bottom for the video*****

Setting the screen scroll lock area improves the user experience and can make a dashboard or other spreadsheet application have more of that App like feel.

In this article, we will look at 3 ways to set the screen scroll lock area in Excel. The first using Worksheet protection, the second using advanced excel options, and the third, using VBA.

Our Example

Our Dashboard contains information from cells A1 to U40 and we want to lock the screen so the user is unable to scroll beyond this point.

1. Worksheet Protection to limit the screen scroll area

When you have protected a worksheet and workbook, the user retains the ability to scroll to the far side or bottom of the worksheet. Worksheet or workbook protection does not lock the scroll unless you first ensure the cells outside the scroll area are locked and cannot be selected. However, when you are creating a dashboard or other spreadsheet application, to improve the user experience, it is often a good idea to lock the scroll. This way the user cannot ‘lose’ the dashboard because they have scrolled too far, and it will give the dashboard a more app-like feel.

Start by selecting the first column of blank cells after the dashboard.

Then press Ctrl + Shift + Right arrow to select all the cells to the right of the sheet. Right-click and select Hide.

This will hide all columns to the right of the dashboard and the user will no longer be able to scroll further to the right than the dashboard.

We can also lock the scroll down in the same way. Select the first row below the dashboard and then press Ctrl + Shift + Down arrow to select all the rows to the bottom of the sheet. Then right-click and select hide.

The user will no longer be able to scroll past the area of the dashboard.

2. Turning off Vertical and Horizontal scroll bars in Excel Options

Using Excel advanced options, it is possible to remove the scroll bars from Excel workbooks.

Open Excel options and go to Advanced.

Scroll down to find Display options for this workbook

Untick Show horizontal scroll bar and Show vertical scroll bar and press ok.

When you return to the workbook the scroll bar will no longer be visible.

3. Locking the scroll with VBA

You can also use VBA to lock the scroll on a worksheet or dashboard. If the developer tab is not available, we need to turn this on. Go to File, then Options.

In options, select customize ribbon and ensure the developer box is ticked.

With the developer tab now available, select Visual Basics Editor.

In the visual basic’s editor, select new from the tab, and then select a new Module.

Then copy and paste the code below into the New module box. Ensure that you have updated the code to lock the screen scroll to the cells you want to be locked.

Sub SetSpecificScrollArea()
DIM ws As Worksheet
Set ws = ActiveSheet
Ws.ScrollArea = “$A$1:$U$40”
End Sub

Close the visual basics editor and return to the worksheet you wish to apply the scroll lock.

From the developer tab, select Macros. This will open a box showing any macros available in the workbook. Select the macro and press run.

Once the macro has run, the user will no longer be able to scroll past the designated cells.

Using the developer tab, you can also confirm, remove, and set the scroll area from properties.

How do I lock the print in Excel?

Workaround.

Open your workbook..

On the File menu, point to Print, and then click Properties. ... .

Click the Advanced button..

In the Job Type list, select Secure Print..

Enter the PIN, confirm the PIN, and then click OK..

Click OK to close the Advanced Properties dialog box..

Click OK in the Printer Properties dialog box..

How do you set a permanent print area in Excel?

Set one or more print areas.

On the worksheet, select the cells that you want to define as the print area. Tip: To set multiple print areas, hold down the Ctrl key and click the areas you want to print. ... .

On the Page Layout tab, in the Page Setup group, click Print Area, and then click Set Print Area..

How do you lock cells in Excel so they print on every page?

On the Page Layout tab, in the Page Setup group, click Page Setup. Under Print Titles, click in Rows to repeat at top or Columns to repeat at left and select the column or row that contains the titles you want to repeat. Click OK.

Why is Excel print area not working?

If your Excel spreadsheet isn't printing properly, clearing or setting a print area can often help. Sometimes the wrong print area has already been set, in which case you want to clear it. To do this from the Page Layout Ribbon, click the Print Area icon and select Clear Print Area.