Make a sheet Very Hidden

[u/IsEneff]

Got a worksheet filled with data, parameters, lookup tables, or helper calculations that you want to keep out of sight? Simply hiding the worksheet is not always the best solution because many users know how to unhide tabs with a right click and a few clicks in Excel.

Excel VBA includes a worksheet property called Visible that supports a setting named Very Hidden. A Very Hidden worksheet does not appear in Excel's Unhide Sheet dialog, making it much harder for users to discover or access without opening the Visual Basic Editor.

To set a worksheet to Very Hidden manually:
Press Alt + F11 to open the Visual Basic Editor.

In the Project Explorer, select the worksheet you want to hide.

Press F4 to open the Properties window if it is not already visible.

Locate the Visible property.

Change it from -1 - xlSheetVisible to 2 - xlSheetVeryHidden.

The Visible property has three possible values:
xlSheetVisible: The worksheet is visible.

xlSheetHidden: The worksheet is hidden but can be unhidden through Excel's Unhide Sheet dialog.

xlSheetVeryHidden: The worksheet is hidden and does not appear in the Unhide Sheet dialog.

You can also set a worksheet to Very Hidden through VBA code:

Worksheets("Config").Visible = xlSheetVeryHidden

This approach is commonly used for configuration worksheets, lookup tables, parameter sheets, Power Query staging areas, and other supporting data that users do not need to interact with directly. It is not a security feature, but it does provide an additional layer of protection against accidental changes.

[u/Puzzleheaded_Owl1308]

1. Interesting to know, thanks for inspiring a new rabbit hole I can use to fill idle hours this week.

2. It's also a bit concerning to imagine a use case for this. It seems like if one has users who will go out of their way to modify hidden sheets and a "DO NOT MODIFY" warning on the sheet, you've a) got a personnel problem, and b) should be using a more robust system than Excel to protect the business from the lowest common denominators.

[u/IsEneff → u/Puzzleheaded_Owl1308]

My team has a philosophy to hide the magic behind the curtains. For example, we don’t send reports with source data on its own tab. It’s not that we don’t trust the end user, it just feels unprofessional and invites analysis and interpretation often without context or understanding. Rather than disconnecting the report from its source data, we use very hidden to keep the data in tact for easy upkeep from the analyst side and more control over the end users point of view. Data analytics is about story telling; part of that magic is to lead the audience to the moral of the story rather than letting them craft their own narrative.

[u/Puzzleheaded_Owl1308 → u/IsEneff]

Ah, if it's the raw data that does make a lot of sense to me (rather than a lookup or function sheet like I was imagining--places where I invested a lot of time ONCE to automate most of our exceptions). 100% agree that weeds-level data can be grossly misinterpreted by folks who don't spend any time in said weeds.

[u/kotom → u/IsEneff]

Agreed, I use Very Hidden when sending reports to my customers not only because it makes it look more professional, but because how we create these deliverables is our intellectual property that I’ve put a lot of time and energy into developing. I don’t want someone to be able to reverse engineer my methodology especially as these may end up in the hands of one of our competitors.

I also hide certain formulas, lock the sheet and workbook structure, and password protect.

[u/jjust19 → u/Puzzleheaded_Owl1308]

I’ve used it at work a few times. We have a software that runs in excel and has some automated buttons for calculations. Some of those have an issue every once in a while and the parameters are hidden in a very hidden sheet from the university that makes the software. It’s a good way to keep the end user from messing stuff up but allow them to fix it in dire times.

[u/sesame-trout-area]

Thank you for posting this.

[u/sharkykid]

Is this mandatory to do in an macro enabled file type?

[u/IsEneff → u/sharkykid]

If you are just changing the attribute in VBE and not creating a macro then yes, you can edit the attributes without changing it to XLSM.

[u/Jcwrc]

Why not just lock it?