Remove hidden sheets with “Inspect Document” or VBA

This post is an extension of the previous one – we talked about hiding worksheets is not a good way to prevent others from seeing the data on hidden sheets.  Since you have read that post, you decided to remove all hidden sheets.  Then you realized you had to unhide them one by one before you could delete them… Is that really the case?

Well… it depends.
Indeed there is a way to remove all hidden sheets (just hidden, not very hidden) quickly.

Go to File –> Info –> Check for Issues –> Inspect Document (don’t be surprise if you haven’t used that before, I am sure you are not alone)

Excel Tips - Remove Hidden Sheets

The “Document Inspector” is opened…

You may simply click “Inspect“, or scroll all the way down to see what are included in this Window:

Excel Tips - Remove Hidden Sheets1

After the inspection, which takes a flash of second, you will see the number of  “Hidden Worksheets” found.

And you are provided an option to “Remove All”…

Wait… Continue reading before pressing that icon.

Excel Tips - Remove Hidden Sheets2


  1. All worksheets removed CANNOT be recovered.  This action CANNOT be undo. Please save your file as a separate file before you perform this action.  
  2. If there are formula referencing to the hidden sheet(s), they will return #REF! afterwards.  You may want to convert all formula to value first. 

When you are ready to remove all hidden sheets, simply click “Remove All”

Then you will see:

Excel Tips - Remove Hidden Sheets3

However, it doesn’t work if you have “very hidden sheets”.

Excel Tips - Remove Hidden Sheets4

What to do if you have “very hidden sheets”?

VBA to rescue. The following lines of VBA code would save your time.

IMPORTANT:  DO NOT run the following codes on your original file as the action is irreversible!  Always test it on a copy!

Sub DeleteHiddenSheets()

Dim ws As Worksheet

Application.DisplayAlerts = False

For Each ws In ActiveWorkbook.Worksheets
 If Not ws.Visible Then
 ws.Visible = xlSheetVisible
 End If
 Next ws

Application.DisplayAlerts = False

End Sub

As you may know, I am not good in VBA at all.  If you have other suggestions, you are more than welcome to leave your comment.   🙂

Online Excel VBA Training


About MF

An Excel nerd who just transition into a role related to data analytics at current company......😊 Recently in love with Power Query and Power BI.😍 Keep learning new Excel and Power BI stuffs and be amazed by all the new discoveries.
This entry was posted in Excel Tips and tagged . Bookmark the permalink.

6 Responses to Remove hidden sheets with “Inspect Document” or VBA

  1. Petros says:

    If hidden sheets are removed, most likely the spreadsheet model will be broken as stated. What we need is a method of making the hidden sheets visible for inspection purposes, without running VBA.

    No need to enable and run macros in suspicious workbooks with hidden sheets. Hidden sheets can be made visible in closed workbooks even without removing Workbook protection! Use this free add-in:


  2. harsha547 says:

    Thank You


  3. FrankT says:

    In Excel 2016 the inspector does delete very hidden sheets as well.


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.