Contents
A huge advantage of Excel spreadsheets is that the user can work with both one sheet and several. This makes it possible to structure information more flexibly. But sometimes it can come with some problems. Well, there are all sorts of situations, it may contain information about important financial assets or some kind of trade secret that should have been hidden from competitors. This can be done even with standard Excel tools. If the user accidentally hid the sheet, then we will figure out what should be done to show it. So, what needs to be done to perform both the first and second action?
This method is the easiest to implement because it consists of two steps.
- First we need to call the context menu. To do this, you need to right-click or press with two fingers on the trackpad, after moving the cursor to the place you want. The last option to call the context menu is only supported by modern computers, and not all. However, most modern operating systems support it, since it is much more convenient than simply pressing a special button on the trackpad.
- In the context menu that appears, look for the “Hide” button and click on it.
Everything, further this sheet will not be displayed.
How to hide a sheet in excel using tools
This method is not as popular as the previous one. However, there is such a possibility, so it would be nice to know about it. There are a few more things to do here:
- Check if you are on the “Home” tab or in another. If the user has another tab open, you need to move to the “Home”.
- There is an item “Cells”. You should click on the corresponding button. Then three more buttons will pop up, of which we are interested in the rightmost one (signed as “Format”).
- After that, another menu appears, where in the middle there will be an option “Hide or show”. We need to click on “Hide sheet”.
- After completing all these steps, the sheet will be hidden from the eyes of other people.
If the program window allows this, then the “Format” button will be displayed directly on the ribbon. There will be no clicking on the “Cells” button before this, since now it will be a block of tools.
Another tool that allows you to hide a sheet is called the Visual Basic Editor. In order to open it, you need to press the key combination Alt + F11. After that, we click on the sheet of interest to us and look for the properties window. We are interested in the Visible option.
There are three options for customizing the sheet display:
- The sheet is shown. Denoted by the code -1 in the picture above.
- The sheet is not shown, but it can be seen in the list of hidden sheets. Denoted by code 0 in the list of properties.
- The leaf is very strongly hidden. This is a unique feature of the VBA editor that allows you to hide a sheet so that it cannot be found in the list of hidden sheets through the “Show” button in the context menu.
In addition, the VBA editor makes it possible to automate the procedure depending on what values, as an option, are contained in the cells or what events occur.
How to hide multiple sheets at once
There is no fundamental difference between how to hide more than one sheet in a row or how to hide one of them. You can simply hide them sequentially in the manner described above. If you want to save a little time, then there is another way. Before you implement it, you need to select all the sheets that need to be hidden. Perform the following sequence of actions to remove several sheets from view at the same time:
- If they are next to each other, we need to use the Shift key to select them. To begin with, we click on the first sheet, after which we press and hold this button on the keyboard, after which we click on the last sheet of those that we need to hide. After that, you can release the key. In general, there is no difference in what order these actions should be performed. You can start from the last one, hold down Shift, and then go to the first one. To implement this method, you need to arrange the sheets to be hidden next to each other by simply dragging the mouse.
- The second method is needed if the sheets are not next to each other. It will take a little longer. To select several that are at a certain distance from each other, you must click on the first sheet, and then sequentially select each next one with the Ctrl key. Naturally, it must be kept pressed, and for each sheet, make a single click with the left mouse button.
Once we have completed these steps, the next steps are similar. You can use the context menu and hide the tabs or find the corresponding button on the toolbar.
There are several ways to show hidden sheets in Excel. The simplest of them is to use the same context menu as for hiding it. To do this, you need to click on any of the remaining sheets, right-click with the mouse (or use the special trackpad gesture if you are from a modern laptop) and find the “Show” button in the list that appears. After we click it, a window will appear with a list of hidden sheets. It will be displayed even if there is only one sheet. 
If hiding was done using a macro, then you can show all the sheets that were hidden with a little code.
Sub OpenAllHiddenSheets()
Dim Sheet As Worksheet
For Each Sheet In ActiveWorkbook.Worksheets
If Sheet.Visible <> xlSheetVisible Then
Sheet.Visible = xlSheetVisible
End If
Next
End Sub
Now it remains only to run this macro, and all the hidden sheets will be immediately opened. Using macros is a convenient way to automate the opening and hiding of sheets depending on what events occur in the program. Also, using macros, you can show a large number of sheets at a time. It’s always easier to do this with code.