Protecting cells from value or format changing and editing in Excel

Data in Excel can be protected from extraneous interference. This is important, because sometimes you spend a lot of time and effort creating a pivot table or a large array, and another person accidentally or intentionally changes or completely deletes all your work.

Let's consider the ways of protecting an Excel document and its single elements.



Protection of Excel cells from changing

How to put protection on a cell in Excel? By default, all cells in Excel are protected (locked). It's easy to check: right-click on any cell, select «Format Cells» – «Protection». We can see that the check box on the «Locked» item is selected. But this does not mean that they are protected from changes.

Why do we need this information? The thing is that Excel doesn’t provide the function allowing you to protect a single cell. We can enable protection of the worksheet, and then all the cells on it will be protected from editing and other interference. On the one hand, it is convenient, but what if we don’t need to protect all the cells, but only some of them?

Let's consider an example. We have a simple spreadsheet with data. We need to send this spreadsheet to branch stores, so that the stores could fill in the «SOLD QUANTITY» column and send it back. To avoid making any changes to other cells, let’s protect them.

First, remove protection from those cells to which employees of branch stores will make changes. Select C3: D7, right-click to open the menu, select «Format Cells» and remove the check box from «Locked».

Now select «REVIEW» – «Changes» - «Protect Sheet» tab. A window appears with 2 check boxes selected. Clear the first one, to exclude any interference of branch stores’ employees, except for filling «SOLD QUANTITY» column. Come up with a password and click OK.

Warning! Do not forget your password!

Now other people can only enter some value in C3: D7 range. Since we have limited all other actions, no one can even change the background color. All the formatting tools on the top toolbar aren’t active, that is, they do not work.



Protection of an Excel workbook from editing

If several people work on one computer, it is advisable to protect their documents from editing by third parties. It is possible to put protection not only on separate worksheets, but also on the whole workbook.

When the workbook is protected, outsiders can open the document, see the written data, but they cannot rename the sheets, insert a new one, change their location, etc. Let's have a try.

Save the previous formatting. That is, you still can make changes only in «SOLD QUANTITY» column. To protect the workbook completely, select «REVIEW» – «Changes» - «Protect Workbook» tab. Click the check box opposite «Structure» item and come up with a password.

Now, if we try to rename the worksheet, we will not succeed. All commands are gray-colored: they don’t work.

The protection from the worksheet and the workbook is removed with the same buttons. When removing, the system will require the same password.