How to Lock Cells in Excel

How to Lock Cells in Excel

Excel is one of the most widely used spreadsheet software worldwide. Managing and analyzing all your data becomes a breeze with multiple features and functionalities offered by Excel. However, while using Excel, sometimes you might accidentally modify data in some of the cells you never intended to edit. This can lead to the loss of valuable numbers and information. Luckily Excel is quite versatile and offers an easy solution to this problem-cell locking!

By locking the cells, you can conveniently protect all your data from intentional or accidental modifications while allowing other cells to be edited. In this article, we will learn the most accessible way, teach you how to lock cells in Excel, and explore different scenarios where this feature can be helpful. So it doesn’t matter if you’re a regular or a seasoned Excel user; read on to learn how to secure your valuable data and prevent any accidental errors in your spreadsheet. 

How to Lock Cells in Excel

Excel has a default setting that allows you to lock the cells, meaning all the cells you have worked on cannot be deleted or reformatted. This way, all the content remains safe and protected from accidental changes. But the default settings also allow you to change the protection options. Here’s how you can do that:

  1. Go to the Review tab on your Excel sheet. 
  2. Now from the options available, click on the Protect Sheet window. 
  3. Excel will then allow you to enter a password required to unlock and open the sheet and then to carry multiple actions or permit multiple users to make changes. 
  4. Then click on OK to protect your sheet. 
  5. The cells can be unlocked by going to the Review tab again, but we don’t recommend doing that unless you have some profound changes to make!

How to Lock Certain Cells in Excel

How to Lock Certain Cells in Excel

Locking all the cells isn’t ideal at all times, so the locking-specific cells feature comes in handy. Using the locking specific cells feature, you allow users to make changes to the sheet except for a few cells that you want to keep the same. For instance, if you have a supermarket and want your employer to update the inventory quantities, you can lock the inventory list and keep the other cells open for quick updates. Here’s a quick way to do that:

  1. Start by selecting all the cells that you don’t wish to lock. These cells can be edited even after your sheet is protected and locked. 
  2. Now right-click on the selected cells, and then choose Format Cells from the menu that opens up. A new tab will open, displaying a series of options; select Protection. 
  3. You will get two options, Locked and Hidden. Now uncheck the Locked option and then click on OK. 
  4. Next, click the Review option, go to Protect Sheet, and then hit OK to protect your Excel sheet. Any cells you didn’t lock will remain open, while the rest will be unlocked for you or your employer to edit. 
  5. But do follow the above step because the locking or unlocking won’t work unless you haven’t protected them. 

How to Unlock a Cell in Excel

In Excel, because of the default setting, cells are locked the minute you protect the sheet. But if there are any cells that users want to make changes to, they must unlock them to have a smooth working experience on the spreadsheet. 

  1. Start by selecting all the cells that you wish to unlock. 
  2. You can either right-click to open the Format tab or, on the options at the top of the screen, click the Format drop-down button. Now select the Format Cells option. 
  3. Choose the Protection tab and then untick the Locked check box. You can also toggle Lock by right-clicking, choosing Format, and then clicking on the Lock Cell option. 
  4. All of these are only effective when the sheet is protected. Click on OK to move further. 

How to Lock Formula Cells in Excel

How to Lock Formula Cells in Excel

You might want to keep the formula of profile, loss, or commission private from your employees in such cases; it’s essential to know how to lock cells with the formula. Here’s how you can do it:

  1. Select all the cells on your Excel sheet, and then right-click. From the drop-down menu, click on Format. 
  2. Now, from the open tab, click Protection, uncheck the Locked box, and click OK. 
  3. Now, choose the Find and Select drop-down menu from the editing options on the Home tab. 
  4. Now go to Special, select the Formulas option, and then confirm by selecting OK. After this, Excel will then select all the formula cells. 
  5. After you see the cells highlighted, then  Press Ctrl+1. 
  6. Return to the Protection tab, check the Locked box, and click OK. 
  7. Your formulas are now protected from everyone’s eyes!


How do I lock cells in Excel F4?

Locking cell references in Excel can be effortlessly achieved by using the F4 key. Once you have selected the desired cell or cell range, simply press the F4 key, and Excel will automatically add the necessary $ sign to the relevant areas in the cell reference.

How do I lock individual cells in Excel without protecting the sheet?

  • Start by selecting the cells you plan to lock on your sheet. 
  • Now go to the Home tab on your screen, and choose the Format option on the top-right of your screen. 
  • From the drop-down menu that appears, choose the Lock Cell feature. 

How do I lock cells in Excel but allow data entry?

  • To prevent users from changing formulas in your spreadsheet, you can take these steps: First, go to the Protection tab and then choose the Lock cells option. 
  • Click OK to confirm your selection. 
  • Now protect your spreadsheet with a password to ensure this rule remains intact.
  • This setup allows users to input data in the sheet but not modify any formulas. 

Bottom Line

Mastering the art of locking cells in Excel is essential for every individual dealing with large amounts of numeral data and information daily. By following the quick and easy steps highlighted in this article, you can protect all the important data from accidental or unnecessary changes, thereby preventing costly errors and ensuring all the data is accurate. Whether you need to lock cells to prevent data changes or to protect any of your sensitive information, Excel offers a range of various options to help you reach your goals. So next time you’re working on an important spreadsheet, remember to take advantage of the lock column cell feature to ensure your data is secure!

Read Also

Abdul Wahab is a Software Engineer by profession and a Tech geek by nature. Having been associated with the tech industry for the last five years, he has covered a wide range of Tech topics and produced well-researched and engaging content. You will mostly find him reviewing tech products and writing blog posts. Binge-watching tech reviews and endlessly reading tech blogs are his favorite hobbies.