Workbooks with many worksheets can be difficult to navigate. One poster has suggested having multiple levels to the worksheet tabs.
As an extension to this, we could have worksheet groupings where several worksheets could be contained within a grouping. You could then select a particular grouping (which would look like worksheet tabs and be placed below the actual worksheet tabs) and then have access to all the worksheets in that particular grouping. There would also be an 'All' grouping that contained all the worksheets.
These grouping could be included in the workbook protection so that you could choose which worksheet groupings users could view
As I have many workbooks with lots of sheets, I'd really like to get a better overview of my work.
Grouping of Sheets with collapse/expand function in the tab list would be great.
(I could be happy with some sheets acting as parent for sub-sheets - dragging a sheet tab onto another sheet tab could attach it as sub-sheet.)
I'd also appreciate the possibility to have multiple rows for sheet tabs.
But the best option would be a configurable navigating pane (on the left) with the following options:
- switch between text list and preview images
- multiple outline levels with view-level selection
- filter protected/unprotected/hidden/unhidden
- enable/disable listing relevant items under each sheet, like:
- - named ranges
- - conditional formatting rules
- - objects (drawing, OLE, etc)
- - tables
- - array formulas
Gareth Hayter commented
I've created an Excel add-in which allows you to create custom 'tags', which are the same concept as 'groupings' referred to here. A sheet can be assigned one or many tags. The sheets can then be filtered by tag(s). It might prove useful until Microsoft adds the feature. FormulaDesk Navigator is still in beta, but you can try it here: https://www.formuladesk.com/formuladesk-navigator
In addition, displayed sheet tabs can be filtered by color(s). Multi-row and vertical sheet tabs are also supported.
Is anything missing?
I love this idea. Another great feature would be to allow the tabs to be repositioned from the bottom to the left side, and then be either displayed full, minimized, or hidden. There are many other Office programs that have similar functionality (think sections/pages in OneNote, or email accounts/folders in Outlook), so it should be easy for Microsoft to stay consistent with their Office design.
How much screen real estate are you willing to sacrifice for the groupings? It would be great if virtually no additional real estate was lost to worksheet groupings. Worst case, color tabs do help in identifying worksheet groups. Maybe the solution is similar to how graphic elements can be grouped: Group, Ungroup, Regroup in the right-click menu, with little real estate devoted to denoting the groupings; certainly not vertical screen real estate.
any updates? Would be useful. Now I constantly have to hide and then unhide tabs in order to make my workbook manageable
I need this right now. Sub-worksheets would be so useful.
Jason Seifried commented
Please do this. Something like the OneNote section->page->subpage would be terrific. Honestly I can't believe that excel doesn't have this yet.
This is an essential improvement. Nowadays the most Excels workbooks contain about 10-30 worksheets.
i can't think of a single more transformative improvement than this. It would be a gamechanger in terms of how people would structure their files going forward.
- Heavy user for 8 years
Grouping of Worksheet tabs and the ability to define groupings by name would be a big improvement. I have used this macro which I expand upon/ change as needed.
Sheets("SHEET2").Visible = True
Dim sh As Worksheet
For Each sh In Worksheets
If sh.Name <> "SHEET1" And sh.Name <> "SHEET2" Then sh.Visible = xlSheetHidden
William Jagger commented
Why isn't this available? I NEED it NOW!
Dean Kanitz commented
With data, analysis and outputs there are typically multiple sheets, that address different issues and outcomes. With the ability of machines in 2016 and newer capabilities and Excel able to handle larger data sets there is a need to be able to better organize the data in layers or levels (sub-layers / levels). Currently, the only option is to add tabs to the to bottom of the screen in the same row. If there was an option to create a sub-tab below the existing it would enhance the organizational performance for those of us that want to both see more than the number of acronyms we can develop and tab colors. In a nutshell it would look like a flow chart, which may open some other routes for interaction with other MS products. Also, Excel functions that create tabs could ask in the menu if the user wanted to place the worksheet as a sub-tab.
Steven Rappaport commented
I've been doing this with VBA but it's messy.
This is a necessary!
Why hasn't this been done already?! I was just looking for a plugin to do this, it would mean I could have my thirteen linked spreadsheets as one spreadsheet.
It would be great to have a group function for sheets, similar to the one for rows/columns. This would allow someone to Collapse/Expand several sheets into one (ideally being able to name it rather than having a "+" sign) without being as intransparent and as the hiding function.
Kenneth Barber commented
Definitely a better alternative to tab colouring.
Simon Hurst commented
The grouping could also be useful in applying similar protection to a set of worksheets in one go, rather than having to do so individually, or relying on VB.