We want to lock the cells after checking the table data and prevent it from being further changed. What can we do?

In today's tutorial, you will learn how to use the Validation function to restrict data change.

Step one, select the E column, and then add a drop-down list named checked.

Click Validation at Data, and you can also use the shortcut Alt+D+L.

In the Setting tab, select List at Allow, and enter checked at Source. Finally, click OK.

By doing so, we can select checked in the drop-down list for the approved data.

Step two,apply Validation to the checked data as a way to prevent it from being modified.

First, select the data area to be locked, and use the shortcut keyALT+D+Lto enter the dialog box, where we can set the condition as Custom. Enter =LEN ( at Formula.

Next, click the first blank cell in column E and make it a reference. Press the F4 key to fix it, then add a closing bracket.

Finally, enter =0

If you are confused about the references we mentioned, our previous tutorial about absolute reference and mixed references will help you. The complete formula is: =LEN($E2)=0

The LEN formula can help you count the number of texts. According to this formula, the number of texts in the E2 cell must be 0; otherwise, an error warning will appear, indicating that the cells with defined data limits cannot be changed at will when checked appears in cell E2.

Meanwhile, we can click the Error Alert tab and customize the warning alert. In this case, we change the alert into The data has been checked and cannot be modified.

Finally, click OK.

At this time, anyone who modified the data marked with checked will receive an error alert.

Note: This function cannot prevent data from being deleted.  

This is all about using Validation to prevent data change. Did you get it?

WPS Spreadsheet is a powerful chart and form processor. In WPS Academy, you can learn not only how to use Spreadsheet to sort out data but also how to corporate numeric information into graphs. By watching the step-by-step tutorials on WPS Academy, you will become a spreadsheet expert soon.