KutoolsforOffice — One Suite. Five Tools. Get More Done.

How to toggle hidden worksheets between visible and hidden in Excel?

AuthorXiaoyangLast modified

When a workbook contains worksheets that you need to view only occasionally, repeatedly unhiding and rehiding them one at a time can be inconvenient. This tutorial explains how to toggle specified worksheets with VBA or show and hide all hidden worksheets with one click using Kutools for Excel.

ComparisonVBA codeKutools for Excel
Best suited forToggling a fixed set of worksheetsQuickly showing and rehiding all hidden worksheets
Works with specific worksheetsYes, by entering their names in the codeToggles all hidden worksheets
Handles renamed worksheetsThe names in the code must be updatedNo worksheet names need to be entered

Toggle specified worksheets with VBA code

VBA is useful when you want to toggle a fixed list of worksheets. However, you must enter the worksheet names in the code and update the code whenever those names change.

Suppose Sheet1, Sheet2, and Sheet3 are hidden. The following VBA code displays them when it is run once and hides them again when it is run a second time.

  1. Click Developer > Visual Basic. Alternatively, press Alt + F11.
  2. In the Microsoft Visual Basic for Applications window, click Insert > Module.
  3. Paste the following code into the module: 
    Sub ToggleHideUnhideDataSheets()
    Application.ScreenUpdating = False

    Dim wb As Workbook
    Dim ShtNames As Variant
    Dim i As Long

    Set wb = ActiveWorkbook
    ShtNames = Array("Sheet1", "Sheet2", "Sheet3")

    For i = LBound(ShtNames) To UBound(ShtNames)
        wb.Worksheets(ShtNames(i)).Visible = Not wb.Worksheets(ShtNames(i)).Visible
    Next i

    Application.ScreenUpdating = True
End Sub
  4. Change Sheet1, Sheet2, and Sheet3 in line 9 to the names of the worksheets you want to toggle.
  5. Press F5 to run the code. Run it again whenever you want to switch the same worksheets back to their previous visibility state.

📝 Notes:

  • The worksheet names in the code must exactly match the names in the workbook.
  • Save the workbook as an Excel Macro-Enabled Workbook (.xlsm) if you want to keep the VBA code.
  • Excel requires at least one worksheet in a workbook to remain visible.

Toggle all hidden worksheets with Kutools for Excel

If you want to switch all hidden worksheets between visible and hidden without specifying sheet names or editing code, the Toggle Hidden Worksheets Visibility feature of Kutools for Excel provides a quicker option.

Kutools for Excel includes more than 300 useful Excel tools. Try it free with full features for 30 days. Download it now.

  1. Open the workbook containing the hidden worksheets.
  2. Click Kutools > View > Toggle Hidden Worksheets Visibility.
    Open Toggle Hidden Worksheets Visibility from the Kutools View menu

All hidden worksheets are immediately displayed. Click the same command again to hide those worksheets.

Frequently Asked Questions

Why does the VBA code return a “Subscript out of range” error?

This usually means that a worksheet name in the code does not exactly match the corresponding sheet tab. Check the spelling, spaces, and punctuation in each name.

Can I add more worksheets to the VBA code?

Yes. Add each worksheet name inside the array and separate the names with commas, such as Array("Sheet1", "Sheet2", "Sheet3", "Sheet4").

Will the Kutools command change the worksheet data?

No. The command changes only the visibility of the worksheets; it does not modify their cell contents.

Can every worksheet in a workbook be hidden?

No. Excel requires at least one worksheet to remain visible.

Related article:

Toggle between hidden and visible columns in Excel

Best Office Productivity Tools

🤖Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions…
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |  Round without Formula ...
Super Lookup: Multiple Criteria VLookup  |   Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |  Big Formula Bar   |  Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells   |  Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets:  12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more
Use Kutools in your preferred language – supports English, Spanish, German, French, Chinese, and 40+ others!

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • 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.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!

All Kutools add-ins. One installer

Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.

ExcelWordOutlookTabsPowerPoint
  • All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
  • One installer, one license — set up in minutes (MSI-ready)
  • Works better together — streamlined productivity across Office apps
  • 30-day full-featured trial — no registration, no credit card
  • Best value — save vs buying individual add-in