To lock a named range in Excel, select the cells it refers to, make sure their Locked setting is on, and protect the worksheet. Excel cannot lock a name itself: cell locking takes effect only after sheet protection is enabled. If other cells should remain editable, unlock those cells before protecting the sheet. These steps are for Excel for Microsoft 365 on Windows.
What You’ll Need
- A workbook containing a named range that refers to one or more worksheet cells. A name that refers to a formula or constant rather than cells cannot be locked this way.
- Permission to change the worksheet’s protection settings. If the sheet is already protected, you may need its password to unprotect it first.
- Optional: a password for protecting the sheet. Keep a record of it somewhere safe; Excel may not be able to recover a forgotten protection password.
Step-by-Step
- Check what the name refers to. Open the worksheet containing the range, then select Formulas > Name Manager. Find the name in the list and check the Refers to entry. It should point to the cells you intend to protect, such as a cell range on a worksheet. Also check Scope: a name can apply to the whole workbook or just one worksheet. Close Name Manager when you have confirmed the target. If you have more than one similarly named item, note the scope and make sure you work on the intended sheet.
- Unprotect the sheet if necessary. If the Review tab offers Unprotect Sheet, select it and enter the password if prompted. You cannot change a cell’s Locked setting while its worksheet is protected. If the sheet is not protected, continue. Protection applies to a worksheet, not just to one name, so this step prepares the whole sheet for the settings you will apply.
- Decide whether other cells should stay editable. Excel marks cells as Locked by default, but that setting does nothing until you protect the worksheet. If you want only the named range locked and the rest of the sheet editable, select the whole worksheet using the small triangle at the top-left corner where the row and column headers meet. Press Ctrl+1 to open Format Cells, select the Protection tab, clear Locked, and select OK. On Mac, use Command+1 to open Format Cells. This step clears the Locked setting for all cells; you will turn it back on for the named range next.
- Select the named range’s cells. Use the Name Box, the small box to the left of the formula bar that normally displays the active cell address. Open its drop-down and choose the name, or type the name and press Enter. Excel should select the cells the name refers to. If the name is worksheet-scoped, first activate its worksheet. If you are uncertain that Excel selected the right cells, compare the highlighted selection with the Refers to entry in Name Manager before changing anything.
- Turn on Locked for the selection. With the named range selected, press Ctrl+1 on Windows, or Command+1 on Mac. In the Format Cells dialog, open Protection, select the Locked checkbox, then select OK. This sets the property for the selected cells; it does not yet stop anyone from editing them. If the name covers several separate areas, check that all of its intended cells are selected and receive the Locked setting.
- Protect the worksheet. Select Review > Protect Sheet. In the dialog, leave Protect worksheet and contents of locked cells selected. You can enter a password, or leave it blank if you do not want a password prompt when protection is removed. Choose which actions users may perform. For example, allow selection of unlocked cells so users can work in editable areas; clear selection of locked cells if you do not want users to select protected cells normally. Select OK, then confirm the password if you entered one.
- Test the result. Try changing a cell in the named range and confirm Excel prevents the edit. Then try entering a value in a cell outside the named range if you intended that cell to remain editable. If Excel blocks both, revisit the whole-sheet unlock step and check for other locked cells. If Excel allows edits in the named range, recheck the name’s selection, its Locked setting, and whether the correct worksheet is protected.
On Excel for Mac, the process is substantially the same: use the Name Box to select the range, Command+1 for Format Cells, and the Review tab’s sheet-protection command. In Excel for the web, protection and cell-formatting options can vary by account and interface; if you cannot find the Locked setting, open the workbook in the desktop app to configure it. Protecting a sheet is intended to prevent routine edits, not to encrypt sensitive workbook contents.
Common Mistakes
- Assuming a named range is protected just because it has a name. Names make cells easier to refer to; they do not change permissions. You still need to set the cells to Locked and protect the worksheet.
- Protecting without unlocking other cells first. Since cells start with Locked enabled by default, protecting the sheet without changing those settings usually blocks edits throughout the sheet, not just in the named range. Unlock the cells users need to edit before turning on protection.
- Selecting the wrong name or scope. A workbook-level name and a worksheet-level name can have similar labels, and a local name may only be available on its own sheet. Verify the target and scope in Name Manager, then confirm the highlighted cells before applying formatting.
- Changing the Locked checkbox but not protecting the sheet. Locked is a cell property, not an active restriction by itself. Until you choose Review > Protect Sheet, users can still edit the cells.
- Expecting sheet protection to hide or secure data. Protection can prevent ordinary editing, but it is not a substitute for file encryption or access controls. Do not rely on it to keep confidential information secret.
- Forgetting the password. A password can make it harder for others to remove protection, but losing it can also make your own changes difficult. Store it securely and retain an unprotected backup if appropriate.
Tips
- Keep the target name meaningful. A descriptive name such as InputLimits or ApprovedRates is easier to verify than a generic label. Use Name Manager to review or edit names; changing a name’s definition can change which cells your protection workflow affects.
- Plan permissions before protecting. Decide which areas people must edit, and unlock those cells in advance. Sheet protection can also restrict actions such as formatting cells, inserting rows, or sorting, depending on the options you allow in the Protect Sheet dialog.
- Use a test copy for complicated sheets. If the worksheet has many named ranges, merged cells, formulas, or existing protection rules, save a separate copy and test the user experience there before applying the settings to the working file.
- Remember that protection is per worksheet. Protecting one sheet does not protect identically named ranges on other sheets. Repeat the setup for each worksheet that needs it.
- Distinguish cell locking from hiding formulas. The Protection tab also has a Hidden option. That option hides a formula from the formula bar only when the sheet is protected; it is separate from blocking edits and does not conceal the displayed cell value.
Troubleshooting
- The entire sheet is locked. Unprotect the sheet, select the whole worksheet, clear Locked in Format Cells, then select the named range and enable Locked for that selection. Protect the sheet again. Check that you did not leave other areas locked intentionally or by accident.
- The named cells are still editable. Confirm that the correct name points to those cells, that those cells show Locked checked, and that sheet protection is active. If you changed the name definition after protecting the sheet, unprotect it and repeat the selection and formatting steps for the updated target.
- The Name Box does not find the name. Open Formulas > Name Manager and check the exact spelling, scope, and Refers to value. A worksheet-scoped name may not appear as expected from another sheet. Activate the sheet where it is defined and select the name there. If Refers to contains a formula or constant instead of cell references, it does not identify cells to lock.
- Format Cells or its Protection tab is unavailable. The worksheet may still be protected, or the workbook may be in a mode that restricts editing. Use Review > Unprotect Sheet first, providing the password if required. If the workbook is shared or read-only, obtain editing access or work with an authorized copy.
- Users cannot edit cells that should be open. Unprotect the sheet and inspect the cells in question using Format Cells > Protection. Clear Locked for those cells, then protect the sheet again. Also check the Protect Sheet options: allowing users to select unlocked cells makes it easier for them to reach editable cells, although it does not itself unlock them.
Frequently Asked Questions
Can I lock a named range without protecting the worksheet?
No. You can set the cells’ Locked property, but Excel enforces that property only while the worksheet is protected. Protecting the sheet activates the setting.
Can I lock just one named range and leave the rest editable?
Yes. Clear Locked for the whole sheet first, turn Locked on for the cells selected by the name, and then protect the sheet. Any other cells that remain Locked will also be protected.
Does locking a range stop people from seeing its values?
No. Sheet protection limits edits and selected actions; it does not hide cell contents. Use appropriate file access controls or encryption if the information must be confidential.
Can I protect more than one named range?
Yes. Set Locked for each range you want protected, leave the cells users need to edit unlocked, and protect the worksheet once. You can select and configure each name separately before enabling protection.
What if the named range includes cells on different sheets?
A worksheet protection setting applies to one sheet at a time. Protect each relevant sheet and configure the cells on that sheet separately. A name that refers to a formula rather than cells cannot serve as a selectable range for this purpose.
Can I remove the protection later?
Yes. Choose Review > Unprotect Sheet and enter the password if one was set. Removing protection does not automatically clear the cells’ Locked settings; it simply stops Excel enforcing them until you protect the worksheet again.
Related
- Goal Seek in Excel: How to Find a Target Value
- Goal Seek in Excel: Where to Find It and How It Works
- Goal Seek in Excel: A Practical Step-by-Step Example
Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.
More in How-to basics
- Conditional Formatting for Duplicates in ExcelIn Excel for Microsoft 365 on Windows, select the cells, then choose Home > Conditional Formatting > Highlight…
- Enable Scroll Lock in Excel: Keyboard and On-Screen MethodsTo enable Scroll Lock in Excel for Microsoft 365 on Windows, press the Scroll Lock or ScrLk key;…
- Excel Data Bars: How to Use Conditional FormattingIn Excel, bar conditional formatting is called Data Bars: it fills each selected cell with a horizontal bar…
- Excel Power Query Not Responding? Fixes That WorkIf Excel Power Query is not responding, wait briefly to see whether a large refresh is still running,…
- Excel Adjusting Print Area: Fit, Set, and Clear PagesTo adjust a print area in Excel for Microsoft 365 on Windows, select the cells you want, then…
- Excel Number Format: How to Show Zero as a Dash in CellsYes. In Excel for Microsoft 365 on Windows, select the cells, press Ctrl+1, choose Custom, and enter #,##0;(#,##0);"-";@.…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.