How to find hidden sheets in Excel, very hidden included
Unhide sheets in four clicks, find the very hidden ones the dialog never lists, and check what a hidden sheet does to the model.
- Auditing
- Model review
On this page
To find hidden sheets in Excel, right-click any sheet tab and choose Unhide. That dialog lists the sheets that someone hid from the menu. It does not list very hidden sheets. Those are set from code, and you only see them if you know where to look. In a financial model, both kinds can still feed the numbers on the visible sheets.
The steps below suit two readers. One has lost a sheet and wants it back. The other has received a model and needs to know everything it contains before they sign off on it.
How to unhide sheets in Excel
Microsoft’s page on hiding and unhiding worksheets gives two routes to the same dialog:
- Right-click any visible sheet tab and select Unhide.
- Or go to Home → Format, and under Visibility select Hide & Unhide → Unhide Sheet.
In the dialog, select the sheets to show and select OK.
In Microsoft 365 you can select several sheets at once. Hold Ctrl (Command on a Mac) to pick separate sheets. Hold Shift and use the arrow keys for a block. Excel for the web has the same right-click Unhide command.
If the sheet you want is in the list, you are done. If it is not, check the four cases below.
Four reasons a sheet still does not show
The sheet is very hidden. Microsoft’s page states it plainly. Sheets set to xlSheetVeryHidden by VBA code do not appear in the Unhide dialog. The next section shows how to find them.
Unhide is greyed out. Either no sheet is hidden from the menu, or the workbook structure is protected. Very hidden sheets do not make the command available. Microsoft’s page on protecting a workbook lists what that protection blocks. The Insert, Delete, Rename, Move, Copy, Hide and Unhide sheet options all become unavailable. To remove the protection, go to Review → Protect Workbook and enter the password if Excel asks for one.
No tabs show at all. The tab bar itself is off, or the window hides it. Microsoft’s page on missing worksheet tabs gives several fixes. The first three are these. Turn on File → Options → Advanced → Show sheet tabs. Maximise the window. Or drag the edge of the horizontal scroll bar to the right.
The whole workbook window is hidden. This is a different setting from a hidden sheet. Go to View → Unhide and pick the workbook. If that command is greyed out, no workbook window is hidden.
Hidden and very hidden: three states
Every sheet has a Visible property with one of three values. Microsoft’s XlSheetVisibility reference defines them:
| Value | Name | What it means |
|---|---|---|
| -1 | xlSheetVisible | The sheet shows normally |
| 0 | xlSheetHidden | Hidden, and the user can show it again from the menu |
| 2 | xlSheetVeryHidden | Hidden, and the user cannot show it from the menu |
The menu can set the first two states only. A value of 2 means someone set it in the VBA editor or in a macro. There is often a good reason, such as a lookup table or a settings sheet that users must not edit. It is still a choice the reviewer needs to know about.
How to find hidden sheets in Excel that Unhide misses
The VBA editor shows every sheet, whatever its state. It needs desktop Excel, and it works in an .xlsx file with no macros. A VBA project locked with a password does not expand without that password.
- Press
Alt+F11to open the VBA editor. - In the Project Explorer (
Ctrl+Rif it is closed), expand the workbook and then Microsoft Excel Objects. Each sheet is listed there. - Select a sheet and open the Properties window with
F4. - Read the Visible property.
2 - xlSheetVeryHiddenmarks a very hidden sheet. - To show it, change the value to
-1 - xlSheetVisible.
On a model with many sheets, clicking each one is slow. A one-line loop prints them all.
List every sheet and its state in one pass
In the VBA editor, press Ctrl + G to open the Immediate window. Type this line and press Enter:
For Each sh In ActiveWorkbook.Sheets: Debug.Print sh.Visible, sh.Name: Next
ActiveWorkbook is the file that is active in Excel. Activate the file you review first. Excel prints one line per sheet, with the value from the table above and the sheet name. Every line that starts with 0 or 2 is a hidden sheet. Nothing in the workbook changes, so you can run it on the live file. Copy the output into your review notes.
To show every sheet at once, adapt a loop from Microsoft. Its Visible property reference has one. The loop sets each sheet’s Visible property to True. That also shows very hidden sheets:
For Each sh In Sheets: sh.Visible = True: Next
This one changes the file. It also stops with an error if the workbook structure is protected. Run it on a copy. Record the original state of each sheet first, so that you can restore it.
Developers can read the same information without opening Excel. Microsoft’s Open XML guide on listing hidden worksheets shows how. In the Open XML SDK, a hidden sheet has a State of Hidden. A very hidden sheet has VeryHidden.
Why hidden sheets matter in a model review
A hidden sheet is not a dead sheet. Microsoft’s hide and unhide page notes it. Data on a hidden worksheet can still be referenced from other worksheets and workbooks. A visible summary can depend on a hidden calculation that nobody has looked at in years.
For each hidden sheet you find, answer three questions:
- What is on it? Unhide it on a copy and read it. Look for inputs, overrides and old versions of a calculation.
- What refers to it? Select its key cells and trace the dependents. The trace dependents guide covers arrows that point to other sheets.
- Why is it hidden? Ask the builder. A lookup table is a fair reason. A second set of assumptions is a finding.
Do not delete a hidden sheet to tidy the file before you know the answers. The Document Inspector (File → Info → Check for Issues → Inspect Document) can remove hidden worksheets. Microsoft’s page on inspecting workbooks warns that this can change the results of calculations. It recommends running the inspector on a copy. The same inspector finds hidden names but does not remove them. The Name Manager does not show hidden names, so ask the builder what they hold.
Record every hidden and very hidden sheet in the issue log, with its reason. Our article on reviewing a model you didn’t build puts this step in the mapping stage, before any formula. The Excel model review checklist has a line for it in Stage 1, the initial review. For the checks that follow, the financial model audit guide goes step by step.
If you build the model: hide with care
Hidden sheets are a normal part of a model. The problems start when nobody else knows they are there. Four habits keep them honest:
- Keep a contents sheet. List every sheet with its purpose and its state: visible, hidden or very hidden. A reviewer then checks your list instead of hunting.
- Use plain Hide for support sheets. Lookup tables, mapping tables and workings then stay one click away.
- Keep very hidden for sheets users must not reach, and say so on the contents sheet. A very hidden sheet that nobody mentions looks like an attempt to hide something, even when it is not.
- Never hide a problem. An override typed on a hidden sheet still flows into the results. Our article on finding hardcoded numbers shows how to catch them.
If users must not show the sheets at all, protect the workbook structure. As the protection page above notes, that makes Hide and Unhide unavailable.
Find every hidden sheet in one step
The manual method takes a dialog, the VBA editor and a loop, and you repeat it for each workbook.
In Formula Audit XL, Quick Navigation (Ctrl + Shift + N) lists every sheet in one panel. A visibility indicator marks the hidden and very hidden ones, and you can show a sheet from the same panel. Model Check (Ctrl + Shift + R) counts the hidden and very hidden sheets on its scorecard and lists their names. The same scorecard counts errors, circular references and broken links.
To check the next model you receive, download Formula Audit XL and start the 7-day free trial.
Frequently asked questions
How do I find hidden sheets in Excel?
Right-click any visible sheet tab and choose Unhide. The dialog lists every sheet that was hidden from the Excel menu. Select one or more sheets and click OK. Very hidden sheets do not appear in this dialog. To find them, press Alt+F11 to open the VBA editor and look at the Visible property of each sheet in the Properties window.
What is the difference between a hidden and a very hidden sheet?
A hidden sheet has the Visible value xlSheetHidden (0). Anyone can show it again from the Unhide dialog, unless the workbook structure is protected. A very hidden sheet has the value xlSheetVeryHidden (2). It does not appear in the Unhide dialog, and only code or the VBA editor can make it visible again. Other sheets can still refer to both kinds.
Why is Unhide greyed out in Excel?
There are two causes. Either the workbook has no sheet hidden from the menu, or its structure is protected. Very hidden sheets do not make Unhide available, so check the VBA editor too. Structure protection makes the Insert, Delete, Rename, Move, Copy, Hide and Unhide sheet options unavailable. Go to Review, select Protect Workbook, and enter the password if Excel asks for one.
Is it safe to delete hidden sheets in a model?
Not before you check them. Data on a hidden sheet can still feed formulas on visible sheets, so a deletion can change the results or turn formulas into #REF! errors. Microsoft gives a similar warning for the Document Inspector, which can remove hidden worksheets. Unhide each sheet, find what refers to it, and work on a copy of the file.