Office hacks: protect Excel data important information easily locked

In normal work, we often have to give some documents to employees for registration or verification, such as registration or verification of employee addresses, contact numbers, ID cards and other information. For documents such as this to save private information, in order to prevent employees from editing documents to see the information of other employees, you can set up a specific cell protection password to ensure that users can only view the specified content (Figure 1).


First of all, create a new name for the “auxiliary table” of the new worksheet, follow the prompts to enter the employee’s address, phone number and other information. Locate a blank cell at the back (e.g., B12) and create a list of open passwords assigned to each employee (Figure 2).


Then create a new table named “Initial Table”, the contents of the table in the format of Figure 1, enter the header data, locate C2 and enter the formula “=IF($A2 = auxiliary table! B2,” “)”, down to the right to fill the C5, D6 cells (Figure 3).


Formula explanation: Here the use of IF function to determine to show the data, when the data input in A2 corresponds to the auxiliary table of the password character that C13 value, at this time in cell C2 shows the auxiliary table B2 data (that is, Zhang San’s address information), and so on; otherwise, cell C2 will show blank. Note here that “$ A2”, “$ C13” use the column relative to the reference, so pull down the reference A2 → A4, C13 → C17 cells in turn.

As above, continue to create a new table named “revised table” of the new table, positioned to B2 and enter the formula “= IF (initial table! F2 <> 0, initial table! F2, auxiliary table! B2)”, down to the right to fill the formula. Formula that when the initial table F2 data is not 0 (that is, when Zhang San address information to modify), then B2 will display the modified value, otherwise the original auxiliary table B2 shows the original address information data, so that the staff modified information data will be automatically synchronized to the table (Figure 4).


After completing the above settings switch to the “initial table”, select F2: G5, right-click and select “Set cell formatting → Protection”, remove the “lock” check box, so that in the open work Table and workbook protection, only these cells can be entered into the editorial data. Similarly, select A2:A5 also perform a similar operation (Figure 5).


Switch to “Review → Protect Sheet”, check the “Selected Unlocked Cells”, click “OK” to enter the protection password, enter the confirmation password again to complete the worksheet protection. So the entire worksheet can only be entered in the above unlocked cells (Figure 6).


In the list of tables at the bottom of the workbook, press Ctrl to select “Revision Table” and “Auxiliary Table”, right-click and select “Hide”. Click “Review → Protect Workbook”, follow the prompts to set the protection password (password is recommended to be set to the same as the protection worksheet, easy to remember), here you must remember this password, otherwise subsequent operations can not open the hidden worksheet to extract data. This way the two tables will be automatically hidden to avoid other users to view the password set in the “auxiliary table”. It is not possible to unhide without a workbook protection password.

Since by default each employee enters the password and edits the finished document, the password entered by the current employee will be saved in column A. In order to prevent the next employee from viewing the password in the workbook, the password will be saved in column B. In order to prevent the next employee from viewing the password of the last edited employee, you can add a macro to clear the password displayed in column A of the document. Excle macro trust first, switch to the “initial table” after pressing “Alt + F11” to enter the ExcelVBA environment, double-click “ThisWorkbook “, select “WorkBook” from the object drop-down box in the pop-up window, select “Open” in the type drop-down list box, and then enter the following code to generate a macro. The role of this macro is to re-open the document each time the user, A2: A5 data will be automatically cleared. After completing the save as .xlms document (Figure 7).


Sub wordkbook_open ()

Range (“A2: A5”).


Range (“A2”).

End Sub

Now we can set the password according to the “auxiliary table” in the above document to each employee, the staff open the document, they can only see their data information in column A by entering the password we provide, and can only be modified in columns F and G, without the corresponding password, the current operator is unable to see other Without the corresponding password, the current operator cannot see other employees’ related information (Figure 8).


The information modified by each employee is automatically synchronized to the revised table, which is hidden and cannot be viewed by other employees. Moreover, each time the document is sent to another employee to open, the password saved by the previous employee will be automatically cleared, so that each employee who opens the document for editing cannot see the information data of others. After completing all the verification work, the producer just click “Review → Unprotect Workbook”, enter the above password to cancel the protection, and then the hidden “Revision Table” to unhide the data extracted from it.

Leave a Comment