Hide Excel Worksheets and Change Visibility

Hiding worksheets in Excel and changing their visibility state is a useful tool with many practical applications.  There are a few ways you can go about hiding Excel worksheets and there are also a couple of variants that every Excel analyst would be wise to know, read on to learn more…

Hiding Worksheets in Excel

Working with Excel files, especially Excel files that will be eventually be used by others or sent to clients or managers, there will come a time where hiding a worksheet or multiple worksheets is a useful practice, equally you may also come across an Excel file created by someone else and you might need to Unhide worksheets to view the file in more detail.



There are many reasons why you could consider hiding a worksheet in one of your Excel files, for example:

  • Hiding a worksheet, or multiple worksheets, can make your Excel file easier for the client, manager or other user(s) to understand. It can help focus their attention on the key worksheets in the file rather than feeling overwhelmed by a lot of different worksheets that either have no information on them or information that is not important.
  • Hiding a worksheet can be practical when it contains lookups or reference data that is used to create other worksheets but is not something the end user(s) necessarily needs to see.
  • Hiding a worksheet can help prevent unauthorised or accidental changes to your file. As with above if you have a worksheet containing reference data hiding that sheet can stop other users changing the information and potentially ruining the file.  For additional protection you would need to adopt some form of password protection but often just hiding a worksheet can be enough if your end-users are not Excel savvy.

It is important to note that hiding worksheets in Excel does not remove them from the file, they are still active and can be referenced on any of the visible worksheets.  For example you can write a formula on a visible worksheet that links to a cell on a hidden worksheet.  All you are doing is hiding the worksheets from view.

Visibility States in Excel

There are three visibility states a worksheet can be in within your Excel file:

  1. Visible: Everyone can see the worksheet when the file is open.
  2. Hidden: The worksheet is not visible but can be easily unhidden with a couple of mouse clicks.
  3. Very Hidden: The worksheet is not visible and requires the user to go into the Visual Basic Editor in order to unhide it. Most Excel users will not understand how to do this and even more security can be added with password protection if you want to stop the more advanced Excel users from having access to the Worksheet(s) you have hidden.

How to Hide a Worksheet in Excel

There are a few ways to change a worksheets visibility:

  1. Manually Hide or Unhide the Excel worksheet
  2. Use the Visual Basic Editor to change the state of the Excel Worksheet
  3. Write VBA Script to Hide or Unhide the Excel worksheet

The first two methods will be the most common ones that you use.  The VBA scripting only becomes useful when it forms part of a larger script that you are running, for example running several different processes in your script that ends with hiding some worksheets in the end result.

Let’s look at each of the three methods in more detail:

Method One: Manually Hide the Excel Worksheet

This is by far the quickest method although it can be easily undone by any Excel users that have a grasp of the fundamentals.

First right-click on the worksheet name you wish to hide and then in the pop-up window select Hide, as shown below:

00086_Hide Excel Worksheets_01

To make the sheet visible again, or Unhide worksheets if you are using an already created file, right-click on one of the visible worksheet names then click on “Unhide” from the pop-up window.  This will make any worksheets that are hidden visible again.

00086_Hide Excel Worksheets_02

This method is useful for tidying up Excel files and is one of the most common methods used.  It is important to remember that it is easy for anyone to Hide or Unhide a worksheet this way so if you need a little extra security I suggest the next method.

Method Two: Use the Visual Basic Editor to change the state of the Excel Worksheet

This method is still very quick and because it gives you access to the actual worksheet state it is more powerful as you can fully hide the worksheet using Excels “very hidden” function.

First open the Visual Basic Editor; this can be opened by the shortcut command ALT+F11:

00086_Hide Excel Worksheets_03

As highlighted in the above image of the VBA Editor the two main areas are:

  1. VBA Project Window: This is where you select the worksheet that you want to change the state of and Hide. Left-Click on the Sheet name that you want to change and it will highlight in blue to show it has been selected.
  2. VBA Properties Window: After you have selected the worksheet you can change the properties of that worksheet in this window. The last category in the window is called “Visible” and when you left-click on that box you can use the drop down arrow to change the state of the Worksheet.

00086_Hide Excel Worksheets_04

The three options are:

  1. XlSheetVisible: Selecting this means the worksheet will be visible to all users.
  2. XlSheetHidden: This replicates manually hiding the worksheet so it will disappear from view but can be easily Unhidden by other users using the manual method of Hiding/Unhiding worksheets.
  3. XlSheetVeryHidden: This option is the most secure way to hide the worksheet and mainly it is very useful when you have an Excel worksheet that contains reference data that you do not want changed. The only way to reverse this choice is to go back into the Visual Basic Editor and change this option to XlSheetVisible and many Excel users will not be aware of how to do this, they will likely never know the worksheet even exists!

Method Three: Write VBA Script to hide the Excel worksheet

The final method to hide an Excel worksheet is to use VBA Script.  It is overkill to write VBA script solely to change the state of a worksheet but there are occasions where you may have a larger piece of VBA script that will need to Hide or Unhide worksheets during part of the processing.  The code to change between the three visibility states is below:

To hide a worksheet named Sheet1 then use:

1
Worksheets("Sheet1").Visible = False

To unhide a worksheet named Sheet1 then use:

1
Worksheets("Sheet1").Visible = True

To make a worksheet named Sheet1 Very Hidden then use:

1
Worksheets("Sheet1").Visible = xlVeryHidden

 

Summary

This post covered a fair amount of detail on the three visibility states of a worksheet looking at the three visibility states:

  • Visible
  • Hidden
  • Very Hidden

Along with teaching the three methods you can use for changing the visibility of a worksheet:

  • Manually (Hide and Unhide only)
  • Via the VBA Editor
  • Via VBA Script

Hopefully that has provided you with a good knowledge of how Excel worksheets can be Hidden or Unhidden to suit your needs.

Keep Excelling,

Need your own copy of Excel?  Purchase the latest version of Microsoft Office that contains Excel at great value here:

Leave a Comment