Lock Data Validation in Excel

Locking data validation in Excel is useful to prevent users from changing or deleting the data validation rules you have applied to a cell or range of cells.

This tutorial shows two techniques for locking data validation in Excel.

Method #1: Protect the Worksheet to Lock Data Validation in Excel

Consider the following dataset with data validation rules applied to cell ranges B2:B10 and C2:C10. The data validation ensures that only specific dates are entered in the cell range B2:B10, and only particular countries are entered in the cell range C2:C10.

Graphical user interface, application, table, Excel

Description automatically generated

We want to lock data validation rules in the cell ranges B2:B10 and C2:C10.

We use the steps below:

  1. Select the cell range A2:C10 where you want data entry. The range includes the cell range B2:C10 which has data validation rules.
  1. Right-click on the selection and choose Format Cells from the context menu to open the Format Cells dialog box.
Graphical user interface, application, table, Excel

Description automatically generated

Alternatively, you can press Ctrl + 1.

  1. Click the Protection tab on the Format Cells dialog box, deselect the Locked option, and click OK to unlock the target cell range.
Graphical user interface, text, application, email

Description automatically generated
  1. Click Review >> Protect >> Protect Sheet.
Graphical user interface, application, table

Description automatically generated
  1. On the Protect Sheet dialog box, uncheck the Select locked cells option, and leave the other options as is.
Graphical user interface, text

Description automatically generated

Optionally, specify a password to unlock the worksheet on the Password to unprotect sheet box on top of the dialog box.

  1. Click OK.

Now that the worksheet is protected, users can only enter data in the unlocked cell range and cannot fiddle with the data validation.

Method #2: Use Excel VBA to Prevent Paste on Data Validation Cells

We can use Excel VBA to prevent pasting data validation cells and destroying the data validation rules.

Suppose we have the following dataset with data validation rules applied to cell ranges B2:B10 and C2:C10. The data validation ensures that only particular dates are entered in the cell range B2:B10, and only specific countries are entered in the cell range C2:C10.

Graphical user interface, application, table, Excel

Description automatically generated

We want to use Excel VBA to prevent pasting over the cell range with data validation.

We use the steps below:

  1. Right-click the tab of the worksheet containing the dataset and choose View Code on the shortcut menu.
Graphical user interface, application, table

Description automatically generated
  1. Copy and paste the following code into the worksheet’s code window:
  1. Save the workbook as an Excel Macro-Enabled Workbook (*.xlsm).

When someone tries to paste on the cells with data validation, they find that they can’t because the Paste Special option is disabled.

Graphical user interface, application, table, Excel

Description automatically generated

Conclusion

This tutorial explained two techniques for locking data validation in Excel. We hope you found the tutorial helpful.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.