When you’re creating a template in Excel for other users often times there is information in the backend that you’d prefer users not be able to access or modify. In some cases it might be preferable to just hide the data entirely, especially if it contains sensitive information.
The easiest way to hide a tab in Excel is simply to right click on a tab and click hide.
The problem with this approach is as easy as it is to hide you can unhide it as well, which many users know how to do, and just involves right-clicking on a tab and clicking Unhide
After that you can see all the sheets that are hidden and by select the sheet and clicking OK you can unhide it.
You could protect your spreadsheet and prevent users from changing the structure but that might not be preferable either as it involves password protection and will not allow users to insert, copy, or even rename worksheets. If they don’t need this functionality then the solution might work for you.
To protect your workbook simply select the Review tab and click on the Protect Workbook button and you’ll see a pop up where you can protect the structure. Make sure to just tick off Structure and enter a password and click OK.
Another way to hide tabs is through one line of VBA code. If I wanted to hide Sheet1 I could use the following code:
Worksheets(“Sheet1”).Visible = xlVeryHidden
If your sheet is named something else then you would just change Sheet1 to the name of the sheet you want to hide.
Now when I go back to my spreadsheet and right click unhide:
Unhide isn’t even an option because there are no tabs that can be unhidden. If I hid another tab without using VBA then only that one would be visible, but the one using the code I used above would not show.
If you want to unhide the tab, the code to unhide is as follows:
Worksheets(“Sheet1”).Visible = True
What you could do is have two different procedures, one to hide a tab and another to unhide it. Then you can assign a shortcut key to each procedure. This will easily allow you to hide and unhide any tabs that you want to be invisible.
If you are not familiar with VBA check out one of my first posts on how to insert code and assign shortcut keys to a macro.
[…] worksheets and don’t even see that they’re there, you may want to consider using VBA to hide them without having to lock your entire workbook down. In some cases, locking a workbook may […]