Undoubtedly, one of the most popular and user-friendly programs is Microsoft Excel. It helps users to organize and format data in rows and columns. When a worksheet is protected, all cells are locked and cannot be edited. It is possible to unlock all of the cells, allowing some to be edited while others remain locked. You can lock only individual cells and ranges before protecting the worksheet, and you can let specific users change only specific ranges of a protected sheet if you want to. In this blog, we’ll learn how to lock specific cells in Excel.
What is the Need to Lock Cells in Excel?
Locking cells in Excel helps you to protect your data from unwanted changes. Let’s say you need to share your Excel spreadsheet with other people, but you don’t want some of the cells to be changed. In that instance, using cell lock to prevent formula and formatting modifications is the best option.
Thus, you can preserve your work by locking the cell in Excel. There are two main steps in this process:-
➤ Locking/unlocking the sheet cells.
➤ Protecting the Excel worksheet.
Prerequisites Steps Must be Done Before you Lock Cells in Excel
Before you begin, keep in mind that by default, all cells in an Excel spreadsheet are locked. However, it will have no effect until the Worksheet is protected. Now that’s out of the way, let’s look at how to simply protect cells in a sheet.
Follow the steps below:-
➤ Select the Ctrl + A keys together to select everything
➤ Format Cells by right-clicking anywhere on the sheet
➤ Using the Ctrl+1 shortcut, you may do this quickly
➤ Click the protection tab in the following window
➤ Check that the checkbox next to “Locked” is checked
➤ This is to see if the cells are locked by default
➤ Protect your Worksheet in order to enforce this
➤ Do a right-click on the Sheet Name >>> Protect Sheet >>> Set Password >>> Set the permissible actions for users >>> Re-Confirm Password is the simplest way to do this
➤ The similar panel is also accessible through the “Protect” group’s Review” tab.
Steps for Lock Specific Cells in Excel
Let’s discuss all the possible way in brief to know how to lock cells in Excel:
Total Time: 25 minutes
Lock Cells in Excel Worksheet
To lock cells in an Excel worksheet, use the following steps:
➤ Select the cells that must be locked
➤ Go to the Home tab, then tap the little arrow across the Alignment group
➤ This will open the Format Cells windows
➤ Now go to the Protection tab in the format cell window and choose the Locked check box
➤ Then press the Ok button to close the popup window
➤ Hit the Review button on the excel ribbon
➤ From the Changes category, select either Protect Workbook or Protect Sheet
➤ Following that, you must reapply the protection to your workbook or worksheet.
Lock some Specific Cells in Excel
Sometimes all you need to do is lock a few specific cells that contain crucial data or calculations. In such circumstances, just protect the cells that need to be locked, leaving the others unprotected. By default, all cells are locked, thus if you add protection to your sheet, all cells will be locked as well. So, make sure that only the cells you wish to lock are protected, and then only apply protection to the entire worksheet.
To lock some specific cells in Excel, follow the procedures below:-
➤ Go to the Home tab after selecting the full worksheet
➤ Then, to present the alignment group, press the symbol of the dialogue box launcher
➤ Now, go to the Protection tab in the Format Cells dialogue box and uncheck the Locked option
➤ Then press the Ok button
➤ Select the cells that need to be locked
➤ Make another tap on the dialogue box launcher in the Alignment group of the Home tab’s Alignment group
➤ Go back to the Format Cells dialogue box and go to the Protection tab, where you may tick the Locked option
➤ This will unlock all of the cells in your worksheet except the one you’ve chosen to be locked
➤ Now go to the Review tab and select the Protect Sheet option from the Changes group
➤ Check whether the option “Protect worksheet and contents of locked cells” is checked or not in the Protect Sheet dialogue box
➤ If it isn’t, double-check it
➤ To apply the password to your sheet, type it in
➤ Now, you may choose which permissions you want to give to other users
➤ You’ll notice that the first two selections are already selected by default
➤ The user can simply select locked or unlocked cells using this method
➤ Aside from that, you can allow other features such as inserting rows/columns or changing the formatting
➤ Toggle your finger over the Ok button
➤ If you’re using a password, you’ll be asked to confirm it
➤ When you try to modify those exact cells again, the following error notice will appear in a pop-up dialogue box.
Lock Excel Cells Using VBA Code
➤ In your Excel spreadsheet, select all cells
➤ Go to the Home tab first, then to Cells
➤ In the Cells section, press the Format option and select Format Cells
➤ This will bring up a dialogue box called Format Cells
➤ Select the Protection tab from the newly opened dialogue box
➤ Uncheck the option Locked here
➤ Now, look in your Excel file for the name of your sheet
➤ Do a right-click on your mouse and select View Code
➤ The Microsoft Visual Basic for Applications dialogue box will appear
Simply type the following code into the dialogue box on the opening white sheet:
➤ Sub protectmydata ()
➤ Dim strPassword As String
➤ Range (“C7:E11”) .Select
➤ Selection.Locked = True
➤ Selection.FormulaHidden = True
➤strPassword = InputBox (“1234”)
➤ ActiveSheet.Protect Password:=strPassword
➤ End Sub
➤ We’ve chosen a data range of C7 to E11 in the provided code
➤ Also, a password like 1234 has been applied
➤ Go to the Debug tab when you’ve finished entering all of the code
➤ There is a Run Sub/User Form (F5) option on this page
➤ Your Excel spreadsheet, as well as a password window, should now be visible
➤ As a result, write the same password that you were given in the VBA code, for example, 1234
➤ After that, whenever you approach making changes in the desired Excel cells, do it with caution
➤ A warning notice will appear, explaining that “you cannot modify the cells because they are protected”.
Protect Entire Worksheet Except Few Cells
Some people choose to protect the entire worksheet by leaving some cells open. When you include interactive features in your worksheet, such as a drop-down list, a situation like this can develop. You also want your drop down menu to work even if the worksheet is protected.
Simply follow the instructions listed below to complete this task:
➤ Select the cells you want to keep unlocked
➤ Use your keyboard to press the Control + 1 key
➤ Go to the “Protection” tab in the Format Cells dialogue box that has opened
➤ Uncheck the Locked box and press the Ok button
These cells should now function appropriately if the full worksheet is protected. To do this, follow the steps below to protect the whole worksheet except for the selected cells:
➤ Select the Review tab, then the Changes group, and finally the Protect Sheet icon
➤ In the Protect Sheet dialogue box that has been opened, type:
➤ Check to see if the “Protect worksheet and contents of locked cells” option is enabled
➤ If it isn’t, double-check it
➤ To apply the password to your sheet, type it in
➤ Indicate the permissions you’d like to give to other users
➤ You’ll notice that the first two selections are already selected by default
➤ The user can simply select locked or unlocked cells using this method
Aside from that, you can allow other features such as inserting rows/columns or changing the formatting.
Lock Formula Cells
To lock formula cells, first unlock all of the cells, then lock only the cells that contain the formula. Finally, secure the sheet. If you don’t know how to do all of these things, follow the steps below:
➤ Select the entire worksheet’s cell
➤ Make a right-click and select Format Cells from the drop-down menu
➤ Go to the Protection tab in the opened format cell window and untick the Locked option checkbox
➤ Then press the Ok button
➤ Select the Find & Select option from the Editing group on the Home tab
➤ Select the option to Go To Special
➤ Select Formulas from the drop-down menu and press the Ok button
➤ After you complete these procedures, Excel will select all of the formula cells
➤ Press the CTRL + 1 key on your keyboard
➤ Select the Locked option from the Protection tab’s check box
Then, on the next screen, press the Ok button.
See Also: How to Remove Duplicates in MS Excel
Read More Information: Freeze Rows and Columns in Microsoft Excel
Conclusion
In this blog, we have explained all the possible ways to lock specific cells in Excel. In any case, if you face any difficulties or have any trouble while implementing these activities, you can connect with QuickBooks Helpdesk Team for further assistance in a precise way.
A Frequently Asked Questions ✍
How Can You Lock Cells Without Protecting Sheets in Excel?
The Excel VBA code can be used to lock cells in Excel without a protective sheet. Though it’s a lengthy procedure, it’s extremely useful in circumstances where other approaches have failed. It’s crucial to understand how to lock cells in Excel VBA.
How Do I Make a Cell Non-Editable in Excel?
When you lock cells in Excel, they become non-editable by default. First, confirm that the cells are unlocked in the Format cells menu. Then, under the format cells menu, lock the cell/range of cells you want to lock. This should be enforced by securing the worksheet. Without a password, you can no longer edit certain cells.
How Do You Keep Formulas Locked in Excel While Allowing Data Entry?
Select Go To special under “Find and Select” in the Home part of the ribbon menu to lock cells containing formulas while allowing other data entry. Formulas can be selected in the Go To special window. This will only choose cells with formulas. Using the shortcut Ctrl+1, navigate to the Format cells dialogue box. Select Lock Cells from the Protection Tab and then click Ok. Apply this rule by password-protecting the Worksheet. Users can now enter data but not alter formulas.
