How To Lock A Cell In Excel Formula, Update Apr-20

May 15, 2020

You are experiencing a worksheet brimming with recipes, and you accidentally hit the erase key, or delete key, or some other number/letter set key.

Presently you’ll be fortunate in case you’re ready to detect the blunder and right it. Be that as it may, in the event that you are not, it might prompt some mistaken outcomes.

What’s more, let me disclose to you this, mistakes in Excel have cost millions to organizations (read this or this).

The odds of such mistakes increment multi fold when you share a document with associates or administrators or customers. www.office.com/setup

One of the approaches to keep this from happening is to bolt the worksheet and all the cells. Be that as it may, doing this would keep the client from rolling out any improvements to the worksheet. For instance, in case you’re sending an exercise manual to your administrator for survey, you might need to permit him to include his remarks or change a few cells.

A superior workaround to this is to bolt just those cells that have recipes in it.

How to Lock Formulas in Excel?

Before I show you how to lock formulas in Excel, there are few things you need to know:

Steps to lock formulas in Excel

Select all the cells and unlock these.
Select all the cells that have formulas (using Go To Special).
Lock these selected cells.
Protect the worksheet.

Now that I have outlined the steps above, let’s dive in and see how to do this (and more importantly, why we must do this):

Step 1: Select All the Cells and click Unlock it.

While you may think that its befuddling, hold on for me and continue perusing.

As I referenced, just the cells that are bolted just as ensured can genuinely be limited. In the event that all the cells are bolted, and I ensure the whole worksheet, it would mean a client can’t transform anything.

In any case, we just need to bolt (limit access) to the cells that have equations in it.

To do this, we first need to open all the cells and afterward select and lock just those cells that have recipes in it.

Here are the steps to unlock all the cells:

Select all the cells in the worksheet
Use the keyboard shortcut Control + 1 (hold the Control key and then press 1). This will open the format cells dialog box.
In the format cells dialog box, select the Protection tab.
Uncheck the ‘Locked’ option.
Select OK.

Step 2: Select All those Cells that have formulas.

Since all the cells have been opened, we have to ensure that the cells that have recipes are bolted.

To do this, we have to initially choose all the cells with equations.

Here are the means to choose all the cells that have equations:

Select all the cells in the worksheet.
Go to Home and inside the Editing group, click on Find & Select.
Click the drop-down, select Go to Special.
In the Go to Special dialog box, choose Formulas.
Click OK.

Step 3: Select All those Cells that have formulas.

Now that we have selected the cells with formulas, we need to go back and lock these cells (enable the lock property that we disabled in step 1).

Once we do this, defensive the worksheet would lock these cells that have formulas, but not the other cells.

Here are the steps to Lock Cells with Formulas:

With the cells with formulas selected, press Control + 1.
In the format cells dialog box, select the Protection tab.
Check the ‘Locked’ option.
Select ok.

Step 4: Secure the Worksheet.

Presently that the ‘Bolted’ property is empowered for cells with recipes (and not for different cells), securing the whole worksheet would just confine access to the phones with equations.

Here are the means to secure the worksheet:

Go to the Review tab.
Click on Protect Sheet.
In the Protect Sheet dialog box, make sure the option ‘Protect worksheet and contents of the locked cells’ is checked.
[Optional] Specify the password.
Click OK.

When you are complete with the over four stages, all the cells that have equations would be bolted, and the user wouldn’t have the option to transform anything in it.

For more information this link office.com/setup.

Search This Blog

www.office.com/setup, office.com/setup