Excel - Password protect workbooks and worksheets in detail Tutorial

In this video, you will learn about password protecting workbooks and worksheets in detail.
The video covers how to change the default protection for cells, lock specific cells in a worksheet, and protect the worksheet using a password.
This will help you secure your workbook and prevent unauthorized changes.
Additionally, the video explains how to protect the structure of the workbook to prevent deletion, moving, hiding, or renaming of worksheets.
It also demonstrates how to protect the entire workbook with a password, requiring it for any changes.
Finally, the video shows how to remove the password and change it if needed.
By following these steps, you can effectively password protect your workbooks and worksheets in Microsoft 365.

6:00
4223 views

Available in Microsoft365

Objectifs :

This document aims to provide a comprehensive guide on how to password protect worksheets and workbooks in Excel, detailing the steps to lock and unlock specific cells, protect the structure of a workbook, and manage passwords effectively.

Chapitres :

Introduction to Worksheet Protection
In Excel, protecting a worksheet ensures that its cells are locked, preventing unauthorized changes. By default, all cells in a worksheet are protected when the worksheet is locked. This guide will walk you through the process of customizing cell protection and managing workbook security.
Locking and Unlocking Specific Cells
To customize which cells are locked or unlocked, follow these steps: 1. **Select the Entire Worksheet**: Right-click on the worksheet and choose 'Format Cells'. 2. **Modify Default Protection**: Go to the 'Protection' tab, uncheck 'Locked', and click 'OK'. 3. **Select Specific Cells**: - For contiguous cells, simply select them. - For non-contiguous cells, hold down the Ctrl key while selecting the desired cells. 4. **Lock Selected Cells**: Right-click on the selected cells, choose 'Format Cells', check 'Locked', and click 'OK'.
Protecting the Worksheet
To protect the worksheet after setting up cell protection: 1. Right-click the worksheet tab and select 'Protect Sheet'. 2. In the 'Protect Sheet' dialog, you can choose various options that allow users to perform specific actions while the sheet is password protected, such as inserting rows. 3. Enter your password, click 'OK', retype the password, and click 'OK' again. Once protected, attempting to edit a locked cell will result in an error, while unlocked cells remain editable.
Unprotecting the Worksheet
To unprotect a worksheet: 1. Right-click the worksheet tab and select 'Unprotect Sheet'. 2. Enter the password and click 'OK'. If you wish to revert the default protection settings, select the cells, right-click, choose 'Format Cells', go to the 'Protection' tab, check 'Locked', and click 'OK'.
Protecting Workbook Structure
Protecting the structure of a workbook prevents actions such as deleting, moving, or renaming worksheets. To protect the workbook structure: 1. Click 'File', then 'Protect Workbook', and select 'Protect Workbook Structure'. 2. Check 'Structure', enter your password, and click 'OK'. 3. After protecting, many options, including 'Delete', will be grayed out when right-clicking the worksheet tab.
Saving and Managing Passwords
To save a password-protected workbook: 1. Click 'File', then 'Save As', and replace the existing file with the password-protected version. 2. Click the tools dropdown, select 'General Options', enter your password in 'Password to modify', and click 'OK'. 3. Confirm your password and click 'Save'. When opening the workbook, you will be prompted for the password. You can choose 'Read Only' to view it without making changes or enter the password to edit.
Removing Password Protection
To remove password protection from a workbook: 1. Click 'File', then 'Protect Workbook', and select 'Encrypt with Password'. 2. Press 'Delete' to remove the password and click 'OK'. To change the password, unprotect the workbook and protect it again with a new password.
Conclusion
This guide has provided a detailed overview of how to password protect worksheets and workbooks in Excel. By following these steps, you can effectively manage access to your data and ensure that sensitive information remains secure. Remember, there is always more to learn about Excel's features and functionalities.

FAQ :

How do I protect a worksheet in Excel?

To protect a worksheet, right-click on the worksheet tab, select 'Protect Sheet', set your desired options, and enter a password.

What happens when I protect a worksheet?

When you protect a worksheet, all cells are locked by default, preventing users from changing their values unless specified otherwise.

Can I unlock specific cells in a protected worksheet?

Yes, you can unlock specific cells by selecting them, right-clicking, choosing 'Format Cells', unchecking 'Locked', and then protecting the worksheet.

How do I change the password for a protected worksheet?

To change the password, you must first unprotect the worksheet, then protect it again with a new password.

What is the difference between locking and unlocking cells?

Locked cells cannot be edited when the worksheet is protected, while unlocked cells can be edited regardless of the protection status.

How can I protect the structure of a workbook?

To protect the structure of a workbook, go to the 'File' menu, select 'Protect Workbook', and choose 'Protect Workbook Structure', then set a password.

What does 'Read-Only' mean when opening a password-protected workbook?

Read-Only means you can view the workbook but cannot make any changes unless you enter the correct password.

Quelques cas d'usages :

Restricting Data Entry in Financial Reports

In a financial report, you can protect specific cells that contain formulas or critical data to prevent accidental changes while allowing users to input data in designated areas.

Collaborative Projects with Limited Editing Rights

When working on a collaborative project, you can protect the worksheet to ensure that only certain team members can edit specific sections, maintaining data integrity.

Preventing Unauthorized Changes in Sensitive Data

In a database of sensitive information, protecting the worksheet ensures that only authorized personnel can modify critical data, enhancing security.

Managing Access in Educational Settings

Teachers can protect worksheets to allow students to fill in answers while preventing them from altering the questions or formulas.

Maintaining Consistency in Templates

When using templates for reports, protecting certain cells ensures that the format remains consistent while allowing users to input their data.

Glossaire :

Worksheet

A single page in a spreadsheet program where data is organized in rows and columns.

Protecting a Worksheet

The process of restricting user access to certain cells in a worksheet to prevent unauthorized changes.

Locked Cells

Cells that are protected from being edited when the worksheet is protected.

Unprotected Cells

Cells that can be edited even when the worksheet is protected.

Contiguous Cells

A range of cells that are adjacent to each other.

Non-Contiguous Cells

Cells that are not adjacent and can be selected by holding the Ctrl key while clicking on them.

Workbook Structure Protection

A feature that prevents changes to the structure of the workbook, such as deleting or renaming worksheets.

Password Protection

A security measure that requires a password to access or modify a workbook or worksheet.

Read-Only Mode

A mode that allows users to view a document without making any changes.

00:00:04

all of its cells are protected, which means they're locked.

00:00:09

So no one can change their cell values.

00:00:12

We password protected Sheet 1, and all of its cells,

00:00:16

in the Password protect workbooks and worksheets video.

00:00:22

You can change what cells are going to be locked

00:00:24

and unlocked before you protect the worksheet.

00:00:28

To lock specific cells in a worksheet, we first have to

00:00:31

change the default protection for cells:

00:00:35

select the entire worksheet, right click it, and click Format Cells.

00:00:42

On the Protection tab, uncheck Locked, and click OK.

00:00:48

To select a range of contiguous cells, select the cells.

00:00:53

To select a range of non-contiguous cells,

00:00:56

click the first cell, or range of cells, and press and hold Ctrl

00:01:01

while you click the other cells, or range of cells, you want to lock.

00:01:07

Right click them, and click Format Cells;

00:01:12

check Locked; and click OK.

00:01:16

Right click the Worksheets tab and click Protect sheet.

00:01:22

In the Protect sheet dialog, there are a lot of options

00:01:25

you can check that allow users to do things with

00:01:27

the worksheet while it's password protected, such as insert rows.

00:01:33

For more information, see Password protect the worksheet,

00:01:37

in the course summary.

00:01:39

I type the password, click OK;

00:01:43

retype the password, and click OK again.

00:01:49

If I try to type in a cell we locked, I get an error.

00:01:55

But I can type in an unlocked cell.

00:02:03

To unprotect a worksheet: right click the worksheets tab,

00:02:07

click Unprotect sheet, type the password, and click OK.

00:02:14

If you changed the default protection for cells in

00:02:17

the previous steps, you may want to select the cells,

00:02:21

right click them, click Format Cells,

00:02:26

click the protection tab, and check Locked

00:02:31

to set the cells back to their default protection; and click OK.

00:02:38

To change the password for a worksheet, you unprotect

00:02:41

the worksheet and protect it again.

00:02:47

As I mentioned in the previous video,

00:02:50

protecting a worksheet does not protect it from being deleted from the workbook.

00:02:55

But by protecting the structure of the workbook

00:02:57

you can prevent worksheets from being deleted, moved, hidden,

00:03:02

unhidden or renamed, and new worksheets can't be added.

00:03:08

Click File; click the Protect workbook button;

00:03:14

and click Protect workbook structure; check Structure,

00:03:20

type your password, click OK,

00:03:24

retype your password, and click OK again.

00:03:29

When I rightclick the worksheets tab, Delete, and many of

00:03:33

the other options are grayed out.

00:03:37

If you decide to start protecting a workbook's structure,

00:03:40

on the Review tab click Protect workbook;

00:03:45

type your password, and click OK.

00:03:52

You can protect the workbook so a password is required to

00:03:55

make changes to it, but not to view it;

00:03:59

people can open the workbook without the password,

00:04:02

but they'll need the password to make changes.

00:04:06

Click File, Save As,

00:04:10

I'm leaving it set to the same filename,

00:04:13

replacing the file that isn't password protected;

00:04:17

click the tools down arrow, click General options,

00:04:22

in Password to modify, type your password and click OK;

00:04:28

retype your password, click OK; and click Save.

00:04:35

I want to replace the unprotected file with the new one

00:04:38

that is password protected, so I click Yes.

00:04:46

When I open the workbook, I'm prompted for the password.

00:04:51

I can click Read-Only and view the workbook, but not make changes to it.

00:04:56

Or I can enter the password and make changes to it.

00:05:01

To remove the password, repeat the steps to create the password,

00:05:05

but delete the password from Password to modify,

00:05:09

and save the workbook using a different name.

00:05:13

In the Password protect workbooks and worksheets video,

00:05:17

we covered protecting a workbook, so a password is required to open it.

00:05:22

To unprotect this workbook: click File,

00:05:27

Protect workbook, click Encrypt with password,

00:05:32

select the password, press Delete, click OK;

00:05:38

and the workbook is no longer protected by a password.

00:05:43

To change the password for a workbook,

00:05:46

unprotect the workbook, and protect it again.

00:05:50

Now you've got a pretty good idea about how to password

00:05:53

protect workbooks and worksheets, of course there's always more to learn.

Mandarine AI: WHAT YOU SHOULD KNOW

RELATED COURSES This tutorial is part of the following courses

2495 Views

Video Training Microsoft Excel - Intermediate level

Increase your scope of action with our intermediate tutorials dedicated to formulas, advanced visual enhancement and data analysis with pivo...

ALL COURSES