8 Ways to Lock a Sheet in Microsoft Excel

Are you ready to take your Excel skills to the next level by learning how to lock a sheet in Excel? This comprehensive tutorial will guide you through the process step by step.

Protecting a worksheet is often essential for a variety of reasons, ranging from safeguarding sensitive data to ensuring error-free collaboration among multiple users. Fortunately, Excel offers a built-in feature that allows you to easily lock your sheets, providing a layer of security and peace of mind.

Stick around until the end to discover various convenient and contextual ways to lock Excel sheets.

Lock Excel Sheet Using Protect Sheet

Protect Sheet is the default command in Excel that allows you to lock a worksheet. It’s available in the Review tab on the Excel ribbon menu.

Protect sheet dialog
Protect sheet dialog

To try out this method, go to the target worksheet. Now, navigate to the Review tab and click on the Protect Sheet button inside the Protect commands block.

The Protect Sheet dialog shall show up. Here, you can type a password inside the Password to unprotect sheet field to strengthen the worksheet security. Now, click OK to save the changes.

You’ll get the Confirm Password dialog if you’ve locked the sheet using a password. Reenter the password and click OK.

Locked worksheet
Locked worksheet

The next time you try to edit any content of it, you’ll get a warning that the worksheet is locked.

There are alternative ways to access the Protect Sheet command. For example, you can press the Alt + R + P + S keys to bring up Protect Sheet.

Right-click menu
Right-click menu

You can also right-click on the sheet name tab at the bottom and find the Protect Sheet option in the context menu.

Use Save As to Lock Excel Sheet

Suppose, your Excel workbook contains templates of datasets, dashboards, tables, objects, and more for learning and practice purposes. You’d like the recipients to open the workbook and make changes to it in the read-only mode. When the recipient wants to save the changes they have made, Excel shall ask them to create a new workbook instead.

You can achieve this by locking all the worksheets in the workbook using the Save As dialog.

Save As dialog
Save As dialog

When you’re on the specific workbook to which you want to add this special sheet-locking feature, press Alt + F + A + O to bring up the Save As dialog.

General Options
General Options

Click on the Tools drop-down arrow in the Save As box to expand its menu. There, you shall see the General Options command. Click on that.

Read and write passwords
Read and write passwords

A pop-up dialog will show up. There, enter a secret passphrase inside the Password to open and the Password to modify fields. Click OK to save the changes you’ve made. You’ll need to reenter passwords in the next dialog boxes.

Confirm Save As
Confirm Save As

Click the Save button on the Save As dialog and click Yes on the Confirm Save As pop-up to implement the changes.

Read Only button
Read Only button

You can now share the workbook with the intended audience along with the password to access the Excel file in read-only. They’ll see a Read Only button where the password goes.

Now, when it’s time to save the changes made on the locked workbook, Excel shall show a pop-up same as the above dialog box. It means a new Save As dialog shall open so the recipient can create a new workbook with the changes upon clicking OK on the warning box.

Use Data Validation to Lock Excel Sheet

Suppose, you want data entry operators to enter specific values in your Excel worksheet. In this scenario, you can lock the sheet with Data Validation rules that only accept preconfigured values. So, data entry agents won’t be able to input any wrong data.

Example data entry sheet
Example data entry sheet

The above worksheet shows a data entry form where sales reps only need to enter the quantities sold in the Qty column to get the total sales price of the listed product. You’ve figured out that the quantity value shall only be in whole numbers ranging from 0 to 9999999999.

Example data entry sheet
Example data entry sheet

Firstly, hide the unused columns and rows of the worksheet using the Hide command.

For rows, click on the first unused row number, like 9 in this worksheet, and press Ctrl + Shift + Down Arrow. Now, right-click on any of the row numbers and select Hide.

Hide columns in worksheet
Hide columns in a worksheet

To hide all columns, click on the column letter of the first unused column, like E in this worksheet. Now, press Ctrl + Shift + Right Arrow to select all the unused columns. Right-click on any of the column letters you selected and choose Hide from the context menu.

Removed unwanted rows and columns
Removed unwanted rows and columns

So far, you’ve eliminated all unused cells from the worksheet to avoid the risk of entering data in the wrong rows and columns.

Unlock selected cell range
Unlock selected cell range

Highlight the empty cells below the Qty column header and press Ctrl + 1 to bring up the Format Cells dialog. Go to the Protection tab and uncheck the Locked checkbox. Don’t forget to click OK to save the changes.

Data validation command
Data validation command

When the target cell range is still selected, click on the Data Validation button inside the Data Tools command block of the Data tab in the ribbon.

data validation criteria
Data validation criteria

You should see the Settings tab of Data Validation. There, click on the Allow drop-down menu and choose the Whole number option.

In Data, choose between. Enter the values you want in the Minimum and Maximum fields. Your sales reps can enter any whole number within this range. Click OK to save the Data Validation rule.

Lock worksheet for data entry
Lock worksheet for data entry

Then, go to the Review tab and lock the worksheet using a password using the Protect Worksheet command.

Allowed entries
Allowed entries

From now on, all of your sales reps can only enter the product quantities sold in the respective cells below the Qty column.

Disallowed entries
Disallowed entries

When they try to change any other visible cells in the data entry form, they’ll get an error message.

Use Excel Read-Only to Lock Excel Sheet

You can add a read-only message to your Excel workbook. When someone opens the Excel file, there will be a pop-up asking the user to access the content in read-only.

If the Excel file is read-only, all the worksheets get locked by default. Any changes made by the collaborators won’t be saved in the same workbook.

Always Open Read-Only
Always Open Read-Only

To activate read-only, press Alt + F + I + P to access the Protect Workbook command button. Click on that and choose the Always Open Read-Only option from the drop-down.

There are more ways to apply the read-only rule to an Excel worksheet which can be found below:

📒 Read More: How To Make a Sheet Read-Only in Microsoft Excel

Lock Sheet With Limited Access to Collaborators

When sharing an Excel worksheet, give view-only access to collaborators so they can only read what’s in the spreadsheet but can’t change the content.

Link to this sheet
Link to this sheet

To use this method to lock the Excel sheet, go to the target worksheet and click on the Share button in the top-right corner of the Excel desktop or web app.

There, click on the Link to this Sheet option. It’ll ask you to choose OneDrive as cloud storage through which you’ll share the link to the sheet.

Settings on link copied dialog
Settings on the link copied dialog

After loading for a few seconds, Excel shall show the Link copied dialog. Here, click on the Settings button.

Set Can view in Link settings
Set Can view in Link settings

Now, the Link settings dialog shall open. Here, you must choose with whom you want to share the worksheet. You can also choose Anyone to share the link with a large audience.

Click the More settings drop-down and choose Can view to set read-only access to the sheet. Click Apply to save the changes.

Now, anyone using this link to access the worksheet can only view the dataset. They can’t change its content.

Lock Sheet in Excel for the Web

The Manage Protection feature in Excel for the web is more intuitive than the Excel desktop app. It offers more options in one place than the Protect Sheet option of Excel for the Microsoft 365 desktop application.

Manage protection on Excel web
Manage protection on Excel web

Open the target Excel workbook from your Microsoft 365 portal. Go to the Review tab and click on the Manage Protection button in the Protection commands block on the ribbon menu.

The Manage Protection navigation panel shall open on the right side of the Excel for the web app.

Enable protect sheet
Enable protect sheet

Click on the Protect sheet radio button to activate the feature. Here, you can also set the following features for the locked worksheet:

Add range for editing
Add range for editing
  • Unlocked ranges: Using Add range you can input cell ranges that remain open for editing even if the worksheet is locked.
Password protecting locked worksheet
Password protecting the locked worksheet
  • Sheet protection password: You can enter a password to strengthen the worksheet’s security.
Manage protection options
Manage protection options
  • Options: You can enable sheet features like Format cells, Format rows, Delete rows, etc., in the locked mode.

Lock Excel Sheet Using Excel VBA

If you like to automate various tasks on Excel using VBA programming or want to lock Excel sheets with a single click, you can use the following VBA script:

VBA script to lock sheet
VBA script to lock sheet
Sub LockSheet()

    ' Declare variables
    Dim wsName As String
    Dim allowedRange As Range
    Dim password As String
    Dim ws As Worksheet

    ' Get the name of the worksheet to be locked
    wsName = InputBox("Enter the name of the worksheet to be locked:")

    ' Get the allowed cell range for editing
    On Error Resume Next
    Set allowedRange = Application.InputBox("Select the allowed range for editing with your mouse:", Type:=8)
    On Error GoTo 0

    ' Get the password
    password = InputBox("Enter the password:")

    ' Check if the worksheet exists
    On Error Resume Next
    Set ws = Worksheets(wsName)
    On Error GoTo 0

    If ws Is Nothing Then
        MsgBox "The worksheet " & wsName & " does not exist. Please check the name and try again."
        Exit Sub
    End If

    ' Unlock the worksheet
    ws.Unprotect password

    ' Lock all cells
    ws.Cells.Locked = True

    ' Unlock the allowed range
    allowedRange.Locked = False

    ' Protect the worksheet
    ws.Protect password

    MsgBox "The worksheet " & wsName & " has been locked. The range " & allowedRange.Address & " is editable."

End Sub
Enter sheet name
Enter sheet name

This VBA macro shows a prompt, so you can enter the worksheet name to be locked.

Input box for editable cell range
Input box for editable cell range

Then, you’ll see another input box, so you can select a cell range you want to keep open for the collaborators to comment or enter notes about the worksheet.

Enter lock sheet password
Enter lock sheet password

Finally, you get a message box to set a password to unlock the worksheet.

Lock Excel sheet using VBA
Lock Excel sheet using VBA

When you appropriately interact with the above prompts, Excel locks the selected worksheet instantly.

To find out how to create a VBA macro using the above script, go through this article:

📒 Read More: How To Use The VBA Code You Find Online

Use Office Scripts to Lock Excel Sheet

If you’ve got access to Office Scripts and want to use this feature to programmatically lock Excel sheets, you can practice this method.

Office Scripts code
Office Scripts code

Go to the Automate tab and click on the New Script button inside the Scripting Tools block.

Copy and paste the following script inside the Code Editor panel. Click Save script to save the code for future use.

Worksheet locked using an Office Script
Worksheet locked using an Office Script

Click Run to execute the Office Script to lock the active sheet automatically.

Conclusions

The skill to protect sheets in Excel is essential if you’re willing to share Excel workbooks with other users in your business or academic institution for collaborative work. Often, collaborators make unnecessary changes and a locked sheet prevents such actions.

So far, you’ve learned multiple methods to protect Excel worksheets. Comment below if you know a better technique that I might have missed. Don’t forget to share your feedback too.

About the Author

Tamal Das

Tamal Das

I'm a freelance writer at HowToExcel.org. After completing my MS in Science, I joined reputed IT consultancy companies to acquire hands-on knowledge of data analysis and data visualization techniques as a business analyst. Now, I'm a professional freelance content writer for everything Excel and its advanced support tools, like Power Pivot, Power Query, Office Scripts, and Excel VBA. I published many tutorials and how-to articles on Excel for sites like MakeUseOf, AddictiveTips, OnSheets, Technipages, and AppleToolBox. In weekends, I perform in-depth web search to learn the latest tricks and tips of Excel so I can write on these in the weekdays!

Subscribe

Subscribe for awesome Microsoft Excel videos 😃

Related Posts

Comments

0 Comments

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Get the Latest Microsoft Excel Tips

Follow Us

Follow us to stay up to date with the latest in Microsoft Excel!

Subscribe for awesome Microsoft Excel videos 😃