How to Make a Cell Read-Only in Excel (2 Simple Methods)

Often you may need to share your Excel file with others. And in that file, you may have many complex formulas or anything and you don’t want anyone to edit these things. For this, you may want to know how to make a cell in Excel read-only. So, none can edit those cells without a password. In this article, I will show you 2 easy methods so that you can learn how to make a cell in Excel read-only.

How to Make a Cell Read-Only in Excel: 2 Easy Methods

Here, assume that you have a datasheet of bank account owners. So, you want to make the Amount column cell Read Only. Here, I will show you 2 easy methods to make a cell read-only in Excel.

1. Make a Cell Read-Only by Using ‘Format Cells’ Command

By this method, you will lock the cells you want to make read-only then you will go to the “Protect Sheet” option to enable the protection. The steps are shown here:

STEPS:

• First, go to the file location and open your Excel file. Now select all cells of the worksheet by clicking on the top left corner or pressing Ctrl + A.

• Now, press the right button of the mouse to open options and select the “Format Cells” option. You can open “Format Cells” by pressing Ctrl + 1 keys.

This will open a dialog box.

• After opening the dialog box, press the “Protection” tab. And unmark the “Locked” button. Then press OK.

• Now, go to the Excel file again, and select the cells that you want to make read-only.

• Now, go to the “Format Cells” option again.

• And now, mark the “Locked” button under the “Protection” Tab. Then press OK.

• Now, go to the Review tab > Protect drop-down list > Protect Sheet button. And there will open a new window.

• Then, type the password (e.g. 1234) in the box first. You will see 3 checkboxes named “Protect worksheet and contents of locked cells”, “Select locked cells” and “Select unlocked cells” are already marked. Leave these boxes as they are. And press OK. 

• Then, you will see another window that will ask you to re-enter the password for confirmation. So, re-enter the password there and press OK. 

• After that, the selected cells will be locked. When you open the file again, you will see that you can’t edit the cells.
• To edit those cells, you have to unprotect them. To unprotect, go to Review > Unprotect Sheet. A window will appear and will ask you to enter the password. Now, enter the password here and press OK. 

NOTES:

• By default, all the cells of the Excel sheet are in “Locked” in the format option. The “Protect Sheet” option only protects the locked cells. We will see a message that we must mark under the password box.
• You may have a question about why you need to unmark the locked button at first. You have to unmark the locked button for all cells at first because without doing this, it will go to “Protected mode” for all cells. But you want it for specific cells.
• And, you have to do all the steps shown above to make the cells read-only. Doing only lock cells will not be effective without enabling the “Protect Sheet” option

Read More: How to Make Excel Workbook Read Only with Password

2. Use a VBA Code to Protect Selected Cells

You can also write a VBA code to make a cell read-only by VBA code without using the “Protect Sheet” option. You will fix which cells to protect inside the code in this method.

STEPS:

• Right-click on the Sheet tab at the bottom. Select “View Code” from the menu.

• Then, there will open the Microsoft Visual Basic for Applications window. Just copy and paste the following code into the window.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 4 Then
If Target.Row = 5 Or Target.Row = 6 Or Target.Row = 7 Or Target.Row = 8 Or Target.Row = 9 Then
Beep
Cells(Target.Row, Target.Column).Offset(0, 1).Select
MsgBox Cells(Target.Row, Target.Column).Address & " Cell are read-only and protected ", _
vbInformation, "Cells Read Only"
End If
End If
End Sub

• Now, press Alt + Q to close the window. And go to the Excel Sheet. You will see that the selected cells in the VBA code are in read-only mode. If you click on any of these cells, the following window will open. And you will be navigated to the adjacent cell.

• Now, save the file.

NOTES: 

• You have to open the Visual Basic window by right-clicking on the Sheet1 tab. So, it will open a macro selecting this worksheet and declaration to SelectionChange. 

•  You will not change the first line. It will same as by default. And after inputting the code just close the window. You don’t have to run it.
• This macro code is efficient for protecting a few selected cells. If you want to change which cells to protect, you have to make changes inside the code and fix the cells in the code.
• And lastly, to remove the read-only mode from Excel, just go to the visual basic window again and remove the code.

Read More: How to Make an Excel File Read Only

Download Practice Workbook

You can download the practice workbook from here:

Conclusion

To conclude, this article tries to help learn how to make a cell in Excel read-only. Practice these methods on your own by downloading the practice workbook. I hope you found this article helpful. Please, drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• [Solved]: All Excel Files Opening as Read Only
• [Fixed!] This Excel Workbook Was Opened in Read-Only Mode
• [Fixed!] Excel Files Open as Read Only from Network

<< Go Back to Excel Read Only | Protect Workbook in Excel | Excel Protect | Learn Excel