You then turn on worksheet protection, which means that locked cells can’t be changed, deleted, moved, or copied over, and that hidden formulas are no longer visible. You can also hide the formulas in one or more cells if you don’t want users to see them. Protection formatting When you use this technique, you format those cells in which you want to allow editing as unlocked, and you format all other cells as locked.
You can prevent this problem by using Excel’s worksheet protection features, which you can use to prevent changes to anything from a single cell to an entire workbook.įor protecting cells, Excel offers two techniques: When you have labored long and hard to get your worksheet formulas or formatting just right, the last thing you need is to have a cell or range accidentally deleted or copied over. Ok, like this, we can protect formulas in excel.Objective 1.2: Manage workbook review Restrict editing If you try to do any kind of action in formula cells, it will show the below message for you.
Click on “Ok” and reenter the password in the next window.Ĭlick on “Ok,” and your formulas are protected. As of now, we are not allowing users to do any action with locked cells except for the selection of cells. In the above window, we can choose other actions that can be performed with locked cells, so by default, first two options are selected if you want to give any other actions to the user, you can check those boxes. In the “Protect Sheet” window, we need to enter the password to protect the locked cells, so enter the formulas as you would like to give. So under the REVIEW tab, click on the “Protect Sheet” option. Now, we need to protect the worksheet in order to protect formulas in excel. Now again, press Ctrl + 1 to open the Format Cell window, and this time makes only these cells as “Locked.”Ĭlick on “Ok” and only selected cells will be locked, and protection applies only for these cells. Look, it has selected only the black font colored cells. Click on “Ok,” and all the cells which have formulas will be selected. From the above window, choose “Formula” as the option. This will take you to the “Got To Special” window like the below one. Select the entire worksheet and press the F5 key to open the “Go-To” window and press on the “Special” tab. Once all the cells are unlocked, we need to lock only formula cells because we need to protect only formula cells, so how do you know which cell has the formula in it?. As you can see under “Protection,” the checkbox of “Locked” is ticked, so that means all the cells are clocked now, so uncheck this box.Ĭlick on “Ok,” and all the cells are unlocked now. In the above window, click on the “Protection” tab. Select the entire worksheet and press Ctrl + 1 to open the Format Cells window. By default, all the cells are locked in excel in excel, so if we protect the worksheet directly, all the cells will be protected, and users cannot work with any of the cells, so first, we need to unlock all the cells of the worksheet. Assume we need to allow the users to work with other cells except for the cells which have formulas, follow the below steps, and protect them. In the above table, all the black-colored cells are formula cells, so we need to protect them. For example, look at the below data in excel.
0 kommentar(er)
