How to hide a sheet so that it cannot be displayed. How to hide sheets and entire workbooks in Excel

The ability in Excel to create separate sheets in one workbook allows you, in fact, to create several documents in one file and, if necessary, link them with links or formulas. Of course, this significantly increases the functionality of the program and allows you to expand the horizons of the tasks. But sometimes it happens that some sheets you created disappear or all their labels completely disappear in the status bar. Let's find out how you can get them back.

Navigation between sheets of the book is possible using shortcuts that are located on the left side of the window above the status bar. We will consider the issue of their restoration in case of loss.

Before we start studying the recovery algorithm, let's figure out why they might disappear in the first place. There are four main reasons why this can happen:

Disable the shortcut bar;
Objects were hidden behind a horizontal scroll bar;
Selected shortcuts have been set to hidden or super hidden;
Delete.

Naturally, each of these reasons causes a problem that has its own solution algorithm.

Method 1: Enable the Shortcut Bar

If there are no shortcuts above the status bar in their proper place, including the active element shortcut, this means that their display was simply disabled by someone in the settings. This can only be done for the current workbook. That is, if you open another Excel file the same program, and the default settings will not be changed in it, then the shortcut panel will be displayed in it. Let's find out how you can re-enable visibility if you disable the panel in the settings.

Method 2: Move the Scroll Bar

Sometimes there are cases where the user accidentally drags the horizontal scroll bar over the shortcut bar. Thus, he actually hid them, after which, when discovered this fact, a feverish search begins for the reason for the lack of labels.

Method 3: Enable showing hidden shortcuts

You can also hide individual sheets. In this case, the panel itself and other shortcuts on it will be displayed. Difference hidden objects from deleted ones is that they can always be displayed if desired. In addition, if on one sheet there are values that are pulled up through formulas located on another, then if an object is deleted, these formulas will begin to display an error. If you simply hide the element, then no changes will occur in the functioning of the formulas, there will simply be no shortcuts for transition. Speaking in simple words, the object will essentially remain as it was, but the navigation tools to navigate to it will disappear.

The hiding procedure is quite simple. You need to right-click on the corresponding shortcut and select the item in the menu that appears "Hide".

As you can see, after this action the selected element will be hidden.

Now let's figure out how to show hidden shortcuts again. This is not much more difficult than hiding them and is also intuitive.

Method 4: Show Super Hidden Sheets

In addition to hidden sheets, there are also super hidden sheets. They differ from the first ones in that you will not find them in the usual list of displaying a hidden element. Even if you are sure that this object definitely existed and no one deleted it.

Elements can disappear in this way only if someone purposefully hid them through the editor VBA macros. But finding them and restoring the display on the panel will not be difficult if the user knows the algorithm of actions, which we will discuss below.

In our case, as we see, the panel lacks the labels of the fourth and fifth sheets.

Going to the window for displaying hidden elements, in the way that we talked about in the previous method, we see that it displays only the name of the fourth sheet. Therefore, it is quite obvious to assume that if the fifth sheet is not deleted, then it is hidden using the VBA editor tools.

First of all, you need to enable macro mode and activate the tab "Developer", which are disabled by default. Although, if in this book some elements were assigned the status of super-hidden, then it is possible that the specified procedures have already been carried out in the program. But, again, there is no guarantee that after hiding elements, the user who did this did not disable it again necessary tools to enable the display of super hidden sheets. In addition, it is quite possible that enabling the display of shortcuts is not performed on the same computer on which they were hidden.

In the Excel Options window that opens, click on the item "Customize the Ribbon". In the block "Main Tabs", which is located on the right side of the window that opens, check the box, if not, next to the parameter "Developer". After that we move to the section "Security Control Center" by using vertical menu on the left side of the window.

In the window that opens, click on the button "Trust Center Settings...".

The window is launching "Security Control Center". Go to the section "Macro Options" through the vertical menu. In the toolbox "Macro Options" set the switch to position "Enable all macros". In the block "Developer Macro Options" check the box next to the item "Trust access to object model VBA projects". After working with macros is activated, click on the button "OK" at the bottom of the window.

Back to Excel settings For all settings changes to take effect, also click on the button "OK". After this, the developer tab and work with macros will be activated.

Now, to open the macro editor, move to the tab "Developer", which we just activated. After that, on the ribbon in the toolbox "Code" click on the big icon « Visual Basic» .

You can also launch the macro editor by typing the keyboard shortcut Alt+F11.
After this, the macro editor window will open, on the left side of which there are areas "Project" And "Properties".

But it is quite possible that these areas will not appear in the window that opens.

To enable area display "Project" click on the item horizontal menu "View". In the list that opens, select a position "Project Explorer". Or you can press the hotkey combination Ctrl+R.

To display an area "Properties" click on the menu item again "View", but this time we select a position in the list "Properties Window". Or how alternative option, you can simply click on function key F4.

If one area overlaps another, as shown in the image below, then you need to place the cursor on the border of the areas. At the same time, it should transform into a double-headed arrow. Then pinch left button mouse and drag the border so that both areas are completely displayed in the macro editor window.

After this in the area "Project" highlight the name of the super-hidden element that we could not find either on the panel or in the list hidden shortcuts. IN in this case This "Sheet 5". At the same time, in the area "Properties" settings are shown of this object. We will be specifically interested in the point "Visible" ("Visibility"). Currently, the parameter opposite it is set "2 - xlSheetVeryHidden". Translated into Russian "Very Hidden" means “very hidden”, or as we previously expressed “super hidden”. To change this parameter and return visibility to the shortcut, click on the triangle to the right of it.

After this, a list appears with three options for the state of the sheets:
- "-1 – xlSheetVisible"(visible);
- "0 – xlSheetHidden"(hidden);
- "2 - xlSheetVeryHidden"(super hidden).
In order for the shortcut to appear on the panel again, select the position "-1 – xlSheetVisible".

But, as we remember, there is still a hidden "Sheet 4". Of course, it is not super hidden and therefore its display can be set using Method 3. It will even be easier and more convenient. But, if we started talking about the possibility of enabling the display of shortcuts through the macro editor, then let's see how it can be used to restore ordinary hidden elements.
In the block "Project" highlight the name "Sheet 4". As we see, in the area "Properties" opposite the point "Visible" parameter set "0 – xlSheetHidden", which corresponds to the usual hidden element. Click on the triangle to the left of this parameter to change it.

In the list of parameters that opens, select the item "-1 – xlSheetVisible".

After we have configured the display of all hidden objects in the panel, we can close the macro editor. To do this, click on the standard close button in the form of a cross in the right top corner windows.

As you can see, now all the shortcuts are displayed in the Excel panel.

Method 5: Recovering Deleted Sheets

But it often happens that the labels disappear from the panel simply because they were removed. This is the most difficult option. If in previous cases when correct algorithm action, the probability of restoring the display of shortcuts is 100%, then when they are deleted, no one can give such a guarantee of a positive result.

Removing a shortcut is quite simple and intuitive. Just right-click on it and select the option in the menu that appears "Delete".

After this, a warning about deletion will appear in the form of a dialog box. To complete the procedure, just press the button "Delete".

Recovering a deleted object is much more difficult.

But you should pay attention to the fact that by restoring a sheet in this way, you will lose all the data entered into the document, starting with its last save. That is, in essence, the user has to choose between what is more priority for him: the deleted object or the data that he managed to enter after the last save.

But, as mentioned above, this option recovery is only suitable if the user did not have time to save the data after deleting it. What to do if the user saved the document or even exited it and saved it?

If, after deleting the shortcut, you already saved the book, but did not have time to close it, that is, it makes sense to delve into the file versions.

But if you saved and closed the file, and the next time you open it you see that one of the shortcuts has been deleted, then you won’t be able to restore it using this method, since the list of file versions will be cleared. But you can try to restore through version control, although the likelihood of success in this case is much lower than with previous options.

However, the likelihood of finding the book you need is low. Moreover, even if she is present in this list and contains a removed element, it is likely that the version will be relatively old and not contain many of the changes that were made later.

As you can see, the disappearance of shortcuts on the panel can be caused by a number of reasons, but they can all be divided into two large groups: Sheets have been hidden or deleted. In the first case, the sheets continue to be part of the document, but access to them is difficult. But if you wish, having determined the way in which the shortcuts were hidden, adhering to the algorithm of actions, it will not be difficult to restore their display in the book. It's another matter if objects were deleted. In this case, they were completely extracted from the document, and their restoration is not always possible. However, even in this case, it is sometimes possible to recover data.

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 all workbook completely, although it is not very clear why you might need this. Anyway, we will show you how to hide worksheets and workbooks and also show them again.

Hiding sheets

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

You cannot hide all sheets in a 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 a new sheet or make a hidden sheet visible.

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, show hidden sheets it is possible in another way. Go to the tab Home(Home). In the section 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 hide an Excel workbook completely, go to the View(View).

In the section 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 present moment workbooks. If you hid workbooks that are in at the 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.

Program file Microsoft Excel called 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 LABELS and check the box. Click OK.

After this, the sheets will appear in the lower left corner of the book. 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 can be useful if you need to create a large number of sheets, when you have to use the 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.

You cannot hide or delete absolutely all sheets. At least one must remain visible.

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 box. 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 windows.

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.

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 are from Excel 2016. If you're 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.