Skip to content

Link to All Sheets in Your Workbook

This article will teach you how to quickly create hyperlinks to link to every sheet in your workbook.

This technique can be quite useful, especially if you find yourself working with a workbook containing an unreasonable amount of worksheet tabs.

Method Summary

Here is a summary of how this method works.

  • List all the sheet names you want to link to.
  • Use the HYPERLINK function to create a link to the first sheet.
  • Use the fill handle to copy the formula down for every sheet.

The real challenge in this method is figuring out how to properly utilize the HYPERLINK function to create a link based on the sheet’s name. But don’t worry, because I will be sure to explain the entire thing in detail.

Step 1 – List the Sheet Names

Before creating any hyperlinks, you must list all the sheet names for the worksheets in your workbook.

Once you have a list of all the sheets, you are ready for step 2.

Step 2 – Use the HYPERLINK Function

Step two is the most difficult part of the process, but once you are familiar with the formula, the rest is easy.

Here is the formula you will put together that uses the HYPERLINK function to create a link for the first listed worksheet.

=HYPERLINK("#'"&A2&"'!A1",A2)

In this formula, the first argument consists of:

  • The pound symbol (#)
  • A single quote ( ‘ )
  • The reference to the cell containing the sheets name (A2)
  • A single quote ( ‘ )
  • An exclamation point ( ! )
  • The cell reference A1

And it is important to note where the double quotation marks are as well as the ampersand symbols.

For the second argument, you simply reference the cell containing the sheet’s name to give the hyperlink the same name.

And once you have this formula created for the first listed worksheet, you are ready for step 3.

Step 3 – Use the Fill Handle

Once you have the formula created for the first worksheet in your list, all you have to do is use the fill handle to copy the formula down for all of the other worksheets.

And once you do that, you are done!

It’s not too difficult once you get down the formula. And if you add new sheets in the future, just add them to the list and use the fill handle to copy the formula down for those cells as well.

This Post Has One Comment

  1. I feel like this blog post was written for me! I’ve been struggling with having inherited workbooks with a crazy amount of tabs in it and having to hyperlink all of them by hand. This is going to change my life for sure!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back To Top