How to protect / lock all but certain cells in Excel worksheet?
By default, all cells in an Excel worksheet are locked; this means that when you protect the worksheet, no one can make changes to any cell unless you specifically unlock them beforehand. However, in real-world applications, you might encounter situations where only certain data entry fields—such as totals, inputs, or comments—need to stay editable, while all other areas of the worksheet should remain secured and unaltered. For example, when sharing templates, data entry forms, or reports with colleagues, you may want to ensure users can only fill out or update designated cells. This approach protects worksheet structure and formulas, and minimizes the risk of accidental changes to critical data.
This comprehensive tutorial will walk you through multiple practical methods to protect or lock all cells in an Excel worksheet except for those you specify. You’ll learn both classic built-in Excel procedures and more streamlined ways using the Kutools for Excel add-in. Additionally, we will introduce an efficient VBA macro method, which is particularly suitable if you often need to automate this process or deal with dynamically changing cell ranges. Each method will be explained with step-by-step instructions, practical tips, and important notes for troubleshooting.
Protect / Lock all but certain cells with normal Excel feature
Protect / Lock all but certain cells with Kutools for Excel
Protect / Lock all but certain cells with VBA macro
Protect / Lock all but certain cells with normal Excel feature
Excel enables you to specify which cells should remain editable after worksheet protection, using its built-in Format Cells and Protect Sheet functions. This method is highly compatible and intuitive, making it suitable for most regular worksheet protection needs, especially when the set of editable cells is fixed and doesn't change often. However, the process can become a bit repetitive when you need to adjust unprotected cells frequently. Please proceed as follows:
1. Select the cells you want others to be able to edit and remain unprotected after you apply sheet protection.
2. Right-click the selected cells and choose Format Cells from the context menu. See screenshot:
3. In the Format Cells dialog box, switch to the Protection tab, and clear the Locked checkbox (do not leave it checked). See screenshot:
Tip: By default, all cells are locked in Excel until you unlock them. Clearing this checkbox only has an effect after you later apply worksheet protection.
4. Click OK to save your changes and exit the dialog. Next, go to the Ribbon and click Review > Protect Sheet to bring up the protection settings dialog. See screenshot:
5. In the dialog box that appears, enter and confirm a password to protect your worksheet. Be careful to remember the password, as there is no way to retrieve it if lost. See screenshot:
Notes:
- Make sure you select permission settings as needed, such as "Select unlocked cells" and "Select locked cells". By default, users are allowed to select both, but you can refine these options based on your scenario.
- Password protection is case-sensitive and cannot be recovered if forgotten.
6. Click OK to finalize your sheet protection. You will now find that all cells in the worksheet are locked and cannot be edited, except for those cells you explicitly unprotected in the previous steps. When users try to change a locked cell, Excel will display a warning message.
Pros: No plugins required, works in any Excel environment.
Cons: The process can become cumbersome if you have to change unlocked cells frequently or manage many variable ranges.
If the unprotected cell range needs to change often, or you want to automate this process for efficiency, consider using a VBA macro described in the next method below.
Protect / Lock all but certain cells with Kutools for Excel
If you find the native Excel process too repetitive for large or frequently changing cell ranges, the Worksheet Design feature in Kutools for Excel offers a simpler and more streamlined solution. With just a few clicks, you can unlock specified cells and apply protection to the rest of the worksheet, ideal for users who frequently need to update which cells remain editable.
1. Select the specific cells you want users to be able to modify (the cells you don't want to protect upon sheet protection).
2. Go to the Ribbon, click Kutools Plus > Worksheet Design to enable the design features specific to worksheet layout and protection. See screenshot:
3. The Design tab will now appear. Click Unlock Cells to instantly unlock the selected cells—Kutools will even confirm visually which cells are now editable. See screenshots:
 |
 |
 |
4. Click OK in any confirmation prompts. Then proceed by clicking Protect Sheet from within the Design tab to apply sheet-level protection. See screenshot:
5. In the dialog box that appears, enter and confirm a password for your protected sheet and finalize the settings. See screenshot:
6. Now, only the cells you designated will remain editable—everything else will be uneditable and protected from accidental changes.
Click to know more about this Selection Unlock feature.
Pros: Quick, effective for large data sets or frequently changing protection requirements.
Cons: Requires installation of Kutools for Excel add-in.
Protect / Lock all but certain cells with VBA macro
If you often need to protect or unprotect different cell ranges, especially when these ranges change dynamically or you are required to apply protection to multiple worksheets/workbooks in batch, leveraging VBA macros can help you automate and speed up the process. This is particularly useful for advanced users, administrators, or anyone performing repetitive protection tasks on large or complex workbooks. The VBA solution allows you to specify or input the range you want to unlock prior to protection, and then automatically sets all other cells as locked and protects the worksheet with your preferred settings.
1. In Excel, go to Developer tab > Visual Basic. (If the Developer tab is not visible, enable it via File > Options > Customize Ribbon.)
2. In the Microsoft Visual Basic for Applications window, click Insert > Module, then copy and paste the following VBA code into the module window:
Sub UnlockSelectedCellsAndProtectSheet()
Dim ws As Worksheet
Dim unlockedRange As Range
Dim passwordInput As String
Dim xTitleId As String
On Error Resume Next
Set ws = Application.ActiveSheet
xTitleId = "KutoolsforExcel"
Set unlockedRange = Application.InputBox("Select the cells to remain editable:", xTitleId, Type:=8)
If unlockedRange Is Nothing Then Exit Sub
On Error GoTo 0
ws.Cells.Locked = True
unlockedRange.Locked = False
passwordInput = Application.InputBox("Enter a password to protect the sheet (optional):", xTitleId, Type:=2)
If passwordInput = "" Then
ws.Protect
Else
ws.Protect Password:=passwordInput
End If
MsgBox "Sheet has been protected. The selected cells remain editable.", vbInformation, xTitleId
End Sub
3. To run the macro, click the 
button or press F5 while the cursor is inside the macro.
4. A prompt will appear asking you to enter the cell range you want to keep editable. Select your target range and click OK.
5. Another prompt will ask you to set a password for protection. Enter a password or leave it blank if you don’t require one, then click OK.
The specified range will remain unlocked and editable, while all other cells will be protected. Any future changes to the unlocked area can be made by rerunning the macro and adjusting the range. This automation is ideal for batch operations or templates with dynamically changing input zones.
Pros: Highly flexible, enables automation for dynamic or repeated protection scenarios.
Cons: Requires some understanding of macros and security; macros must be enabled in Excel.
Troubleshooting and suggestions:
- Ensure the specified range input matches actual worksheet ranges to prevent errors.
- Always save your workbook before running macros to avoid losing data.
- If the macro does not work, check whether "Macros" are enabled, or review for typographical errors in the range address.
- If using workbook protection, you will need to adjust the code accordingly.
Demo: Protect / Lock all but certain cells with Kutools for Excel
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in