3 Easy Ways to Unhide All Sheets in Excel 2017

Hidden sheets in Excel can be a nuisance, especially when you're trying to analyze or manage data efficiently. Whether you've inherited a workbook with hidden sheets or you've accidentally hidden them yourself, knowing how to unhide all sheets in Excel 2017 can save you considerable time. Here, we'll explore three straightforward methods to unhide all your sheets at once, ensuring that you have full access to your entire workbook without the hassle of un-hiding each sheet one by one.

Method 1: Using VBA Script

If you’re comfortable with Visual Basic for Applications (VBA), you can use a script to unhide all sheets in your Excel workbook quickly:

1. Press Alt + F11 to open the VBA editor.
2. In the VBA editor, go to Insert > Module to create a new module.
3. Copy and paste the following VBA code into the module window:

Sub UnhideAllSheets()
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        ws.Visible = xlSheetVisible
    Next ws
End Sub

4. Close the VBA editor.
5. Run the macro by pressing Alt + F8, selecting UnhideAllSheets, and clicking Run.

⚠️ Note: This method requires basic familiarity with Excel VBA. Always backup your workbook before running macros to avoid data loss.

Method 2: Via Excel Options

For those who prefer not to delve into coding, Excel offers a less technical way to unhide sheets:

1. Right-click on any visible sheet tab.
2. Select Unhide from the dropdown menu. An Unhide dialog box will appear.
3. Since this method only allows un-hiding one sheet at a time, you’ll need to repeat this process for each hidden sheet:
   • Click on the sheet name you want to unhide and then press OK.
   • Repeat for all sheets.

💡 Note: This method is more practical for workbooks with only a few hidden sheets.

Method 3: The Ultimate Shortcut

There’s an even quicker way if you’re open to some additional tools:

• Download an add-in like Jan’s Excel add-ins or Excel-AddIn-BetterTools.
• After installation, use the specific button or menu option provided by the add-in to unhide all sheets at once.

✅ Note: Be cautious when downloading add-ins; only use trusted sources to avoid security risks.

In wrapping up, un-hiding all sheets in Excel 2017 can be done efficiently through various methods, whether you're a VBA enthusiast, prefer manual control, or opt for a quick tool-based solution. Remember, while these techniques work for Excel 2017, similar methods are applicable to newer versions of Excel. Each approach has its own merits, so choose the one that best fits your skill level, comfort with Excel, and the specific needs of your workbook management. Understanding these methods not only helps in streamlining your workflow but also in deepening your knowledge of Excel's capabilities.

Can I unhide multiple sheets at once without VBA?

+

No, with the default settings in Excel 2017, you can only unhide sheets one at a time through the “Unhide” dialog box. For multiple sheets at once, VBA or add-ins are necessary.

Is there a risk in using VBA to unhide sheets?

+

Yes, any VBA script can potentially harm your Excel workbook if not correctly written or if it interacts with your data in unintended ways. Always ensure you have a backup of your workbook before running macros.

Can these methods be used in Excel 365?

+

Absolutely. While the article focuses on Excel 2017, the methods described are also applicable to Excel 365, although the interface might look slightly different.