Sometimes you could have more than one worksheet in your Excel workbook and some of them might be confidential. If you don’t make sure that the confidential sheets are hidden before you share your workbook with others, your data might be compromised. If a sheet is not hidden, others can view the data in it and easily overwrite it. There definitely is the option to hide/unhide a sheet in the regular way, where you right-click on the sheet to hide/unhide and choose the respective option. But this option is not secure and it allows other users to easily override your security settings. So what is the solution if others can easily override your settings even if you hide your sheet? Well, we are here today to talk about that solution.
In this article, we explain in detail how you can deeply hide or unhide Excel worksheets in your workbook. Even if you use this method to secure your sheets, it can be overridden. But it definitely is not going to be straightforward for a naive user who doesn’t have any idea that you have hidden some sheets in your workbook. So, what are you waiting for? Let’s jump in!
Section 1: Normal Way of Hiding/Unhiding Sheets in Excel
If you are just looking for a way to hide/unhide your worksheet and if you are not bothered about increasing the security of your settings, then you can simply go ahead with the normal hide/unhide option.
Step 1: Let’s say you have an Excel workbook with many sheets as shown in the screenshot below.
Step 2: Now right click on the worksheet that you want to hide and click on the Hide option.
Step 3: If you look at the sheets tab now, you can see that the sheet you selected is hidden.
To unhide the sheet, simply right click anywhere on the sheets tab and then click on the Unhide option.
Step 4: Select the sheet that you want to unhide from the Unhide window and then press the OK button.
Step 5: That’s it. The hidden sheet should now be visible.
Also Read: How To Hide Sheets, Gridlines And Cells In Excel.
As obvious, anyone can right click on the sheets tab and unhide any sheet as they please. If you are bothered about this flaw in security, go ahead and read the rest of the sections in this article.
Section 2: How to Deeply Hide a Single Sheet
The main issue with the above method is that other users can simply right click and unhide your hidden sheet. So, let’s see whether we can somehow disable this unhide option.
Step 1: Right click anywhere on the sheets tab and then click on the View Code option from the right click context menu.
Step 2: On the left pane of the window, under the section Microsoft Excel Objects, click on the sheet that you want to hide.
Step 3: Now click on the View tab at the top and then click on the Properties Window option.
Alternatively, you can click the F4 shortcut key to open the properties window of the selected sheet.
Step 4: On the Properties Window, choose the option xlSheetVeryHidden from the dropdown menu associated with the option Visible.
Step 5: Now you can see that the selected sheet is hidden.
Even if you right click on the sheets tab and try to hit the Unhide option, you will find it greyed, fulfilling the purpose of others not being able to unhide your hidden sheet easily.
Section 3: How to Unhide a Deeply Hidden Single Sheet
To unhide a sheet whose visibility property is set to xlSheetVeryHidden, you can follow the steps below.
Step 1: Right click on the sheets tab anywhere and then click on the View Code option from the right click context menu.
Step 2: Now on the left window pane, under Microsoft Excel Objects, click on the sheet that you want to unhide. Press the key F4 to bring up its properties window.
This time, choose the option xlSheetVisible from the dropdown menu associated with the option Visible.
Step 3: Well, that’s it. Your sheet must now be unhidden. Enjoy!
Section 4: How to Deeply Hide All the Sheets, Except the Active Sheet, at Once
The method described in Section 2 works perfect if you have to hide only one or two sheets. But what if you have more than three sheets and you want to hide all the sheets except the one that is active? Well, going to the properties window of each one and setting the visibility property to xlSheetVeryHidden is definitely not practical. Let’s now discover a more feasible and practical approach to tackle this scenario.
Step 1: Right click anywhere on the sheets tab once again and hit the View Code option.
Step 2: When the Visual Basic editor is open before you, click on the Insert tab at the top and then hit the Module option.
Step 3: When the Module editor opens up, copy and paste the following script onto it.
'This macro from The Geek Page will hide all the worksheets except the active worksheet Sub geekPageHideAllExceptActiveSheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetVeryHidden Next ws End Sub
Once you have copied the script, you can close the Module Editor by clicking on the Close button at the very top, as saving the file is not mandatory.
Script Explanation
The script takes each worksheet in the workbook one by one. It checks whether the sheet currently being checked is the active sheet or not. If not, then its visibility property is set to xlSheetVeryHidden.
Step 4: Now, we need to run the macro we just created to hide all the sheets except the active one.
For that, let’s enable the Developer mode first. If you already have it enabled, you can straight away jump to Step 7, skipping the steps in between. Else, follow the steps below.
Click on the FILE tab at the top ribbon.
Step 5: Now, on the left pane of the window, click on Options.
Step 6: On the left pane of the Word Options window, click on the Customize Ribbon tab first.
Now on the right-most end, check the checkbox corresponding to the Developer option as shown in the screenshot below.
Hit the OK button to proceed.
Step 7: Now that the DEVELOPER mode is enabled, under the DEVELOPER tab, click on the button named Macros.
Step 8: From the list of macros available, click on the one that we created, which is geekPageHideAllExceptActiveSheet.
Once you have clicked on geekPageHideAllExceptActiveSheet macro, click on the Run button.
Step 9: That’s it. All the sheets in your workbook, except the active one, are now hidden.
Even if you try to unhide them by right clicking on the Sheets tab, you will find that the Unhide button is disabled.
Section 5: How to Unhide Deeply Hidden Multiple Sheets at Once
Since there is a way to hide multiple sheets at once, there should be a way to unhide them at once too. Well, let’s walk through that way in the following steps.
Step 1: Right click on the sheets tab and click on the View Code option.
Step 2: On the left window pane, under the Modules section, double click on the module that you created in Section 4, in this case, Module 1.
Once the module is open, hit the Enter key once and copy and paste the following script onto it.
'This Macro from Geek Page will unhide all the worksheets Sub geekPageUnhideAllSheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub
Once the script is copied, just like before, simply hit the Close button at the top of the Visual Basic editor.
Script Explanation
The script takes in all the worksheets in the workbook onto a variable. The visibility property of each worksheet in the workbook is then set to xlSheetVisible.
Step 3: To run the macro that we just created, click on the DEVELOPER tab at the top and hit the Macros button under it.
Step 4: Now simply click on the unhide macro, which is geekPageUnhideAllSheets, and hit the Run button to execute it.
Step 5: Viola! All your hidden sheets are now in their places, all smiling!
Please do tell us in the comments section which method is your favorite.
Stay tuned for more super cool tricks, tips, and how-to articles.