10 Tips For Working With Excel Protection
Some online articles and video tutorials are littered with falsehoods that have suckered millions of Excel users over the years. Some whoppers persist, repeated in reasonable voices, until they are accepted as the truth. Except they're not! Learn how to protect your data and Office files. Here’s your guide to separate fact from fiction when it comes to protection features in Excel.
#1 Files Encrypted Using Office 2007 Or Later Are Extremely Secure
Security will be compromised, if files with a legacy format are used with MS-Office 2007 or later. The encyprtion used for Office documents saved in 97-2003 legacy file formats (e.g. xls, doc) is RC4, which is not as strong as the recommended AES encryption used in Open XML files: xlsx, xlsm, xlsb, docx, docm etc. For secure encryption, save files in the Open XML format only.
Read about how to password protect opening an Excel file here
Read about how to password protect opening an Excel file here
#2 OpenXML Files Encrypted With A Strong Password Can't Be Hacked
The DocRecrypt tool gives IT admins who have configured the escrow key feature options to get access to password protected OpenXML files. An IT admin can use the tool and the private key of the escrow certificate to decrypt your files! Microsoft has designed this backdoor, so corporate users cannot be locked out of their critical files.
Caution: The Office DocRecrypt tool cannot be used to recover files that were password-protected before escrow key deployement!
Caution: The Office DocRecrypt tool cannot be used to recover files that were password-protected before escrow key deployement!
#3 Excel 2013 Sheet Protection Can Be Cracked With Internet Code
Office 2013 has silently introduced security changes in order to strengthen Excel protection. Excel versions up-to 2010 used the outdated SHA1 algorithm. These 16-bit long plain hashes are very easy to hack, so working passwords can be recovered fast or removed with code readily on the internet.
However, the SHA512 hash algorithm used Excel 2013 and 2016 is quite secure and cannot be hacked with VBA brute force attack code published online.
Read more about sheet protection removal:
However, the SHA512 hash algorithm used Excel 2013 and 2016 is quite secure and cannot be hacked with VBA brute force attack code published online.
Read more about sheet protection removal:
#4 Excel-Mac 2016 Offers Strong Protection Identical To Excel's 2013
As of summer 2015, sheet passwords set with Excel for Mac 2016 can be easily removed with password breakers suitable for use with Excel 2010 and prior.
Read more about the strength of password protection across Excel versions:
Read more about the strength of password protection across Excel versions:
- Strong sheet protection in Excel 2013
- Weak sheet protection in Excel 2010 and prior
#5 Data Cannot Be Copied, If Protected Cells Cannot Be Selected
Worksheets can be protected in a way that users can select neither unlocked, nor locked cells. Simply uncheck the first two options (select locked / unlocked cells) before protecting a worksheet.
Let's assume that you have received such a worksheet and you need to copy a range of cells without unprotecting the sheet. Here is the workaround:
Let's assume that you have received such a worksheet and you need to copy a range of cells without unprotecting the sheet. Here is the workaround:
- Enter the address of the range you want to copy in the Name box
- Press the Copy shortcut, Ctrl-C (marching ants should appear around the range)
- Select a new worksheet and Paste data
|
|
#6 VBA Code Cannot Be Read Without Breaking Password
VBA code stored in worksheets (document modules) can be read from workbooks with unprotected structure (Review > Protect Workbook), even if the VBA project is password locked or even if it is unviewable!
Read the workaround here
Read the workaround here
#7 VBE Project Locking Password Can Be Removed In Seconds
An Unviewable+ VBA project cannot be defeated by numerous password hacking software or step-by-step instructions readily available on the internet.
Read how to protect your VBA projects against password hacking:
Read how to protect your VBA projects against password hacking:
#8 There Is No Visual Indication That Very Hidden Sheets Exist
You can hide any worksheet or chart sheet in a workbook to remove it from view. I bet you already know that Excel has two levels of hidden sheets, hidden and very hidden sheets. The difference between the two is simply that very hidden sheets do not appear in the Unhide dialog box.
If the workbook contains both hidden and very hidden sheets, when a user opens the Unhide dialog box, the very hidden sheets do not appear. However, there is another dialog where the user may be able see that very hidden sheets exist in the workbook, if the very hidden sheets have been password protected!
Why this matters? Well, a user who is visually alerted to the presense of very hidden sheets, may try to use an Excel add-in to make the veryHidden sheets visible or even remove sheet protection. Let's start with 3 visible protected sheets, then hide the second one and make the third veryHidden via VBE.
If the workbook contains both hidden and very hidden sheets, when a user opens the Unhide dialog box, the very hidden sheets do not appear. However, there is another dialog where the user may be able see that very hidden sheets exist in the workbook, if the very hidden sheets have been password protected!
Why this matters? Well, a user who is visually alerted to the presense of very hidden sheets, may try to use an Excel add-in to make the veryHidden sheets visible or even remove sheet protection. Let's start with 3 visible protected sheets, then hide the second one and make the third veryHidden via VBE.
Finally, click File tab > Info > Protect Workbook and notice that the veryHidden sheet does appear under the locked sheets list (called Permissions in Excel 2010).
I find it quite puzzling that Microsoft has opted to alert users through the standard user interface that very hidden sheets exist in workbooks. This tip has been tested in all Excel versions with a Backstage view such as 2010, 2013 and 2016. If you are a workbook developer and don't want users to view the sheets you have hidden via VBE, please do not password protect them!
Read how to unhide and protect sheets in closed workbooks in any Excel 2007 or later:
Read how to unhide and protect sheets in closed workbooks in any Excel 2007 or later:
#9 Entire Columns Can Always Be Deleted In Unprotected Worksheets
Nancy, one of our avid readers, was editing a financial model prepared by her staff and wanted to delete an entire column from a large worksheet. She right-clicked on a column header, but was quite perplexed when she saw that the Insert and Delete controls were disabled (greyed-out), so that the column could not be deleted.
At first, she thought that the worksheet was inadvertently password protected in Excel 2016 and tried to use our Office Password Eraser application to remove the password. However, the program reported that the worksheet was unprotected!
What has actually happened, is that Excel prevents the deletion of an entire column, if it overlaps with at least 2 Excel Tables in order to avoid data corruption in Tables.
At first, she thought that the worksheet was inadvertently password protected in Excel 2016 and tried to use our Office Password Eraser application to remove the password. However, the program reported that the worksheet was unprotected!
What has actually happened, is that Excel prevents the deletion of an entire column, if it overlaps with at least 2 Excel Tables in order to avoid data corruption in Tables.
An entire column cannot be deleted, if it overlaps with 2 or more stacked Excel Tables
Tip: To prevent entire column deletion in unprotected worksheets from accidental tinkering, create 2 empty Excel Tables under your data, spanning the entire length of the range used.
#10 Some Hidden Rows Cannot Be Unhidden In Unprotected Sheets
|
|
Hidden rows and rows with height set as low as 0.08 look identical. However, rows with height set to 0.08 or slightly higher cannot be unhidden by the Unhide command. You need to adjust row height to make them fully visible. Rows with height between 0 and 0.07 can be unhidden as usual using the Unhide command from the contextual menu.
