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.

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.

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.

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.

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.

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.

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.

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

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.

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.

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.

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.

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

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.

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.

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.

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

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

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.

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.

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.

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

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.

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.

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

• Unlocked ranges: Using Add range you can input cell ranges that remain open for editing even if the worksheet is locked.

• Sheet protection password: You can enter a password to strengthen the worksheet’s security.

• 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:

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

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

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.

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

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.

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.

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.