Very hidden sheets in Excel

This tutorial clarifies the difference between hidden and very hidden sheets, explains how to make a worksheet very hidden and how to view very hidden sheets in Excel.

Are you exasperated because you cannot find the spreadsheet one of your formulas refers to? The sheet does not appear among other tabs at the bottom of your workbook, nor does it show up in the Unhide dialog box. Where on earth could that sheet be? Simply, it is very hidden.

What is a very hidden worksheet in Excel?

As everyone knows, an Excel sheet can be visible or hidden. As a matter of fact, there are two levels of worksheet hiding: hidden and very hidden.

Unhiding a sheet that was hidden normally is very easy. All you have to do is right-click any visible worksheet, click Unhide, and select the sheet you want to view. Very hidden sheets are a different story. If the workbook contains only very hidden sheets, you won't even be able to open the Unhide dialog box because the Unhide command will be disabled. If the workbook contains both hidden and very hidden sheets, the Unhide dialog will be available, but very hidden sheets won't be listed there.

Technically, how does Excel distinguish between hidden and very hidden worksheets? By the Visible property of the sheet, which can have one of these values:

  • xlSheetVisible (or TRUE) - the sheet is visible
  • xlSheetHidden (or FALSE) - the sheet is hidden
  • xlSheetVeryHidden - the sheet is very hidden

While anyone can toggle between TRUE (visible) and FALSE (hidden) by using Excel's Unhide or Hide commands, the xlVeryHidden value can only be set from within the Visual Basic Editor.

From the user's perspective, what is the difference between hidden and very hidden sheets? It is simply this: a very hidden sheet cannot be made visible via the Excel user interface, the only way to unhide it is with VBA. So, if you want to make some of your worksheets much more difficult to unhide by others (e.g. those containing sensitive information or intermediate formulas), apply this higher level of sheet hiding and make them very hidden.

How to make Excel worksheets very hidden

As already mentioned, the only way to make a sheet very hidden is by using the Visual Basic Editor. Depending on how many sheets you want to hide, you can proceed with one of the following methods.

Make a worksheet very hidden by changing its Visible property

If you want to completely hide just one or two sheets, you can change the Visible property of each sheet manually. Here's how:

  1. Press Alt + F11 or click the Visual Basic button on the Developer tab. This will open the Visual Basic Editor with the Project Explorer window in the top-left panel displaying a tree of all open workbooks and their sheets.
  2. Press F4 or click View > Properties. This will force the Properties window to appear just below Project Explorer (please see the screenshot below). If the Properties window is already there, skip this step :)
  3. In the Project Explorer window, click on the worksheet you want to make very hidden to select it.
  4. In the Properties window, set the Visible property to 2 - xlSheetVeryHidden.

Make an Excel worksheet very hidden

That's it! As soon the Visible property is changed, the corresponding sheet tab will disappear from the bottom of your workbook. Repeat the above steps for other sheets if necessary and close the Visual Basic Editor window when finished.

Make active worksheet very hidden with VBA code

If you have to hide sheets on a regular basis and are annoyed about having to do it manually, you can automate the job with a single line of code. Here's the macro that makes an active worksheet very hidden:

Sub VeryHiddenActiveSheet() ActiveSheet.Visible = xlSheetVeryHidden End Sub

If you are writing a macro for other users, you may want to take care of situations when a workbook contains only one visible sheet. As you may remember, it's not possible to hide absolutely all worksheets in an Excel file (whether you are making them hidden or very hidden), at least one sheet should remain in view. So, to warn your users about this limitation, wrap the above macro in an On Error block like this:

Sub VeryHiddenActiveSheet() On Error GoTo ErrorHandler ActiveSheet.Visible = xlSheetVeryHidden Exit Sub ErrorHandler: MsgBox "A workbook must contain at least one visible worksheet.", vbOKOnly, "Unable to Hide Worksheet" End Sub

Make multiple worksheets very hidden with VBA code

In case you want to set all selected sheets to be very hidden, go through all of the selected sheets in an active workbook (ActiveWindow) one by one and change their Visible property to xlSheetVeryHidden.

Sub VeryHiddenSelectedSheets() Dim wks As Worksheet On Error GoTo ErrorHandler For Each wks In ActiveWindow.SelectedSheets wks.Visible = xlSheetVeryHidden Next Exit Sub ErrorHandler: MsgBox "A workbook must contain at least one visible worksheet.", vbOKOnly, "Unable to Hide Worksheets" End Sub

How to unhide very hidden sheets in Excel

Now that you know how to completely hide sheets in Excel, it's time to talk about how you can view very hidden sheets.

Unhide a very hidden worksheet by changing its Visible property

To be able to see a very hidden worksheet again, you just need to change its Visible property back to xlSheetVisible.

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. In the VBAProject window, select the worksheet you want to unhide.
  3. In the Properties window, set the Visible property to -1 - xlSheetVisible.

Done!
Unhiding a very hidden sheet in Excel

Unhide all very hidden sheets with VBA

If you have quite a lot of very hidden sheets and you want to make them all visible again, this macro will work a treat:

Sub UnhideVeryHiddenSheets() Dim wks As Worksheet For Each wks In Worksheets If wks.Visible = xlSheetVeryHidden Then wks.Visible = xlSheetVisible Next End Sub

Note. This macro only unhides very hidden sheets, not worksheets hidden normally. If you want to display absolutely all hidden sheets, then use the below one.

Unhide all hidden and very hidden sheets at a time

To show all hidden sheets in an active workbook in one go, you simply set the Visible property of each sheet to TRUE or xlSheetVisible.

Sub UnhideAllSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible Next wks End Sub

How to use Very Hidden Sheets macros

To insert any of the above macros in your Excel workbook, perform these usual steps:

  1. Open the workbook where you want to hide or unhide sheets.
  2. Press Alt + F11 to open the Visual Basic Editor.
  3. On the left pane, right-click ThisWorkbook and select Insert > Module from the context menu.
  4. Paste the code in the Code window.
  5. Press F5 to run the macro.

To keep the macro, be sure to save your file as an Excel macro-enabled workbook (.xlsm). For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.

Alternatively, you can download our sample workbook with macros and run the desired macro directly from that workbook.

The sample workbook contains the following macros:

  • VeryHiddenActiveSheet - makes an active sheet very hidden.
  • VeryHiddenSelectedSheets - makes all selected sheets very hidden.
  • UnhideVeryHiddenSheets - unhides all very hidden sheets in an active workbook.
  • UnhideAllSheets - shows all hidden sheets in an active workbook (hidden normally and very hidden).

To run the macros in your Excel, you do the following:

  1. Open the downloaded workbook and enable the macros if prompted.
  2. Open your own workbook.
  3. In your workbook, press Alt + F8, select the macro of interest, and click Run.

For example, here's how you can make all of the selected worksheets very hidden:
A macro to make all of the selected worksheets very hidden

I hope this short tutorial has shed some light on Excel's very hidden sheets. I thank you for reading and hope to see you on our blog next week!

Sample workbook for download

Very Hidden Sheets macros (.xlsm file)

Table of contents

52 comments

  1. Svetlana,

    You did help me today using this "In the Properties window, set the Visible property to -1 - xlSheetVisible."

    More blessings to you!!
    Noel

  2. Hello, I have come a cross excel macro with best features but hidden formular and even can't do anything to manipulate i.e like editing, paste formular or share(protected worksheets). Does workbook with license authority not able to be edited? ?

  3. Also . . . in the use of an Array, can a sequential group of worksheets be identified? So the Array might list specific worksheets like "Cash Flow", "Financial Position", etc., but also include all worksheets between "Sales of AAA" and "Sales of ZZZ". That way, when I add worksheets in the future between Sales of AAA and Sales of ZZZ, they will automatically be VeryHidden.

    • Steve,
      This sample macro makes all the sheets between "Jan" and "May" very hidden; feel free to change these as needed. For the macro to work, the startIndex sheet (Jan) should come before endIndex sheet (May) in your workbook.
      ---

      Public Sub VeryHideBetweenSheets()

      Dim startIndex As Integer
      Dim endIndex As Integer

      startIndex = ActiveWorkbook.Worksheets("Jan").Index
      endIndex = ActiveWorkbook.Worksheets("May").Index

      If startIndex < endIndex Then
      For i = 1 To ActiveWorkbook.Worksheets.Count
      If i >= startIndex And i <= endIndex Then
      ActiveWorkbook.Worksheets(i).Visible = xlSheetVeryHidden
      End If
      Next i
      End If
      End Sub

  4. Thank you so much for this helpful article!! One question: regarding the code to hide multiple worksheets that I have selected, how do I modify that code to use an Array of worksheets? In other words, when I run the macro, I want it to "VeryHide" the same worksheets every time: ("Cash Flow", "Financial Position", etc.)

    • Hi Steve,

      Here you go:

      Public Sub VeryHideSheets()
      ActiveWorkbook.Worksheets("Cash Flow").Visible = xlSheetVeryHidden
      ActiveWorkbook.Worksheets("Financial Position").Visible = xlSheetVeryHidden
      End Sub

  5. Hi i am hafiz from bangladesh, i want to hide a specific workbook and userform & other excel want to open, but i don't know how its possible, please someone help me.
    regards
    Hafiz

  6. Hello,
    Thanks , Very Useful Codes !

    Best Regards

  7. I ran both macros. i also tried to manually change the visibility in VBA Editor but it did not work. I can type and save the changes in the file but when I add a module in VBA editor, I am not able to save it.
    Thanks,
    Alireza

    • Hi Alireza,

      This may be a stupid question, but anyway - did you save the workbook as macro-enabled (.xlsm)? Though, the fact that your manual changes in the VBA editor are not saved shows that the problem in not in the macros. BTW, what error do you get on saving? Unfortunately we are unable to identify the cause remotely because we have never run into such issue.

  8. I appreciate your quick reply. Sorry that I fogort to mention that it was about the Excel file (2013 version) that I have. I can see the list of the worksheets under the VBAProjects in VBA Editor but they are hidden. Even your VBA code that i used to unhide them did not work.
    Thanks,
    Alireza

    • Thank you for the details, Alireza!

      For the sake of clarity, did you run the UnhideVeryHiddenSheets macro or UnhideAllSheets, or both?

      Also, there are two more things to check:
      - Is your workbook or worksheets(s) protected?
      - Try to unhide your sheet manually via the VBA Editor (please see "How to unhide a very hidden worksheet by changing its Visible property"). Did it work?

  9. Hi Svetlana,
    I tried to unhide a sheet by using your VBA codes but i got the following error:
    Run-time error '1004': Method 'visible' of object'_Worksheet' failed
    I would like to know if there is any way to make the worksheet visible, please.
    Thanks,
    Alireza

    • Hi Alireza,

      I have just downloaded a sample workbook from this post and all the macros worked fine on my machine. Please specify your Excel version and localization, which macro your where trying to run, and whether you tried to unhide a sheet in our sample workbook or in your own one?

  10. So here is the code from a macro i created to select a sheet and hide the other sheet (used for navigating from a dashboard to individual sheets, there is a corresponding macro to go back).

    Sub gotohoursturnover()
    ' gotohoursturnover Macro
    Sheets("Home").Select
    Sheets("Dashboard (Hours & Turnover)").Visible = True
    Sheets("Home").Select
    ActiveWindow.SelectedSheets.Visible = False

    My question is how do i make this statement "ActiveWindow.SelectedSheets.Visible = False" make the sheet very hidden?

    I have tried replacing the "false" with "xlSheetVeryHidden" but it just throws up an error.

    Yes it may be simple but i am just working out vba lol.

    Thanks

    Mark

  11. Hii there,
    I am not familiar with macros,i needed a code to hide all the sheets in my workbook except the first sheet. And at the same time i want the other sheets to be opened when the user selects the buttons provided in the first sheet.
    I used the following code & the sheets are getting hidden but the buttons wont take the user to next pages either.

    Sub HideWorksheet()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
    If ws.Name ThisWorkbook.ActiveSheet.Name Then
    ws.Visible = xlSheetHidden
    End If
    Next ws
    End Sub

    Thank you.

  12. Hi Svetlana,
    this post is very useful for my goal. However, if the other (visible) sheets refer to the part of the content of this very hidden sheet (e.g. =VeryhiddenSheet!A1), then the very hidden sheet becomes unprotected, does it? Everybody else can add a new formula (=VeryhiddenSheet!A2) to get its content.

    • Hi Daniel,
      You are absolutely right. If someone knows the name of a very hidden sheet, they can get its content with a formula. I don't know a 100% reliable way to prevent this.

      • Could you simply just set the worksheet as protected and have all cells that have a formula as locked and hidden in their properties. This will prevent others from seeing such formula referencing the veryhidden sheet.

        • The name of the very hidden sheet is visible in the VBA editor so, although locking the formula cells is a good idea, it is not 100% foolproof. If the very hidden sheet contains named ranges, the Name Manager also gives away the secret.
          The following is convoluted, but you could:
          - put your secret calculations in very hidden sheet #1
          - use a second very hidden sheet #2 to refer only to the cells that you want the user to see
          - refer to the sheet #2 cells in the visible sheet #3 that the user accesses
          - go back to sheet #1 and add columns and rows to push the formula cells into obscure positions
          - now add random numbers and text to the empty cells of sheet #1
          - set the very hidden parameters for sheets #1 & #2
          - lock the workbook structure
          Even if the user finds the name of sheet #1, it is not possible to read the formulae and virtually impossible to find the real data as it is buried amongst the random stuff. Using the intermediate sheet #2 stops the user finding out where the 'good stuff' in sheet #1 is hidden.

  13. Hi Svetlana,

    Really like the way you explain things.

    Is there a way to write vba code that protects the structure of a workbook and that protects workbooks and worksheets without having a user input a password, but by using vba code in a "password = $%^&assl" type assignment statement?

    Thanks for you help.

    Hope you're having a great holiday season.

    Sydney

  14. How do I address the hidden sheet in VBA?

    With ThisWorkbook.Worksheets("Plates")
    myString = "!" & ThisWorkbook.Worksheets("Plates").Range(.Cells(2, 1), .Cells(lstrow, 3)).Address
    lbxPlate.RowSource = ("Plates" & myString)
    End With
    lbxPlate.Selected(0) = True

    Thanks for any assistance,

    John G.

  15. Hi, when i run the macro ,i get run time error and shows "method visible of object _worksheet failed". Sheets still very hidden.Help...

  16. Hi, when I run the macro myself I get error 400 and when I download your sheet and ALT+F8 I get a Runtime error 1004. Sheets still very hidden. HELP!

    • Hi Lisa,

      The first thought that comes to mind is that you are trying to unhide sheets in a protected workbook. To check this, please go to the Review tab > Changes group, and have a look at the Protect Workbook button. If it is highlighted, the workbook is protected, and you need to remove the protection first. For more information, please see How to unlock a protected workbook in Excel.

  17. Hi Svetlana.

    This was one of the most helpful blog with every detail. Could you tell me what are the steps to save this file with hidden sheets and send it to others without the danger of being visible. Can this sheet be protected?

    • Hi Tasneem,

      Before saving the file, protect the Workbook structure with password: Review tab > Protect Workbook. This will prevent unhiding both hidden and very hidden sheets as well as hiding, renaming, moving, or deleting worksheets. For more information, please see How to protect a workbook in Excel.

      • Protecting a workbook will slow down the macros. Using interface only won't work to a protected workbook.. Thus calling a very hidden sheet without unhiding works?

  18. I can not work with my Excel Sheet because it's protected. And many of these sheets are hidden in it, I can not unhide them ... please help me.

    • Share the workbook

  19. Sir,
    Required a formula if a cell contains a word in that word 4th Letter P = individual and if it C = corporate

    • Hi Govind,

      If my understanding of the task is correct, the following formula should work a treat:

      =IF(MID(A1,4,1)="p", "individual", IF(MID(A1,4,1)="c", "corporate", ""))

  20. Thanks svetlana, for ur share. ur post have been provoked me to know something knew everyday about excel.

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)