6 ways to make Excel sheets very hidden (invisible)

This week, while working on a project, I wanted to hide a worksheet in Excel. However, I didn’t want users to unhide it or even know it was there. I wanted to make the Excel sheets very hidden (i.e. invisible to other users).

I’m sure many users know how to hide a sheet. However, most are unaware of the ability to make a sheet very hidden, which makes it invisible.

This post looks at the differences between hidden and very hidden and 6 ways to make sheets very hidden.

Table of Contents

• What is a very hidden worksheet?
• Difference between hidden and very hidden sheets
• How to make Excel sheets very hidden
• Do you have the Developer tab enabled?
• Worksheet Properties
• VBA Properties
• Run a VBA Macro
• Office Scripts
• Hide all sheet tabs
• Changing file structure
• Are hidden sheets completely invisible?
• Conclusion

What is a very hidden worksheet?

Have you ever used a workbook where a formula refers to a sheet you just can’t find? The sheet is not a tab at the bottom, and is not in the list of hidden sheets. Where could it be?

Look at the screenshot below. You can’t see Sheet2 because it is sheet is very hidden.

Difference between hidden and very hidden sheets

Rightly or wrongly, hidden worksheets are commonly used to:

• Remove workbook clutter – hides the previous versions and workings which are no longer relevant. (In my opinion, it’s better to keep these visible, so you remember to delete them later. Otherwise, the clutter builds up in the background).
• Hide workings from users – users don’t need to see workings to be able to use a spreadsheet. Hiding is an excellent way to focus attention on what is required.
• Provide basic protection – prevents users from accidentally changing things that might break the spreadsheet.

Hiding a sheet is available in the standard user interface. There are lots of options to hide a sheet:

• From the ribbon:
  • Click View > Hide
  • Click Home > Format > Hide & Unhide > Hide Sheet
• Right-click the sheet tab and select Hide from the menu

Since many users know about hidden sheets, they can just as easily unhide them.

• From the ribbon, Click Home > Format > Hide & Unhide > Unhide sheets…
• Right-click a visible sheet and select Unhide from the menu

In the Unhide dialog box, select the sheet and click OK.

Note: In Excel 365 and Excel 2021 and later, we can unhide multiple sheets; prior to that, it had to be performed for each sheet individually.

Very hidden sheets can be used for the same purpose as hidden. But, the special thing about very hidden sheets is they do not appear in the Unhide list. So, for example, in the screenshot at the start, we could see a formula using Sheet2, but that sheet was nowhere to be found; it was very hidden.

OK, so let’s take a look at 6 ways to make Excel sheets very hidden.

How to make Excel sheets very hidden

In this section, we provide 6 ways to make worksheets very hidden. We’re looking at options for Excel Desktop and Excel Online,

Do you have the Developer tab enabled?

The first three methods require the Developer tab to be enabled. To do this:

1. Right-click on an empty ribbon section and select Customize the Ribbon… from the menu.
2. The Excel Options dialog box opens.
3. Ensure the Developer option is checked, then click OK.

You should now have the Developer tab visible in the ribbon.

Worksheet Properties

The first method for making worksheets very hidden uses the Control Properties dialog box.

1. From the ribbon, click Developer > Properties
2. The Properties dialog box opens. Change the Visible setting from -1 – xlSheetVisible to 2 – xlSheetVeryHidden
3. The change is applied instantly, and the workbook disappears

Test it out. You will notice the worksheet does not appear in the unhide list.

This method can only be applied to the active sheet, and must be used on a sheet-by-sheet basis.

Now the worksheet is hiddenm we cannot activate it. So we cannot use this method to make the worksheet visible again. For that, we can use another method.

VBA Properties

The second method is similar to the first, but uses an alternative interface. The good news is we can use this method to make any very hidden worksheets visible again.

For this, we need to open the Visual Basic Editor.

1. Click Developer > Visual Basic (or press Alt + F11)
2. The Visual Basic Editor opens
3. If the Properties window in the bottom-left is not open, click View > Properties from the VBA menu
4. In the Project window, select the sheet to be hidden
5. Then, in the Properties window, change the visible property from -1 – xlSheetVisible to 2 – xlSheetVeryHidden

To make a worksheet visible again, change the setting to -1 – xlSheetVisible.

Run a VBA Macro

The previous methods were all manual. So, let’s move on now to look at more automated methods.

The following are example VBA codes for making sheets very hidden (and making them visible again 😁). We won’t go into how to use the VBA code in detail. If you are using these methods, I assume you already know how to run macros.

Very hide the active worksheet

The code below hides the active worksheet of the active workbook.

Sub activeWorksheetVeryHide()

'Very hide the active worksheet
ActiveSheet.Visible = xlSheetVeryHidden

End Sub

Very hidden worksheets cannot be active; check out other methods below to make the worksheet visible again.

Very hide a named worksheet

The following code makes Sheet1 of the workbook containing the VBA code visible.

Sub namedWorksheetVeryHide()

'Very hide a named worksheet in the workbook
ThisWorkbook.Sheets("Sheet1").Visible = xlSheetVeryHidden

End Sub

Having made the worksheet very hidden, to make it visible again, use the following code.

Sub namedWorksheetVisible()

'Make the named worksheet in the workbook visible
ThisWorkbook.Sheets("Sheet1").Visible = xlSheetVisible

End Sub

Very hide all selected worksheets

The following code makes the selected worksheets in the active workbook very hidden.

Sub allSelectedWorksheetsVeryHide()

'Create variable to hold worksheets
Dim ws As Worksheet

'Loop through each selected worksheet
For Each ws In ActiveWindow.SelectedSheets

    'Very hide the worksheets
    ws.Visible = xlSheetVeryHidden

Next ws

End Sub

Once the worksheets are hidden, they are no longer selected. Therefore, look at the methods below to make multiple worksheets visible again.

Very hide a list of worksheets

To make multiple named worksheets very hidden, use the code below. This code will very hide Sheet1 and Sheet2 in the workbook which contains the VBA code.

Sub namedWorksheetsVeryHide()

Dim sheetList As Variant
Dim i As Integer

'Get the list of sheet names to very hide
sheetList = "Sheet1|Sheet2"

'Split the list into an array
sheetList = Split(sheetList, "|")

'Loop through each item in the array
For i = LBound(sheetList) To UBound(sheetList)

    'Very hide a named worksheet in the workbook
    ThisWorkbook.Sheets(sheetList(i)).Visible = xlSheetVeryHidden

Next i

End Sub

Very hide all worksheets except the active sheet

It is not possible to hide all worksheets, as Excel requires at least one visible sheet. The following code hides all except the active sheet.

Sub allExceptActiveWorksheetVeryHide()

Dim activeWs As Worksheet
Dim ws As Worksheet

'Declare the active worksheet
Set activeWs = ActiveSheet

'Loop through each worksheet in the active workbook
For Each ws In ActiveWorkbook.Worksheets

    'Check if the worksheet is the active worksheet
    If ws.Name <> activeWs.Name Then
        
        'Hide the worksheet
        ws.Visible = xlSheetVeryHidden
    
    End If

Next ws

End Sub

Make all very hidden worksheets visible

This final code will make all very hidden worksheets visible again.

Sub allVeryHiddenWorksheetsVisible()

'Create variable to hold worksheets
Dim ws As Worksheet

'Loop through each worksheet
For Each ws In ActiveWorkbook.Worksheets

    'Check if the worksheet is alrady very hidden
    If ws.Visible = xlSheetVeryHidden Then
    
        'Very hide the worksheets
        ws.Visible = xlSheetVisible
    
    End If

Next ws

End Sub

For more VBA code examples for working with worksheets, check out this post: https://exceloffthegrid.com/worksheet-properties-actions/

Office Scripts

Excel Online does not support VBA Macros; instead, it has its own automation language called Office Scripts. If you are using this method, I am assuming you already know how to run Office Scripts.

Very hide the active worksheet

The following script very hides the active worksheet of the active workbook.

function main(workbook: ExcelScript.Workbook) {

//Very hide the active worksheet
workbook.getActiveWorksheet().
  setVisibility(ExcelScript.SheetVisibility.veryHidden)

}

Since very hidden sheets cannot be active, look at the examples below for making a worksheet visible again.

Very hide a named worksheet

The following code makes Sheet1 of the active workbook very hidden.

function main(workbook: ExcelScript.Workbook) {

//Very hide the named worksheet
workbook.getWorksheet("Sheet1").
  setVisibility(ExcelScript.SheetVisibility.veryHidden)

}

Use the code below to make Sheet1 visible again.

function main(workbook: ExcelScript.Workbook) {

workbook.getWorksheet("Sheet1").
  setVisibility(ExcelScript.SheetVisibility.visible)

}

Very hide a list of worksheets

The example below makes Sheet1 and Sheet2 of the active workbook very hidden.

function main(workbook: ExcelScript.Workbook) {

//List of sheets to very hide
let sheetList = "Sheet1|Sheet2"

//Convert sheetList to an Array
let sheetArray = sheetList.split("|");

//Loop through all items in the sheetList
for (let i = 0; i < sheetArray.length; i++) {

  //Very hide the worksheet
  workbook.getWorksheet(sheetArray[i]).
    setVisibility(ExcelScript.SheetVisibility.veryHidden)

}

}

Very hide all worksheets except the active sheet

Excel must have at least one sheet visible. Therefore, the code below hides all except the active sheet.

function main(workbook: ExcelScript.Workbook) {

//Get the active worksheet
let activeWs = workbook.getActiveWorksheet();

//Loop through all worksheets
for (let i = 0; i < workbook.getWorksheets().length; i++) {

  //Check if worksheets is the activeworksheet
  if (workbook.getWorksheets()[i].getName() != activeWs.getName() ) {

    //Very hide the worksheet
    workbook.getWorksheets()[i].
      setVisibility(ExcelScript.SheetVisibility.veryHidden)

  }

}

}

Make all very hidden worksheets visible

The code below snippet makes all very hidden worksheets visible. It does not change the status of any hidden worksheets.

function main(workbook: ExcelScript.Workbook) {

//Get an array of all the worksheets
let wsArray = workbook.getWorksheets();

  //Loop through the array of worksheets
  for (let i = 0; i < wsArray.length; i++) {

    //Check if worksheets are very hidden
    if (wsArray[i].getVisibility() == "VeryHidden") {

      //Make worksheet visible
      wsArray[i].setVisibility(ExcelScript.SheetVisibility.visible)

    }

}

}

For more Office Script code examples for worksheets, check out this post: https://exceloffthegrid.com/office-scripts-working-with-worksheets/

Hide all sheet tabs

There is an option in Excel desktop to hide worksheet tabs. This option does not change the visibility of the worksheets; they are still visible. However, without the ability to use the tabs, this may prevent users from knowing a sheet exists (effectively the same result as making them very hidden).

1. Click File > Options from the ribbon to open the Excel Options dialog box
2. In the Advanced section, uncheck the Show sheet tabs option.
3. Click OK to close the dialog box.

The sheet tabs are no longer visible in Excel desktop.

The VBA code to toggle this option is:

Sub toggleSheetTabs()

'Toggle worksheet tabs on/off
ActiveWindow.DisplayWorkbookTabs = Not ActiveWindow.DisplayWorkbookTabs

End Sub

Note: Excel Online does not support this feature. The tabs will be visible if the workbook is opened in Excel Online.

Changing file structure

This final method is quite a niche option. But you never know when it might come in useful. We are going to unpack the Excel workbook and change the source code.

Before attempting this, please back up the Excel file, as any mistakes may corrupt the file.

1. Rename the Excel workbook so the file extension is .zip, instead of .xlsx
2. Navigate into the zip file and find the file called \xl\workbook.xml
3. Copy and paste the file outside of the zip folder
4. Open the file using a text editor like Notepad
5. Within the code, you will find code similar to the following
   <sheet name=”Sheet2″ sheetId=”2″ r:id=”rId2″/>
   In that code, we can add the statement to make a sheet very hidden (see the bold section below)
   <sheet name=”Sheet2″ sheetId=”2″ state=”veryHidden” r:id=”rId2″/>
6. Save the file with the additional code. Then, copy and paste the file back into the \xl\ zip folder.
7. Accept the option to replace the file.
8. Change the file extension of the zip file back to .xlsx.

On opening the workbook, you will find the sheet is now very hidden.

Are hidden sheets completely invisible?

A common question about this is: Are hidden sheets completely invisible?

Hopefully, having read this far, you will realize the answer is: No. The methods shown above to make sheets visible are available to other users too. However, most users don’t know about these techniques. Even if they do, they still need to care enough to look for the very hidden sheets.

Conclusion

As we have seen, there are many ways to make Excel sheets invisible (very hidden). With a bit of knowledge, it is not a difficult task.

We can to use the Developer tab, and the Visual Basic Editor to apply the setting manually. But if this is an action you perform regularly, you may prefer the automated options using VBA and Office Scripts.

Have we missed any methods? Please let us know in the comments.

---

What next?

Discover how you can automate your work with our Excel courses and tools.