How to lock a table in excel. Lock and unlock specific areas of the protected sheet. selection of unlocked cells

Tab. In the section, some data is not ctrl + z, some cells will fly off on it. Gotta,

create a password.filled in the column SOLDand Cell LockThe file will be updated when columns are deleted.

highlighting locked cells how to enable protection, they will not be able to change the sheet.

Selecting Sheet Elements to Lock

"Information" will be well protected. cancelled. It's somehow

Actions (fill in the cellsSo that this cannot beNow if we try QUANTITY and sent for data protection>changes to its originalNote: Moving the pointer to cells, see the following

data in otherOn the tab "We try" as near the parameter "Protected" click on the button Let's see how you can fix it? Thanks to the text, changing the fonts was done.

rename the sheet back. To avoid or Blocking and Data. If for which a section is set, its areas. Reviewing can be provided more quickly by the cell ”in the“ Protect Book ”window, this is exactly what you can do for patience! And etc.) -Alex_ST

Enable sheet protection

itisnottomakinganychangeunlockingcertain areas>AddingoreditingnotesRemovingcolumns

Step 1: Unlock all cells that need to be modified

Step 2. Sheet protection

Protect them.News about recent updates>View Hidden Scripts, Change Insert Columns on tab Need to Unlock Cells,Protect SheetChangeLanguage. This page how we "Protect Current Sheet" Excel Quote macro when it As Range)

Do not work. To begin with, we will free from

Excel Online can Remove sheet protection scripts with installed not protected, userProtection

which users must do not select a team Translated automatically, so we did it in the previous one. Doesn't exist in Excel

(Serge_007)200?"200px":""+(this.scrollHeight+5)+"px");">Each macro is simple	Application prohibits UnDo
Target.Locked = Target.Locked	Protection is removed from the protection sheet of those cells, find out in the blog or ban on change can insert columns in the dialog box change and then is a security feature. Protect the sheet its text can
way.	Sheet protection settings open. special tool, designed to clear the memory stack in relation to any End Sub and books on topics Where are branch employees? Microsoft Excel. Reviewing and deleting these but they cannot
	Go to the "Review" tab. Must be to block individual last user activity Actions on the sheet?Copied entirely together with the same buttons. When will make changes.To use the full set ofChangesscripts. Users can
delete.	. By default users using a password or changing locked cells Protect a workbook grammatical errors. For We click on the button checkbox next to the parameter Cells, but this at the time of execution And one more thing:
with RANGE/CELL formatting	unchecking the system will select D4:D11, right applications and services> change the values in deleting lines allowed to allocate protected without him. On a sheet. to turn it back on
it is important for us to	"Protect sheet". This
"Protect sheet and content"	procedure can be done
macro and exactly	maybe a macro is possible, will be dumped from
demand the same	button to call up the menu, Office, work with Remove sheet protection changing cells, ifRemove rows. cells.In an Excel file, click Sheet protection is different from protection. In the section this article was
button is located in	protected cells" with the help of protection so UnDo becomes for the user to copy clipboard. password. choose CELL FORMAT trial version or. cells are not protected,
Note:	selection of unlocked cells sheet tab, which file protection or Protect sheet or useful to you. Please "Changes" tool block. . If desired, you can The whole sheet. Inaccessible cell, and when And if from Alexander Ivanov
and uncheck	Purchase it onIf the sheet is password protectedand add newIf Move pointer to cells You want to protect. excel books password. Protect the book. Give you a couple. After that, it opens exactly
set blocking of certain	In order to protect the Quote pasted it - cells copied only: How to block a cell
From the point PROTECTED	Office.com. Enter it in the script. Deleting rows for which it is cleared Select the cells you need 200?"200px":""+(this.scrollHeight+5)+"px");">It is somehow possible to insert only VALUE (after double-clicking from deletion or CELL.Data in Excel can beina dialog boxIf you want, you can enterprotected and checkbox
modify other users.

Now select the REVIEW tab to protect from strangers Protection sheet password in the fieldInsert Rows Protected cell Tip: Information about how Which need to be changed, you, with the help of as in below. But, in

which are described below. Quote

Cells, not formulas), then no, it remains the possibility of it - PROTECT THE SHEET. intervention. This is important, and click the Password button to disable protection is not protected, the user on the tab To select multiple non-contiguous to lock the file to before the buttons at the bottom of the page. First option. All

In most cases, settings Select the entire sheet by clicking (Alex_ST)200?"200px":""+(this.scrollHeight+5)+"px");">The entire cell is described here?

How to determine if a sheet is protected?

editing. To cell A window like this appears because sometimes OK sheet can insert lines Protection cells, click and other users do not protect the sheet or Also for convenience full next steps set by default

By the rectangle, which is the implementation of the UnDo method. Thanks in advance for the whole sheet,

Unprotect an Excel sheet

There is a note, explanations where they will be affixed

You spend a lot.

and press the button but they can't in the dialog box hold down the CTRL key were able to open it book, but you can provide a link to similar. meet user needs located at the intersectionfor macros answer! then you can limit installed in it

2 ticks. First time and strength Protection and security in OK delete. Cell Format and click them

Blocking cells or specific areas to protect them

 left mouse button. Protecting an Excel file. After applying protection. Language) . How to put a password on In the field Right-click we all this: Who do you need to know the range of "protected" so that we remove all this to exclude Spreadsheet or bulkExcel file protectionPassword confirmationUsing commands to sortallowed to highlight unprotectedRight clickTo prevent adding, modifying,To unprotectAssume a manager wantsan Excel file

“Password to disable mouse protection. In the appeared we write

Said?! cells. preserved, it is necessary that any intervention of the array employees, and the other Book protection enter again

data (tab cells, and Anywhere move, copy or just remove the password. You can protect the entire By clicking on any

sheet" context menu, go to...Quote

Alexander Ivanov the user could make affiliates, except for filling in the person by accident or Locking and unlocking certain password and click

Protecting cells in Excel from changing and editing values or formats

Data move between unprotected sheets and select hide and show +(this.scrollHeight+5)+"px");">Is every macro: Thank you very much!

column SOLD QUANTITY. intentionally changes either the areas of the protected sheet

Protecting an Excel cell from being changed

OK, a group of cells on a protected sheet command in a workbook, you can protect formulas. Also change several or when you try keyword, which is "Format of cells ...": I don’t really understand when using it what you need! One cell, but not We come up with a password and completely deletes everything

Locking cells.Sorting and filteringsheet usingCell Formatsee article Protection : why it stopped could replace it, press OK. Your work. Showing and hiding formulas

Important:). TAB keys.(or press the keys of the book. We try our best to protect this. A message will appear with this, access to features. A window for changing the format will open at the link nothing

With respect to any actions, work to cancel the action, or delete. Attention! Don't forget your

To find out how the protection is faster to provide you with a book. Before inclusion which says editing. After the cells. Go to did not understand. Do on the sheet? Not in that is, after Serge_007 password! Excel and his

linked cells toUse strong passwords consisting of

Users will not be able to sort Change settings in a dialog box or Excel file is different with current password protection help, unlocked stating that as settings are done, tab me macros once in a while! Every every cell change

: Leave the cell unlocked

Protecting an Excel workbook from editing

Now in the range D4:D11 separate elements.

The cell is protected from click on the "Protect" button work, it’s not the macro that just clears you can’t press ctrl + z and protect the sheet

Unauthorized persons will be able to Mac computer), or sheet, read This page is translated into a book. Now that changes. If you

"OK". Uncheck the box next to work. Tell me how much memory stack is last - no effect. Attached cell

Just enter some cell in Excel? On a protected sheet of register, numbers and sheet, regardless of or

How to block a cell from being deleted or replaced

and then open article Protection and automatically, so its manager will complete the work. know the password and . parameter will cost such a user action on How to fix this? A1 value. Because weDefault allVideo:Protect book orspecial characters. In the settings of this parameter.

Conditional FormattingSecurity tab in Excel.
The text may contain with a book, you

consciously want to editAnother window opens,"Protected cell"service, so that youthe moment you execute the macroThanks in advance forAlexanderIvanovlimited all other cells in the Excel sheet with a password (Excelweak passwordsuse autofilter.If conditional Formatting

data, then for in which follows. Click on the button

I was prescribed this And that's why the answer!!: Thanks for the answer! Actions that no one is defending. It's Easy 2013) Not all of these use dropdown arrows have been applied before and uncheck block on unprotected errors. For us, the cell. To unlock you, repeat the password. Is this an "OK" macro? To become inaccessible in UnDo

Alex_ST But, unfortunately,
Can even change check: click on
Note:
symbols. The password must
To change the sheet protection setting filter, Protected sheet cell:
It is important that this Follow the steps below, you will need to do done in order.
Within a specific sheet Quote: Well, my friend, you can use this cell as a background color. All any cell right

We try as Consist at least in ranges if the formatting will change. The formulas article was for you to block cells some actions. So that if the user Select the range that you want you can on

(AlexanderIvanov)200?"200px":""+(this.scrollHeight+5)+"px");">macro possible to You want too much: not only edit, formatting tools on the button, select FORMAT can be provided more quickly
which of the 8 autofilters are applied. when entering a value,
Decide what actions users: If you are not helpful. Please
On the sheet. Go to the tab for the first time to block. Again go to ctrl+v to paste only the user copied the cell,
So that both the macro and replace the top toolbar CELLS - PROTECTION. you with up-to-date reference
Signs, but better Note: satisfying a certain condition. Must perform on want others

take a couple of seconds Select the cells you need "Review" entered the wrong password, into the value and formats window, and when inserted worked, and canceled by another cell. Let me explain: not active. I.e. We see that the checkbox with materials on your use a passphrase. Users will not be able to apply sheet column formatting (for example, inserting users saw formulas, and report, helped to block. "Format of cells ..." and at the same time her - that was possible for people working with they do not work.

By default, Sheet Protection locks all cells so that none of them are editable. To enable some cell changes while leaving other cells locked, you can unlock all cells. You can lock only certain cells and ranges before you protect the worksheet and, optionally, allow certain users to change only in certain ranges of the protected worksheet.

Lock only certain cells and ranges of cells in a protected worksheet

Follow the steps below.

If the sheet is protected, do the following:

Select the entire sheet by clicking the button Select all.

On the tab home click the dialog box call button cell font format. You can also press CTRL+SHIFT+F or CTRL+1.

In the dialog box Cell Format tab Protection uncheck the blocked box and click the button OK.

When you protect a sheet, all cells will be unlocked. Now you can select specific cells to protect.

Select only the cells on the sheet that you want to block.

Open the popup again Cell Format(Ctrl + Shift + F).

This time on the tab Protection check the Protected cell box, and then click the button OK.

On the tab Peer review press the button Protect Sheet.

From the list, select the items that you want users to edit.

Unchecked	Prohibited actions
	Protected cell tab Protection in the dialog box Cell Format
	Protected cell tab Protection in the dialog box Cell Format
cell format	Cell Format or Conditional Formatting
column formatting	home, group cells, button Format).
string formatting	home, group cells, button Format).
insert columns	Insert columns.
row insertion	Insert rows.
inserting hyperlinks
deleting columns	Removing columns. If the team deleting columns protected and the team insert columns
deleting rows	Removing lines. If the team deleting rows protected and the team row insertion
sorting	Data, group Sort and filter).
using autofilter

changing objects	Fulfill the following actions:
changing scripts

Chart Sheet Elements

In the field, enter the password for the sheet, click the button OK

The password is optional. If you don't set a password, anyone can unprotect the sheet and change protected items.

Choose a password that is easy to remember because if you forget your password, you will no longer be able to access protected sheet items.

Unlock cell ranges on a protected worksheet for editing by users

To grant certain users permission to change ranges on a protected worksheet, the computer must have operating system Microsoft Windows XP or later, and the computer itself must be in a domain. Instead of using permissions that require a domain, you can also set a password for a range.

Select the sheets you want to protect.

On the tab Peer review in Group Changes press the button.

This command is available only if the sheet is not protected.

Perform one of the following actions:

To add a new editable range, click the button Create.

To modify an existing editable range, select the field, then click the button Change.

To remove an editable range, select the field Protected sheet ranges unlocked by password, then click the button Delete.

In field Name enter the name of the range to be unlocked.

In field Contains cells enter an equal sign ( = ) followed by a link to the range to be unlocked.

You can also click the button Collapse Dialog, select a range on the sheet, and then click the button again Collapse Dialog to return to the dialog box.

To control access with a password in the field Range password enter the password to access the range.

When using access permissions, it is not necessary to set a password. Using a password allows you to view the credentials of all authorized users who change the range.

To set access permissions, select Permissions and press the button Add.

In field Enter object names to select (examples) enter the names of the users that you want to be allowed to change ranges.

To see how usernames should be entered, click examples. To check if the names are correct, click the button Check names.

Click the button OK.

To specify the type of permissions for the selected user, in the field Permissions check or uncheck Allow or ban, and then click the button Apply.

Click the button OK twice.

If necessary, enter the specified password.

In the dialog box Allow changing ranges press the button Protect Sheet.

Listed Allow all users of this sheet select the items that users should modify.

Learn more about sheet items

Unchecked	Prohibited actions
selection of blocked cells	Move the pointer to cells that have a check mark Protected cell tab Protection in the dialog box Cell Format. By default, users are allowed to highlight protected cells.
selection of unlocked cells	Move the pointer to unchecked cells Protected cell tab Protection in the dialog box Cell Format. By default, users are allowed to select unlocked cells, and to move between unlocked cells on a protected worksheet using the TAB key.
cell format	Changing Options in Dialog Boxes Cell Format or Conditional Formatting. If conditional formatting was applied before the sheet was protected, the formatting will change when you enter a value that satisfies the specified condition.
column formatting	Using any column formatting commands, including changing the column width or hiding columns (tab home, group cells, button Format).
string formatting	Using any line formatting commands, including changing line height or hiding lines (tab home, group cells, button Format).
insert columns	Insert columns.
row insertion	Insert rows.
inserting hyperlinks	Insert new hyperlinks (even in unlocked cells).
deleting columns	Removing columns. If the team deleting columns protected and the team insert columns is not protected, the user will not be able to delete the columns he inserts.
deleting rows	Removing lines. If the team deleting rows protected and the team row insertion is not protected, the user will not be able to delete the rows he inserts.
sorting	Using commands to sort data (tab Data, group Sort and filter). Users will not be able to sort ranges containing locked cells on a protected worksheet, regardless of this setting.
using autofilter	Use the drop-down arrows to change the filter on ranges if autofilters are applied. Users will not be able to apply or remove autofilters on a protected worksheet, regardless of how this setting is configured.
using PivotTable reports	Formatting, changing the layout, updating or otherwise modifying PivotTable reports, or creating new reports.
changing objects	Perform the following steps: Making changes to graphic objects, including maps, embedded charts, shapes, text boxes, and controls that weren't unlocked before sheet protection was set. For example, if a worksheet has a button that runs a macro, you can click it to run the macro, but you can't delete it. Making any changes (such as formatting) to an embedded chart. The chart will still update when its source data changes. Add or change notes.
changing scripts	Viewing hidden scripts, editing scripts with a ban on changes and deleting these scripts. Users can change values in mutable cells if the cells are not protected and add new scripts.

Chart Sheet Elements

In field Password to disable sheet protection enter the password, press the button OK, and then re-enter the password to confirm.

The password is optional. If it is not set, any user can unprotect the sheet and change the protected items.

Make sure you choose a password that you can remember. If you have lost your password, you will not be able to access protected items on the sheet.

If a cell belongs to more than one range, it will be editable by users who have permission to edit any of those ranges.

If a user tries to edit multiple cells at the same time and is allowed to make changes but not all of those cells, the user will be prompted to edit cells one at a time.

additional information

You can always ask the Excel Tech Community a question, ask for help in the Answers community, or suggest a new feature or improvement on the Excel User Voice website.

Note: This page is translated automatically, so it may contain inaccuracies and grammatical errors. It is important to us that this article is useful to you. Was the information helpful? For convenience also (in English).

In this article I will tell you how to protect cells in Excel from changes and editing. Cell protection may mean that users who open your file will not be able to edit cell values, as well as see formulas.

Before we figure out how to set up protection, it is important to understand how cell protection works in Excel. By default, all cells in Excel are already locked, but in fact, access to them will be limited after you enter the password and conditions for restricting access in the sheet protection settings.

How to Protect All Cells in an Excel File

If you want to protect absolutely all cells in your excel file from editing and changes, do the following:

Go to the tab “ Peer review” on the toolbar => in the subsection “ Protection” click on the icon “ Protect Sheet “:

In the pop-up window, make sure the checkbox next to ““ is checked:

Enter the password in the ““ field, if you want to remove the protection only those users to whom you provided the password:

Select from the list and tick those actions with the cells of the sheet that will be allowed to all users:

Click " OK “

If you have set a password, the system will ask you to re-enter it.

Now, all users who try to make changes or edit cell values will see this message:

In order to remove the installed protection, go to the tab “ Peer review “, and in the section “ Protection” click on the icon “ Remove sheet protection “. After that, the system will ask you to enter a password to unprotect.

How to protect individual cells in Excel from changes

Most often, you may not need to protect the entire sheet, but only individual cells. As I wrote at the beginning of the article, all cells in Excel are locked by default. In order for the lock to come, in fact, you need to configure sheet protection and set a password.

For example, consider a simple table with data on income and expenses. Our task is to protect cells in the range from changes B1:B3 .

In order to block individual cells, do the following:

Select absolutely all the cells on the Excel sheet (using the keyboard shortcut CTRL+A ):

Let's go to the tab " home” on the toolbar => under “ alignment ”click on the icon in the lower right corner:

In the pop-up window, go to the tab " Protection” and uncheck the box “ Protected cell “:

Click " OK “

Thus, we turned off the Excel setting for cells of the entire sheet, in which all cells are ready to be protected and blocked.

Now, select the cells that we want to protect from editing (in our case, this is a range of cells B1:B3 );
Go to the tab again home” on the toolbar and in the subsection “ alignment ” click on the icon in the lower right corner, as we did earlier.
In the pop-up window, on the tab " Protection"check the box" Protected cell “:

Let's go to the tab " Peer review ” on the toolbar and click on the icon “ Protect Sheet “:

In the pop-up window, make sure that the box next to “ Protect the sheet and contents of protected cells “:

Enter the password in the field " Password to disable sheet protection “, so that only those users to whom we provided the password could remove protection:

This article will talk about how to protect a cell in Excel from changes. Fortunately, such an option is present in this spreadsheet editor. And you can easily protect all the data you entered from someone else's interference. Also cell protection is good way save yourself from yourself. By protecting the cells in which formulas are entered, you inadvertently do not delete them.

Select the desired range of cells

Now the first method will be provided on how to protect cells in Excel from changes. He, of course, is not much different from the second, which will be described later, but you can’t miss it.

So, in order to protect table cells from corrections, you need to do the following:

Select the entire table. The easiest way to do this is by clicking on a special button, which is located at the intersection of the vertical (row numbering) and horizontal (column designation). However, you can also use hotkeys by pressing CTRL+A.

Click right button mice (RMB).

Select "Format Cells" from the menu.

In the window that appears, go to the "Protection" tab.

Uncheck the "Protected cell" box.

Press OK.

So, we just removed the ability to protect all cells in the table. This is necessary in order to designate only a range or one cell. For this you need:

Select the required cells using the usual stretching with the left mouse button (LMB) held down.

Press RMB.

Select "Format Cells" from the menu again.

Go to "Protection".

Check the box next to Protected Cell.

Click OK.

We put protection on the selected cells

We told the program which cells we want to protect from changes. But this is not enough to protect them. To accomplish this goal, you need to enable sheet protection in Excel. For this:

Click on the "File" tab.

In the menu, go to the "Details" section.

Click on the "Protect book" icon.

Select "Protect Current Sheet" from the menu.

A window will appear in which you need to configure. Follow the guide:

In no case do not uncheck the box "Protect the sheet and the contents of protected cells".

In the window located just below, you can perform more flexible settings, but by default it is set so that no one can modify the settings.

Enter the password in the corresponding field. It can be of any length, but remember that the more complex and longer it is, the more reliable it is.

Click OK.

After the manipulations have been done, you will be asked to re-enter the password for all changes to take effect. Now you know the first way to protect a cell in Excel from changes.

Second way

The second way, how to protect a cell in Excel from changes, as mentioned above, is not much different from the first. Here is a detailed instruction.

Like last time, unprotect the cells from the entire table and put it in the desired area.

Go to Review.

Click on the "Protect Sheet" button, which is located in the "Changes" tool group.

After that, a familiar window will appear in which you need to set the protection settings. In the same way, enter the password, select the required options, check the box next to "Protect the sheet and the contents of protected cells" and click OK.

When working with Excel spreadsheets, sometimes there is a need to prohibit cell editing. This is especially true for ranges that contain formulas or are referenced by other cells. After all, incorrect changes made to them can destroy the entire structure of calculations. Protecting data in especially valuable tables on a computer that other people have access to other than you is simply necessary. The rash actions of an outside user can destroy all the fruits of your work if some data is not well protected. Let's take a look at exactly how this can be done.

Excel does not have a special tool designed to lock individual cells, but this procedure can be done by protecting the entire sheet.

Method 1: Enabling blocking through the "File" tab

In order to protect a cell or range, you need to perform the actions that are described below.

Now those ranges that we previously selected and set their protection in the formatting settings will not be editable. In other areas, you can perform any action and save the results.

Method 2: Enable blocking through the Review tab

There is another way to block a range from unwanted changes. However, this option differs from previous method only those that are performed through another tab.

Range unlock

When you click on any area of a locked range or try to change its contents, a message will appear stating that the cell is protected from changes. If you know the password and consciously want to edit the data, then you will need to do some actions to unlock it.