How to protect cells in Excel from editing and content changes

If the Excel spreadsheet contains important data that needs to be protected from outside interference, then this is quite possible to do. Sometimes a person puts a lot of effort into creating a PivotTable or array that includes a huge amount of data, and another person accidentally deletes or changes everything. If there is no backup, this can turn into a tragedy. To prevent it, you can protect the Excel document or some part of it.

There are many more situations when you need to protect a document or a separate range (cell):

1. If the Excel spreadsheet contains unique formulas that other people shouldn’t see. For example, a formula for calculating the price that competitors could steal. 
2. To focus the user’s attention on specific columns so that he cannot even accidentally enter data in the wrong column. 
3. In learning. For example, if you create a crossword puzzle in Excel, it would be correct to mask the answers. To do this, you can place them on a separate sheet, block it and hide the formulas by which the program would determine whether the answer is correct. 

There are many other situations in which you might want to lock a workbook, sheet, or specific range. Let’s look at them in more detail.  

How to protect Excel cells from being changed

You can protect an Excel cell in several ways: use the File menu to lock individual cells, or use the Review tab. It is also possible to lock a specific range or a single cell. Each person can choose the one that will be the most attractive for him.

Method 1: Using the File Menu

To use the “File” menu to lock cells, you must perform the following sequence of steps:

1. Select all cells to be blocked. There are several ways to do this: through the combination Ctrl + A or by clicking on a special rectangle that is located on the coordinates panel. In the first case, you need to click once if a cell that is outside the table has been selected before and twice if it is within it.
   

   

2. After that, by right-clicking on any part of the selected area, after which we select the “Format Cells” item in the panel that appears.
   

   

3. A small window will appear with many tabs. We are interested in the latter (with the signature “Protection”). First we need to make sure that there is no checkmark next to the “Protected cell” item. After that, press the “OK” key.
   

   

4. Now, by any method (for example, using the mouse), we select those cells that should not be changed by outsiders. In the case of our example, this range is the column where the formulas are written. Next, using the right click on the selected section of the table, we call up the menu, where we select the same item “Format Cells”.
   

   

5. Now put a checkmark next to this item. Confirm the changed parameters with the OK key.
   

   

But doing the steps just given is not enough. You must first protect the sheet itself. Once this is done, the user will not be able to edit certain cells. To do this, you need to click on the “File” button, which is located in the upper left corner of the latest versions of Excel.

Next, the main menu of the program will appear. We are interested in the “Details” section, which we go to. There are many settings, one of which is “Protect Book”. When you click on this button, a large number of options appear, but we are interested in “Protect the current sheet.”

Next, a window with options will open. Be sure to make sure that the box next to the item “Protect the sheet and the contents of protected cells” is checked. Below you can select additional parameters that are set voluntarily. Here you can more flexibly configure the protection of cells and the document as a whole, not only for one user, but also for several. For protection, it is necessary to enter a password with which a person can change the protection or unlock settings. After entering the settings, click the “OK” button.

Let’s describe some parameters:

1. Highlight blocked cells. After that, a person without a password will not even be able to click on the locked cell.
2. Highlighting unlocked cells. The same, only applies to those cells that were not blocked.
3. Cell formatting. The user does not have the ability to change the appearance of the cells.
4. Column formatting.
5. String formatting. What is the previous one, what is this paragraph, prohibits changing the appearance of columns or rows in general. 
6. Insert columns/rows/hyperlinks. These are three different clauses that mean no new columns or rows or links can be added.
7. Remove columns/rows/hyperlinks.

You can also manage some other options to make file protection more flexible.

Next, another window will appear in which you need to re-enter the password. To confirm your actions, you need to press the “OK” button again.

Now the cells are securely protected from editing. But if you still need to make changes, you can always enter a password and do it. The same cells that were not previously blocked, anyone can change at their discretion.

Method 2: Using the tools of the Review tab

This is another way to protect cells, which is no more complicated than the previous one. The first five paragraphs of the previous instructions are generally identical. That is, you need to make sure that the protection of the entire sheet is removed and remains exclusively for certain cells.

Then you need to go to the “Protection” tool group, which is located on the “Review” tab, and then click the “Protect Sheet” button.

All these items are located on the main panel at the top of the Excel window.

After that, we will see a window that is already so familiar to us. In it, you need to specify the necessary settings for protecting cells or the sheet as a whole. In all other respects, the actions are similar to those that we described earlier: enter a password and so on.

Attention! If the size of the Excel window is small, then the dimensions of the “Protect” button will also change. It will become smaller, and you can give specific commands using the pop-up menu that appears after clicking on this button.

How to Protect Individual Cells in an Excel File

The sequence of actions that need to be taken to add protection to specific cells does not fundamentally change. But there is one problem here. The fact is that such an opportunity is not included in the standard functionality of this program. Of course, this is not convenient. What actions can be taken?

For example, we have a simple range that needs to be sent to other departments of the store so that they enter their data and send it. However, they cannot be allowed to edit other cells. Therefore, they need to be protected. 

First you need to make sure that those cells that will be edited are not protected. To do this, select the required range. After that, we call the “Format Cells” window in the same way as described above, only at the same time we select the “Protected cell” item.

After that, go to the item “Review” – “Protect Sheet”. Next, a dialog will appear in which two checkboxes will be set by default. The first one we need to remove. After that, we protect this window with a password, and confirm our actions by pressing the OK button.

Now the range D4:D11 is open for editing by other people, while those who do not have a password cannot make the necessary changes. We see that the sequence of actions is very similar. You just need to select not the entire sheet, but a specific range. That is, with the help of one function, you can do several actions at once. Very comfortably.

How to Protect All Cells in an Excel File 

Sometimes it may be necessary to password-protect not only one sheet, but the entire file. This can be useful, for example, if a lot of people can access the document. If you protect it, you can still view it, but a person without a password is not allowed to make any changes. 

After protecting the entire workbook, any adjustments will be prohibited, including renaming sheets, inserting new ones, changing their location, changing the contents of cells, and so on. 

What can be done to make this possible? Simply click “Protect book” on the “Review” tab and enter the appropriate checkboxes next to the object of protection – the structure of the document or windows. Next, you need to enter a password.

Now you can check everything in practice. If it is not possible to make changes to the sheet name, then the protection has worked. You can also find that all the teams have turned gray. It says the same.

If you need to remove protection, then you need to follow the same path, just uncheck the boxes that were checked earlier. 

How to unprotect Excel cells from editing content

If you try to tritely edit a cell, a dialog box immediately appears warning you that the sheet is protected. What can be done to remove the blocking of the contents of the cells or the whole sheet?

1. Find the Review tab in the menu bar at the top of the screen. There you can find the button of interest to us.
2. After making changes, you need to check by changing the contents of different cells of this workbook located on different sheets. For example, you can try renaming a sheet as a test. If you right-click on the name, a context menu will appear, but all possible options will be grayed out and cannot be clicked. This means that they are not active. 

To remove protection in this case, you need to do the same steps, only in reverse. The system will offer a window for entering the password that was assigned in the first paragraph. 

How to hide the formula of a locked cell

 The top row always contains the formula, even if the cell shows the result. And if a cell is locked, you may want to hide it from view. Especially if the formula is complex, and it is a secret. To accomplish this task, you must check the box next to the Hide Formulas menu on the Protection tab of the Format Cells window.

After we click the “Protect Sheet” button, the formula will be automatically hidden from view. 

There is another method – to simply prevent the user from selecting cells. To do this, you need to protect the sheet in a way through the “Review” menu. In the settings in the password entry window, uncheck the box next to the “Highlight locked cells” option. After that, we confirm our actions and enter the password again. 

After that, a person will not even be able to select a cell, which automatically protects it from viewing the formula. 

Protecting an Excel workbook from editing

An Excel workbook is the same as a file. Like a standard book, it consists of sheets, each of which in turn consists of columns, cells and rows. Each of these elements can be protected in the manner described above.

But what can be done to protect the book as a whole? It is logical that the sequence of actions is the same as in the section about the file, because the book and the file are one and the same. That is, the sequence of actions is as follows:

1. Go to the “File” tab.
2. Click the “Protect the book” button and set the protection using a password.
3. Enter your password.
4. Confirm the password entered in the previous step. This item is needed in order to check whether the person entered the password correctly in the previous step. Without it, it would be easy to make a mistake. And since the wrong code was entered into the program, it would not be possible to unlock the book. Confirming your password helps resolve this issue. 

Thus, protecting a book is a simple task that does not require much effort. The same goes for individual cells, rows, columns.