How to Hide an Excel Sheet So That Others Cannot Unhide it Easily

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.


ADVERTISEMENT

 

24 Unhide Macro Min

 

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.

 

1 Example Scenario Min

 

Step 2: Now right click on the worksheet that you want to hide and click on the Hide option.

 

2 Hide Min

 

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.

 

3 Unhide Min

 

Step 4: Select the sheet that you want to unhide from the Unhide window and then press the OK button.

 

4 Unhide Sheet Min

 


ADVERTISEMENT


Step 5: That’s it. The hidden sheet should now be visible.

 

5 Sheet Unhidden Min

 

Also Read:  How To Hide Sheets, Gridlines And Cells In Excel.


ADVERTISEMENT

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.

 

6 View Code Min

 

Step 2: On the left pane of the window, under the section Microsoft Excel Objects, click on the sheet that you want to hide.

 

7 Select Sheet Min

 

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.

 

8 Properties Min

 

Step 4: On the Properties Window, choose the option xlSheetVeryHidden from the dropdown menu associated with the option Visible.

 

9 Very Hidden Min

 

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.

 

10 Unhide Greyed Min

 

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.

 

11 View Code Min

 

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.

 

12 Make Visible Min

 

Step 3: Well, that’s it. Your sheet must now be unhidden. Enjoy!

 

13 Sheet Unhidden Min

 

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.

 

14 View Code Min

 

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.

 

15 Insert Module Min

 

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.

 

 

16 Macro Min

 

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.

 

 

17 File Min

 

Step 5: Now, on the left pane of the window, click on Options.

 

18 Options Min

 

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.

 

19 Developer Enable Min

 

Step 7: Now that the DEVELOPER mode is enabled, under the DEVELOPER tab, click on the button named Macros.

 

20 Macros Min

 

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.

 

21 Run Macro Min

 

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.

 

22 Unhide Min


ADVERTISEMENT

 

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.

 

23 View Code Min

 

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.

 

24 Unhide Macro Min

 

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.

 

25 Macros Min

 

Step 4: Now simply click on the unhide macro, which is geekPageUnhideAllSheets, and hit the Run button to execute it.

 

26 Run Unhide Macro Min

 

Step 5: Viola! All your hidden sheets are now in their places, all smiling!

 

27 Sheet Unhidden Min

 

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.

ADVERTISEMENT