Get Shift Done: Tips and Tricks

Excel 365: How to Protect Cells in a Shared Worksheet

Do you have a shared worksheet in Microsoft Excel 365 containing important data or carefully-constructed formulas you don’t want altered, accidentally or deliberately? Perhaps it’s the standard hourly rate. Maybe it’s equipment part numbers, or a shipping-cost calculation. Whatever it is, people should just keep their hands off it!

Fortunately, Excel makes it possible to prevent people from changing things. The following steps show how to protect critical data while allowing users to enter information in only the fields you want them to access.

Keep in mind that protecting worksheet data is different than protecting a workbook with a password. The latter security measure is to ensure no one can access a workbook without the proper credentials — a key to get in. Protecting the cells in a shared worksheet means that the cells or range cannot be modified.

Protecting a worksheet is a two-part process that initially may appear to be slightly counter-intuitive. By default, every cell in your worksheet is configured as protected, so we need to unprotect them before activating the actual security feature.

On your worksheet, highlight ranges and cells to which users should have access. You can select multiple single cells by holding down CTRL and clicking on the cells individually.

In the following example, I selected a single cell. This is where the user would enter a value, and the spreadsheet would take care of calculating the rest, using a formula in the cell beneath it. No users should have the ability to alter the formula or the data within the matrix.

Next, right-click on any of your selected cells. Select Format Cells… from the context menu.

Navigate to the Protection tab, and uncheck Locked. (This option should be enabled by default.) Click OK.

Now that we established the authorized cells, we can protect the actual worksheet.

To do this, navigate to the Review Ribbon and choose Protect Sheet.

You are presented with a dialog box containing a long list of security options.

First, ensure the top box is checked. This protects the worksheet and all its contents, following whatever options you select below. You need to enter a password as well; it’s required for anyone to make any changes to the security options enabled on the sheet.

For simplicity’s sake, it is usually easiest to only allow users to “Select unlocked cells” and deselect any other options. This prevents confusion about where your users can interact with the worksheet.

If you want to refine your security options, there are many more selections to choose from. These are summarized for your convenience:

Once you select the desired options, click OK. You are prompted to re-enter your password to authorize the changes.

The protection features you configured for the worksheet are now in effect. If you followed the example outlined above, you’ll see that you can now only click on and type in the authorized cells.

If several people will access this workbook at the same time, you should enable worksheet sharing. Do to this, return to the Review Ribbon and click on Share Workbook.

This open another dialog box with sharing options. The checkbox on top permits multiple users to make entries simultaneously. This is useful when a coordinated team enters data into different fields. Once each team member individually saves his changes, the worksheets are merged into a master copy. Note that if there are multiple changes to a single cell, the last save takes precedence.

The field below show how many users are currently accessing this worksheet. This is useful for determining compliance and usage.

If you decide to enable simultaneous usage, click OK to confirm the changes.

Congratulations, you can now rest easy while other users are in your worksheet, knowing that your critical data and hard work is secure!

GSD: Tips and Tricks is brought to you by Xero, the cloud accounting software solution for your small business. With Xero, you can log in anytime, anywhere to get a real-time view of your cash flow and manage your books. Start your free 30-day trial today.