How to hide sheets from most Excel users. How to properly hide a sheet in Excel

In order to be able to hide information from prying eyes, and perhaps 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 workbook must contain at least one visible sheet), you need to left-click on the first desired sheet, then, while holding down the Shift key, click on the last one, then right-click mouse button in the area of 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 using standard Excel tools; when you call the context menu, the “Display ...” item (in some versions it is called “Show ...”) is not active. To make a sheet very hidden, you need to go to the VB (Visual Basic) editor, this can be done in different ways, but the easiest is to use the Alt+F11 key combination, select the desired sheet in the VBA workbook project, and then in the “Visible” property of this sheet select the “xlSheetVeryHidden” parameter.

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 matching characters 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's tab and select the "Unhide" 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 the horizontal scroll extension as shown in the image:

But in both the first and second cases, you can switch between sheets using the hotkey combination CTRL+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 very last row (for example, 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 using standard means (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. 🙂

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:

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:

But in both the first and second cases, you can switch between sheets using the hotkey combination CTRL+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:

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

Good day, dear visitor!

In this lesson we will look at such an interesting question, 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 easiest and most affordable way to hide a 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 list window that appears for selecting hidden sheets, select 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 does this process actually happen, and there is nothing complicated in it and you yourself, without knowing Visual Basic, can do it in an elementary way, namely, take 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 users of Excel 2003 and below, this can be done from 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 the 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

If you are working with a 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 anyway, then go to: Tools menu - Excel Options - Advanced - Section Show workbook options - 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 of the required sheets of the book. 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:

You can hide any worksheet to remove it from the view. You can also hide the workbook window to remove it from the workspace. 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.