“A complex Excel worksheet often has a large number of data and formulas that can cause significant losses when accidentally manipulating one of the cells for any reason.” One way to protect a table is to lock all worksheets and cells. However, if you are going to review the worksheet to your leader, do you expect him to have the qualifications to add comments to your worksheet and modify some of the data? Therefore, a more reasonable method is to lock some key formula cells!
Step 1 Unlock all cells
“Unlock”? Aren’t we going to protect the cell?
Yes, we’re trying to protect cells with formulas. However, to ensure that after the next operation, other cells can be modified in addition to the cells that really need to be protected. So this step is essential.
Step：
Select all cells in the worksheet (you can use CTRL + A, or click the Select button in the upper-left corner of the table)
Right-click to open the format cells
On the format cells tab, don’t choose locked
Step 2 Select all cells with formulas
After you unlock all the cells, we select the cells with formulas.
There are two ways to quickly and accurately navigate to all the formula cells:
First, choose “go to Special” in “Find and select”;
The second is to click on the F5, and then the pop-up window, select “Special”
After you open the go to special, click Formulas and check all 4 secondary options.
You’ll find that all the formula cells in the worksheet are selected.
Step 3 locking cells with formulas
Similar to the first step, open format cells and check locked.
Step 4 Protection Worksheet
Since we have locked all cells with formulas, as long as the protection worksheet is set, these cells can be impregnable without a password.
Click on the “Review” tab
Then click “Protect sheet”
In the pop-up window, select Protect worksheet and contents of locked cells
After that, if someone wants to change without authorization, then it’s only a dialog box that waits for him:
After you qualify only the cells with formulas, you can protect the most important core of the table, and you can give other users a certain amount of data to adjust, add comments right.
If you want to further conceal your formula, you can even check “hidden” in the “format cells” tab, so that others will be more difficult to break through the security barrier you set.
See you next week.