How to protect specific cells in excel. Protecting cells in Excel from changing or editing values or formats

All tips

You can put protection on a sheet so that everyone can view the contents of the sheet, but nothing can be changed in it. You can also block everything except a range of cells for data entry. And sometimes we can simply hide formulas or data sources.

How to protect a sheet in Excel

In the data table, we will protect the sheet cells from changes, data in the header and the totals row.

Now let's check. Try entering data in any cell outside the range B2:B6. As a result, we receive the message: “The cell is protected from changes.” But if we want to format any cell on the sheet (for example, change the background color), we can do this without restrictions. We can also make any changes in the range B2:B6 without restrictions. How to enter data and format it.

As you can see in the figure, the “Sheet Protection” window contains a large number of options that can be used to flexibly restrict access to sheet data.

How to hide a formula in an Excel cell

It often happens that the most valuable thing on a sheet is the formulas, which can be quite complex. This example saves formulas from accidental deletion, modification or copying. But they can be viewed. If we go to cell B7, then in the formula bar we will see: “SUM(B2:B6)”.

Now let's try to protect the formula not only from deleting and editing, but also from viewing. There are two ways to solve this problem:

Prevent cells from being selected on the sheet.
Enable hiding cell contents.

Let's look at how to implement the second method:

Now go to cell B7 and make sure that nothing is displayed in the formula bar. Even the result of calculating the formula.

Note. The “Protection” tab is only available when the sheet is unprotected.

How to hide a sheet in Excel

Let's say we need to hide purchase prices and markups in the price list:

Attention! Sheet protection is the least safe protection in Excel. You can get a password almost instantly using hacking programs. For example, such as: Advanced Office Password Recovery– this program allows you to remove protection from Excel sheets, macros, etc.

Helpful advice! To see hidden sheets Excel and find out their true number in a protected workbook, you need to open the macro editing mode (Alt+F11). The left "VBAProject" window will display all the sheets with their names.

But even here access can be blocked with a password. To do this, select the tool: “Tools” - “VBAProjectProperties” - “Protection” and enter the password in the appropriate fields. On the other hand, if set passwords This means that the book hides some of the data from the user. And with a strong desire, the user will sooner or later find a way to gain access to the data. This should be remembered when you want to show only part of the data and want to hide part! IN in this case you should correctly assess the level of secrecy of information that is provided to other persons. Responsibility for safety lies primarily with you.

Note. Grouping when protecting an Excel sheet does not work with any settings. You can make the grouping function available in full mode on a protected sheet only using macros.

You can protect information in an Excel workbook different ways. Set a password for the entire book, then it will be requested every time you open it. Put a password on separate sheets, then other users will not be able to enter and edit data on protected sheets.

But what if you want other people to be able to work normally with an Excel workbook and all the pages that are in it, but you need to limit or even prohibit editing data in individual cells. This is exactly what this article will discuss.

Protecting the selected range from modification

First, let's figure out how to protect the selected range from changes.

Cell protection can only be done if you enable protection for the entire sheet. By default, in Excel, when you enable sheet protection, all cells located on it are automatically protected. Our task is to indicate not everything, but the range that is needed at the moment.

If you need another user to be able to edit the entire page, except for individual blocks, select all of them on the sheet. To do this, click on the triangle in the left top corner. Then click on any of them right click mouse and select Format Cells from the menu.

In the next dialog box, go to the “Protection” tab and uncheck the item "Protected cell". Click OK.

Now, even if we protect this sheet, the ability to enter and change any information in blocks will remain.

After that, we will set restrictions for changes. For example, let's disable editing of blocks that are in the range B2:D7. Select the specified range, right-click on it and select “Format Cells” from the menu. Next, go to the “Protection” tab and check the “Protected...” box. Click OK.

The next step is to enable protection for of this sheet. Go to the tab "Review" and click the "Protect Sheet" button. Enter the password and check the boxes for what users can do with it. Click OK and confirm your password.

After this, any user will be able to work with the information on the page. In the example, fives are entered in E4. But when you try to change text or numbers in the range B2:D7, a message appears that the cells are protected.

Set a password

Now let’s assume that you yourself often work with this sheet in Excel and periodically need to change the data in protected blocks. To do this, you will have to constantly remove the protection from the page, and then put it back. Agree that this is not very convenient.

Therefore, let's look at the option of how you can set a password for individual cells in Excel. In this case, you will be able to edit them by simply entering the requested password.

Let's make it so that other users can edit everything on the sheet except the range B2:D7. And you, knowing the password, could edit blocks in B2:D7.

So, select the entire sheet, right-click on any of the blocks and select “Format Cells” from the menu. Next, on the “Protection” tab, uncheck the “Protected...” field.

Now you need to select the range for which the password will be set, in the example it is B2:D7. Then go to “Cell Format” again and check the “Protectable...” box.

If there is no need for other users to edit the data in the cells on this sheet, then skip this step.

Then go to the tab "Review" and press the button "Allow changing ranges". The corresponding dialog box will open. Click the “Create” button in it.

The name of the range and the cells it contains are already specified, so simply enter Password, confirm it, and click OK.

We return to the previous window. Click “Apply” and “OK” in it. This way, you can create multiple ranges protected with different passwords.

Now you need to set a password for the sheet. On the tab "Review" Click the “Protect Sheet” button. Enter the password and check the boxes for what users can do. Click OK and confirm your password.

Let's check how cell protection works. In E5 we introduce sixes. If you try to remove a value from D5, a window will appear asking for a password. By entering the password, you can change the value in the cell.

Thus, knowing the password, you can change the values in protected cells of the Excel sheet.

Protecting blocks from incorrect data

You can also protect a cell in Excel from incorrect data entry. This will come in handy when you need to fill out some kind of questionnaire or form.

For example, a table has a column "Class". There cannot be a number greater than 11 or less than 1, meaning school classes. Let's make the program throw an error if the user enters this column the number is not from 1 to 11.

Select the desired range of table cells – C3:C7, go to the “Data” tab and click on the button "Data checking".

In the next dialog box, on the “Options” tab, in the “Type…” field, select “Integer” from the list. In the “Minimum” field we enter “1”, in the “Maximum” field – “11”.

In the same window on the tab "Message to be entered" Let's enter a message that will be displayed when any cell from this range is selected.

On the tab "Error message" Let's enter a message that will appear if the user tries to enter incorrect information. Click OK.

Now if you select something from the range C3:C7, a hint will be displayed next to it. In the example, when we tried to write “15” in C6, an error message appeared with the text that we entered.

Now you know how to protect cells in Excel from changes and editing by other users, and how to protect cells from incorrect data. In addition, you can set a password, knowing which certain users will still be able to change data in protected blocks.

Rate this article:

(1 ratings, average: 5,00 out of 5)

Webmaster. Higher education with a degree in Information Security. Author of most articles and computer literacy lessons

Discussion: 13 comments

Answer

How to protect cells in Excel from editing, step-by-step instruction and ways to protect data in table cells.

Do you want to know how to consistently earn money online from 500 rubles a day?
Download my free book
=>>

From time to time, when working with tables in Excel, you need to install protection against intentional or accidental editing. This especially applies to ranges containing formulas for performing any calculations.

After all, if another user makes some changes to the work you have done previously, the consequences can be very unpleasant.

How to protect cells in Excel from editing

In Excel you can install it like full protection pages from data correction, or partial. Therefore, the choice remains solely with the user who decides to protect the data.

Protecting specific cells

So, if you need to protect a specific cell from making adjustments, then do the following:

The principle of operation is similar when using the “Review” tab.

But in this case, first select “Protect Sheet” and then check “Highlight locked cells.”

Thus, if someone wants to make changes in a certain area, then without knowing the password you previously set, nothing will happen.

How to remove protection

To unlock access and make it possible to make adjustments, also through “Review” or the “Cells” section in the main menu, go to “Sheet Protection” and select “Unprotect Sheet”. Next, enter the password specified when blocking.

It is best to write down the password and keep it in a safe place. If you lose it, you cannot recover your password. When entering your password, it is important to enter uppercase and lowercase letters correctly. Since if you wrote, for example, the name of your dog with capital letter, and then entered the password with a small one, then you will be told that the password is incorrect.

Protecting the page

In a situation where you need to protect the entire table from unexpected changes, you need to:

Go to “Review” and in the “Changes” subsection click on “Protect Sheet”.
Next, uncheck the first line and leave it next to the “Select unlocked cells” entry.
Then, in the “Password for...” field, enter the password and repeat it.
Click on “OK”.

Universal method of protection

If you need to protect not a specific area in the table, but all the data on the page as a whole, then move your mouse to the lower left corner, where the sheets are located.

By the way, the name of the completed sheet may not be the standard “Sheet 1”, but something else. It's not very important.

So let's get started:

As a result, when you try to make any changes, you will be prompted for a password. When you need to open access to be able to make adjustments, then also move the mouse over the bottom of the program window on the left side.

Then, right-click on it. Next, select “Unprotect…”, or use “Review” for these purposes.

Summary

As you can see, when the question arises about how to protect cells in Excel from editing, there are several options for solving it. It all depends on whether you need to protect all the data on one sheet or only certain areas.

So, first of all, how to distinguish a protected cell from an unprotected one? Select the cell/cells, right-click on one of them and context menu select “Format Cells...”.

Step 2

Go to the “Protection” tab. This is the rightmost tab in the cell formatting window.
Look at the two checkmarks that are there.
If the top (“Protected cell”) is checked, it means that editing these cells will not be available if you enable sheet protection. If you remove it, it can be edited even in protection mode.
If the bottom one (“Hide formulas”) is checked, when protecting the sheet, you will not be able to see the cell formula in the formula bar.
Click “OK” to close this window.
By default, all cells have the top one marked

Step 3

Select “Tools → Protection → Protect sheet...” from the menu at the top.

Step 4

You will see a window in which you can configure exactly how the sheet will be protected. You can enter a password in the text field that will need to be entered to remove the protection.
Now about the main checkboxes in the “Allow all sheet users” box. “Selecting blocked cells” - is it possible to select cells that have the “Protected cell” checkbox (see step 2), “Selecting unblocked cells” - that do not have the checkbox. “Formatting cells/columns/rows” – is it possible to format the format of cells (color, font, borders, ...), columns (width), rows (height). “Insert columns/rows/hyperlinks” – whether it is possible to add columns, rows to the page, insert hyperlinks, “Deleting columns/rows” – whether it is possible to delete columns and rows. Click “OK” to protect the sheet.

Step 5

Now you will not be able to change the contents of any of the cells that have the “Protected cell” checkbox checked. If you try, you will see a window saying that you cannot do this.

Step 6

To remove sheet protection, select “Tools → Protection → Unprotect sheet...” from the menu. If you specified a password when protecting the sheet, Excel will ask you for the password.

Step 7

Now to practice. First, decide which cells should be protected and which should not. There are two examples in the figure - cells that should be blocked are marked in dark.
In the left one it’s easier to first block all the cells, then unlock the rectangular field.
In the right one it’s easier to first unlock everything, then block the necessary cells.

Step 8