Table of Contents offers an excellent way to navigate through multiple sheets.
Google Sheets is a top cloud-based spreadsheet program boasting many tools to organize and analyze datasets. You will find a handful of tools to navigate swiftly through multiple sheets within the same Google spreadsheet.
Many of us use the tabs section that runs across the bottom of the Google Sheets to jump from one sheet to another quickly.
However, things get confusing when dealing with tens of sheets available in a single Google spreadsheet.
The solution is a table of contents.
In this article, we will discuss the steps to create a Google Sheets Table of Contents. It allows users to jump across various sheets or tables within the same spreadsheet.
Download the Example Google Sheets
Here is the spreadsheet with example data we used to demonstrate the steps to create a table of contents in Google Sheets. Make sure to download it and follow me along as we move through the article.
Note that practice is the key to success. If your Google Sheet is ready with the required dataset, skip downloading the above file.
Step-by-Step Guide To Creating Google Sheets Table of Contents
You can easily create TOC in Google apps like Docs and Slides. On the other hand, when it comes to Google Sheets, you need to go through an extra workaround.
But don’t worry! Because it will hardly take a few minutes to insert a Table of Contents in Google Sheets.
STEP #1 – Create TOC Table
Google Sheets is an online spreadsheet program that allows users to organize data in tabular form.
In contrast to Google Docs’ simple table of contents, Google Sheets boasts a range of formatting tools that take TOC aesthetics to the next level.
Let us start by creating a table as shown below,
The first column includes links to various sheets, and the second column briefly describes the purpose of each sheet.
STEP #2 – Insert Links for Each Sheet
You can insert links using two methods. Both of them are pretty straightforward and almost similar.
The final choice depends upon your specific taste.
First Method: Using the Insert link Option
This is the most common and quickest method to insert links for your table of contents.
Here’s how it works,
- Click on the cell “C2“
- Right-click to see the following options
- Select “Insert link“
- A new popup will be displayed on your screen
- Now, click on the “Sheets and named ranges” button as shown in the following image
- A list of available sheets within the spreadsheet is displayed
- Select the second one
You will see the sheet name is inserted in the cell. If you click on that cell or sheet name, you will be redirected to that respective cell.
Second Method: Using the HYPERLINK Function
This method is for intermediate and advanced Google Sheets users who rely on functions to get the job done.
Before we dive into the actual process of using the HYPERLINK function to insert TOC links, let us first understand the function.
Explained: The HYPERLINK Function in Google Sheets
It is a simple function used to create a clickable link to open a specific URL. The general syntax for the function is as follows:
=HYPERLINK(URL, [link_label])Here’s how you need to deal with the arguments of the above formula,
- “url” – You need to enter the target URL and use double quotation marks. In general, you can insert site links as well as email addresses.
- “link_label” – It can be also called the hypertext. Here, you can define the text, which will be displayed as a clickable link.
As mentioned earlier, make sure to use double quotation marks for both of these arguments.
Q. How to Get the Link to a Sheet?
Now, the main question is how you can get the sheet link to insert in the HYPERLINK function.
Here are the steps,
- Click on the target sheet name as shown below
- Hover to the address bar of your browser
- You will notice the GID as shown in the following image
- Copy the GID and make sure to include the “#” part
A GID is a series of unique numbers in a Google spreadsheet. Simply put, a GID represents a unique ID assigned to each sheet.
Finally, let us use both the HYPERLINK function and sheet URL (GID) to create a table of contents.
- Click on the cell “A2“
- Type “=hyperlink”
- Select the first option from the popup or press the “Tab” key
- Replace the URL argument with the GID for the first sheet
(Make sure to use the double quotation marks, otherwise; the formula won’t work at the end) - Similarly, let us put the hypertext as “Dataset” and enclose it within the double quotation marks
- Complete the parenthesis using “)“
- Press the “Enter” button on your keyboard
Our final formula should look like the following,
=HYPERLINK("#gid=613703419","Dataset")Note that the GID number or link will change depending on your spreadsheet.
You need to follow the same steps for the rest of the sheets.
If your spreadsheet includes a huge number of sheets, using the HYPERLINK function becomes tedious and time-consuming.
I would suggest using the first method (Right-click method) for such scenarios.
STEP #3 – TOC Table Formatting
Once you complete the TOC table, it is the time for formatting.
You can start by increasing the header height and changing the colors. Here’s how I did it.
Now, let us apply borders.
- Select the entire table range
- Hover to the toolbar section that sits below the main menu
- Click on the “Borders” icon
- Choose the first option named “All borders” as shown in the following image
The black borders will be applied by default. You can play around with the colors and border thickness to make the table more attractive. Refer to the following image.
Lastly, turn off the gridlines using the following steps.
- Go to the main menu
- Click on the “View” tab
- Select the first option named “Show“
- Click on the “Gridlines” option from the popup
That’s it.
Our Table of Contents is all ready.
How to create a Table of Contents in Google Sheets for a specific Table Range
In the above section, we learned the steps to create a table of contents that helps us navigate through different sheets.
But what if we create a table of contents section to jump across various tables on the same sheet?
Yes, that’s possible. Google Sheets allows you to create a unique link for each table.
Let’s get started,
In the above example, we have two different tables named “Table 1” and “Table 2”..
We need the table range and sheet name to create a link for those tables.
The table range for the first table is “A8:C19” as shown in the following image.
So, the link for this table would be as follows,
Data!A8:C19- Here, the “Data” is the sheet name
Similarly, for the second table, the range is “E8:G19“. Refer to the following image.
The link for the second table would be,
Data!E8:G19Let us use these links and create a table of contents in a same sheet.
- Select the cell “B3“
- Right-click to see the following options
- Choose the “Insert link” option
- Click “Sheets and named ranges” as shown below
- You will see the following option
- Click on the “Select a range of cells to link” option
- Copy and paste the link for the first table, as discussed above
- Press the “OK” button
Follow the same steps to insert the link for the second table.
Our table of contents should look like below.
Note that the hypertext for the links we have inserted will include the sheet name and table range. To edit that, you need to follow the steps below.
- Select the target cell containing the link
- Right-click to see the more options
- Choose the “Edit link” option from the list
- A new popup will appear on your screen
- Enter the desired name in the first empty box
- Press the “Apply” button
I am using the names “Table 1” and “Table 2”. So, in my case, the TOC table looks like below.
To Summarize: Google Sheets Table of Contents
Inserting a TOC table in Google Sheets is pretty straightforward.
In contrast to other Google apps like Google Docs, which comes with automatic Table of Contents functionality, Google Sheets requires manual effort.
However, those manual efforts are negligible when your Google spreadsheet contains several sheets (around ten or so).
Let us know if you need any help creating a table of contents in Google Sheets.
Remember to explore our blog section to learn more tips and tricks for organizing and analyzing datasets in Google Sheets like a pro.