, , ,

Hide and Unhide Tabs using Drop-down Menus in Excel (Updated Apr 2024)

I’d like to share a handy little trick I learned this week for hiding and unhiding tabs in Excel using drop-down menus. Now this method requires you to use some basic VBA code and to save the Workbook as a Macro-Enabled file but don’t be intimidated – no previous coding experience is required to make this work. I’ve recorded a short video tutorial that walks you through the process and included the VBA code you will need below. I’ve also added a download link to the Excel file used in this tutorial.

Why Add this to my Real Estate Model?

If you’ve spent much time working with real estate financial models, you’ve probably come across a model with way too many tabs. Perhaps it’s a portfolio model with tabs for dozens of properties, or a complex DCF with a multitude of behind-the-scenes calculation tabs. Whatever the reason, having a lot of tabs in your model can kill the user experience. This trick will allow you, the creator of the model, to determine which tabs are visible based on criteria selected by the user.

So, for instance, imagine you build a portfolio model for up to 30 properties with one tab for each property. You then add a drop-down menu on your summary tab where the user can select the number of properties in the portfolio. If the user selects 12 for example, 12 property tabs are left visible and 18 property tabs are automatically hidden from view. If the user wants to add more properties, she simply chooses more properties from the drop-down menu and the relevant tabs automatically become visible.

  • Quick Tip: If you have our (free) ‘Excel 4 CRE’ add-in installed, you can ‘unhide all worksheets’ by simply clicking ‘Ctrl+Shift+?‘.

Step-by-Step Video Tutorial – Hide and Unhide Worksheets

In this video tutorial, I walk you step-by-step through the process of creating a feature to hide and unhide worksheets from a dropdown menu.

The VBA Code

Here is a template of the VBA code used in the video. Replace the text in blue with text specific to your model.

Private Sub Worksheet_Change(ByVal Target As Range)

Sheets(“TAB NAME“).Visible = False
Sheets(“TAB NAME“).Visible = True
End If

End Sub

The Holy Grail Has Arrived – AI as Our Personal VBA Developer

Since creating this tutorial, a monumental tool has arrived that greatly simplifies projects such as this: large language models (or LLMs). As of 2024, the most used LLMs include OpenAI’s GPT-x (i.e. ChatGPT), Gemini, Claude, Llama, etc. These language models are more than capable of instantly writing virtually any VBA code you might need in real estate financial modeling.

For instance, if I ask Gemini the following: “please provide step-by-step logic, instructions, and VBA code in order to hide and unhide certain worksheets based on a given dropdown menu”, the AI returns instructions similar to what I shared above together with this VBA code:

Sub Worksheet_Visibility()
Dim selectedOption As String
Dim ws As Worksheet

‘ Get the selected option from the dropdown
selectedOption = Sheets(“ControlSheet”).Range(“A1”).Value ‘ Adjust cell reference if needed

‘ Loop through all worksheets
For Each ws In ThisWorkbook.Worksheets
Select Case selectedOption
Case “DataSheet1”
ws.Visible = IIf(ws.Name = “DataSheet1”, xlSheetVisible, xlSheetVeryHidden)
Case “DataSheet2”
ws.Visible = IIf(ws.Name = “DataSheet2”, xlSheetVisible, xlSheetVeryHidden)
Case “Show All”
ws.Visible = xlSheetVisible
Case Else
‘ Do nothing if no match
End Select
Next ws
End Sub

With this new AI tech, anyone – regardless of coding capability – is able to create virtually any custom Excel functionality they might imagine. It’s quite extraordinary! I highly recommend becoming proficient using LLMs and using them to craft VBA code for yourself.

By the way, we’re having a ton of fun at A.CRE playing around with this tech. If you haven’t already, browse the various applications for AI in commercial real estate that we’ve been exploring.


This version of the model is only compatible with Excel 2013, Excel 2016, and Excel 365.

Download the Hide and Unhide Tabs Using Drop-Down Menus In Excel Tool

To make this tool accessible to everyone, it is offered on a “Pay What You’re Able” basis with no minimum (enter $0 if you’d like) or maximum (your support helps keep the content coming – typical real estate tools sell for $25- $100+ per license). Just enter a price together with an email address to send the download link to, and then click ‘Continue’. If you have any questions about our “Pay What You’re Able” program or why we offer our models on this basis, please reach out to either Mike or Spencer.

We regularly update the tool (see version notes). Paid contributors to the tool receive a new download link via email each time the tool is updated.

Version Notes


  • Updates to Version tab
  • Cleaned up tab color formatting
  • Misc worksheet formatting updates


  • Initial release

About the Author: Born and raised in the Northwest United States, Spencer Burton has over 20 years of residential and commercial real estate experience. Over his career, he has underwritten $30+ billion of commercial real estate at some of the largest institutional real estate firms in the world. He is currently President and member of the founding team at Stablewood. Spencer holds a BS in International Affairs from Florida State University and a Masters in Real Estate Finance from Cornell University.