Hiding many worksheets at once is easy in Excel. Click on a sheet, hold down the control or the shift key, click on the other sheets you’d like to hide, right-mouse click and select “Hide”. Bạn đang xem: Hide or unhide worksheets
What if you wanted lớn unhide all the sheets in one go?
Find out 3 methods you can use to unhide all sheets at once.
UPDATE 2021: Good news! If you have Office 365 you can now unhide multiple sheets at once!
If you don’t have Office 365, continue reading.
One of the first “tricks” an Excel user learns is khổng lồ hide và unhide a sheet.
This is an exceptionally useful feature as it allows us to store data in a sheet, such as lists & tables, but keep the user of the workbook from seeing, manipulating, & more importantly, corrupting the information on the hidden sheet.
As with most things in Excel, there is more than one way lớn hide a sheet or multiple sheets. One of the easiest methods is khổng lồ select a sheet (or select multiple sheets using standard Windows CTRL và Shift selection techniques), right-click the sheet tab then select “Hide”.
As an example; suppose you have twelve sheets labeled “January” through “December” và you want lớn hide all the monthly sheets except “December”.
Select the “Jan” sheetHold down the Shift keySelect the “Nov” sheetRight-click on any selected sheet tabClick “Hide”
Unfortunately, unhiding multiple sheets in a single step is not as easy. If you right-click a sheet tab và select “Unhide”, the proceeding dialog box only allows a single sheet lớn be selected for the unhide operation.
This means you will have to lớn perform the unhide operation eleven times lớn restore all the hidden sheets to a visible state.
Never fear, a solution is here (actually, three solutions)
An often-overlooked feature in Excel is the ability lớn save a custom view.
Custom views can be used to “save” the hidden or visible states of rows and columns. This is convenient when you wish to show details of data for one printout, but a summarized version of the data in a different printout.
Hide the desired rows and/or columns.Click View (tab) -> Workbook Views (group) -> Custom Views -> Add… and give the current configuration a name.
If you change the hidden/visible state of rows and/or columns but then wish lớn return to the saved configuration, repeat the process (View (tab) -> Workbook Views (group) -> Custom Views), select your saved view then click “Show”.
The screen will immediately return lớn the desired state.
Custom views also work with the visible/hidden states of worksheets. If we create a custom view prior khổng lồ hiding ANY of the sheets (View (tab) -> Workbook Views (group) -> Custom Views -> Add…), we can hide as many sheets as we like. When it comes time lớn redisplay all the sheets, we repeat the process và select our “normal” view và click “Show”.
All the sheets have returned.
There is one negative to lớn this process. Custom views vì chưng not work with Data Tables. The moment you showroom a Data Table khổng lồ ANY sheet, the Custom Views feature becomes inoperable.
Because more và more people use Data Tables in their workbooks (and why wouldn’t you? They’re AMAZING!!!), we need khổng lồ explore another way of unhiding all hidden worksheets.
Xem thêm: Vẽ Hình 3D Đơn Giản Mà Ai Cũng Có Thể Thực Hiện, Cách Vẽ Hình 3D Đơn Giản Bằng Bút Chì
Right up front, this does not require the use of macro-enabled workbooks. This technique can be performed in any Excel workbook.
Open the Visual Basic Editor by pressing Alt-F11 on the keyboard or right click on any sheet tab và select View Code.
Don’t concern yourself with what you see in the ensuing window; all of that is for another day.
Activate the Immediate Window by clicking View -> Immediate Window (or CTRL-G).
Now we will run a macro. This macro will loop through all the hidden sheets and revert their visibility states khổng lồ “visible”. We will use a “For…Each” collection loop to lớn perform this operation.
NOTE: If you are interested in learning about this command và many other useful things macros can bởi for you, visit the liên kết at the over of this tutorial.
In the Immediate window, typefor each sh in worksheets: sh.visible=true: next sh
(press Enter)
All the sheets have returned to lớn a visible state.
What does that code mean? Let’s break down the code.
for each sh in worksheets
This establishes a collection (list) of all worksheets và allows us to lớn refer khổng lồ each sheet individually with the alias “sh”.
sh.visible=true
With the first sheet in the collection, phối the visible property lớn “true”. This makes the sheet visible lớn the user.
next sh
This selects the next sheet in the collection and returns lớn the first statement lớn repeat the process.
This process will repeat for as many sheets as are in the collection.
If this code is something you will use frequently, you can save the code in a Notepad file và then copy/paste it back into the Immediate Window whenever needed.
This technique is covered in detail in the Excel VBA course (link below if you are interested in becoming a VBA Powerhouse) but will be summarized here.
If this feature is to lớn be used often across many different workbooks, it’s worth taking the time to set this feature up on the QAT.
We will create a simple macro & store it in a special place in Excel called the Personal Macro Workbook.
Creating the Macro
Give the macro a name (“Unhide_All” is a good name.) Macro names cannot contain spaces.Click OKOpen the Visual Basic Editor (Alt-F11).In the Project Explorer panel (upper-left), click the plus-sign next to lớn the entry labeled “VBAProject (PERSONAL.XLSB)”.Click the plus-sign next khổng lồ the folder labeled “Modules”.Enter the following code:Sub Unhide_All()Dim sh As Worksheetfor each sh in worksheets: sh.visible=true: next shEnd Sub
