Cập nhật nội dung chi tiết về How To Unhide Sheets In Excel (All In One Go) mới nhất trên website Beiqthatgioi.com. Hy vọng thông tin trong bài viết sẽ đáp ứng được nhu cầu ngoài mong đợi của bạn, chúng tôi sẽ làm việc thường xuyên để cập nhật nội dung mới nhằm giúp bạn nhận được thông tin nhanh chóng và chính xác nhất.
In case you prefer reading a tutorial over watching a video, below is a detailed written tutorial on unhiding sheets in Excel.
When you work with data that is spread across multiple worksheets in Excel, you may want to hide a few worksheets. This could be to avoid the clutter or to not show some data to your client/manager by hiding some worksheets and only keeping the useful ones visible.
And in some cases, you may have a workbook that has some hidden sheets and you want to unhide some or all of these worksheets.
In this tutorial, I will show you some methods to unhide worksheets in Excel (manually as well as automatically using VBA). I will also show you how to selectively unhide worksheets based on the name or a condition.
So let’s get started!
Unhiding Sheets Manually
If you only have a few worksheets that are hidden, you can manually unhide some or all of these worksheets.
Suppose you have an Excel workbook that has 10 worksheets that are hidden.
Below are the steps to manually unhide worksheets (one at a time):
The above steps would unhide the select worksheet.
Note: Unfortunately, there is no in-built functionality in Excel to quickly unhide all the hidden worksheets (or a way to select more than one worksheet and unhide it). As of now, you need to use the unhide dialog box where you can only select one worksheet to unhide.
Unfortunately, there is no in-built functionality in Excel to quickly unhide all the hidden worksheets (or a way to select more than one worksheet and unhide it). As of now, you need to use the unhide dialog box where you can only select one worksheet to unhide.
While there is no-inbuilt functionality to unhide in bulk, you can easily do this with a simple VBA macro code.
Unhide All Sheets At One Go
With VBA, you can easily unhide worksheets in bulk.
For example, if you have 10 hidden worksheets, you can create a simple VBA code to unhide all the worksheets or you can unhide based on a condition (such as unhide only those where there is a specific prefix or year in the name).
Note: The methods covered in this tutorial doesn’t require you to save an Excel workbook in a macro-enabled format (.XLSM) to use the VBA code.
Using Immediate Window
VB Editor in Excel has an immediate window where you can type a line of code and instantly execute it right away.
Below are the steps to use this above line of code to unhide sheets through immediate window:
In the Immediate window, copy and paste the following line of code: For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next Sheet
Place the cursor at the end of the line
Hit the Enter key
That’s it!
The above steps would instantly unhide all the sheets in the workbook.
Once done, you can close the VB Editor.
The best part about this is that you can do this on any workbook. You don’t need to worry about saving the workbook in a macro-enabled format. Just execute a line of code and instantly unhide all the sheets in the workbook.
Let me also quickly explain the below VBA code that we have used in the immediate window to unhide sheets:
For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next SheetThe above code uses a For Next VBA loop to go through all the sheets in the workbook and set the visible property to TRUE. Once the visible property of all the sheets is changed, the code will end.
The colon (:) used in the code above is equivalent to a line break. While it looks like a single line of code, it has three parts to it which are separated by two colons.
If you’re interested in learning more about the immediate window and some awesome things you can do with it, here is a detailed tutorial about it.
In case you have to unhide worksheets quite often, another good way could be to have the macro code to unhide sheets in the Personal macro workbook and save the icon in the Quick Access Toolbar.
This is by far the most efficient way to unhide sheets in Excel (most useful when you get a lot of workbooks with hidden sheets and you have to unhide these).
The trick here is to save the code to unhide sheets in the Personal Macro Workbook.
Below is the code that you need to add to the Personal Macro Workbook:
Sub UnhideAllSheets() For Each Sheet In Sheets Sheet.Visible = True Next Sheet End SubBelow are the steps to add this code to the Personal Macro Workbook:
In the Record Macro dialog box, change the Store macro in setting to – Personal Macro Workbook.
Remove any existing code and copy and paste the above code.
Close the Vb Editor
The above steps allow you to make the Personal Macro Workbook visible in the VB Editor and place the code to unhide sheets in it.
Now all you need to do is add this code to the Quick Access Toolbar so that you can use it anytime from any workbook.
Below are the steps to add this code to the Quick Access Toolbar:
Select the macro code to unhide sheets
The above steps would add this macro code to unhide sheets in the Quick Access Toolbar.
Unhide Sheets With Specific Text in the Name
With VBA, you can also unhide sheets based on the name.
For example, suppose you have a workbook that contains sheets with years in the name and you want to unhide all the ones where the year is 2020.
You can use the below code to unhide all the sheets with the text 2020 in it:
Sub UnhideSheetsWithSpecificText() For Each ws In ThisWorkbook.Worksheets ws.Visible = xlSheetVisible End If Next ws End SubThe above uses the For Next loop to go through each worksheet in the workbook. The IF Then condition then checks the name of the worksheet and if it contains the specified text (which is 2020 in this code), it will change the visible property to make it visible.
And if the name doesn’t contain the specified text, it will leave it as is.
You can also modify this code to hide sheets based on the text in the name.
For example, if you want to quickly hide all the worksheets where the name contains the text ‘2020’ in it, you can use the below code:
Sub HideSheetsWithSpecificText() For Each ws In ThisWorkbook.Worksheets ws.Visible = xlHidden End If Next ws End SubNote: You can save this code in a regular module inside VB Editor or you can save this in the Personal Macro Workbook. In case you save it in a regular module and need to use it again later, you need to save the workbook as a macro-enabled workbook (.XLSM format).
Unhide Selected Sheets (Based on User Selection)
You can also use VBA to give the user the flexibility to choose whether to unhide a sheet or not.
This can be done by showing a message box that asks the user to select whether to unhide a sheet or not. If selected, it unhides that sheet, else it moves to the next one.
Below is the code that will do this:
Sub UnhideSheetsUserSelection() For Each sh In ThisWorkbook.Sheets Result = MsgBox("Do You Want to Unhide " & sh.Name, vbYesNo) If Result = vbYes Then sh.Visible = True End If Next sh End SubThe above code goes through each sheet in the workbook and checks whether it’s already visible or not. If it’s hidden, then it shows the message box with the name of the worksheet.
As a user, you can now decide whether you want to keep this sheet hidden or unhide it.
This can work well if you have some worksheets that are hidden and you want to take a call for every sheet individually.
Note: You can save this code in a regular module inside VB Editor or you can save this in the Personal Macro Workbook. In case you save it in a regular module and need to use it again later, you need to save the workbook as a macro-enabled workbook (.XLSM format).
Here is a tutorial where I show how to save the code in the regular module in Excel (search for the ‘Where to put this code’ section in this article)
Unhide All or Selected Sheets Using Custom View
This is a less known method in case you want to quickly unhide all the worksheets (or some selected worksheets).
For example, suppose you have an Excel workbook with 10 worksheets. You can create a view where all these 10 sheets are visible. In the future, if you have some sheets hidden and you want o go back to the view where all the sheets were visible, you can do that by selecting the already saved custom view.
Don’t worry, you don’t lose any changes you made after creating the custom view. All custom view does is takes you back to the Excel view when you created it. So if some worksheets were visible when you created the view and are now hidden, selecting that custom view would unhide these sheets.
The intended use of Custom View is to allow users to create different views. For example, if you’re an analyst, you can create different views for different departments in your organization. So you can have a specific set of worksheets (or cells/rows/columns) visible for one department and another set for another department. Once you have these views, instead of changing this manually, you simply activate the view for a department and it will show you worksheets (or rows/columns) relevant for them only.
Below are the steps to create a custom view in Excel:
Unhide all the worksheets to begin with
Enter any name for this view where all the sheets (or selected sheets) are visible
Once the view is created, you can anytime ask Excel to activate this view (which would make all those sheets visible that were visible when you created the view).
Below are the steps to show/activate a custom view:
In the Custom Views dialog box, select the view that you want to show
This would instantly unhide sheets and show those that were visible when you created that custom view.
Unhiding Sheets that are ‘Very Hidden’
Sometimes, despite having some hidden sheets in your workbook, you would not be able to unhide it manually.
This could be because these sheets are not just hidden – these are ‘very hidden’.
You can still unhide these ‘very hidden’ sheets by using the VBA code that we have covered above.
Just copy-paste the below code in the immediate window and hit enter and it would instantly unhide all the sheets (hidden as well as very hidden).
For each Sheet in Thisworkbook.Sheets: Sheet.Visible=True: Next SheetI also have a full tutorial on how to hide sheets and make these very hidden (in case you’re interested in learning)
You may also like the following Excel tutorials:
How To Unhide Sheets In Excel: Show Multiple Or All Sheets At Once
How to unhide sheets in Excel
If you want to see just one or two hidden sheets, here’s how you can quickly unhide them:
Note. Excel’s Unhide option only allows you to select one sheet at a time. To unhide multiple sheets, you will have to repeat the above steps for each worksheet individually or you can unhide all sheets in one go by using the below macros.
How to unhide sheets in Excel with VBA
In situations when you have multiple hidden worksheets, unhiding them one-by-one might be very time consuming, especially if you’d like to unhide all the sheets in your workbook. Fortunately, you can automate the process with one of the following macros.
How to unhide all sheets in Excel
This small macro makes all hidden sheets in an active workbook visible at once, without disturbing you with any notifications.
Sub Unhide_All_Sheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible Next wks End SubShow all hidden sheets and display their count
Like the above one, this macro also displays all hidden sheets in a workbook. The difference is that upon completion, it shows a dialog box informing the user how many sheets have been unhidden:
Sub Unhide_All_Sheets_Count() Dim wks As Worksheet Dim count As Integer count = 0 For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible count = count + 1 End If Next wks MsgBox count & " worksheets have been unhidden.", vbOKOnly, "Unhiding worksheets" Else MsgBox "No hidden worksheets have been found.", vbOKOnly, "Unhiding worksheets" End If End SubUnhide multiple sheets that you select
If you’d rather not unhide all worksheets at once, but only those that the user explicitly agrees to make visible, then have the macro ask about each hidden sheet individually, like this:
Sub Unhide_Selected_Sheets() Dim wks As Worksheet Dim MsgResult As VbMsgBoxResult For Each wks In ActiveWorkbook.Worksheets If wks.Visible = xlSheetHidden Then MsgResult = MsgBox("Unhide sheet " & chúng tôi & "?", vbYesNo, "Unhiding worksheets") If MsgResult = vbYes Then wks.Visible = xlSheetVisible End If Next End SubUnhide worksheets with a specific word in the sheet name
In situations when you only want to unhide sheets containing certain text in the their names, add an IF statement to the macro that will check the name of each hidden worksheet and unhide only those sheets that contain the text you specify.
In this example, we unhide sheets with the word ” report” in the name. The macro will display sheets such as Report, Report 1, July report, and the like.
To unhide worksheets whose names contain some other word, replace ” report” in the following code with your own text.
Sub Unhide_Sheets_Contain() Dim wks As Worksheet Dim count As Integer count = 0 For Each wks In ActiveWorkbook.Worksheets wks.Visible = xlSheetVisible count = count + 1 End If Next wks MsgBox count & " worksheets have been unhidden.", vbOKOnly, "Unhiding worksheets" Else MsgBox "No hidden worksheets with the specified name have been found.", vbOKOnly, "Unhiding worksheets" End If End SubHow to use the macros to unhide sheets in Excel
To use the macros in your worksheet, you can either copy/paste the code in the Visual Basic Editor or download the workbook with the macros and run them from there.
How to insert the macro in your workbook
You can add any of the above macros to your workbook in this way:
Open the workbook with hidden sheets.
Press Alt + F11 to open the Visual Basic Editor.
Paste the code in the Code window.
Press F5 to run the macro.
For the detailed step-by-step instructions, please see How to insert and run VBA code in Excel.
Download the workbook with the macros
Alternatively, you can download our sample workbook to unhide sheets in Excel that contains all of the macros discussed in this tutorial:
Unhide_All_Sheets – unhide all worksheets in an active workbook momentarily and silently.
Unhide_All_Sheets_Count – show all hidden sheets along with their count.
Unhide_Selected_Sheets – display hidden sheets you choose to unhide.
Unhide_Sheets_Contain – unhide worksheets whose names contain a specific word or text.
To run the macros in your Excel, you do the following:
Open the downloaded workbook and enable the macros if prompted.
Open your own workbook in which you want to see hidden sheets.
For example, to unhide all sheets in your Excel file and display the hidden sheets count, you run this macro:
How to show hidden sheets in Excel by creating a custom view
So, what we are going to do now is create the Show All Sheets custom view. Here’s how:
That’s it! All hidden sheets will be shown immediately.
Note. This method does not show very hidden sheets. The only way to view such sheets is to unhide them with VBA.
Cannot unhide sheets in Excel – problems and solutions
If you are unable to unhide certain sheets in your Excel, the following troubleshooting tips may shed some light why.
1. The workbook is protected
2. Worksheets are very hidden
If your worksheets are hidden by VBA code that makes them very hidden (assigns the xlSheetVeryHidden property), such worksheets cannot be displayed by using the Unhide command. To unhide very hidden sheets, you need to change the property from xlSheetVeryHidden to xlSheetVisible from within the Visual Basic Editor or run this VBA code.
3. There are no hidden sheets in the workbook
This is how you unhide sheets in Excel. If you are curious to know how to hide or unhide other objects such as rows, columns or formulas, you will find full details in the below articles. I thank you for reading and hope to see you on our blog next week!
Macros to unhide worksheets in Excel
You may also be interested in
How To Delete One Or All Pivot Tables In Excel?
How to delete one or all pivot tables in Excel?
Pivot table is a very useful function for viewing or calculating data in Excel, so we may usually insert Pivot tables into a worksheet or multiple worksheets. But do you know how to delete one or all pivot tables in a worksheet or the whole workbook?
Delete one pivot table in a worksheet Delete all pivot tables in the whole workbook with VBA
Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
Delete one pivot table in a worksheet
In Excel, you can delete one pivot table as follow:
3. And press Delete button on the keyboard, the pivot table will be removed.
Delete all pivot tables in the whole workbook with VBA
If you have multiple pivot tables in a workbook, delete them one by one is too time-consuming. Now you can use a VBA to remove them at once.
1. Press Alt + F11 to display the Microsoft Visual Basic for Applications window.
VBA: Remove all pivot tables in the whole workbook.
Sub DeleteAllPivotTablesInWorkbook() 'Updateby20140618 Dim xWs As Worksheet Dim xPT As PivotTable For Each xWs In Application.ActiveWorkbook.Worksheets For Each xPT In xWs.PivotTables xWs.Range(xPT.TableRange2.Address).Delete Shift:=xlUp Next Next End SubRelative Articles:
Reuse: Quickly insert complex formulas, charts and anything that you have used before; Encrypt Cells with password; Create Mailing List and send emails…
More than 300 powerful features. Supports Office/Excel 2007-2019 and 365. Supports all languages. Easy deploying in your enterprise or organization. Full features 30-day free trial. 60-day money back guarantee.
Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
Open and create multiple documents in new tabs of the same window, rather than in new windows.
How To Unhide Rows In Excel
Download Article
Download Article
Unhiding a Specific Row
Find the hidden row. Look at the row numbers on the left side of the document as you scroll down; if you see a skip in numbers (e.g., row 23 is directly above row 25), the row in between the numbers is hidden (in 23 and 25 example, row 24 would be hidden). You should also see a double line between the two row numbers.
It’s in the drop-down menu. Doing so will prompt the hidden row to appear.
Unhide a range of rows. If you notice that several rows are missing, you can unhide all of the rows by doing the following:
This tab is just below the green ribbon at the top of the Excel window.
If you’re already on the Home tab, skip this step.
This option is in the “Cells” section of the toolbar near the top-right of the Excel window. A drop-down menu will appear.
You’ll find this option in the Format drop-down menu. Selecting it prompts a pop-out menu to appear.
Adjusting Row Height
This tab is just below the green ribbon at the top of the Excel window.
If you’re already on the Home tab, skip this step.
This option is in the “Cells” section of the toolbar near the top-right of the Excel window. A drop-down menu will appear.
It’s in the drop-down menu. This will open a pop-up window with a blank text field in it.
Enter the default row height. Type 14.4 into the pop-up window’s text field.
Community Q&A
Add New Question
The top 7 rows of my Excel worksheet have disappeared. I’ve tried to “unhide” from the Format menu, but nothing happens. What do I do?
You’ll have to unlock the cells (via the format pop-up), then hide them all before unhiding them.
There is a possibility you did not hide the rows but reduced your rows’ height to minimum. Select all rows above and below of your 7 rows and increase rows height from format menu. It will re-adjust the height of rows and your rows will be visible.
This article was written by Jack Lloyd. Jack Lloyd is a Technology Writer and Editor for wikiHow. He has over two years of experience writing and editing technology-related articles. He is technology enthusiast and an English teacher. This article has been viewed 309,392 times.
How helpful is this?
Updated: November 17, 2020
Thanks to all authors for creating a page that has been read 309,392 times.
Robert Theriault
“I had some rows hidden and couldn’t figure out how to unhide the rows. The article solved that problem for me. Thanks” …” more
Bạn đang đọc nội dung bài viết How To Unhide Sheets In Excel (All In One Go) trên website Beiqthatgioi.com. Hy vọng một phần nào đó những thông tin mà chúng tôi đã cung cấp là rất hữu ích với bạn. Nếu nội dung bài viết hay, ý nghĩa bạn hãy chia sẻ với bạn bè của mình và luôn theo dõi, ủng hộ chúng tôi để cập nhật những thông tin mới nhất. Chúc bạn một ngày tốt lành!