Learning how to lock a cell in Excel is one of the most useful skills for sharing workbooks where some cells should be editable and others should not. Whether you're protecting formulas in a budget template, securing input forms for a team, or guarding a complex calculation engine, cell locking is the right tool. The trick is that locking by itself does nothing — locking only takes effect once the worksheet is protected. Most people who say cell locking does not work simply skipped the second step.
This guide walks through the full process: how Excel's lock and unlock attributes work, how to protect specific cells while leaving others editable, how to lock formulas while allowing data entry, how to password-protect the worksheet, and how to unlock everything when you need to make edits. The steps work the same in Excel 365, Excel 2019, Excel 2021, and Excel for the Web, with small menu placement differences between Windows and macOS.
By the end of this guide you should be able to set up a clean, professional workbook where users can enter data only in the cells you choose, while every formula and reference cell is fully protected. We'll also cover common gotchas — what happens if you forget the password, why the lock doesn't appear to work the first time, and how to grant trusted users access while still locking everything else.
Cell locking is sometimes confused with file-level password protection, which is a completely different feature. Cell locking controls editability of individual cells within a worksheet. File-level password protection controls who can open the workbook in the first place, and Excel will refuse to display the contents until the password is entered. Most workflows benefit from sheet-level cell locking; only sensitive data warrants the additional step of file-level encryption with a strong password protecting the entire workbook from unauthorized opening.
One last preliminary point worth understanding: cell locking applies per worksheet, not per workbook. If your file has multiple sheets, each sheet has its own lock attributes and its own protection state. Protecting Sheet1 has no effect on Sheet2. To lock cells across multiple sheets, you have to repeat the process on each sheet — or use a short VBA macro to apply the same protection settings across every sheet at once for distribution-grade templates that get rebuilt on a regular cadence.
Two-step process: 1) Mark cells as locked or unlocked via Format Cells → Protection, then 2) protect the worksheet via Review → Protect Sheet. Locking has no effect until the sheet is protected. By default every cell is locked in a new workbook, so you usually want to unlock the input cells first, then protect the sheet to leave only formulas locked. Use Ctrl+1 to open Format Cells fast.
Every cell in an Excel worksheet has a lock attribute that is either on or off. By default, all cells in a new worksheet have lock turned on. The lock attribute alone does nothing visible to the user. The lock only takes effect when you protect the worksheet through Review → Protect Sheet. Once protection is on, locked cells refuse edits while unlocked cells continue to accept input normally. This two-step model trips up almost everyone the first time.
To lock specific cells while leaving others editable, the standard pattern is to invert the default. First, select all cells (Ctrl+A) and unlock them through Format Cells → Protection. Then select the cells you want to lock — typically formulas or reference cells — and turn the lock attribute back on for those cells only. Finally, protect the worksheet. The result is a workbook where the cells you marked as locked refuse edits while every other cell continues to accept input from users.
Some users prefer the opposite approach: they keep the default lock state on most cells and unlock only the few input cells they want users to edit. Both approaches reach the same end state. Which is more efficient depends on the ratio of input cells to formula cells in your specific workbook. If most cells are formulas with only a handful of input cells, leaving the default lock and unlocking the input cells is faster. If most cells are input cells with only a few formulas, the invert pattern described above is faster overall.
Per-cell flag set through Format Cells → Protection → Locked checkbox. Defaults to ON for every cell in a new worksheet, which is why protection without unlocking input cells locks the entire sheet.
Worksheet-level setting under Review → Protect Sheet. When on, locked cells reject edits. When off, the lock attribute is purely metadata and has no effect on user input or worksheet behavior.
Sister attribute to Locked, also under Format Cells → Protection. When on and the sheet is protected, the formula bar shows nothing for that cell. Useful for hiding proprietary calculations from end users.
Advanced feature under Review → Allow Edit Ranges that lets specific users (with passwords) edit defined ranges even when the sheet is otherwise protected. Often overkill for simple needs but powerful for shared templates.
Open your workbook and select the cells where users should be allowed to enter data. These are typically the input cells in a budget template, the data fields in a form, or whatever ranges should remain editable. Press Ctrl+1 to open the Format Cells dialog, click the Protection tab, and clear the Locked checkbox. Click OK. The cells now have lock turned off but are visually identical — there is no color change or icon indicating the new state.
If your input cells are scattered, hold Ctrl while clicking each range to select multiple non-adjacent areas at once. Format Cells applies to every selected cell. For large workbooks, use named ranges or Excel Tables to make the selection repeatable. Some power users color-code their input cells with a light fill color (yellow is conventional) so the editable areas are visually obvious before protection is even applied. The visual cue helps end users find the right cells quickly.
If you're working with an Excel Table (created with Insert → Table or Ctrl+T), the table structure makes selection easier. Click the column header arrow to select an entire column inside the table, or click the table corner to select all data rows at once. Tables also handle cell formatting and locking consistently when new rows are added, since the table structure carries the formatting forward automatically. This makes Tables an excellent foundation for any workbook that mixes locked and unlocked regions across long lists of records.
For workbooks that grow over time, consider unlocking entire rows or columns rather than specific cells. If users add new rows of data, you want the lock state to extend to those new rows automatically. Unlocking a full column means anything entered in that column remains editable, including future entries. Combine this approach with data validation rules to constrain what users can type, and you have a robust template that stays consistent as the data set grows.
Select the cells. Press Ctrl+1 (or right-click → Format Cells). Click the Protection tab. Clear the Locked checkbox. Click OK. This is the most common method and works in every version of Excel including Excel for the Web. The Locked checkbox state shows as a dash or filled box if your selection contains a mix of locked and unlocked cells, which is your hint that the selection isn't homogeneous yet.
Select the cells. Click Home → Format → Lock Cell to toggle. The Lock Cell button shows highlighted when the selection is locked and unhighlighted when unlocked. This is the fastest way to toggle the lock attribute on the fly without opening the Format Cells dialog. Good for quick adjustments on a single cell or contiguous range during workbook setup.
There is no built-in keyboard shortcut to toggle Lock directly. The closest workflow is Ctrl+1 → Alt+P → Alt+L → Enter, which opens Format Cells and steps through to the Locked checkbox. Power users sometimes record a one-line VBA macro mapped to a custom shortcut to toggle the Lock attribute on selected cells with a single keystroke for repeated workbook prep.
For large workbooks, a one-line macro Selection.Locked = False unlocks the current selection, and Selection.Locked = True locks it. Combine with named-range references for repeatable templates. VBA also lets you set Hidden simultaneously, lock the sheet, and apply a password in three lines — useful for distribution-grade templates that are rebuilt frequently.
Once the cells are configured correctly, click Review → Protect Sheet. A dialog appears with a list of actions you want to allow protected-sheet users to perform — selecting locked cells, selecting unlocked cells, formatting cells, inserting rows, sorting, and so on. The default settings allow users to select both locked and unlocked cells but block edits to locked cells. For most data-entry templates, the defaults are fine. For more restrictive templates, clear additional options to limit what users can do beyond simple data entry.
Add a password if you want to prevent users from removing protection on their own. The password is optional. A password-free protected sheet still blocks edits to locked cells, but any user can click Review → Unprotect Sheet and remove protection without challenge. With a password, the same step requires the password before protection is removed. Choose a password you can recall — there is no built-in recovery for forgotten Excel sheet passwords, and third-party recovery tools have varying success rates with newer Excel formats.
Click OK. If you set a password, Excel asks you to confirm by re-entering it. Once the dialog closes, your worksheet is protected. Click any locked cell and try to type — Excel will pop up a message saying the cell is read-only. Click any unlocked cell and you can type freely. The result is exactly what you wanted: a workbook where users can only edit the cells you intentionally chose to leave editable, and every other cell is safely locked behind sheet protection.
The Protect Sheet dialog has a long list of allowable actions. By default, users can select cells (both locked and unlocked) and that's about it. If you want users to be able to format cells, insert rows, or sort data within unlocked ranges, check the relevant boxes before applying protection. For most simple data-entry templates the defaults are appropriate. For more interactive templates where users need to sort or filter, additional permissions are necessary to keep the worksheet usable while still preventing edits to formulas and reference cells.
The most common cell locking scenario is a budget or financial model where users enter values in input cells and formulas calculate totals, summaries, and ratios. The formulas should be locked so users cannot accidentally overwrite them; the input cells should remain freely editable. Excel makes this easy with the Find and Select feature. With your worksheet open, press Ctrl+A to select all cells and unlock everything. Then press F5 to open the Go To dialog, click Special, and select Formulas. Click OK. Excel selects every cell containing a formula.
With formulas selected, press Ctrl+1 to open Format Cells, click the Protection tab, check the Locked box, and click OK. Now only the formula cells have the Locked attribute. Click Review → Protect Sheet to apply protection. Users can edit any cell except formulas. This pattern takes about thirty seconds once you know the keystrokes and produces a clean, professional template that anyone can use safely without breaking the underlying logic of your calculations.
You can also lock formula cells while hiding the formulas themselves from view in the formula bar. In Format Cells → Protection, check both Locked and Hidden. When the sheet is protected, users see the calculated value in the cell but the formula bar shows nothing for that cell. This is useful when you want to distribute a template without revealing how the calculations work. It is not bulletproof — sophisticated users can extract formulas through other means — but it discourages casual inspection of proprietary logic embedded in your workbook.
For repeatable templates, name your input ranges so the lock setup can be repeated programmatically. Select your input cells, click in the Name Box (the cell-reference dropdown to the left of the formula bar), and type a friendly name like InputArea. Now you can refer to the range by name in formulas and macros. A short macro can clear protection, unlock everything, then re-lock everything outside InputArea, then re-protect the sheet — useful when your template structure evolves over time.
One workflow tip worth mentioning: when distributing a protected template, save it as an Excel Template (.xltx) file rather than a regular workbook. Each user who opens the template gets a fresh copy with their own filename, so they cannot accidentally save changes back to the original.
Combined with sheet protection on the input worksheet, this is the cleanest way to share a calculator or budget tool with a wide audience while keeping the source pristine and easy to update centrally as your needs change going forward without disturbing any user copies that have already been saved separately on different devices in different locations.
For workbooks distributed widely, consider adding a brief instruction on the worksheet itself — a header row or a comment — telling users which cells accept input. Visual conventions like a yellow fill color on input cells, gray fill on calculated cells, and a header row labeling sections all help users find the right places to type. Templates that combine clear visual cues with proper cell locking dramatically reduce support questions from the people using the workbook day to day.
To unlock a protected sheet, click Review → Unprotect Sheet. If no password was set, protection is removed immediately. If a password was set, Excel prompts for it. Type the password and click OK. The sheet is unprotected and every cell becomes editable again. From there you can adjust the lock attributes on individual cells, change formulas, or restructure the workbook. When you're done, re-protect the sheet through the same Review → Protect Sheet dialog.
If you've forgotten the password, recovery options depend on the Excel version. Modern Excel formats use stronger hashing that makes recovery difficult. Some third-party tools claim password recovery for legacy .xls files but newer .xlsx formats are largely resistant. The most reliable workaround is to keep an unlocked master copy in a secure location alongside the distributed protected copy, so password loss doesn't mean rebuilding the model from scratch when you need to make edits to the underlying calculations.
Setting the Locked attribute on cells but never running Review → Protect Sheet. The lock has no effect because protection is the trigger that activates it. This is the single most common mistake among new Excel users approaching cell locking for the first time.
Forgetting that every cell starts locked, so simply protecting the sheet locks everything including the cells you wanted users to edit. The fix is to unlock the input cells first, then protect the sheet to leave only the intended cells locked.
Setting a sheet protection password and then losing it. Modern Excel formats are largely resistant to password recovery. Always keep an unlocked master copy of any protected workbook in a separate location for safekeeping in case the password is lost.
Sheet protection blocks edits but does not encrypt the file. For sensitive data, use File → Info → Protect Workbook → Encrypt with Password to apply real file-level encryption that prevents the workbook from being opened without the password.
Excel includes a more advanced feature called Allow Edit Ranges, accessible from Review → Allow Edit Ranges. This lets you define multiple ranges within a protected sheet, each with its own optional password, and grant edit permission to specific users. The result is a single workbook where different users can edit different ranges based on a password they were issued, while everything else remains locked. This is overkill for most needs but extremely powerful for departmental templates where different teams own different sections.
To use it, click Review → Allow Edit Ranges → New, give the range a friendly name, specify the cell range, and set a password if needed. Repeat for each range. Then protect the sheet as usual. When a user tries to edit a range, Excel prompts for that range's password. If the password matches, the user can edit; otherwise the cell behaves as a locked cell. Combined with Active Directory permissions in enterprise environments, this feature can become quite sophisticated and useful for tightly controlled financial workflows.