Hello and welcome back to the Excel Tips and Tricks! This week, we are revisiting a Creator post in which we are looking at how hiding and unhiding worksheets works.
Hiding a worksheet
If a worksheet will not be needed on a regular basis - say for example it is a sheet with the text for dropdown menus elsewhere in the workbook, or a set of basic model data that is unlikely to change - you can hide it. Hiding a worksheet will mean that users will not see the sheet, but that the values in it are still stored in the file. This simplifies navigation in books with many worksheets.
You can hide a worksheet from the View tab, or by right clicking on the sheet tab at the bottom of the screen:
The same menus also allow you to unhide a hidden worksheet if you need to review or update it.
(Un)hiding multiple sheets
Excel will allow you to hide and unhide multiple worksheets at once. To select multiple sheets at a time, hold down the Ctrl key while selecting individual sheets. If you need to select several consecutive sheets, select the first sheet, then hold down the Shift key when clicking on the last sheet in your range to select all the sheets in between.
'Very hidden' worksheets
It is possible to set a worksheet to not only be hidden, but not to appear on the list of hidden sheets. This status, called 'very hidden', is available only from the VBA Editor (Alt + F11):
These worksheets can be made visible again only by using the VBA editor or a VBA script. Whether or not hiding worksheets in this way is good practice is an ongoing debate!
Reviewing spreadsheets with hidden sheets
It is important to check for hidden and ‘very hidden’ worksheets when performing a review of a spreadsheet. There may also be instances where a formula may reference to hidden and ‘very hidden’ sheets which may mean additional logic is obscured from view. This may be deliberate to aid usability, but it is important to consider that it may also be deliberate to make the data manipulation less transparent!
If you are looking for more information on how to review spreadsheets for hidden sheets, you may refer to this ICAEW thought leadership piece on ‘How to review a spreadsheet’. You will also find more information on how to use the INFO function to help check for hidden sheets.
Archive and Knowledge Base
This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.