How to Lock Cells in Excel (Protect Formulas and Data)

Easily Lock Cells in Excel by Protecting Worksheets

by Avantix Learning Team | Updated May 26, 2022

Applies to: Microsoft^® Excel^® 2013, 2016, 2019, 2021 or 365 (Windows)

It's easy to lock cells in Excel to protect data or formulas. This is typically a two-step process. First, unlock the cells where you want to allow users to make changes to the data or formulas and then lock the remaining cells by protecting the worksheet. You can opt to use a password when locking cells but you don't have to use a password.

Recommended article: How to Password Protect Your Excel Worksheets and Workbooks

Do you want to learn more about Excel? Check out our virtual classroom or in-person classroom Excel courses >

Lock all cells in a worksheet

To lock all cells in an Excel worksheet:

1. Display the worksheet with the cells you want to lock or protect.
2. Press Ctrl + A to select all (you may need to press Ctrl + A twice).
3. Right-click any of the selected cell(s) and choose Format Cells or press Ctrl + 1. The Format Cells dialog box appears.
4. Click the Protection tab.
5. Ensure Locked is checked or selected. Cells are locked by default.
6. Click OK.
7. Click the Review tab in the Ribbon.
8. In the Changes group, click Protect Sheet. A dialog box appears.
9. Check or uncheck the desired options (it's usually best to leave the first two checked). Be sure to scroll down to see all of the available options.
10. Enter a password if you want (you will need to set a password if you don't want others to be able to unprotect the sheet). Passwords are case sensitive and you should keep a copy of your passwords.
11. Enter the password again if you entered in a password in the previous step.
12. Click OK (twice if necessary). You will be unable to enter or change data or formulas in the locked cells.

The Protect Sheet dialog box appears as follows:

If you try to edit locked cells, a dialog box will appear indicating that the cell or chart you're trying to change is on a protected sheet.

To unprotect the sheet:

1. Display the worksheet you want to unprotect.
2. Click Unprotect Sheet on the Review tab in the Ribbon.
3. Enter the password if prompted.
4. Click OK.

You can also right-click a sheet tab and choose Unprotect Sheet from the menu.

Lock specific cells

To lock specific cells in a worksheet (so users can enter data in unlocked cells):

1. Select the cells you want to unlock. You can also select entire rows or columns (typically by Ctrl-clicking or Shift-clicking the row or colum headings).
2. Right-click the selected cell(s) and choose Format Cells or press Ctrl + 1. The Format Cells dialog box appears.
3. Click the Protection tab.
4. Uncheck or deselect Locked.
5. Click OK.
6. Click the Review tab in the Ribbon.
7. In the Changes group, click Protect Sheet. A dialog box appears.
8. Check or uncheck the desired options.
9. Enter a password if you want.
10. Enter the password again if you entered in a password in the previous step.
11. Click OK (twice if necessary).

You will be able to enter or change data or formulas in the unlocked cells.

The Format Cells dialog box with the Protection tab selected appears as follows (Locked is not selected):

Lock cells with formulas

To lock cells with formulas in a worksheet:

1. Display the worksheet with the cells you want to lock or protect.
2. Press Ctrl + A to select all (you may need to press Ctrl + A twice).
3. Right-click any of the selected cell(s) and choose Format Cells or press Ctrl + 1. The Format Cells dialog box appears.
4. Click the Protection tab.
5. Uncheck or deselect Locked.
6. Click OK.
7. Press Ctrl + G. A dialog box appears.
8. Click Special. The Go To Special dialog box appears.
9. Click Formulas.
10. Click OK.
11. Right-click any of the selected cell(s) and choose Format Cells or press Ctrl + 1 to display the Format Cells dialog box.
12. Click the Protection tab.
13. Check or select Locked.
14. Click OK.
15. Click the Review tab in the Ribbon.
16. In the Changes group, click Protect Sheet. A dialog box appears.
17. Check or uncheck the desired options.
18. Enter a password if you want.
19. Enter the password again if you entered in a password in the previous step.
20. Click OK (twice if necessary).

In the Go To Special dialog box below, Formulas is selected:

Go to Special is a great command to find and select specific cells. You could select all blank cells in a range, unlock them and then protect all other cells. Go To Special also appears on the Home tab in the Ribbon in the Find & Select drop-down menu.

This article was first published on December 31, 2017 and has been updated for clarity and content.

Subscribe to get more articles like this one

Did you find this article helpful? If you would like to receive new articles, JOIN our email list.

More resources

How to Use Flash Fill in Excel (4 Ways with Shortcuts)

How to Freeze Row or Column Headings in Excel Worksheets

10 Great Excel Navigation Shortcuts to Move Around in Workbooks

3 Excel Strikethrough Shortcuts to Cross Out Text or Values in Cells

How to Replace Blank Cells in Excel with Zeros (0), Dashes (-) or Other Values

Related courses

Microsoft Excel: Intermediate / Advanced

Microsoft Excel: Data Analysis with Functions, Dashboards and What-If Analysis Tools

Microsoft Excel:  Visual Basic for Applications (VBA) | Introduction

Microsoft Word: Intermediate / Advanced

Microsoft Project: Introduction

VIEW MORE COURSES >

Our instructor-led courses are delivered in virtual classroom format or at our downtown Toronto location at 18 King Street East, Suite 1400, Toronto, Ontario, Canada (some in-person classroom courses may also be delivered at an alternate downtown Toronto location). Contact us at info@avantixlearning.ca if you'd like to arrange custom instructor-led virtual classroom or onsite training on a date that's convenient for you.

Copyright 2022 Avantix^® Learning

You may also like

Microsoft, the Microsoft logo, Microsoft Office and related Microsoft applications and logos are registered trademarks of Microsoft Corporation in Canada, US and other countries. All other trademarks are the property of the registered owners.

Avantix Learning |18 King Street East, Suite 1400, Toronto, Ontario, Canada M5C 1C4 | Contact us at info@avantixlearning.ca