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

How to Generate a Table of Contents in Google Sheets 29

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.

See also  How to Use REGEXMATCH in Google Sheets [Complete Guide]

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.
See also  How to Indent in Google Sheets [4 Methods]

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

how to Generate a Table of Contents in Google Sheets 1

Step 2

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

how to Generate a Table of Contents in Google Sheets 2

Step 3

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

How to Generate a Table of Contents in Google Sheets 3

Step 4

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

How to Generate a Table of Contents in Google Sheets 4

Step 5

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

How to Generate a Table of Contents in Google Sheets 5

Step 6

An input text box appears, passes the data range

How to Generate a Table of Contents in Google Sheets 6

How to Generate a Table of Contents in Google Sheets 7

Step 7

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

How to Generate a Table of Contents in Google Sheets 8

Step 8

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

How to Generate a Table of Contents in Google Sheets 9

Step 9

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

How to Generate a Table of Contents in Google Sheets 10

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.

How to Generate a Table of Contents in Google Sheets 11

How to Generate a Table of Contents in Google Sheets 12

How to Generate a Table of Contents in Google Sheets 13

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.

See also  How to Use IMPORTRANGE Function in Google Sheets (Complete Guide)

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

How to Generate a Table of Contents in Google Sheets 14

1.2 Go to Data > Named Ranges

How to Generate a Table of Contents in Google Sheets 15

1.3 Give your range a Name and Click Done

How to Generate a Table of Contents in Google Sheets 16

Step 2

Click on a cell, go to Insert > Link

How to Generate a Table of Contents in Google Sheets 17

Step 3

Click on “Sheets and Named Ranges

How to Generate a Table of Contents in Google Sheets 18

Step 4

All “Named ranges” will appear here

How to Generate a Table of Contents in Google Sheets 19

Step 5

Click on an appropriate Named Range to add it

How to Generate a Table of Contents in Google Sheets 20

Step 6

Your Named range is linked with the selected cell.

How to Generate a Table of Contents in Google Sheets 21

Step 7

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

How to Generate a Table of Contents in Google Sheets 22

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

See also  How to Use QUERY Function in Google Sheets (User Guide)

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

How to Generate a Table of Contents in Google Sheets 23

Step 2

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

How to Generate a Table of Contents in Google Sheets 24

Step 3

Go to Insert > Link

How to Generate a Table of Contents in Google Sheets 25

Step 4

Click on “Sheets and Named Ranges

How to Generate a Table of Contents in Google Sheets 26

Step 5

Click on “Select a range of cells to link

How to Generate a Table of Contents in Google Sheets 27

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

How to Generate a Table of Contents in Google Sheets 28

Step 7

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

How to Generate a Table of Contents in Google Sheets 29

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.

How to Generate a Table of Contents in Google Sheets 30

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.

how to Generate a Table of Contents in Google Sheets 31

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

how to Generate a Table of Contents in Google Sheets 32

Download/Copy Google Sheets Templates

Notes

  • The table of contents is based on the link concept in google sheets
  • Links can be added or removed anywhere in google sheets, and any range within the same workbook can be added to the table of contents on any sheet
  • You can design the link remove the underline, change font color, increase or decrease font size, change the display text, and many other formatting and editing can be done as simple as you do for normal text.
  • Named ranges are highly effective to use to generate a table of contents in google sheets.

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

See also  How to Use VLOOKUP in Google Sheets (User Guide)

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. 

See also  Extract Numbers from Strings in Google Sheets [REGEX Functions]

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general