How to Lock an Excel Spreadsheet From Editing (Fast)
Since Excel is an excellent spreadsheet software used all around the globe for data storing and crunching, the safety of data stored into it is of utmost importance.
You can ensure the safety of data within your Excel sheets by protecting these sheets for editing. You can lock them altogether or only include a precautionary prompt to them before users can access them 🔑
Also, it should not be necessary to lock an entire sheet, you can also lock only certain cells from a sheet. To learn how can lock an Excel spreadsheet from editing and do much more about its security, continue reading the guide below.
Grab the workbook here so you can tag along, and let’s set the scene with an example below.
How robust is Excel worksheet protection
Before we learn different ways to protect your sheet against unauthorized edits, or access, here’s something I want you to know about worksheet protection in Excel 💪
The encryption algorithm used by MS Excel for protecting sheets and workbooks is very basic and anyone with a basic coding knowledge can crack it in seconds (especially for older versions of Excel).
Excel’s protection features are meant to prevent accidental changes, formula breaking, and unwanted deletion of data. Or maybe to just draw users’ attention towards careful usage of the sheets 🧾
Hence, if you’ve some very sensitive or confidential information, it is never advisable to store it in password-protected sheets and rely on Excel’s protection features.
How to lock an Excel spreadsheet from editing
If you want to restrict editing by unauthorized users in your spreadsheet, you can always do this by locking the spreadsheet for edits.
Microsoft Excel offers multiple protection features to prevent unauthorized editing access to a specific spreadsheet. Follow the steps below to learn how 🔐
Step 1) Go to the Excel workbook containing the worksheet you want to protect.
Step 2) Go to the Review tab > Protect Group > Protect Sheet.
This opens the Protect Sheet dialog box as shown below.
Step 3) Enter the password you want to set up to lock this spreadsheet.
Ensure the ‘Protect worksheet and contents of locked cells’ checkbox is checked.
Step 4) Check the boxes for the functionalities you want to allow the users of this sheet to have from selecting locked cells to formatting them and as you like it.
The more cells you check, the more access users will have to this sheet and its functionalities.
Carefully set up this password and preferably note it down somewhere accessible so you remember it. If you forget this password, your worksheet will probably be locked forever.
Kasper Langmann, co-founder of SpreadsheetoStep 5) Confirm the password when prompted.
The following password prompt will require you to retype the password you’ve set up so that any incidental mistake in typing the password doesn’t lead to you setting up the wrong password 🐱💻
Step 6) Select Ok to lock the sheet.
Once the sheet is locked, whenever any user, including you, tries to make changes to any cells of the sheet, Excel will show him/her the following error prompt.
To make any edits to this sheet, users will have to unprotect the sheet by entering the password (that we just set up now).
To unprotect the sheet:
Step 7) Go to the Review tab > Unprotect Sheet.
Step 8) Enter the password within the prompt that comes next.
Only the users having access to this password will be able to make edits to this sheet.
This is a basic and straightforward way how you can lock sheets in Excel for editing 🚴♂️
It is to be borne in mind that protecting a worksheet out of a whole workbook only restricts users from editing it. However, they can still access it, view it, and perform actions allowed while you protect the sheet. It is a read-only workbook for them.
Kasper Langmann, co-founder of SpreadsheetoAnother way how you can protect a given sheet in Excel is through the Sheet tab. Here’s how you do it:
Step 1) Select the relevant tab of the sheet that you want to protect.
Step 2) Right-click on this sheet tab to launch the context menu for it.
Step 3) From the context menu, click on the Protect Sheet button.
This opens the Protect Sheet dialog box 🛡
Step 4) Enter the password you want to set up to lock the spreadsheet.
Step 5) Choose the actions you want to allow users to perform from the Protect sheet dialog box.
Step 6) Confirm the password when prompted.
Step 7) Click okay to lock the sheet.
Lock an Excel Spreadsheet from Viewing
A step ahead from locking a worksheet from being edited, if you want to lock a spreadsheet from being viewed, you need to use workbook-level protection 🔍
Excel doesn’t offer any built-in feature to hide individual sheets directly from viewing. Even when you password-protect a sheet, users will be restricted from editing it but, they will still be able to view it.
To restrict a sheet from being viewed by password-protecting it, protect the entire workbook by following these steps.
Step 1) Launch the Excel workbook containing the sheet to be locked for viewing.
Step 2) Right-click the tab of the sheet you want to lock.
Step 3) From the context menu that opens up next, click on the Hide option.
Step 4) Go to the Review tab > Changes group > click on Protect Workbook.
Step 5) Enter a password to password-protect the workbook.
This password will be required to unprotect the workbook and unhide sheets.
The entire science lies in ensuring the “Structure” option for the workbook is checked. Locking the workbook structure prevents users from making structural changes to the workbook without entering the password, like unhiding sheets or renaming them. Hence, they will not be able to view the hidden sheet unless they enter the password for this workbook.
Kasper Langmann, co-founder of SpreadsheetoStep 6) Reenter the password to confirm it.
Step 7) Save the workbook by pressing the Ctrl + S key.
Now again right-click on any worksheet tab to see the Hide and Unhide options have been disabled.
Users can only hide or unhide any sheet (and view it) after they unprotect the workbook from the review tab by entering the password for it 👀
This is a way out of locking a spreadsheet in excel for viewing.
Locking only certain cells of a spreadsheet
If you do not want to lock an entire spreadsheet but only certain cells from it ⌨
Step 1) Select all the cells of your sheet by pressing the Ctrl key + A.
Step 2) Right-click the selected cells and choose Format Cells from the resulting context menu or press Ctrl + 1.
Step 3) In the Format Cells dialog box > Go to the Protection tab.
Step 4) Uncheck the option “Locked”.
Step 5) Now select the cells that you want to lock for editing.
Step 6) Right-click the selected cells and choose Format Cells from the resulting context menu.
Step 7) In the Format Cells dialog box > Go to the Protection tab.
Step 8) Check the option “Locked”.
Locking these cells until now is not going to make any difference unless you protect the sheet through the following steps 👇
Step 1) Select the cells from your sheet other than those that you want to be locked for editing.
Step 2) Go to the Review tab > Protect Sheet button.
Step 3) Protect the sheet by setting up a password and then confirming it.
With this, you have unlocked the entire sheet except for the cells that you selected and set to locked.
Now try editing any cell from the locked cells of the sheet and Excel will present the following error prompt before you.
These cells cannot be edited unless you unprotect the sheet using the password you’ve set up.
However, try editing the unlocked cells and Excel won’t give you any error prompt for editing these.
This is how you can lock certain cells of your sheet and leave the others unlocked for editing.
Conclusion
In this tutorial, we have seen different methods show you can protect an Excel spreadsheet against inadvertent or accidental changes by locking it for editing.
By setting up a password, you can not only look at your Excel spreadsheets for editing but you can also prevent them from being viewed or accessed by unauthorized individuals. Or, you can simply add a prompt to alert users of the sensitivity of the Excel file.
If you enjoyed learning these methods to protect your sheets in Excel, you’d surely love the following tutorials on spreadsheet protection. Check these out here.
Written by Kasper Langmann
Hi, I'm Kasper Langmann👋
I'm the co-founder of Spreadsheeto, a certified Microsoft Office Specialist, and a Microsoft MVP.
With over 10 years of experience, I’ve taught Excel to millions of people worldwide.
I spent over 17 hours researching and writing this tutorial.
Last updated on August 29th, 2024.
Before you go, sign up for my free Excel course (+100,000 students) ->
