Security and integrity of your data in Excel are of utmost importance, and there are different ways to ensure this, like password protecting and locking cells in your Excel worksheets.
In this article, you will learn how to lock cells in Excel worksheets through our comprehensive step-by-step guides. Read on to learn more.
Table of Contents
How to Lock Cells in Excel and Protect the Workbook
All cells in your worksheet will be locked to outside users by default if you protect your worksheet. Locking the cells will have no effect until you protect your worksheet with a password.
Locking your cells means that the content in them can’t be edited, and the cells can’t be deleted or formatted with anyone, including yourself until you unprotect your worksheet. That’s where the true value of locking cells lies, stopping self-sabotage and making accidental changes to important formulas and data.
Since all the cells are locked by default, you will just need to protect your worksheet in order for the locking to take effect.
There are a few ways you can protect your worksheet. Below are the steps to take to protect your worksheet and lock cells.
- Open Excel and highlight or select all cells in your worksheet.
- Right-click on the selected cells and click on Format cells in the pop-up sub-menu that appears, or click on Ctrl + 1 on your keyboard after you have selected your cells.
- Click on the Protection tab at the right of the format cells menu.
- Make sure the first checkbox which says Locked is checked, and press OK. Now all your cells are locked. This is also a way of confirming that all cells were locked by default in the first place.
Now proceed to protect your worksheet.
- Right-click on the name of your worksheet at the bottom of Excel.
- In the sub-menu that pops up, select Protect Sheet.
- In the protect sheet window that appears after clicking on protect sheet, Enter a password of your choice in the field provided for you.
- Select the actions you want other users to be able to do on your worksheet.
- Click OK after you are done. Your worksheet is now protected, and your cells are locked.
Another way to achieve the same results of protecting your worksheet is by:
- Clicking on the Review tab on Excel’s Ribbon at the top.
- Select Protect Sheet, and this will open the same protect sheet window where you can proceed normally to protect your worksheet.
To unprotect your worksheet, Right click on the worksheet name at the bottom of Excel and select Unprotect Sheet in the pop-up sub-menu, then enter the password again if prompted to.
How to Lock Specific Cells in an Excel Worksheet.
Maybe you don’t want to lock all cells in your worksheet and want to lock specific cells which might not be placed right next to each other. Then worry not because there is a way in which you can lock only specific cells of your choosing.
Locking specific cells prevents accidental changes in your worksheet and ensures the integrity of your data.
Remember, to lock specific cells, you will first have to unlock all cells if they’re already locked.
Below are the steps on how to lock specific cells in an Excel worksheet:
- Open Excel and highlight or select all cells in your worksheet.
- Right-click on the selected cells and click on Format cells in the pop-up sub-menu that appears, or click on Ctrl + 1 on your keyboard after you have selected your cells.
- Click on the Protection tab at the right of the format cells menu.
- In the format cells tab, uncheck the checkbox that says Locked to unlock the cells in your worksheet and press OK.
- Next, proceed to select the cells you want to lock, Hold Ctrl on your keyboard, and select the cells with your mouse if they are positioned far from each other.
- After selecting your cells, right-click on them and select Format Cells in the pop-up sub-menu, or alternatively, you can press Ctrl + 1 on your keyboard to open the format cells window.
- In the format cells window that appears, check the checkbox that you previously unchecked named Locked.
- Click OK.
Now only the cells you selected will be locked in your worksheet. It is also important to note that when you are unlocking cells that were previously locked under a protected worksheet, you will need to enter the password that was used to protect the worksheet if there was one attached to it.
How to Lock Formula Cells in an Excel Worksheet
- Click on the Home tab on Excel’s ribbon, and once in the Home tab, click on the Find & Select button.
- Select the Go to Special button under the Find & Select options.
- In the window that appears, check the formulas circle and click OK.
Excel will then select the cells with formulas.
- Now that your formal cells are selected, you can proceed to lock them. Right-click on them and select Format cells.
- Navigate to the Protection tab and check the locked checkbox to lock your formula cells. Remember that for them to be actually locked, you will also need to protect your worksheet, as shown in the above methods.
Another important thing to remember is to always save your spreadsheet so that the lock can function as intended when you reopen your work.
Frequently Asked Questions
How Do I Lock Cells in Excel but Allow Data Entry?
Allowing data entry in locked cells in Excel can be accomplished when protecting your worksheet. You are provided with actions that you want to be able to happen within the worksheet, including data entry. You need only check the appropriate checkboxes. You can also choose to only protect the cells with formulas in them instead. You may also want to consider using multiple selection boxes to prevent incorrect data entry.
How Do You Lock Excel Cells So They Are Always Visible?
To make sure that the cells you lock are always visible, under the Format cells window, make sure that the Hidden checkbox is unchecked while your locked cells are selected.
Wrapping Up
Protecting your sheet in both Google Sheets and Excel can be as simple as locking the cells. Now you know how to lock cells in Excel worksheets, and you are closer to being a Microsoft Excel professional.
If you found this guide useful, you’ll love our premium templates too. Check them out below, and remember to use the code SSP to save 50% at checkout.
Related:
- How to Convert Excel to Google Sheets
- How to Use HLOOKUP in Microsoft Excel
- The Best Excel Courses to Make You a Skilled User in No Time
- An Easy Guide to VLOOKUP in Excel
- How to Remove Rows in Microsoft Excel
- The Ultimate Guide to Excel Version History
- How to Track Changes in Excel
- Master the Excel Consolidate Features
- How to Remove Read Only From Excel
- How to Find Circular References in Excel
