Learn step by step about how to protect a worksheet in Excel!

Do you know what the worksheet protection command in Excel is for? Do you know how it works and how to use it? If you want to discover the answer to all those questions, you are in the right place!

The worksheet protection is basically used to prevent users from accidentally changing or deleting data in a worksheet.

This protection tool can be quite useful, once, in some cases, it can take hours to create the right formula and this data can be accidentally removed at any time, especially if the worksheet is used by many users.

Did you see how important this command is? So, follow this article to learn more, here we will talk about:

• What is Microsoft Excel and what does it do?
• How does the worksheet protection command work?
• How to protect an Excel worksheet in practice?
• How to protect the whole worksheet?
• How can specific users be able to edit certain cells in Excel?
• Is it possible to protect a workbook?
• How to protect an Excel file with a password?

What is Excel and what does it do?

Excel is a mathematical software developed by Microsoft that allows its user total control over any kind of worksheet, besides being very popular in management routines, it also offers a big mathematical library, with several formulas, even allowing graphics plotting.

If you are confused about some functions or formulas in Excel and wish to learn more about how to use the advantages of this software, we have a gift for you!

How to start my Excel journey?

Today one of the most demanded skills in selection processes is the use of Excel. And to give you a good idea of the first steps and first functions, we created the Excel eBook for Beginners.

By downloading our eBook, you will have a step-by-step and practical guide to be able to create your first spreadsheets, and use your most basic and essential functions.

Download it now!

How does the worksheet protection command work?

The first step to protecting a worksheet is to decide if we want to protect a specific area or the whole worksheet.

The worksheet protection allows you to lock part of the worksheet and unlock others areas, such as where the user can input data and update it daily, making sure to protect the formulas, rows, columns, and formatting of this worksheet.

There are people who believe that worksheet protection with a password safeguards secret information present. So, let’s learn how to protect a worksheet in practice?

How to protect an Excel worksheet in practice?

The company “Sweets” produces twenty different cookie types every day and needs to record and track the production and its costs.

In a simple worksheet, the employees recorded the number of cookies produced per day so that the costs can be correctly calculated.

See the image that illustrates our example:

All cells in Excel are locked by default. So, we must select the cells we want to protect, which in this case is only the “quantity”, as the employees need to update this field frequently.

In this example, other fields remain unchangeable, as they contain calculated data or data from the company itself, information that should not be changed by these users.

The first step in this task is to select the range of values you will allow users to edit, in this case, the column “quantity”. Next, right-click and choose the option “format cells”.

The format cells dialog will open, then go to the “Protection” tab and uncheck the option “locked”, finally click “OK”, as you can see:

How to protect the whole worksheet?

To protect an entire worksheet first of all you must go to the review tab and chose the option “protect worksheet”, it will show three ways for locking the worksheet, as in the image:

Let’s go through each of them and to get started we will create an easy password to learn the concept. In the following window, type the password “1234”:

You can simply protect the worksheet without using a password. See how you can control what the user will be allowed to do with those locked cells. In our case, we will only allow them to select the cells.

So click “OK”, retype the same password, and then click “OK” one more time. Now your worksheet is protected, now try to edit the cells that were not unlocked and the cells that were unlocked.

How can specific users be able to edit certain cells in Excel?

Another very interesting feature is to lock the worksheet and allow just a few users to edit specific cells, in other words, it is like we create different users to a file, each one with its own password.

Nowadays, several companies have an intranet system, and because of that many users can access the same file.

In some cases, not even all departments should have access to all information or be able to edit this data. So, this kind of command can be used to create a different password for each department (financial, sales, human resources, etc).

In our example, we could leave a password with the sales team, allowing them to change the price, while other employees would just change the number of products.

Let’s consider that the “Selling price” column can only be edited by the sales office. For this case, we will create a password that will be available just for this department.

Still in the review tab, click in “allow edit ranges”, as in the image:

In the following window, click in New and fill in the fields as shown in the figure below, entering the password 1234:

Click OK, retype the password and click OK again. Check if the permission has been created:

Click on “Protect Sheet” and enter the 1234 password to finish the protection.

Now, try modifying any cell from the “Selling price” column. Did you notice the difference?

Excel also allows the user to edit certain cells only using the password created for that range. Very useful, right?

Is it possible to protect an Excel workbook?

Yes, it is possible to protect an Excel workbook with a password and that is exactly what we are going to learn right now.

By protecting a workbook, you will be locking the workbook structure, in a way that the sheets cannot be deleted, moved, hidden, renamed and new worksheets or charts cannot be added.

You can also prevent windows in a workbook from being moved, resized, unhidden, or closed.

To do this, go to the review tab > protect workbook, as shown below:

In the password box, insert the password 1234 and click OK:

Now, try changing the workbook by creating a new worksheet or column, for example.

How to protect an Excel file with a password?

We still have one last protection option, the password protection of the whole file. This is the most secure protection format, which limits file access.

Go to the menu File > Info > Protect Workbook > Encrypt with Password:

Choose encrypt with a password and add the password 1234.

Click OK to confirm the password and save the file over the existent file. Close the file and try opening it again to test. Now, Excel asks you to enter the password for opening this file.

To unprotect the sheet and remove this password, you can just go to the same place, delete the password and save it one more time.

Did you enjoy the content of this article? Stay with us and get access to our 100% free Excel course and learn all the basic functions!

Do you want to learn Excel with our experts 100% free?

Are you tired of missing out on incredible career opportunities because you don't know about the main Excel tools?

So you are in the right place! In the Excel for Beginners course, you will learn to work with data in an agile way.

In addition, the course is available for FREE, just click on the banner button below and embark on this journey of knowledge!