Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Sheet clusters in singel Excel file.

Copper Contributor

When trying to combine multiple sheets to work together we have to use linking separate files. Let's take as example monthly files with sheets containing data for single days and a summary. For this purpose we need to create 12 files, each 29-31 sheets. Then we need to link the files to combine the data from them into annual.

My idea is that excel file contains clusters of sheets. Each cluster is represented by a tag in the upper part of screen (like in MS Edge, or better like in AutoCad files tags, or somewhat like notebooks in MS Onennote). Clicking on such tag opens a cluster of sheets it contains, This solution will allow the user to create multiple content files. The annual is only one example. It can be for example annual clusters with company branch sheets or region clusters with customers' sheets. You will find a dozen of useful in a second.

Such ordanisation of sheets will help to group them for faster, more inyuitive and effective work. No problems with missing links between files due to moving one or changing its name. Much easier to copy and multiple the content, as when just saving new file creates whole structure without need for relinking files. Any change of the cluster name will result in automatic change of the links as usually with changing the names inside a file.

I believe this structural change would be a significant step up in Excel comparable to change in menus to the ribbon type.

 

2 Replies

@Adam_Smiechowski 

Your idea of sheet clusters or clusters of related sheets within a single Excel file is interesting and could potentially enhance the organization and usability of Excel workbooks, especially for complex projects or annual summaries. While Excel does not currently have a built-in feature for clusters or tags in the way you describe, so far I know, but there are some alternative approaches you can consider:

1. Worksheet Groups:

  • Excel allows you to group worksheets. You can select multiple sheets, right-click, and choose "Group." This does not provide tags, but it allows you to collapse/expand groups of sheets.

2. Table of Contents:

  • Create a dedicated sheet as a table of contents where you can list and hyperlink to different sections or clusters within your workbook.

3. Cell Hyperlinks:

  • You can create hyperlinks in cells that jump to specific sheets. This way, you can have a navigation structure on a summary sheet.

4. VBA (Visual Basic for Applications):

  • If you are comfortable with VBA, you could create a custom user form or sidebar that acts as a navigation panel. Each button or link could activate or show/hide specific sheets.

5. Excel Add-ins:

  • Explore the possibility of creating or finding Excel add-ins that provide additional organizational features. Some third-party tools might offer enhanced workbook navigation.

6. Power Query and Power Pivot:

  • Depending on your needs, Power Query and Power Pivot can be used to consolidate and organize data in a way that might align with your goals.

It's worth noting that Microsoft periodically updates Excel, and new features are added over time. Your feedback and suggestions can be submitted to Microsoft through their official channels, and they often consider user feedback for future updates and releases. AI was partially deployed to support the text.

 

My answers are voluntary and without guarantee!

 

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.

@NikolinoDE 

Thanks for answering.

I am afraid none of the proposed available solutions except add-ins are the kind of my proposition and would be able to work sufficiently easy. And I did not find an add-in like that neither.

Regarding MS they can be in the field of spreadsheets the cat fat enough to not to care, who knows.

 

All the best for the coming year!