Lock All Cells | Lock Specific Cells | Lock Formula Cells
You can lock cells in Excel if you want to protect cells from being edited.
Mar 29, 2019 - Locking cells in an Excel spreadsheet can prevent any changes from. Now only the cells you 'locked' are protected from editing instead of the. Sep 4, 2017 - Most people know that you can protect individual rows, columns, or cells in Excel. You can even protect the entire sheet. What they're not aware.
Lock All Cells
By default, all cells are locked. However, locking cells has no effect until you protect the worksheet.
1. Select all cells.
2. Right click, and then click Format Cells (or press CTRL + 1).
3. On the Protection tab, you can verify that all cells are locked by default.
4. Click OK or Cancel.
5. Protect the sheet.
All cells are locked now. To unprotect a worksheet, right click on the worksheet tab and click Unprotect Sheet. The password for the downloadable Excel file is 'easy'.
Lock Specific Cells
To lock specific cells in Excel, first unlock all cells. Next, lock specific cells. Finally, protect the sheet.
1. Select all cells.
2. Right click, and then click Format Cells (or press CTRL + 1).
3. On the Protection tab, uncheck the Locked check box and click OK.
4. For example, select cell A1 and cell A2.
5. Right click, and then click Format Cells (or press CTRL + 1).
6. On the Protection tab, check the Locked check box and click OK.
Again, locking cells has no effect until you protect the worksheet.
7. Protect the sheet.
Cell A1 and cell A2 are locked now. To edit these cells, you have to unprotect the sheet. The password for the downloadable Excel file is 'easy'. You can still edit all other cells.
Lock Formula Cells
To lock all cells that contain formulas, first unlock all cells. Next, lock all formula cells. Finally, protect the sheet.
1. Select all cells.
2. Right click, and then click Format Cells (or press CTRL + 1).
3. On the Protection tab, uncheck the Locked check box and click OK.
4. On the Home tab, in the Editing group, click Find & Select.
5. Click Go To Special.
6. Select Formulas and click OK.
Excel selects all formula cells.
7. Press CTRL + 1.
8. On the Protection tab, check the Locked check box and click OK.
Note: if you also check the Hidden check box, users cannot see the formula in the formula bar when they select cell A2, B2, C2 or D2.
Again, locking cells has no effect until you protect the worksheet.
9. Protect the sheet.
All formula cells are locked now. To edit these cells, you have to unprotect the sheet. The password for the downloadable Excel file is 'easy'. You can still edit all other cells.
How to lock and protect selected cells from editing in Excel?
It's easy to lock and protect the whole worksheet or workbook with clicking the Protect Sheet button or Protect Workbook button under Review tab. However, sometimes you may need to lock and protect only specific cells or selections in a sheet. How would you like to do? This article will guide you to lock and protect selected cells or ranges in Excel with following steps:
Lock and protect selected cells from editing with Kutools for Excel
Lock and protect selected cells from viewing by encrypting (2 steps)
Easily protect/lock some cells from editing in Excel
Kutools for Excel provides a quite easy way to protect or lock specified cells from editing in Excel: unlock the whole worksheet by Selection Unlock button, next lock the specified cells you will lock from editing by Selection Lock button, at last protect current worksheet.Full Feature Free Trial30-day! Click for detailed steps.
Office Tab Enable Tabbed Editing and Browsing in Office, and Make Your Work Much Easier...
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by80%
Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
More than300 powerful features; Works with Office2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Lock and protect selected cells from editing with modifying cell formatting
The following steps will guide you to unlock all cells in current firstly, lock required cells and ranges, and then protect current worksheet. Please do as follows:
1. Click the Arrow at the upper-left corner of worksheet to select all cells in active worksheet.
2. Right click any selected cell, and select the Format Cell item from the context menu. See below screenshot:
3. In the Format Cells dialog box, uncheck the Locked option under Protection tab, and click OK button. See above screenshot:
4. Select cells and ranges that you want to lock, right click and select the Format Cell item from the context menu.
5. In the Format Cells dialog box, check the Lock option under Protection tab, and click OK button. See above screenshot:
6. Click Review > Protect Sheet to protect current worksheet. See below screenshot:
7. Enter a password in the blank box in the popping up Protect Sheet dialog box, and click the OK button. And then retype the password into the another Confirm Password dialog box, and click the OK button. See screenshot above.
And now it locks and protects only cells and ranges you selected in the Step 4, while unselect ranges are editable.
Lock and protect selected cells from editing with Kutools for Excel
Kutools for Excel's Selection Unlock utility and Selection Lock utility enable you to unlock/lock all selected cells with only one click, and then you can protect the whole worksheet with allowing unlocked cells to be edited.
1. Click the Arrow at the upper-left corner of worksheet to select all cells in active worksheet.
2. Click Enterprise > Worksheet Design to activate the Design tab, and then click the Design > Selection Unlock. See screenshot: Then a dialog box comes out to tell you selected cells have been unlocked. Just click the OK button to close it.
3. Select cells and ranges that you want to lock, and click the Design > Selection Lock. Then click the OK button to close the popping up Kutools for Excel dialog box.
4. Click the Design > Protect Sheet or Review > Protect Sheet, and then you can follow the same steps we introduced in the first method to protect current worksheet.
Lock and protect selected cells from viewing by encrypting
If you have Kutools for Excel installed, it’s another Encrypt Cells utility can help you quickly lock and protect selected cells quickly. You can do as follows:
1. Select the cells that you want to lock and protect, and click the Enterprise > Encrypt Cells.
2. In the opening Encrypt Cells dialog box, type your password into both Password box and Confirm box, and click the Ok button. Then you will see all selected cells are encrypted at once as following screen shot shown:
Note: You can decrypt these encrypted cells with clicking Enterprise > Decrypt, and then type you password into opening dialog box.
Kutools for Excel- Includes more than300 handy tools for Excel. Full feature free trial30-day, no credit card required!Get It Now
Demo: lock and protect selected cells in Excel
Kutools for Excel includes more than300 handy tools for Excel, free to try without limitation in30 days. Download and Free Trial Now!
Quickly mass password protect multiple/all worksheets in Excel
Normally Excel's Protect Sheet feature can only protect active sheet. However, Kutools for Excel's Protect Worksheet utility enables to batch protect multiple (or all) worksheets with same password by only 2 steps.
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by80%
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...
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 than300 powerful features. Supports Office/Excel2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features30-day free trial.
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 by50%, and reduces hundreds of mouse clicks for you every day!
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.