Excel: Worksheet protection vs security

Article
1/17/2024

Credits

Originally posted at: EXCEL SECURITY AND NOT EXCEL SECURITY, THAT’S THE QUESTION

Executive overview

Excel has various levels of protection.
Many people use worksheet protection. This feature is designed as a simple blocker to avoid unwanted edits to your sheet. Users can open and use the file without the protection password.

By design, Excel sheet protection NOT a security measure, to keep data secret or to hide IP or formulas from unauthorized parties. The worksheet protection password is fairly easy to remove as explained below.

If you want actual protection in your Excel sheet, you need to use the encryption feature, but then every user will be forced to enter a password to open the file.

Applies to

This discussion actually applies to the latest version of Excel in the Office 365 version. Some options or features might not be available or might not apply to previous/older versions of MS office.

Introduction

If you spend a lot of time to build a smart calculations or data management solutions in Excel, it's very likely that you want to protect your hard word, or the smart layout or the intelligence behind your calculations. Or simply avoid any accidents crippling the nice layout.

Most people will first think about worksheet protection to achieve this, but there are some more options.

Excel Security options

If you create or open an Excel sheet (in current version of Office 365), you can add security via the menu "FIle" then choose the "Info" option.

Then click the "Protect Workbook" option.

Worksheet protection

In short: Worksheet protection is not intended to be a security feature.

And that’s documented at: https://support.microsoft.com/office/protect-a-worksheet-3179efdb-1285-4d49-a9c3-f4ca36276de6

From the Microsoft support document, the security impact of the protection features is explained as (quote):

"*Important: *

Worksheet level protection is not intended as a security feature. It simply prevents users from modifying locked cells within the worksheet.
Protecting a worksheet is not the same as protecting an Excel file or a workbook with a password. See below for more information:

To lock your file so that other users can’t open it, see Protect an Excel file.
To prevent users from adding, modifying, moving, copying, or hiding/unhiding sheets within a workbook, see Protect a workbook.
To know the difference between protecting your Excel file, workbook, or a worksheet see Protection and security in Excel."

Even with worksheet protection, the formulas are stored in the file as this is what allows you to later modify the formulas and for the cells to update their values. Because the file is not encrypted, a user could inspect the file contents to determine what the formulas are.

For advanced Excel users:

There are mechanisms that allow you to remove formulas from the workbook while keeping the cell values the same. When this is done, these cell values no longer update as they no longer have a formula.
If you want to strip out a formula that refers to another workbook, you can use the break link feature for external links (Data tab > Queries and Connections section > Edit Links > Break Link) – this keeps the current value of the cell and removes the formula referencing the external workbook.
If you want to strip out a formula regardless of where it refers to, the easiest way to do this is to copy the cell and paste as value to the same cell.

Implementing worksheet protection

Implementing worksheet security using encryption

Managing cell security

Before you activate worksheet protection, you need to consider unblocking cells to allow edit when protection is activated. Right click the target cells, you want to leave unprotected.

In the cell format options, "Locked" is enabled by default. Uncheck if you want to edit after password lock.

Next, to activate worksheet protection, right click the worksheet tab (below) and click the Protect sheet option.

You can select which kind of protection you need on the level of the sheet and cells.

You need to enter the password and then reconfirm, of course.

Now, when you try to edit the cells that are blocked, you'll get an error.

The actual Excel security: sheet encryption

To implement actual security, you need to encrypt the file.
When you want to use encryption, go back to the file menu and workbook protection, as explained earlier.

Choose the encryption option

When you save the file and try to reopen it, you'll get a password prompt.

In one of the next chapters below, I'll show what happens with the file security, and if you can hack it... or not.

Hacking Excel worksheet protection

The actual reason for this article, is that the worksheet protection is NOT a security feature and more important, the worksheet protection can be broken in a matter of seconds.

You'll find a lot of password cracking tools, brute force password guessing or macro scripts to crack the passwords. Don't bother if you simply want to remove the password protection.

The worksheet protection is embedded in the XLSX file, as XML. And you should consider the XLS sheet as a compressed/zipped dossier/file collection of config files containing the hashed password.

And that's exactly the easy shortcut to remove the password, remove the password hash.
I won't go in detail on the steps, but it's about renaming the XLS file to zip, opening the zip, removing the pasword hash, saving the file, rename to XLS and open your sheets without password protection.

The method is explained over here: http://www.excelsupersite.com/how-to-remove-an-excel-spreadsheet-password-in-6-easy-steps/

Hacking encryption? (Nope!)

Using the XLS to Zip rename, you can inspect the file content of the encrypted file.

When you try the same technique, removing the encryption info...you'll notice that the "EncryptionInfo" is not allowing to save. And you can't remove the encryption (at least not this way, here we stop... )

When you try to remove the encryption tag, including the cipher and password hash, you'll notice it won't work.

Reporting security issues to Microsoft

If you think or suspect to have found a security issue in a Microsoft product, don't hesitate to report it.

To report a vulnerability in a Microsoft product or service, got to the Microsoft Security Response Center (MSRC) website at : https://www.microsoft.com/msrc.

You can track the status of your report as the MSRC team will work with you to investigate and resolve the issue. Or confirm that a suspected behaviour is not a security issue, but a light-weight protection to avoid layout incidents.

Share via