How to Lock Cells in Excel

Excel is a multidisciplinary spreadsheet program that allows you to protect the contents and specific data in a sheet that the user desires or wants in a spreadsheet, being rendered unavailable for editing or further changes. In doing so, you can choose whether users are allowed to select or edit a specific cell or a large array of cells, insert or delete rows or columns, allow conditional formatting, sort specific content in a sheet or variety of other options, that can only be applied to cells not locked or cells that are available for editing.

You’ve worked tremendously hard on your spreadsheet and given your blood, sweat and tears on it (too much of an exaggeration, since Excel makes these things easier for you!) and now you want to be certain of the fact, that anyone you share the information contained in the spreadsheet doesn’t inadvertently, or even deliberately in some cases, considering the politically driven world we are surrounded with (who are we kidding!), change cells that needn’t be changed or modified. Well, thankfully, Microsoft Excel is a program that is to our rescue and can prevent this deliberate or even unintentional changes and modifications to data and can protect the desirable content through the feature of locking, which enables the user to lock cells and protect them from being modified or changed.

You can either lock all the cells in a worksheet or even certain desirable specific cells, allowing only certain areas of spreadsheet be available for editing and modifications to the end user. The article entails complete information with steps and examples of locking cells in Excel!

How to Lock All the Cells in a Worksheet

By default, whenever a spreadsheet or document is locked, it is put into protected view, such that all the cells or the complete content in the worksheet is locked. This means they cannot be formatted in the sense of inserting and deleting rows or columns, and the data contained in the spreadsheet cannot be edited.

However, the silver lining is: The locked cells can be selected altogether, but and their inability to get edited or formatted can be changed in protection options.

Before studying unlocking, let us discuss the steps of locking all the cells in worksheet to prevent certain desirable data be edited or modified from the hands of untrustable world!

  1. Click on the Review tab.
  2. Click on the option Protect Sheet in the Review menu. In the Protect Sheet window, enter a password of your choice that would be required to ‘unprotect’ the sheet or make the sheet available for editing, in case, in future, the need arises to edit the data in the worksheet, but is completely optional and user’s choice.
  3. Click on OK button in the Protect Sheet dialog box to protect the entire sheet.

When you or anyone tries to edit any of the cells that are locked, which in the case is the complete worksheet, the following message would be displayed:

The cells, which further need to be edited or modified, can only be unlocked when the sheet is unprotected view, which can further be done by following some easy steps:

  1. Click on Review Tab on Menu Bar.
  2. Choose Unprotect Sheet Option onto the Review Tab. Enter the password, if required, or provided when the sheet was made protected.

How to Lock Specific Cells in a Worksheet

There would be times or requirements when the pre-user wants that only certain cells should be available for the users to edit or format, while the other cells should store that data permanently, and should not be made available to edit, which may lead to erroneous results or impacts.

Consider the example of spreadsheet, Society Work, where though the fields or columns called: S.No, Names, Branch and Department Selected For, would be constant and need not be edited, the field or column called: Payment Status, needs to be regularly updated. Thus, in this case, all cells expect Payment Status need to be locked, since they should not be edited or formatted by other users, as it may lead to ‘troubles’ or even corruption in ‘Society Work’ (Such is the politics, I tell you)!

  1. Select the cells that you don’t want to be locked:
    These would be the cells that would be made available to the user to edit afterwards and the content inside them can be changed or modified.
  2. Right-click on your selection of cells, click on the option ‘Format Cells’, and further click on the Protection tab in the dialog box.

    Also, in an alternate manner, click on Home Tab in the Menu Bar, provided the cells to be formatted are already selected, click on the expansion icon next to the option of Alignment, and then click on Format cells option. Further, in the Format cells dialog box, click on Protection Tab.

  3. Click on the check box next to ‘Locked’ option, which is by default, highlighted and click on OK.
  4. Click on Review Option on Menu Bar, and further, click on Protect Sheet option to protect the sheet.
    Now, any cells that the pre-user did lock under the Format Cells option will be locked, while the unlocked cells will be editable and can be modified.

Note: Cell locking (or unlocking) won’t take effect, as written in Protection Tab of Format Cells Dialog Box, until you do step 4, of protecting the sheet in Review Tab.

If You Want the Majority of Cells in a Spreadsheet Locked

To lock the majority of the cells in the spreadsheet, following steps need to be followed sequentially:

  1. Select the cells that you or the pre-user want to remain unprotected, that is to be available for editing or to get modified. To select nonadjacent or discontinuous cells, hold down CTRL key on the keyboard and click onto the cells that you want to remain unprotected.
  2. Right click to get the Format Cells option, and the further Format Cells dialog box to appear.
  3. Click to clear the Locked check box and click OK.
  4. In Excel, click on the Review tab on the Menu Bar, and click on the Protect Sheet option. Type a password if you want one, that is, it is optional and then click on OK.

Note: This is the same as previous topic, but has been explained again, to form the basis of the next topic, wherein majority of cells in the spreadsheet would be unlocked.

If You Want the Majority of Cells in the Spreadsheet Unlocked

To leave the majority of the cells on the spreadsheet unlocked, following steps need to be followed (needless to write, sequentially!):

  1. Select the entire worksheet by clicking the Select All button, which can be done using the right click or through the gray rectangle in the upper-left corner of the worksheet where the row 1 and column A headings meet, or by pressing the keys CTRL+A or CTRL+SHIFT+SPACEBAR simultaneously on the keyboard.
  2. Select the cells that you or the pre-user want to remain protected, that is to be unavailable for editing or to get modified. To select nonadjacent or discontinuous cells, hold down CTRL key on the keyboard and click onto the cells that you want to remain protected.
  3. Right click to get the Format Cells option, and the further Format Cells dialog box to appear.
  4. Click to clear the Locked check box and click OK.
  5. In Excel, click on the Review tab, available on Menu Bar, and click on the option, Protect Sheet. Type a password, for unlocking the cell, which was optional, and then click on OK.

Tip from the Experts

If you want to quickly or immediately, lock or unlock cells in an Excel spreadsheet, without going through the complete procedure of locking and unlocking, the following shortcut can be adopted. This also applies on cells, that are non-contiguous or discontinuous, that is, those cells that aren’t next to each other, a ‘cheat’ keyboard shortcut can be used:-

After selecting a cell or group of cells that need to be locked or unlocked, use the Format Cells dialog box, that can be made available by selecting the desired cells, and right clicking, as above to lock or unlock it. Then select your next cell(s) and hit F4 to repeat your last action, which would get repeated and reduce the workload.

A big sorry to those who read the entire blog with utmost sincerity, but the basics make a person perfect!

Comments are closed.


Magoosh blog comment policy: To create the best experience for our readers, we will only approve comments that are relevant to the article, general enough to be helpful to other students, concise, and well-written! 😄 Due to the high volume of comments across all of our blogs, we cannot promise that all comments will receive responses from our instructors.

We highly encourage students to help each other out and respond to other students' comments if you can!

If you are a Premium Magoosh student and would like more personalized service from our instructors, you can use the Help tab on the Magoosh dashboard. Thanks!