How to Create a Table of Content for Multiple Excel Workbooks at Once.

Table of contents in Excel? Don’t underestimate its power! Here’s why you might be surprised you need one.

When you work with workbooks that have multiple sheets, a Table of Contents (TOC) keeps everything organized and users experience navigation ease, promoting a more professional and well-structured document.

While manually creating a TOC for every workbook can be tedious and time-consuming, several Microsoft tools offer a smarter solution. In this article, we’ll use these tools to create a TOC and automatically add it to every workbook, both previously created and new ones. This way, with a few adjustments each time, a new workbook will have a ready-made TOC, saving you significant time and effort.

Note: For this process to work, the location of the files you wish to work with must be saved to One Drive.

WHAT IS A TABLE OF CONTENTS?

A Table of Contents is a sheet within an Excel workbook that provides a better and well-organized understanding of the structure of your workbook. It helps to easily navigate through other worksheets within the same workbook with the use of hyperlinks, especially while working in a workbook with multiple worksheets.

BENEFITS OF A TABLE OF CONTENTS

Quick Access: No more scrolling! A TOC lets you see all the workbook’s content at a glance, making it easy to jump to specific sections.
Effortless Navigation: Forget hunting for information. With a TOC, you can find key details quickly without sifting through every sheet.
Professional Polish: A TOC adds a touch of sophistication, making your workbook look organized and user-friendly.
Teamwork Booster: Think of a TOC as a blueprint for your workbook in a collaborative project, it ensures everyone is on the same page regarding the structure, minimizing confusion and promoting efficient teamwork.

CREATING A TABLE OF CONTENTS

Now that we understand the need for Table Of Contents, let’s create one and then add the TOC to multiple workbooks simultaneously. For this, we’ll be working with two(2) tools.

Officescripts
Power automate

OFFICE SCRIPTS

Office scripts is a Microsoft Excel feature that allows us to automate common tasks in Excel, making it easier for us while working on repetitive tasks. These scripts are written in a programming language called TypeScript – which is similar to JavaScript. These scripts can then be used on their own or even combined with Power Automate to create automated workflows across different applications.

POWER AUTOMATE

Power Automate is a Microsoft cloud-based platform that enables us to incorporate and synchronize all our data analysis, by creating automated workflows across multiple applications and services.

GETTING THE SCRIPT

The script can be written from scratch, but for this blog, you can copy the script ‘here’. This script allows us to automate this process for as many projects as we want. Next, launch Excel on your computer. Office script works on both Excel online and desktop. For the purpose of this demo, we will use Excel Desktop, navigate to the Automate tab and then click on ‘New Script’.

In the Code Editor, delete every line of code and paste the code you copied from the link above.

Save the script in the code editor and rename it to a name you’ll easily remember, preferably TOC, so it’ll be easier to locate in case you have a lot of scripts in your Automate tab already.

CREATING THE FLOW IN POWER AUTOMATE

To launch Power Automate, sign in to Office.com and navigate to Power Automate, then, click on ‘create’ next, and select ‘instant cloud flow’

You can rename the flow to whatever name you prefer. For the trigger, set it to ‘manually trigger a flow’. After selecting manually trigger a flow, click on the create button.

Now we should see the first step of our flow which is the trigger. Click on the cross icon below the box to create the next step.

A section for ‘add an action’ will appear on your screen. Search for “list files in folder” and select the one under one drive for business.

The reason for getting the ‘list of files in folder’ is that we’ll add a TOC to several workbooks. We can’t start listing all the files individually since there’ll be so many, instead, we save the files in one folder. And that’s the folder we’ll be working with.

Now go to your file explorer and create a new folder. Make sure this folder is saved in your One Drive.

Next, in Power Automate, fill in the parameters for ‘list files in folder’ but this has only 1 parameter, which is to select a folder. Click on the folder icon within the box to fill in the parameter, and navigate to the root folder. Once you’ve done that, all the folders you have on One Drive will appear. select the new folder you created in your file explorer.

For the next step, click on the cross (plus) icon below the last step we just did (list files in folder).

A section for ‘add an action’ will appear on your screen. Search for “run script” and select the one under Excel online(business), then fill in the parameters for ‘run script’.

Location – OneDrive for business

Document library – OneDrive

File- unique ID

The ‘unique id’ is a distinct identifier used to uniquely distinguish items within our workflow or data processes. In this case, we don’t know what our file is called, because we’re getting the list of all the files in a folder, so we have to find a way to run the script on a file-by-file basis. This is where the ‘unique id’ comes into play.

To get the ‘unique id’, go to the dynamic content, select ‘id’, and then Add.

Once you’ve selected the ‘unique id’, a ‘For each’ action will be applied to the ‘run script’ step. This is just an indicator to let us know that the process of our flow will be added to all the files in the folder we created.

Next, we click on the script tab to select the script to run our flow. Select the script you copied in the first link ‘here’. If you saved it in the code editor, then it should be among the list of scripts that’ll show once you click on the tab. Once you’ve selected the script, go ahead and save the flow.

TESTING THE FLOW

Once the flow has been saved, open one drive on your computer, search for the folder you created, copy the Excel files you want to add a Table of Content to and paste them into that folder. Head back to Power Automate, click on ‘test’, select ‘manually’, then ‘test’, and finally ‘run flow’

When you head back to Excel and open any of the files in the folder you created, you should see that a TOC has been added to it. The same goes for all the files in that folder.

The beauty of it is that whenever you create or receive a new file, and you want to add a TOC to it, you only have to copy or move the file into the folder you created, and a TOC will be added to it automatically.

CONCLUSION

The Table of Contents is more than just a navigating tool, it also helps to improve efficiency and organization across multiple domains. By harnessing the power of a Table of Contents, we can improve collaboration, and facilitate the organization of retrieval of relevant data. As Excel continues to evolve, the TOC remains a timeless feature of the essence, to spreadsheet users.