Hide a sheet in the Excel hidden sheets list. How to make a sheet very hidden

Twitter

Sometimes situations arise when you need to hide a sheet with all its contents from prying eyes. Very often this is done from context menu sheet (for lucky owners of 2007 Excel. Read more:). But when using this method It is necessary to protect the workbook, otherwise everyone will be able to display hidden sheets (with the same right-click - Display). But what if the book does not need to be protected? After all, it is often necessary to leave users the ability to work with the structure of the book, for example, adding sheets. How then can I hide the sheet so that it cannot be displayed? There is a fairly simple way through the VBA editor, and you don’t need any programming skills at all. So, we decided which sheet should be hidden. Further:

go to the VBA editor( Alt+F11)
press the keyboard shortcut Ctrl+R or go to the menu View-Project Explorer(to display the VBA Project Objects window)
click F4 or through View-Properties Window(to display the properties window)
in the left part of the window (in Project Explorer) we look for the book in which the sheet needs to be hidden and open its folder (in the screenshot it is VBA Project (Book 1))
open the folder Microsoft Excel Objects and select the sheet with the desired name there
in the Properties Window ( Properties Window) find the property Visible and assign it a value xlSheetVeryHidden

To then display this sheet you need the property Visible set value - xlSheetVisible. In this case, to display the sheet, you will need to go to the VBA editor - simple method will not be displayed. And there is no need to protect the book. Which is exactly what we needed.
For greater security, you can protect your VBA project so that you cannot display a worksheet from VBA without providing a password. This will not affect the functionality at all.
In the same VBA window( Alt+F11):

Tools-VBAProject Properties-tab Protection;
To protect, check the box " Lock project for viewing"; to unlock - removed;
the password itself is entered/removed in the fields Password And Confirm password.

You can hide all sheets in the workbook, except the sheet named “Visible”, with the following macro:

Sub Hide_All_Sheets() Dim wsSh As Object For Each wsSh In ActiveWorkbook.Sheets If wsSh.Name<>"Visible" Then wsSh.Visible = xlSheetVeryHidden "display - xlSheetVisible; make the sheet simply hidden - xlSheetHidden Next wsSh End Sub

Good day, dear visitor!

In this lesson we will look at this interest Ask,how to hide a sheet in Excel our book. Actually, why is all this being done, but there is only one meaning here - this is to hide from other users certain information that is stored on a hidden sheet. There can be many reasons for this, including hiding the database, no matter what someone’s clumsy fingers cause damage to the data or hiding intermediate data or something unnecessary, but it’s a pity to delete it in case it comes in handy, or you can figure out why you need to be “encrypted” .

So, if your name is Mata Hari or James Bond, sit back and listen to the basics of the secret and mysterious. We will look at 2 options to hide a sheet in Excel, these are:

Let's look at all the methods step by step and in more detail, why and how best to use them, what advantages they have, let's get started:

This is the simplest and affordable way hide sheet in excel, it is best used when you are not hiding anything valuable, but only removing unnecessary sheets from the visualization or those sheets on which .

In order to hide sheet in excel or to display it you need to take a few simple steps:

1. Call the context menu by right-clicking on the sheet that you want to hide.

2. In the menu you called up, you need to press the item "Hide" and the required sheet will be hidden out of sight.

3. For the reverse procedure to display a hidden sheet in Excel, you again call the context menu by clicking on any label with the name of the sheet.

4. In the context menu, select the item "Show".

5. In the selection list window that appears hidden sheets, choose the one you need and everything again becomes visible and accessible.

Note that there is nothing complicated about hide and show hidden sheet in Excel there is nothing. Therefore, this method is needed for those who simply want to remove unnecessary information, but for more serious hidden information you need the following option.

This is the case when the user will not even suspect the presence of hidden sheets in the book and practically no tricks will help him identify them or even guess their presence.

How exactly does this process take place, and there is nothing complicated about it and you yourself, without knowing, Visual Basic You can easily do this, namely, follow all the steps that I will describe:

Step 1 : We open, in fact, the Visual Basic editor itself, in which all changes will be made. For Excel users 2003 and below, this can be done in the menu “Tools” - “Macro” - “Visual Basic Editor”, and for users above Excel 2003 launch on button click "Visual Basic Editor", having passed, to the "Developer" tab or just click Alt+F11.

Step 2 : Go to the menu "View" - "Project Explorer", to display the project objects window Visual Basic Application (VBA) or just press the key combination Ctrl+R(if it is enabled, skip this item).

Step 3 : Go to the menu again "View" - "Properties Window", display the window properties or click on the selected object F4.

Step 4 : In the properties window that opens "Properties", you need to find a property called "Visible" and select a value for it from the drop-down menu "xlSheetVeryHidden".

That's all! Now about the existence of this sheet cannot be found from any source other than the Visual Basic editor and in all Excel menus it stopped showing up. As they say, people say, you sleep less, you know.

For, in general, specific reliability, you can protect your VBA project; this does not affect functionality in any way. And the actual protection is installed like this:

1. On "Visual Basic Editor" select menu "Tools" - "VBAProject Properties", select a tab "Protection".

2. To activate protection, you need to check the box "Lock project for viewing", and accordingly, to unlock it, you will remove it.

3. Well, actually write down and delete your password in the fields "Password" And "Confirm password".

You may also be interested in articles on how to hide other Excel elements: “ ” and “ ”.

Well, that’s basically all I wanted to tell you!

Economics is the art of satisfying unlimited needs with limited resources.
Lawrence Peter

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, all you need to do is click right click 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 using 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 standard means 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 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 symbols matches for sheet names.

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

Next short code VBA can also help you show all hidden sheets at once.

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 above mentioned methods cannot 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 press 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.

If you are working with large file and many sheets, then for convenience you hide non-working sheets or reference sheets. Right? But sometimes, you urgently need to display all hidden sheets. It is known that in order to display a sheet, you need to right-click on the list of sheets or the shortcut of one sheet - Display - Select a sheet to display.

What if there are 10 such sheets, and what if there are 70 (I had this happen once)? How to return all hidden sheets at once quickly?

In order to display all hidden sheets, there is a useful macro ()

sub ShowShts() dim a for each a in worksheets a.visible=true next end sub

Click in the window that opens and paste the text above. Close the window. You have created . You can call the created macro by clicking and selecting the desired macro from the window that opens.

How to show all hidden sheets if they are missing?

Most likely, the horizontal scroll bar has moved to the left. Look at the scroll bar (in the picture) and drag it to the right. There should be labels underneath

If there are no shortcuts, then go to: Menu Tools - Excel Options— Advanced — Section Show workbook parameters — Show sheet shortcuts. Check the box!

P.S. Don’t forget that you can switch between sheets using the keys, this is very convenient.

You can select multiple sheets by holding down the button Ctrl and clicking on each the necessary sheets books. And to select several sheets in a row, you can select the first sheet, hold down the Shift button and select the last sheet. In this case, all sheets between them are selected (this method can also apply to files in folders). Then right-click on the sheets - Hide.

Also read the interesting article, “ «.

Share our article on your social networks: