How to Lock Certain Cells, Rows, or Columns in Microsoft Excel
You can protect only the cells you need to in your spreadsheet. Here’s how to lock certain cells in Excel to prevent accidental changes.
One nice feature of Microsoft Excel is the ability to protect a workbook. You can set a password so that only those with the password can access it. But what if you only want to protect (lock) specific cells, rows, or columns on a spreadsheet?
Examples can include creating an invoice, timesheet, or expense report. You want to allow editing for necessary cells; however, you don’t want others to edit cells containing company details, formulas, drop-down lists, or other information.
Here, we’ll show you how to lock certain cells, ranges of cells, rows, or columns in your Excel spreadsheet.
Unlock the Entire Sheet
You may not realize it, but the cells in an Excel sheet are locked by default. This gives you a head start if you decide to password-protect a spreadsheet. So the “locked” setting only applies if you protect the sheet.
To lock only specific cells, you’ll need to first unlock them all.
- Open your workbook and go to the sheet you want to work from in Excel.
- Select the entire sheet by clicking the triangle in the upper left corner of the sheet.
- With the sheet selected, either right-click and choose Format Cells or click Format > Format Cells in the ribbon on the Home tab.
- Pick the Protection tab and uncheck the box for Locked.
- Click OK.
Lock Certain Cells
Next, select the cells, range of cells, rows, or columns you want to lock. Once you lock them, you’ll then protect the sheet to prevent changes to the locked cells.
- With the cells selected, either right-click and choose Format Cells or click Format > Format Cells in the ribbon on the Home tab.
- Pick the Protection tab and check the box for Locked.
- Click OK.
- Go to the Review tab and click Protect Sheet.
- Enter the password you want to use and check the box for Protect worksheet and contents of locked cells. Be sure to jot down the password or pop it into your password manager. If you forget the password, it cannot be recovered or reset.
- In the list of allowed items, check the box for Select unlocked cells. This lets the user(s) enter the information needed in the unlocked cells. Optionally, check any other boxes for items you want to allow users to do in the sheet.
- Click OK.
- Enter the password again to confirm it and click OK.
Before you share your sheet, give it a test. Try to edit a locked cell. You should receive a message letting you know that the cell is on a protected sheet.
To make changes to the locked cells yourself, simply unprotect the Excel sheet. Go to the Review tab, click Unprotect Sheet in the ribbon, and enter the password.
Protect Your Data and Lock Certain Cells in Excel
When you create a form or sheet that you want others to fill out or add to, locking cells you don’t want touches keeps them safe. This prevents unwanted changes without a lot of hassle or extra work to make it happen.
If you use Sheets in addition to Excel, take a look at our how-to for locking cells in Google Sheets too!
