How do you unlock only certain cells in Excel?

Protecting your Excel worksheet prevents others from making changes to the original data. If you want to allow others to add or change data in only certain parts of the worksheet, you can unprotect specific ranges of cells. You can also require other users to enter a password before editing these areas, or you can limit the editing to certain users. Excel allows you to make these changes either by using the ribbon commands or by using mouse shortcuts.

Ribbon Commands

1. Select the "Review" tab.

2. Click "Unprotect Sheet" if the worksheet is already protected.

3. Click "Allow Users to Edit Ranges."

4. Click the "New" button in the dialog box to create a new editable range.

5. Click inside the "Refers to Cells" field, and then drag your cursor over the range of cells in the worksheet you want to make editable. To unlock multiple areas at once, hold down the "Control" key while selecting the additional cells. The selected cell ranges appear automatically in the "Refers to Cells" field.

6. Type a password in the "Range Password" field if you want to require password access, or click the "Permissions" button to specify which users can edit the fields. Click "OK" when done.

7. Click the "Protect Sheet" button to re-enable protection, and then click "OK."

Mouse Shortcuts

1. Unprotect the entire worksheet if its already protected. Right-click the sheet tab at the bottom of the screen and select "Unprotect Sheet."

2. Highlight an area of the worksheet you want to unprotect. To highlight several areas at once, hold down the "Control" key while selecting the additional cells.

3. Right-click one of the highlighted areas and select "Format Cells." Select the "Protection" tab, and then clear the check mark from the "Locked" box. Click "OK."

4. Right-click the sheet tab at the bottom of the screen, select "Protect Sheet" and then click "OK." The entire sheet is now protected, except for the areas you unlocked in the previous step.

In Excel, as we all known, all cells are locked by default, when you protect the worksheet, it will not be modified. But, sometimes, you want to protect all cells but some specific cells which can be edit by others. This article, I will talk about some quick tricks for you to solve this problem.

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 normal Excel feature

In Excel, you can apply the Format Cells and Protect Sheet utilities to protect all cells but leave some specific ones, please do as follows:

1. Select the cells that you want to unprotect.

2. Then right click, and choose Format Cells form the context menu, see screenshot:

3. In the Format Cells dialog box, click Protection tab, and keep Locked option unchecked from the checkbox, see screenshot:

4. Then click OK to close this dialog box, and go on clicking Review > Protect Sheet, see screenshot:

5. In the following dialog box, please enter and confirm the password that you want to protect the cells:

6. At last, click OK button, all the cells have been protected but leaving the specific cells unprotected.

Protect / Lock all but certain cells with Kutools for Excel

If you are tired of the above method, I can talk about an interesting way for you to solve this task. Kutools for Excel’s Worksheet Design feature may do you a favor.

After installing kutools for Excel, please do as follows:

1. Select the specific cells that you don’t want to be protected.

2. Then click Kutools Plus > Worksheet Design, see screenshot:

3. And a new Design tab will be displayed, and then click Unlock Cells to unlock the certain selected cells, see screenshots:

4. Then click OK button, and go on clicking Protect Sheet under the Design tab, see screenshot:

5. And then, type the password and confirm it, see screenshot:

6. And now, all the cells in this worksheet are protected except the selected ones. You can modify them as you need.

Click to know more about this Selection Unlock feature.

Download and free trial Kutools for Excel Now !

Demo: Protect / Lock all but certain cells with Kutools for Excel

Kutools for Excel: with more than 300 handy Excel add-ins, free to try with no limitation in 30 days. Download and free trial Now!

The Best Office Productivity Tools

Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%

• Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails...
• Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
• Merge Cells/Rows/Columns without losing Data; Split Cells Content; Combine Duplicate Rows/Columns... Prevent Duplicate Cells; Compare Ranges...
• Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
• Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
• Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
• Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
• Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
• More than 300 powerful features. Supports Office / Excel 2007-2021 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.

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!

How do I unlock certain columns in Excel?

Can I lock selected cells in Excel?

How do I leave certain cells unprotected in Excel?

How do I lock certain cells in Excel for editing?