Do you want to hide data in excel to prevent unwanted eyes from catching it?
In this blog, you will learn how to easily hide data in excel with simple methods. Lets dive right in.
Hide data in Excel. Learn how to do it.
Before that, You can Download the Exercise File from the link below
The most valuable thing in your Excel Files is the data you create and store.
sometimes, if it is viewed without authorization, the outcome can be devastating.
You might be storing personal information about employees or clients, or financial records. Or usernames and Passwords for different sites. May be credit cards, and bank account information. In all case, identity theft is a real possibility.
In this article I show you one of the simple techniques you can put in place to keep your data from being breached.
Protection is like insurance: when everything goes according to plan, you feel justified for not having bothered with it. However, when disaster strikes, you wish you’d have been more prepared.
So, let’s see how we can hide valuable information from being viewed in Excel.
Because our intention is to hide some of the worksheets having sensitive data, from prying eyes, then will do that using 3 techniques: Basic, Intermediate and Powerful.
In our exercise file, we have a worksheet storing Employee information, a second one storing Clients information, then we have 3 other worksheets with sensitive data: Bank accounts, online login information then email accounts with Username and password.
How can I prevent anyone from viewing the contents of these worksheets whether intentionally or unintentionally?
Basic Technique
Hide the sheets only.
You can select any sheet then right-click on the sheet tab and select Hide.
However, anyone with basic knowledge of Excel, can Right-Click on any other sheet and select Unhide ►Then select the sheet to unhide ► OK
And that will bring back the sheet. So this technique is a weak technique since it can be reversed.
Intermediate Technique
Hide Sheets and Protect Workbook.
This method complements the previous one to prevent reversing the visibility of any sheet.
After Hiding one or multiple sheets ► Click on the Review Tab ► Click on Protect workbook
Type a password then confirm the password ►OK
Advantage of this technique:
No one without the password can unhide the worksheet. The Unhide command is not even active.
Disadvantage of this techniques:
- We cannot access the regular functionality for the other worksheets, like Inserting, deleting, Renaming, Copying, Change Tab Color…
- By switching to the visual Basic Editor (ALT + F11) anyone can know that there are some sheets hidden.
Let’s provide the correct password and unhide the worksheets.
Therefore, I have a preference to the third technique, which will be done in the visual basic editor without any coding.
Powerful technique:
In this technique, we’ll be hiding 3 sheets in the visual basic editor.
To switch to the visual basic editor, we use the shortcut ALT + F11
To the left side we see: The Project Explorer window and the Properties window. If they are not displayed, we can select them from the View menu. Alternatively, you can use the shortcuts CTRL + R (for the Project Explorer) and F4 (for the Properties window).
In the Project Explorer Select the name of the sheet you want to hide.
Then go to the last option in the Properties window.
Click on the drop list for the visible property: You have three options (XLSheetVisible, XLSheetHidden, XLSheetVeryHidden) ► Select XLSheetVeryHidden.
This option can not be reversed from Excel. So if we hit ALT + F11 to go back to Excel, then right click on a sheet Tab ► as if the Very Hidden Worksheet, does not exist.
However, it can be changed back to XlSheetVisible from the properties window in the visual Basic editor.
To avoid that, we need to password protect the VBA project. But because protection is intended to protect codes► we need to insert a blank module for the protection to stick. Otherwise it will disappear automatically.
So, to switch back to the VBE, we hit ALT + F11
Click on the “Insert” menu and select ► Module
We do not need to write anything in that Module but that’s the only way to guarantee that protection does not go away.
To prepare for applying protection, Close the Properties Window, then click on the Collapse symbol for the project.
Right Click on the Project name in the project Explorer ► Select VBAProject Properties… ► The VBA Properties window opens.
In the VBA Project Properties dialog box ► Click on the Protection Tab
Check the box: Lock project for viewing
Type a Password
Confirm the Password
Hit OK
The Project will be protected ONLY after closing the VBE, Saving the Excel File and closing the Excel File.
Reopen the Excel File
Switch to the VBE (ALT + F11)
Try to expand the VBA Project (by Clicking on the + sign) ► You need to provide the Password
If you want to simple show or hide a row or a columns you can read this article to learn more
Now your Very Hidden Sheets are protected from Viewing 😊
Watch the Tutorial Here
