How to Unhide Sheets in Excel - Compute Expert

How to Unhide Sheets in Excel


Home >> Excel Tutorials from Compute Expert >> Excel Tips and Trick >> How to Unhide Sheets in Excel





In this tutorial, you will learn how to unhide sheets in excel completely.

In the Excel workbook that we work on, we sometimes have hidden sheets. If we want to display or work on the data of those sheets, we need to unhide them first. There are several methods you can use to do this sheet unhiding process in excel.

Want to know what are those methods and how to implement them in excel? Read this tutorial until its last part!

Disclaimer: This post may contain affiliate links from which we earn commission from qualifying purchases/actions at no additional cost for you. Learn more







How to Unhide Sheets in Excel 1: Format Menu

There are three basic methods to unhide sheets in excel that we will discuss in this tutorial. The first method is by using the format menu in excel.

The steps to implement this method are quite simple. You can see and follow them below.

  1. Go to the Home Tab and click the Format dropdown button there

    How to Unhide Sheets in Excel - Screenshot of Step 1-1

  2. Highlight the Hide & Unhide choice from the dropdown list. Then, click Unhide Sheet…

    How to Unhide Sheets in Excel - Screenshot of Step 1-2

  3. Select the worksheet that you want to unhide and click OK

    How to Unhide Sheets in Excel - Screenshot of Step 1-3

  4. Done!

    How to Unhide Sheets in Excel - Screenshot of Step 1-4




How to Unhide Sheets in Excel 2: Sheet Right-Click Menu

You can also unhide the sheets in your excel workbook by right-clicking your sheet tab first. Follow the complete steps to do it below.

  1. Right-click on one of your worksheet tabs

    How to Unhide Sheets in Excel - Screenshot of Step 2-1

  2. Click Unhide...

    How to Unhide Sheets in Excel - Screenshot of Step 2-2

  3. Select the worksheet that you want to unhide and click OK

    How to Unhide Sheets in Excel - Screenshot of Step 2-3

  4. Done!

    How to Unhide Sheets in Excel - Screenshot of Step 2-4




How to Unhide Sheets in Excel 3: Custom Views

The last method to unhide sheets that we will discuss is by using the excel custom views feature. Custom Views is a feature you can use to save and load your excel workbook display settings.

To use this feature to unhide sheets, you should save when you still display the worksheets you want to unhide. This is so you can load that custom view when you want to unhide and display those worksheets again.



With that in mind, we will discuss the steps to implement this method in two parts. They are the part when we save the custom view and the part when we load the custom view we saved.


Saving the Custom View

  1. Make sure the tabs of the worksheets you want to unhide later are still on display

    How to Unhide Sheets in Excel - Screenshot of Step 3-1-1

  2. Go to the View Tab and click the Custom Views button there

    How to Unhide Sheets in Excel - Screenshot of Step 3-1-2

  3. Click the Add button

    How to Unhide Sheets in Excel - Screenshot of Step 3-1-3

  4. Give the name for the custom view you save in the text box. Then, click OK

    How to Unhide Sheets in Excel - Screenshot of Step 3-1-4




Loading the View

  1. Go to the View Tab and click the Custom Views button there

    How to Unhide Sheets in Excel - Screenshot of Step 3-2-1

  2. Highlight the custom view name where your hidden worksheets are still on display. Then, click Show

    How to Unhide Sheets in Excel - Screenshot of Step 3-2-2

  3. Done!

    How to Unhide Sheets in Excel - Screenshot of Step 3-2-3




How to Unhide Multiple Sheets in Excel

Need to unhide not just one sheet but multiple sheets at once?

Unfortunately, we still cannot do it if we use the Unhide dialog box we trigger using methods 1 and 2. Excel doesn’t allow us to select more than one worksheet to unhide there for some reasons.

If you want to unhide multiple sheets at once in excel, you should use the custom view method we discuss earlier (if we don’t use VBA to do it). That means you have to save the custom view where the worksheets you want to unhide are still on display. When you want to unhide them later, you can just load the view.

If you use methods 1 and 2, that means you need to unhide your worksheets one by one!



How to Unhide All Sheets in Excel at Once

Similar to the method to unhide multiple sheets we just discussed, use the custom view method for this one too.

Save the custom view where all the worksheets in your excel workbook are still on display. When you need to unhide all the sheets again, you can just load the custom view!




How to Check Whether There is a Hidden Sheet in Your Excel Workbook

To check if there are hidden sheets in your workbook or not, right-click on one of your worksheet tabs. If the Unhide… choice is grayed out in the right-click menu, that means you most probably don’t have hidden worksheets.

How to Unhide Sheets in Excel - Screenshot of the Unhide... Choice Appearance Example if There Is No Hidden Worksheet

There is still a possibility that there are very hidden worksheets even though you cannot choose the Unhide… there. However, you shouldn’t have something like those unless you use a macro-enabled workbook file (if you do, you can refer to the next part of this tutorial to try to unhide your very hidden worksheets).

If you can choose the Unhide… choice, then that means you have hidden worksheets. Just do one of the methods we have discussed earlier to unhide them if you want.



Why I Cannot Unhide My Sheets? Possible Reasons and Solutions

You cannot unhide the worksheets in your workbook? Some things can cause that to happen. Here are those which are probably the most likely reasons and their solutions.

  • Reason: You may don’t have any worksheet to unhide.
    Solution: Remember again what you did to the worksheets you want to unhide. You probably deleted them instead of hid them or you moved them instead to another place

  • Reason: You are in the workbook that doesn’t have those hidden worksheets you want to unhide.
    Solution: Check again the workbook where you are in. Can it be that the worksheets you want to unhide are in a different workbook? If so, then open the workbook where the hidden worksheets are in instead

  • Reason: You hid your worksheets using the very hidden settings.
    Solution: If this is the case, then you cannot unhide them using the normal method. Refer to the next part of this tutorial to unhide them

Hope one of those points above can help to solve your problem!



How to Unhide Very Hidden Sheets in Excel

You are in a macro-enabled excel workbook file and you think your worksheets are very hidden? It can be annoying if you want to unhide those worksheets since you cannot use the normal methods.

However, you surely can do it if you want. Just follow the steps below to display those very hidden worksheets.

  1. Press the Alt + F11 (Fn + Option + F11 in Mac) buttons on your keyboard
  2. You have opened the VBA editor of your workbook. Make sure your Project Explorer and Properties Window boxes are on display on the left side of your VBA editor. If they don’t, click View in the menu bar on the top and choose Project Explorer or Properties Window (depending on which one is missing in your VBA editor)

    How to Unhide Sheets in Excel - Screenshot of How to Unhide Very Hidden Sheets, Step 2

  3. In the Project Explorer box, highlight by clicking the sheet name you want to unhide

    How to Unhide Sheets in Excel - Screenshot of How to Unhide Very Hidden Sheets, Step 3

  4. In the Properties Window box, click the Visible dropdown button there. Choose -1 - xlSheetVisible from the dropdown list

    How to Unhide Sheets in Excel - Screenshot of How to Unhide Very Hidden Sheets, Step 4

  5. Close the VBA editor. Now, your previously very hidden worksheet should already be on display!

    How to Unhide Sheets in Excel - Screenshot of How to Unhide Very Hidden Sheets, Step 5




Exercise

After you have understood the methods to unhide sheets in excel, now let’s do an exercise to deepen your understanding!

Download the exercise file below and do the instruction.

Link to the exercise file:
Download here

Instruction:

Try to unhide the worksheet with the name A using method 1 and with the name B using method 2. Then, implement method 3 to simulate how to save and load the custom view of your worksheets!

What is the result? Can you do it?



Additional Notes

Besides saving your worksheet tabs display, custom views will also save your zoom level, window panes, and other display-related settings.



Related tutorials you should learn from:



Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!





Want to Learn More About Excel?


Get updated excel info from Compute Expert by registering your email. It's free!




Follow our tutorial content also on


Hand-Picked CE Tutorials

Excel Calculation

How to Sum in Excel

Subtraction in Excel

Multiplication in Excel

Division Excel Calculation

Average Excel Calculation



Excel Formula

VLOOKUP Excel Formula

Excel IF Function

SUM Formula in Excel

COUNTIF Formula in Excel

COUNT Function in Excel



Excel Tips and Trick

How to Print in Excel

Convert Number to Text Excel

Excel Worksheet Definition

Excel Range Definition

How to Add Columns in Excel



Excel Consultation

Contact Us

Privacy Policy

Affiliate Disclosure

Terms & Condition



© 2021 Compute Expert