excel lock cellsMicrosoft Excel is an electronic spreadsheet and part of the Microsoft Office suite. It’s used when you need to calculate, analyze and assess data. Since Excel is so easy to learn and use, as you can learn in this course, it has become the most popular spreadsheet management software.  Excel software has integrated basic and complex mathematical formulas in it keeping mind the requirements of the changing business world. These formulas can be used to generate values from your Excel data and make it easier for you to analyze your data using Excel.

Your Excel worksheets can be shared with other users. You can prevent the users from making any changes to the cell data or formatting, or mess up with the formulas you have used to generate values on your worksheet by locking the cells. You can protect a specific cell, group of cells or entire cells and even rows within an Excel worksheet. You can also protect formulas when sharing the spreadsheet with others if you choose not to disclose how the values were generated.

To help you learn Excel by example  we put together simple methods to show you how to lock the cells in your MS Excel worksheet.

Method I – Using Format Menu and Cell Option

  1. Open the Excel worksheet in which you want to lock the cell. Click on the individual cell that you want to lock.
  1. Click on the “Format” menu and choose “cells” option and click on the “Protections” tab.
  1. Click on the checkbox next to the “Locked” field. Click on the “Ok button and return to the spreadsheet.
  1. Now click on the “Review” option and choose Protection Sheet. Here click on the “Protect Sheet” option.
  1. Enter a password and also confirm the password. Click “Ok” button. This will protect the value and formatting of the cell from being altered.

Method II – Protect the cell by Right Clicking on the Cell

This is the simplest way to protect the format, data and value of the cell.

Select the cell or the range of cells that you want to protect. Right click on the cell or cells.

Choose “Format Cells” from the drop down menu. Go to “Protection” tab.  Here check the “Locked” box. If you want to hide the formula you have to select “Hidden” checkbox also.

To activate this function, go to the “Review” tab and find the Changes group. Within this group, select the option “Protect Sheet.” Here ensure that the “Protect Worksheet and contents of the lock cells” option is selected. Enter a password in the “Password to Unprotect Sheet” field.  Re-enter the password and click “OK” button.

Method III – Protecting the Cell that contains the Formula

This privilege is set when you want to share the values of data stored in the Excel sheet without disclosing the formula that you used for the calculation.  Here are the steps.

  1. Open the Excel worksheet on which you want to protect the formula cell. Right click on the cells and select “Format Cells.” Select the “Protection” option.  The hidden option lets you hide the content of the cells. However, if you have to activate this option, you have to protect the worksheet.
  1. Select the “Review” tab and find the Changes group. Within this group, select the option “Protect Sheet.” Here ensure that the “Protect Worksheet and contents of the lock cells” option is selected. Enter a password in the “Password to Unprotect Sheet” field.  Confirm the password and click “OK” button.
  1. Now its time to verify whether the cell is locked and the formula is hidden. Click on the cell which you locked. Look at the formula bar and notice it’s blank.

Now you can share this workbook with other users. The formula is protected and is not visible to them. To unlock or to see the formula you need to have the password. This method can be applied to a single cell or group cells.

Freezing or Locking Rows or Columns

If you are working with large number of rows and columns, if you want to keep an area of the worksheet visible when you scroll to another of the worksheet, you can lock rows or columns or both by freezing or splitting pane.  There are a few points that you need to keep note of while using command.

You can use Freeze Panes option only for the rows at the top and the columns on the left side of the worksheet. You cannot freeze the rows and columns in the middle of the excel worksheet.

 Step 1

STEP1

 Step 2

 Select Window group on the View tab, click the “Freeze Panes” buttons. This will open a drop-down menu.

STEP2

 Step 3

You can do one of the following

Only those who know the password can make any changes to the protected or locked cells.

STEP3

We hope this shows you how to effectively lock cells in Excel. Like we said earlier, Excel is pretty easy to learn and use. You always have our beginners Excel course to refer back to. Or perhaps you may want to explore this crash course on using Excel like a Pro.

Top courses in Excel

Use Excel Like A Pro. Fast.
Kieran Luke
4.6 (984)
Excel Macros and VBA: Automate Your Excel Workload
Think Forward Online Training
4.7 (437)
Excel XML, XPath and XSLT Workflows
Grant Gamble
4.5 (176)
Bestseller
Microsoft Excel - Excel from Beginner to Advanced
Kyle Pew, Office Newb LLC
4.6 (152,171)
Bestseller
The Ultimate Excel Programmer Course
Daniel Strong
4.4 (12,944)
Bestseller
Microsoft Excel - Data Analysis with Excel Pivot Tables
Maven Analytics, Chris Dutton
4.6 (19,301)
Bestseller

More Excel Courses

Excel students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo