Hidden and Very Hidden Sheets in Excel - <a href="https://youtu.be/pHx8ZyKH9zY">Skip to Video</a>
As most of us know, Excel, being such an amazing tool, gives us the ability to Hide sheets from view. However, what many are not aware of is that Excel also offers the ability to set sheets as very hidden.
Hidden sheets are common in large workbooks. They help to declutter a workbook. And, they also offer some protection. By hiding a sheet with important formulas, a user would have to go out of their way to unhide the sheet to edit or delete the formula. But that is by no means foolproof, as most users know how to unhide a sheet if they really wanted too. Adding password protection also has its limitations as it can lock other functionalities, we might not want to be locked. In cases like this, the use of very hidden sheets comes invaluable.
However, very hidden sheets also pose a threat as they can these sheets can be used to hide data or formulas whose purpose is of fraudulent nature.
<h3>How to hide and unhide sheets in Excel</h3>
To hide a worksheet in Excel, Select the worksheet and right-click. Then select Hide.
<img src="https://theexcelclub.com/wp-content/uploads/2020/09/1_hidden-and-very-hidden-sheets-in-excel.png" alt="hidden and very hidden sheets in excel" width="272" height="288"><br/>
You can see now that Sheet 1 has been hidden as the tab is no longer available.
<img src="https://theexcelclub.com/wp-content/uploads/2020/09/2_hidden-and-very-hidden-sheets-in-excel.png" alt="hidden and very hidden sheets in excel" width="371" height="124"><br/>
To unhide a sheet, select a tab, right-click, and select unhide. Then select the hidden sheet you want to unhide.
<img src="https://theexcelclub.com/wp-content/uploads/2020/09/3__hidden-and-very-hidden-sheets-in-excel.png" alt="hidden and very hidden sheets in excel" width="330" height="211"><br/>
<h3> Very Hidden Sheets</h3>
The difference between hidden and very hidden sheets in an Excel workbook is the ability to find and unhide them. Very hidden sheets are not exposed when you select Unhide. You do not even have the option from the tab to make a sheet very hidden in the first place. So how do you hide and unhide very hidden sheets in Excel?
Burying sheets as deep as very hidden is done via the Developer ribbon. If the developer tab is not available, you will need to first switch it on. Under file, select Options, then select Customize ribbon. Then under Main Tabs, check the box for Developer.
<img src="https://theexcelclub.com/wp-content/uploads/2020/09/4_hidden-and-very-hidden-sheets-in-excel.png" alt="hidden and very hidden sheets in excel" width="435" height="370"><br/>
Once the developer ribbon is available, select Visual Basics.
<img src="https://theexcelclub.com/wp-content/uploads/2020/09/5__hidden-and-very-hidden-sheets-in-excel.png" alt="hidden and very hidden sheets in excel" width="602" height="122"><br/>
This will open the VBA editor. On the right of the screen, you will have the Project Explorer and the Properties.
<img src="https://theexcelclub.com/wp-content/uploads/2020/09/6_hidden-and-very-hidden-sheets-in-excel.png" alt="hidden and very hidden sheets in excel" width="390" height="686"><br/>
If these are not showing, select View and then select Project Explorer and Properties Window.
<img src="https://theexcelclub.com/wp-content/uploads/2020/09/7_hidden-and-very-hidden-sheets-in-excel.png" alt="hidden and very hidden sheets in excel" width="272" height="282"><br/>
The Project Explorer shows all the worksheets in the workbook regardless of their visibility state. By selecting any of the sheets we can see the properties of the selected sheet in the properties pane.
At the bottom of the Properties panel, we have a Visible option. Using the dropdown users can select between -1 – xlSheetVisible, 0 – xlSheetHidden, and 2- xlSheetVeryHidden.
<img src="https://theexcelclub.com/wp-content/uploads/2020/09/8__hidden-and-very-hidden-sheets-in-excel.png" alt="hidden and very hidden sheets in excel" width="534" height="335"><br/>
If we select 2- xlSheetVeryHidden, the worksheet will be removed from view and will not be available to unhide from the tabs.
<img src="https://theexcelclub.com/wp-content/uploads/2020/09/9_hidden-and-very-hidden-sheets-in-excel.png" alt="hidden and very hidden sheets in excel" width="206" height="251"><br/>
<h3>How do you know if your workbook has very hidden sheets?</h3>
It can be hard to identify very hidden sheets in a workbook. You can unhide very hidden sheets using the visual basics editor and changing the visual properties of the sheet as we did to make the sheet very hidden in the first place. But finding the very hidden sheets in the first place can be hard. Maybe the workbook has some, maybe it does not.
To locate unhidden sheets, you could go through all the sheets in the visual basic editor and check their properties, but that would be a long-time consuming exercise. Especially if it is a particularly large workbook with a lot of sheets. And then sure there might not even be very hidden sheets.
Most Spreadsheet auditing software will include a check for very hidden sheets. Excel's own add-in, <a href="https://theexcelclub.com/document-entire-excel-workbook-with-the-click-of-a-button/">Spreadsheet inquire</a> will allow you to carry out a workbook analysis that will identify the number and name of very hidden sheets.
Once you have identified a workbook contains very hidden sheets, you can unhide all these sheets together with a bit of VBA.
<h3>Unhide Very hidden worksheets with VBA</h3>
VBA is not my strong point, so a quick search of the web and I was able to find a code to unhide all very hidden sheets in a workbook. This code is courtesy of<a href="https://www.ablebits.com/office-addins-blog/2017/12/20/very-hidden-sheets-excel/"> ablebits.com</a> and it will unhide all hidden worksheets with the workbook all in one go.
<pre>Sub UnhideVeryHiddenSheets ()</pre>
Dim wks As Worksheet
For Each wks In Worksheets
If wks.Visible = xlSheetVeryHidden Then wks.Visible = xlSheetVisible
Next
End Sub
<h3><center>Take A FREE course with us Today!</center></h3>
<a href="https://courses.theexcelclub.com/courses/practical-beginner-excel-365"><img src="http://theexcelclub.com/wp-content/uploads/2019/01/beginner-excel-259x300.png" alt="FREE beginner excel training" width="259" height="300"><br/></a>
<center>The Ultimate Excel Formulas Course</center></h3>
* includes XLOOKUP and will soon include Dynamic Arrays
<a href="https://courses.theexcelclub.com/courses/the-ultimate-excel-formulas-course-excel-365"><img src="http://theexcelclub.com/wp-content/uploads/2019/09/excel-365-formulas-course-254x300.png" alt="" width="254" height="300"><br/></a>
<center>Become a Power Pivot Hero</center></h3>
<a href="https://courses.theexcelclub.com/courses/power-pivot-excel-365-from-zero-to-hero" data-elementor-open-lightbox=""><img src="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?fit=441%2C513&ssl=1" sizes="(max-width: 441px) 100vw, 441px" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?w=441&ssl=1 441w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=258%2C300&ssl=1 258w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=410%2C477&ssl=1 410w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=100%2C116&ssl=1 100w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/01/power-pivot.png?resize=275%2C320&ssl=1 275w" alt="Power Pivot online training course" width="441" height="513" data-lazy-loaded="1"><br/></a>
<center>GET and TRANSFORM DATA like a PRO</center></h3>
<strong>Power Query Excel 365</strong>
<a href="https://courses.theexcelclub.com/courses/power-query-excel-365-get-and-transform-data" data-elementor-open-lightbox=""><img src="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?fit=432%2C455&ssl=1" sizes="(max-width: 432px) 100vw, 432px" srcset="https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?w=432&ssl=1 432w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=285%2C300&ssl=1 285w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=410%2C432&ssl=1 410w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=100%2C105&ssl=1 100w, https://i1.wp.com/theexcelclub.com/wp-content/uploads/2019/10/power-query.png?resize=275%2C290&ssl=1 275w" alt="" width="432" height="455" data-lazy-loaded="1"><br/></a>
<center>Learn DAX for Power Pivot and Power BI</center></h3>
<a href="https://courses.theexcelclub.com/courses/master-dax-fundamentals-power-bi-amp-power-pivot" data-elementor-open-lightbox=""><img src="https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?fit=434%2C460&ssl=1" sizes="(max-width: 434px) 100vw, 434px" srcset="https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?w=434&ssl=1 434w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=283%2C300&ssl=1 283w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=410%2C435&ssl=1 410w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=100%2C106&ssl=1 100w, https://i0.wp.com/theexcelclub.com/wp-content/uploads/2017/05/1.0.png?resize=275%2C291&ssl=1 275w" alt="DAX for powerpivot course" width="434" height="460" data-lazy-loaded="1"><br/></a>
<h4><center> </center></h4>
<h3>Over to you</h3>
Do you use or have you encountered very hidden worksheets? If so, what were the nature and use of these sheets, and what method did you use to hide and unhide them. Please do share your experience in the comments below
Don’t have a Hive wallet or a Steempress Account?
<h4>I would suggest that you sign up directly for your own hive wallet and sign into steempress using your wallet. this way all rewards will be paid directly to your wallet within 7 days. You can use this link to sign up now for your Hive wallet</h4>
<h4>>> <a href="https://hiveonboard.com/create-account?ref=paulag" target="_blank" rel="noreferrer noopener">GET HIVE WALLET NOW</a><<</h4>
If you sign up using the comments section below you will get a Steempress account. Steempress will hold any rewards you earn until you have a hive wallet.
Have questions? Please use the Hive powered comments section below and we will do our best to help you. Alternatively, you can <a href="https://theexcelclub.newzenler.com/support/">contact us with this link</a>.
Like what you see? I do hope that you will share this article across your social profiles
<center>Community Invitation</center></h3>
<center>- <a href="https://peakd.com/c/hive-102332/about">Excel For All</a> -</center></h3>
<center>Decentralized and tokenized</center></h3>
<center><a href="https://peakd.com/c/hive-102332/about">Join today</a></center></h3>
<center><strong>Sign up for my newsletter – Don’t worry, I wont spam. Just useful Excel and Power BI tips and tricks to your inbox</strong></center></h4>
<a href="https://theexcelclub.newzenler.com/f/email-signup" role="button">
Sign Up Now
</a> <br /><center><hr/><em>Cross posted from my blog with <a href='https://wordpress.org/plugins/steempress/'>SteemPress</a> : https://theexcelclub.com/excel-hidden-and-very-hidden-sheets-whats-the-difference/ </em><hr/></center>
hiveblocks