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.
Step-by-Step Video Tutorial – Hide and Unhide Worksheets
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)
If [CELL NAME] = “SOME VALUE” Then
Sheets(“TAB NAME“).Visible = False
Sheets(“TAB NAME“).Visible = True
- Excel file used in tutorial on hiding and unhiding tabs using drop-down menus in Excel.
- To see VBA code, right-click on Summary Tab and click ‘View Code’
About the Author: Born and raised in the Northwest United States, Spencer Burton has nearly 20 years of residential and commercial real estate experience. Over his career, Spencer has helped close $4.5 billion and underwrite $30 billion of commercial real estate at some of the largest institutional real estate firms in the world. He is currently the Head of Real Estate Investments and member of the founding team at Stablewood Properties.