How to Make Table of Contents in Google Sheets (3 Methods)

In this article, we will learn how to make table of contents in google sheets using three different methods.

Table of contents is a very useful feature in text content, it is commonly used in book guides, audio, books, business files, and much more. In google sheets, we don’t have a direct resource to create or build the table of contents. But, in Google Docs or MS Word, we have a direct method to make a table of content and easily we can add our headings and subheadings to it. In google sheets, we don’t have data in such form, mostly we don’t have headings or subheadings to add to the table of contents when working in google sheets file. Rather we may have sections, different tables, and ranges, that can be added into a table of content to make it easy to navigate and provide ease to new users to go anywhere by just on click in a large dataset. A table of contents helps us to navigate and to see the content of the entire file quickly, we see where to go and we can click on it to instantly reach that particular section.

Table of Contents in Google Sheets

In google sheets, we work with large files, we may have various sections of the data and further sub-sections, to keep it in the proper format we mostly used other files within the workbook. Using other files or using the same file, in both cases we can add a table of contents. In this article, we will learn how to generate a table of contents in google sheets from separate files, a specific range in another file, or multiple ranges in the same or other files within the workbook. We need to learn how to generate a table of contents in google sheets to add quick links to all the sections, and to add links to the ranges that exist in other files, this helps a lot when working with a huge dataset.

• To add easy navigation throughout the workbook
• To link all the main sections on the top for a quick redirect
• To avoid long scrolling and manual searching among multiple sections inside the dataset.

How to Make Table of Contents in Google Sheets

Here we will see each section in detail. We will learn how to generate a table of contents in google sheets looking at all possible use cases you may have while working with larger data sets.

Firstly, let’s see how to add a table of contents for a single file where we have different sections and we want to have easy navigation to each section of a long dataset.

How to Create a Table of Contents in Google Sheets – Adding Ranges to Table of Contents Within a File

In this section, we will learn, how to add links to the table of contents to google sheets when we have the data in a single file but in different sections, we will add these sections to the table of contents with a link and we will see how easy it becomes to navigate to each section.

I have a file in which I have the department data of a company, where each department’s employees’ names, IDs, designation, and ages are kept. Now normally I need to scroll down to the end to see the data of the last department, so every time I need to pass through all the depts when I need to go to the last dept. We will solve this problem in this method

Step 1

All the data sections in the same file

Step 2

Go to the top where you want to add a table of contents

Step 3

Click on Insert > Link (Ctrl + K is a shortcut)

Step 4

A dialog box will appear, click on “Sheets and Named Ranges”

Step 5

Now you have two options “Sheet1”, and “Select a range of cells to link”, select the second option

Step 6

An input text box appears, passes the data range

Step 7

Click OK, and a link will be added to the selected cell

Step 8

Click or mouse over the cell, a link will appear.

Step 9

Click on the link, it will redirect you to the range you linked instantly.

Step 10

You can edit the text and keep any text (add a custom text and press enter key to save it as the display text) as well as you can use any formatting here color, font, style, etc. The link will be working accurately regardless of formatting.

This is how simply you added an item on the top that is linked with the actual range and redirected you to that range.

But there is a problem with this method, the method is can be very hectic and confusing to remember the cell ranges, and we need to add ranges manually, a better solution is using Named Ranges, we are going to see that next.

How to Make a Table of Contents in Google Sheets – Using Named Ranges

Doing the same work but using named ranges now, we are going to learn how to generate a table of contents in google sheets using named ranges. Now named ranges are used for various purposes I have previously used named ranges in many tutorials. We will see how to use named ranges here with the Table of contents.

Step 1

Make your range a “Named Range”

1.1 Select the range

1.2 Go to Data > Named Ranges

1.3 Give your range a Name and Click Done

Step 2

Click on a cell, go to Insert > Link

Step 3

Click on “Sheets and Named Ranges”

Step 4

All “Named ranges” will appear here

Step 5

Click on an appropriate Named Range to add it

Step 6

Your Named range is linked with the selected cell.

Step 7

Hover on the cell to copy, edit, or remove the link.

This is how you can add Named ranges in the Table of contents in google sheets.

How to Make a Table of Contents in Google Sheets – Adding Multiple Sheets within a Workbook

Now for many reasons, you might your sections are on different sheets. We will learn how to generate a table of contents in google sheets by adding multiple sheets within a workbook, we may have many sheets that contain a different kind of data, but as an example of a book where we have a lot of pages and in the starting, we have a table of contents of the entire book. Similarly, here, we will put the data into our table of contents in the first sheet from multiple sheets.

Let’s see another example here.

For this example, I have a dataset in which I have multiple sheets having different sections of the overall data.

Step 1

A workbook with multiple sheets

Step 2

In the very first sheet, go to the cell where you want to generate a table of contents.

Step 3

Go to Insert > Link

Step 4

Click on “Sheets and Named Ranges”

Step 5

Click on “Select a range of cells to link”

Step 6

A dialog box will appear with a textbox in which you have to pass the range of cells, you can pass this value by selecting the range (regular selection) from any sheets within the workbook

Step 7

I am selecting a range from another sheet, then Clicking OK.

Step 8

You have successfully linked the range from another sheet to your main sheet, you can click on it and it will quickly redirect you to the specified range in any sheet.

Note: The Sheet Name along with a range of cells is not looking good you can edit it by clicking on the edit button as I have described above.

This is how easily you can add the contents in the table of contents from various sheets within a workbook.

Note: If you don’t link specific sections of any other sheet, instead you want to link the sheet itself without any specific range so you do it easily, in inside the insert link dialog box, you have a list of all your sheets in the file, you can click on any sheet from the list to link the sheet without any specific range inside that sheet.

That’s all from how to generate a table of contents in google sheets. I hope you have understood and learned all three methods I tried to cover in this article.

Your Final Result

Download/Copy Google Sheets Templates

Notes

While naming your range you cannot use (-) hyphens between words, you must use underscore

FAQ

How can I generate a table of contents in google sheets?

In google sheets, you don’t have any specific method to generate a ready-made template of the table of contents like Ms Word or google docs. Instead, we have other functions, and logic to generate a table of contents, we use links to add items on the top of the sheet and design that area just like a table of contents layout so it mimics a table of contents. In this article we have seen three methods to add links to generate a table of contents, you can refer to the methods above to learn it step-by-step with screenshots. 

Conclusion

Wrapping up how to generate a table of contents in google sheets, we have learned what is a table of contents, how it helps us to manage big datasets, how helpful a table of content is, and why should we learn to generate it, we have discussed all these things in first three sections, then we moved on to practical working and we have seen three easy methods to generate a table of contents. The first method was about a single file where you have multiple sections in your dataset, then we moved on and talked about the named ranges, and in the last section, we saw how to add items from multiple sheets within a workbook in our table of content that can be on any sheet.

So, guys, I hope you find this article helpful please share your thoughts on “how to generate a table of contents in google sheets” in the below comment section. Goodbye. Keep learning with Office Demy. Thank you.