Hide sheet in excel vba. How to make a hidden sheet invisible in excel

If you use in your Excel workbook separate sheet To store values or formulas that are referenced by other sheets, you may want to make this sheet invisible. Great news! In Excel, you can easily hide sheets in a workbook.

You can even hide the entire workbook completely, although it is not very clear why you would need this. Anyway, we will show you how to hide worksheets and workbooks and also show them again.

Hiding sheets

To hide a sheet (labels at the very bottom Excel windows), right-click on the shortcut you want to hide, and in the context menu click Hide(Hide). The shortcut will disappear, but will not be completely removed.

Can't hide all sheets workbook. If you try to hide the last visible sheet, you will receive this warning:

The book must contain at least one visible sheet.
To hide, delete, or move selected sheets, you must first insert new leaf or make visible hidden sheet.

To display a sheet, right-click on any visible tab and select from the menu that appears Show(Unhide).

In the dialog box that appears (Unhide), select the sheet you want to make visible and click OK.

Comment: You can make only one sheet visible in one action.

In addition, you can show hidden sheets in another way. Go to the tab home(Home). In chapter Cells(Cells) click Format(Format). In a team group Visibility(Visibility) click Hide and show(Hide & Unhide) and select from the menu that opens Show sheet(Unhide Sheet).

A dialog box will appear Displaying a hidden sheet(Unhide). Select the desired sheet and click OK, as described above.

Hiding the workbook completely

To completely hide the working Excel workbook, go to the tab View(View).

In chapter Window(Window) click Hide(Hide).

The Excel window will remain open, but the table area will be empty. To display the workbook, click View > Window > Display(View > Window > Unhide).

A dialog box will appear Displaying a hidden book window(Unhide) with a list of open in currently workbooks. If you hid workbooks that are in this moment are not open, they will not be on this list.

If you hide a workbook and save the file, the next time you open the file, the workbook will still be hidden. The book will remain hidden until you display it yourself.

Sometimes you need space for information that users cannot read or change. Build a secret place in your workbook, a place where you can store data, formulas, and other little things that are used but not visible on your sheets.

The easiest way to hide such a sheet is to right-click on the sheet tab and select the option Hide(Fig. 1). However, a little more experienced users will be able to display a sheet hidden in this way by clicking on the tab of any visible sheet and selecting the option Show. You can simply protect the sheet by going through the menu Review –> Protect sheet. However, it will still be completely visible - data, formulas and everything else.

Rice. 1. Team Hide sheet

Download the note in or

This note offers even more radical remedy to hide data - change the worksheet property Visible, by assigning it a value xlVeryHidden in the VBA editor, but without writing VBA code.

Go through the menu Developer –> Visual Basic or press Alt+F11 to open the VBA Editor window (Figure 2).

In the Project - VBAProject window, find the name of your workbook (Book1 in our example) and expand its hierarchy by clicking the plus sign to the left of the workbook name. Expand the folder Microsoft Excel Objects to see all the sheets of the book (in Fig. 2 these manipulations have already been completed).

Select the sheet you want to hide (in our example, Sheet1) and open its properties by selecting the menu command at the top of the Editor window View –> Properties Window(or by pressing F4). Make sure the tab is selected Alphabetic and find the property Visible at the very bottom in the (Name) column. Click the property value field on the right Visible and select last value, 2 – xlSheetVeryHidden(Fig. 3).

Rice. 3. Change property Visible for sheet Sheet1

After you have selected option 2 – xlSheetVeryHidden – in the properties window Properties, Sheet1 disappears from the list of sheets in the book (Fig. 4). To display this window, you can, for example, right-click on the left or right arrow in the lower left corner of the Excel window next to the sheet labels.

Rice. 4. Sheet hidden using property Properties, not in the list of sheets in the book

This method of hiding a sheet is not completely reliable, as an advanced user can do reverse operation– go to the VBA editor and return the -1 xlSheetVisible property.

Based on materials from the book by D. Kholey, R. Kholey. Excel 2007 Tricks, pp. 36–37

You can hide any worksheet to remove it from the view. You can also hide the workbook window to remove it from work area. Data from hidden sheets and workbooks is not visible, but may still be referenced from other sheets and workbooks. If necessary, hidden sheets and workbooks can be displayed.

By default, the taskbar displays all the workbooks that open, but you can hide or show them on the taskbar if necessary.

Note: The screenshots in this article were taken in Excel 2016. If you are using a different version, the interface may be slightly different, but the features will be the same.

Hide or show a sheet

Notes:

When you show a hidden worksheet, you can only do this one at a time when using Visual Basic for Applications (VBA).

If sheets are hidden by VBA code that assigns the property xlSheetVeryHidden, the Unhide command will not display these hidden sheets. If you are using a workbook that contains VBA code and are having problems with hidden sheets, contact the workbook owner for more information.

Hide or show the workbook window

Notes:

Hide or show workbook windows on the Windows taskbar

Excel 2013 implemented in Unified Document Interface, where each book will open in a separate window. The steps below apply only to Excel 2007 and 2010 for Windows.

This article provides several ways to hide all hidden sheets and tables in Excel step by step.

Toggle the visibility of a hidden worksheet - one click to show all hidden sheets and one click to restore hidden ones

With convenient switch button Worksheets provided by Kutools for Excel, you can show all hidden worksheets in the active workbook with one click, and then click it again to make all hidden worksheets invisible. Fully featured 60-day free trial!

Recommended Productivity Tools for Excel/Office

Show all hidden worksheets one by one in Excel

We can apply Show feature to show hidden sheet at a time in Excel. Please do the following:

1 . Click home > Format > Hide and show > Show. See screenshot:
Attention: You can also right-click any of the sheet names in the Sheet tab bar and select Unhide from the context menu.

2 . Now all hidden sheets are listed in Unhide dialog window. Select the worksheet you want to show and click OK Button.

3 ,Then the selected hidden sheet is displayed. Repeat step 2 above to show all the invisible sheets one by one.

Attention: Showing all hidden worksheets is not as easy as it can be when hundreds of hidden worksheets need to be hidden. You may have to click hundreds of times to repeat operations. If you want an easier way, please do so according to the procedures that the following methods provide.

Show all hidden worksheets by VBA code

The following short VBA code can also help you to display all hidden sheets at the same time.

1 ,Hold Alt+F11 keys in Excel and it opens Microsoft Visual Basic for Applications window.

2 . Click Insert > module, and paste the following macro in the module window.

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

3 , press F5 to run this macro. And hidden sheets will be displayed immediately.

Show all hidden worksheets from Kutools for Excel with one click

Kutools for Excel Show all hidden sheets The feature can help you easily unhide all hidden sheets with just one click.

click Kutools > Show/Hide > Show all hidden sheets, see screenshot:

Then all hidden sheets are shown at once. See screenshot:

Attention: The number displayed after the function indicates the total number of hidden sheets in your workbook. If there is no hidden sheet, the function will be grayed out and cannot be clicked.

Show all hidden sheets using Hidden Sheets Visibility feature

Kutools for Excel also provides convenient Worksheets(Toggle Hidden Worksheets Visibility) is a feature that allows you to show or hide all hidden sheets with one click in the active workbook.

Under Kutools tab please click Worksheet button to show all hidden sheets and click Worksheets Click the button again to restore the hidden status.

Show all very hidden sheets of Kutools for Excel

Sometimes the above methods may not show hidden sheets. This is because these worksheets are very hidden. In this situation, you can apply the Hide/Unhide Workbooks and Sheets feature of Kutools for Excel to quickly unhide them.

1 . Click Kutools > Show hide > .

2 In the Hide/Show Workbooks and Sheets dialog box, click to select the active workbook and click show all V Sheets chapter. See screenshot.

Now all hidden sheets, including very hidden sheets, are shown in large quantities. Please close the dialog as needed.

Hide/show workbooks and tables hide or show multiple opening workbooks and their worksheets, including the most hidden ones.

In order to be able to hide information from prying eyes, and maybe from your own, Excel 2007 and higher provides the ability to hide sheets. In this case, you can make the sheets of the workbook either hidden or very hidden, and, if necessary, make them visible again, that is, display (show).

How to make a sheet hidden?

Making a sheet hidden is very simple; to do this, you need to right-click on the tab of the desired sheet and select “Hide” in the context menu that appears. In order to hide several sheets in a row, or all sheets except the active one (the book must contain at least one visible sheet), you need to left-click on the first desired sheet, then, while holding Shift key, click on the last one, then right-click in the area of the selected sheets and select “Hide” from the context menu. All selected sheets become hidden at once. Similarly, you can selectively select sheets by using the Ctrl key instead of the Shift key.

How to make a sheet very hidden?

Workbook sheets can be made very hidden. Such sheets cannot be displayed as standard sheets. using Excel, when calling the context menu, the “Display...” item (in some versions it is called “Show...”) is not active. To make the sheet very hidden, you need to go to the VB (Visual Basic) editor, you can do this different ways, but the simplest is to use the Alt+F11 key combination, select the desired sheet in the VBA workbook project, and then select the “xlSheetVeryHidden” parameter in the “Visible” property of this sheet.

To return the sheet to visibility, you need to change the "Visible" property back to xlSheetVisible.

How to show hidden sheets?

The reverse procedure, when it becomes necessary to display hidden sheets and make them visible, may require a lot of patience. To display hidden sheets, right-click in the sheet name area and select the “Show...” menu item.

After this, a window appears with a list of hidden sheets and hidden sheets are displayed on the screen by selecting one sheet from the entire list. It will not be possible to display them all at once.

You will have to make the sheets visible one at a time. Displaying even ten hidden sheets will be quite a tedious task, let alone more of them.

What should I do if I can’t hide or show a sheet?

If the workbook structure is protected, the “Hide” and “Show” items of the context menu will be inactive. In order to perform any actions with sheets, it is necessary to remove the book protection.

Displaying all sheets, selectively displaying and hiding sheets by mask

The Excel add-in presented below allows you to facilitate and speed up some of the manipulations performed with workbook sheets. The add-on allows you to:

1) Make all sheets hidden except the active one;

2) make all sheets except the active one very hidden;

3) display all hidden sheets at once, regardless of whether they are hidden or very hidden;

4) hide and show sheets by mask using Special symbols matches for sheet names.

video on working with the add-on

macro (add-in) for quickly hiding and showing sheets

The best way to hide a sheet in Excel

For clarity, let's look at an example. Hide Sheet3 in the usual way(using the "Hide" option in the context menu).

And “Sheet2” will be hidden using the parameters of the VBA macro editor. To do this, perform the following steps:

Open the VBA editor by pressing the hotkey combination Alt+F11.
In the Project window, select Sheet2. And in the “Properties” window, find the “Visible” property and select the “2-xlSheetVeryHidden” parameter for it. And close the editor window.
To show hidden Excel sheets, right-click on the sheet tab and select the “Show” option.

We do not see our “Sheet3” in the lists of hidden sheets. It is safely hidden. To see it again, you need to go to the VBA editor and change the parameters in the “Visible” property to “-1-xlSheetVisible”.

Note. The third parameter “0-xlSheetHidden” gives the same result as normal hiding of a sheet using the context menu.

Helpful advice. You can visually hide sheet labels:

Using the parameter settings: “File” - “Options” - “Advanced” - “Show sheet shortcuts”.
Using an extension horizontal scroll, as it shown on the picture:

But in both the first and second cases, you can switch between sheets using a combination of hot keys CTRL keys+PageUp and CTRL+PageDown.

Useful Lifehack in Excel

To prevent inserting new rows and columns in Excel without using sheet protection, you need to do this:

Go to the last cell relative to rows and columns XFD1048576. To do this, you can enter the cell address XFD1048576 in the name field and press Enter. And if the sheet is empty, you can press the hotkey combination CTRL+ → (left arrow) to move the cursor to the last column of XFD1, and then CTRL+ ↓ (down arrow) will move the cursor to the last cell of XFD1048576.
Enter any value in the last cell XFD1048576, you can even use a space " ".

That's all, now you can't insert a row or column into the sheet. If you need to prohibit inserting only rows, then enter any value in the most last line(eg A1048576). And if you prohibit inserting only columns, then enter any value in the last column (for example, XFD1).

Securely hide sheets that cannot be displayed standard means(with the Show option). Useful lifehacks for working with sheets and cells.

Sometimes some sheets in the book have to be hidden from the eyes of users. The classic method involves hiding the sheet through the menu Format - Sheet - Hide or right-click on the sheet tab - Hide:

The problem is that the user, having gone to the menu Format - Sheet - Display or by right-clicking on any sheet tab and selecting Show (Unhide), will see the names of hidden sheets and understand that some information is hidden from him:

Therefore, it is better to make sure that the user does not even realize that there are any hidden sheets in the book. To do this, open the Visual Basic editor:

in Excel 2003 and older - by selecting from the menu Tools - Macro - Visual Basic Editor
in Excel 2007 and later - by clicking on the button Visual Basic Editor on the tab Developer or by clicking ALT+F11

We are looking for this window on the screen:

If it is not visible, you can display it through the menu View - Project Explorer (top part) And View - Properties Window(Bottom part).

In the upper part of the “tree” we find and select our sheet (in the picture - Sheet1), and in the lower part we find the property Visible(at the end of the list) and do it xlSheetVeryHidden.

Voila! Now you can see this sheet and find out about its existence only in the Visual Basic editor and it will not be displayed in any Excel windows or menus. The less you know the better you sleep. 🙂