If you have any experience in Excel, then you have probably found a few reasons to want to secure your workbooks. Here’s an example of what happened to Mary. She spent weeks making the perfect worksheet for her sales team to collect data about their sales. Then, accidentally, one of the sales reps deleted five critical columns along with all the information the team had already captured. Yikes! Imagine how you would feel if that happened.
Security is certainly important for protecting high-dollar sales information from those who might unknowingly employ the delete key. Almost everyone who shares a workbook could benefit from some of Excel’s security options. Many of them are hidden gems that most people don’t know about.
Let’s look at a few of them and explore why you might use them.
If you have a team who needs to see a workbook, but you don’t want them to make any changes to it, a simple solution is in Windows File Explorer. You can right-click on the workbook file name to open the Properties window. You can set the attributes to either Read Only or Hidden.
READ ONLY – Read Only allows them to look but not touch! It’s like when you have the workbook open on a shared drive, and no one else can make changes.
HIDDEN – The other option, Hidden, is very tricky! It actually hides it in plain sight. While it still resides in the same location, no one – not even YOU – can see it. So how do you access it? Save a link to the workbook BEFORE you hide it! Be careful though, if someone pinned the workbook in Excel, they will still have access to it after it’s hidden.
Save As Password Options
This is so hidden, most people never find it on their own. Excel allows you to add a password to the workbook with multiple options: Password to Open, Password to Modify and Read-only Recommended.
Password to Open means no one – not even YOU – can open the workbook without the password! It’s good to keep track of it somewhere – especially if you have a lot of passwords.
Password to Modify is great for a template that you have created for others to use. The only way they can change it is to save it with a new name.
Read-only Recommended reminds you to make it read only when you save it. So where is this hidden gem? When you save a workbook, you will see a link called Options. Click it and the classic Save As opens with the word Tools next to the Save button. Click the Tools dropdown to see General Options and that’s where you can set the password.
BONUS: By the way, in Save As you can also save it as a PDF which means no one can modify the workbook in Excel, but then you probably already knew that one!
Excel provides a variety of options to hide or show specific information. For example, you can hide or show columns or rows by right-clicking on them. Unfortunately, it’s pretty obvious they are hidden when the header numbers or letters are out of sequence. But this is useful when you want information to just be out of the way while you work on the neighboring cells.
Here’s the gem: Did you know you can also hide a worksheet!? This is beneficial when you have data that will roll up into one worksheet that the boss needs, but you don’t want to confuse him with all the other worksheets that contain the raw data. Right-click on each of the tabs to hide them! To show them again, right click on the NON-HIDDEN tab, select Unhide, then choose which sheets to show from the list.
These are just a few of Excel’s hidden security gems. Start using the ones that make sense for you to help protect your data.