Tips – Locking only cells in Excel

I looked high and low for a way to lock isolated cells in Excel without a need to unprotect the whole worksheet whenever any cell was being edited, and I finally came across two options; the first was my preference:

Option 1:

To ONLY protect or lock individual cells in Excel without locking the whole worksheet

1. Select the whole worksheet using either ‘Ctrl + A’ or click in the top left corner of the sheet (little pale arrow).
2. Right click any cell and select “Format Cells …”. In the Protection tab, remove the checkmark next to “Locked”. Click OK.
3. Now select the cells or the range that you want to protect. Right-click on it and select “Format cells …”.
4. In the Protection tab, check the box next to ‘Locked’.
5. Once the cells you want to lock are ticked, go to the tab and select ‘Review’, then ‘Protect Sheet’.
6. Ensure you tick ‘Select locked cells’ and ‘Select unlocked cells’ in the ‘All users of this worksheet…’ submenu.
7. Assign a password and confirm twice.

Note that the password will only be needed if unlocking the worksheet to edit the locked cells. If other unlocked cells are being manipulated, there will be no need to unlock/unprotect the worksheet.

To summarise, unlock the whole sheet first, then lock the cells you want protected, then protect the sheet.

Option 2:

You can use VBA code to prevent some cells from being edited, but that only helps with accidental edits. Someone who knows a bit about Excel can easily circumvent it by either opening the workbook without allowing macros, or by temporarily disabling events.

1. Right-click the sheet tab.
2. Select ‘View Code’ from the context menu.
3. Copy the code listed below atm no 8 into the worksheet module.
4. Change A2,C2,A4,C4 to the list of cells that you want to protect. You can use ranges too: A2:B10,D2:H10
5. Switch back to Excel.
6. Save the workbook as a macro-enabled workbook (*.xlsm).
7. Instruct users to allow macros when they open the workbook.
8. Private Sub Worksheet_Change(ByVal Target As Range)
   If Not Intersect(Range(“A2,C2,A4,C4”), Target) Is Nothing Then
   Application.EnableEvents = False
   Application.Undo
   Application.EnableEvents = True
   End If
   End Sub