How to protect cells in your Excel report in 3 easy steps

A new version of this post has been published here: http://easy-excel.com/?p=561

Why is it important to protect cells?

If you create an Excel report for someone, it is important that you somehow visualise which cells they are allowed to change, and which cells they should not touch. The common way to do this is to use a blue font for the assumptions, i.e. the values that can be changed.

Unfortunately, this is not always enough. Inevitably, someone will try to enter values into the calculation cells as well, and when they do, the whole report might be useless. We need to protect the cells from being tampered with, and the good news is that it takes less than a minute!

Using formatting to highlight assumptions

First, here’s how I use formatting (blue font) to show which values you are allowed to change. But remember, as it is still possible to make changes in all the other cells, someone might change a formula, and the report will never show the right results again!

P24_1_Protect cells in Excel

How to protect cells in Excel in 3 easy steps

1. If you just click on Protect Sheet on the Review Ribbon, all the cells will be protected. So, first we have to select the cells that should not be protected (the ones with the blue font)!

2. Use the shortcut Ctrl+1 to open the Format Cells window. Select the Protection tab and uncheck Locked. Now, these cells will not be locked when we protect the sheet.

P24_2_Protect cells in Excel

3. Go to the Review Ribbon and choose Protect Sheet. If you want, you can protect it with a password, but that is usually not necessary. Just click OK, and everything but the assumption cells is locked!

P24_3_Protect cells in Excel

To open the locked cells for editing, just click Unprotect (and the password if needed).

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s