How to make a hidden sheet invisible in excel? How to make a sheet very hidden

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 to display hidden sheet, you can only do this one by one only 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.

Excel has three levels of sheet visibility: visible, hidden, and very hidden. Visible The sheet appears in the sheet list of sheets in the workbook. Hidden the sheet is not displayed in the list, but it can be displayed standard methods. The so-called Very hidden a mode that can only be set and removed using macros. Very hidden sheets are not displayed in the list of sheets, moreover, they are not displayed in the "Display Hidden Sheet" dialog box.

Since most Excel users do not know about this method, it is convenient to use if you need to hide part of the information.

How to make a sheet very hidden

Method number 1. Via VBA-Excel add-in

Most convenient way, which does not require special knowledge, and also allows you to quickly hide a large number of sheets - install the VBA-Excel add-in. In order to hide or show sheets in Excel you must:

Method number 2. Using the sheets property in the code editor

To hide a sheet you need to:

To display a hidden sheet, you can set the value in a similar way -1 - xlSheetVisible.

Method No. 3. Using Macros

We can say that this is the most the hard way, however, more universal. Using programming you can set various conditions hiding sheets and much more. But let’s just focus on hiding/displaying for now.

Open the code editor with a keyboard shortcut Alt+F11.
In the left menu, expand the project with the same name as your book.
Create new module(if you don't already have one) in which we will write the code to hide the sheets.
Go to the module and paste the following code into it:

Sub HidingSheets() " Remove extra lines of code depending on the result you want to get Sheets("SheetName").Visible = xlSheetHidden " Hide sheet in the usual way Sheets("SheetName").Visible = xlSheetVeryHidden " Make a super hidden sheet Sheets("SheetName").Visible = xlSheetVisible " Display the sheet End Sub

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 proposes an even more radical means of hiding data - changing 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 the 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, since an advanced user can do the opposite 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

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 sheets workbook both hidden and 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 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 in 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 show hidden sheets you need toRight-click in the sheet name area and select the "Display..." 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, you must remove 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 characters matches for sheet names.

File Microsoft programs Excel is called a BOOK. And a book, as usual, consists of sheets. Their default number is 3, but you can create as many as you need. Sheet labels can be easily switched with one click: they are shown in the lower left corner of the book and are named as standard: SHEET1, SHEET2, etc. Can be switched by a combination of hot CTRL keys+ PageUp (PageDown). But this is not always convenient. And why aren’t their labels visible? Let's look at the settings.

How to return shortcuts

Let's see what a book should look like so that you can easily move from one sheet to another. Here they are. This is the lower left corner Excel workbooks default.

But someone in the lower left corner may have a completely different picture.

What should you do in this case, when when you open a book, it does not show a panel with bookmarks? Excel needs to be configured. If you have the 2007 version of the program, the path to the necessary settings located in OFFICE (located at the top left, in the very corner of the book) – EXCEL SETTINGS – ADDITIONAL – SHOW PARAMETERS FOR THE NEXT BOOK. Here we find the line SHOW LABEL and check the box. Click OK.

After this, the sheets will appear in the lower left corner of the workbook. If you have Excel 2003, then the path is as follows: SERVICE – PARAMETERS – VIEW. For versions 2010-2013: FILE – PARAMETERS – ADDITIONAL.

How to hide and show sheet labels

There is another function in Excel. You can hide and show sheets in your existing bookmarks panel. This may be useful if you need to create large quantity sheets, when you have to use arrows to find what you need.

In this case, temporarily unnecessary sheets can be hidden so that they do not take up space on the panel.

This is done very simply. Let's assume that we need to hide sheets 10, 11 and 12. To do this, first right-click on the tenth sheet and select HIDE.

We do the same with the eleventh and twelfth. It turns out the following.

The sheets are hidden but not deleted. The information on them is also saved. And if we try to rename SHEET13 to SHEET10, the program will not allow us to do this.

We'll have to come up with a different name for the leaf. Or leave everything as is.

Hidden sheets are returned to their places in the same way. Right-click on any visible sheet and select SHOW. The program will show a list of hidden sheets and prompt you to select the one you want to display. You cannot set a command for all sheets at the same time. You need to do this one by one.