I am a huge fan of computers and information technology. One of my favorite programs to use is Microsoft Excel. It is so versatile.
I use it for so many different things, not all number-based. I’ve used it for tracking writing submissions, exercise, and coin collections, producing calendars, verifying my paycheck is correct, and balancing my checkbook, among other things. I’ve even written a choose-your-own-adventure story in Excel which puts the reader’s information into the story and changes the outcome as the reader makes their decisions.
There is one thing that has always bothered me about Excel, especially when you have large workbooks with many sheets. The worksheets run horizontally across the bottom rather than vertically down the side. This can make navigation and labeling problematic.
If you are a visual person, like I am, who likes to see things globally rather than in a constricted view, then it’s likely that you’ve been frustrated by this too, especially if you have long, descriptive worksheet names that take up a large portion of the visible space in the row of sheet names.
I created the Worksheet Browser Add-In to rectify that issue. It allows you to:
- See a more complete, common sense, view of your worksheets, both hidden and visible.
- Quickly switch between workbooks.
- Quickly switch between worksheets.
- Easily hide and unhide worksheets.
- And, for developers, very hide and unhide worksheets.
- The Worksheet Browser is free-floating and can be moved around the screen. Excel will remember its last position if you close and re-open it. It can also be minimized and will still free-float in minimized mode.
- In case a workbook’s structure is protected, there is also a text indicator showing if hide/unhide is available (Not available in structure-protected workbooks).
This works on Excel 2003, 2007, 2010, and 2013 file types. It works on Windows 7 and Windows 10. I don’t know if it will work on a MAC.
Update 4/27/18: This is not working with Excel 2016 yet.
There are three versions of the Worksheet Browser available. I tend to use the EX version since a lot of my workbook names are similar at the beginning and that allows me to see more of the name. However, all three can be installed and open at the same time if you wanted to switch between them.
WorksheetBrowser: This is the basic worksheet browser. Typical users probably don’t need more than this.
WorksheetBrowser VH: This version also has the capability to hide/unhide Very Hidden worksheets. You can see which sheets are Very Hidden by the addition of “v: “ to the sheet name in the lower hidden sheets window.
WorksheetBrowser EX: This is the extended version. It has been widened to show more of each workbook and sheet name. It is also taller so that you can see more sheets in the hidden and visible windows. It still has the capability to hide/unhide Very Hidden worksheets and denotes them with the “v: “ label.
The minimized view on this one is different from the other two versions. However, a perk of this view is that you can still use the Worksheet Browser to switch between workbooks without maximizing it.
I use this daily. Some of my workbooks have almost 100 worksheets. Still, this Add-In may not be for everyone and it does have some drawbacks.
- It does take up some screen space.
- It does not work properly if you have 2 or more windows open from the same workbook (View > New Window >).
- Sometimes, when you try to open the Add-In you will receive an error that I haven’t fixed yet. It only happens when you have multiple workbooks open in separate instances of Excel. Without getting technical about “separate instances”, if you encounter the error just restart Excel and your re-open your workbooks and the Worksheet Browser should work again. I’ll update the files once I get the issue fixed.
How to install an Excel Add-In
- Save the add-in to this directory: C:\Users\[YOUR USER NAME]\AppData\Roaming\Microsoft\AddIns
(This is one of the default locations that Microsoft looks for add-ins. You can try saving it in another location but I know it works from this one.)
- Then, in Excel, go to File > Options >
- Click on “Add-In”
- At the bottom change Manage drop down to “Excel Add-ins”
- Click “Browse” and locate the Add-In you selected (“WorksheetBrowser.xlam”, etc.)
- Once opened it should populate in the list to the left
- Make sure it is checked and click OK
How to access Excel Add-Ins
Once installed, you can find the Add-In on the Add-Ins tab on the Ribbon. It will be a button called “Worksheet Browser”, “WorksheetBrowser VH”, or “WorksheetBrowser EX”, depending on which Add-In you chose.
If you don’t wish to use the Worksheet Browser tool, here is another method that I found out about that can help you navigate quicker in Excel without my Add-In.
Right-click on the navigation buttons in Excel. It will pop-up a list of worksheets. If you have over 15 worksheets then the pop-up will give you the option for “More Sheets”.