MS Excel: Lock Cells with These Steps

Microsoft 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.

2. Click on the “Format” menu and choose “cells” option and click on the “Protections” tab.

3. Click on the checkbox next to the “Locked” field. Click on the “Ok button and return to the spreadsheet.

4. Now click on the “Review” option and choose Protection Sheet. Here click on the “Protect Sheet” option.

5. 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.

2. 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.

3. 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

• To lock a single row or group of rows, select the row below the row or rows that you want to keep visible when you scroll.

• Select the column on the right of the column or the columns that you want to keep visible.

• If you want to lock both rows and columns, click the cell below and to the right of the rows and columns that you want to keep visible.

 Step 2

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

 Step 3

You can do one of the following

• Click “Freeze Top Row” to lock only one row.

• Click “Freeze First Column” to lock one or more columns.

• Click “Freeze Panes” to clock one row or column or to lock both rows and columns at the same time.

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

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.