In one our earlier blogs, we discussed how to create an Index/TOC sheet in an Excel workbook.

Don’t recall? No worries, read it again here.

Got it now. Great!

 

Now, a lot of our readers came back and asked a rather intuitive follow-up question.

They asked, “But, how do we navigate between the different sheets of the workbook?”

Let us explain further.

Take the same example we had in the earlier blog.

We have an Excel spreadsheet with an Index sheet in the front.

There are 10 other sheets (illustration) as well in the same workbook.

Let us say, you are on sheet 2018 of the workbook and want to come to sheet 2009.

 

What is the easiest way to do it?

Well, you can manually move over from sheet 2018 to sheet 2009 in the example above.

Right?

No, but that is so tiresome.

Especially if you have to do it in larger workbooks with 100 or more sheets!

Think if you wanted to navigate from Sheet 100 to Sheet 1!

Wouldn’t this be so irritating to scroll left all the way to the first sheet!

 

Is there an easier way to accomplish this?

Well, yes you can work out something more convenient.

Let us show you how!

For simplicity we will refer to the same workbook to illustrate this

 

Step 1: Create a hyperlink to the Index sheet

Let us say, we are on sheet 2009 of the Excel workbook.

We go to a blank cell of this sheet preferably one which is blank in all the other sheets as well.

Say this is cell A2 of the workbook.

 

On this cell, we will create a hyperlink to the Index sheet.

We write “Back : Index” on this cell.

Right click and select the “Link” option

The moment we select the Link option, an Insert hyperlink window opens up.

Simply select the Link to option as “Place in this document”

And on the right side of the dialog box, select the “Index” sheet as reference.

The idea is to create a hyperlink on this cell to the Index sheet such that the user can go back easily.

So far so good?

 

So we now have a hyperlink to the Index sheet on cell A2 of the sheet 2009.

What’s next?

 

Step 2: Create the hyperlink to Index sheet on all the other sheets.

First copy (Press CTRL + C) cell A2 of the sheet 2009.

Now we need to paste it all the sheets 2010 until 2018.

How do you do this lightning fast?

 

First of all, we need to select all the sheets from 2010 to 2018.

You can simply select the starting sheet i.e. 2010 press the SHIFT key and select the last sheet i.e 2018.

It will automatically select all the sheets in the workbook between sheet 2010 and sheet 2018.

Notice the selected sheets now appear in white color.

Once all the sheets are selected, any changes on 1 sheet will be reflected in all the selected sheets.

So be careful with whatever you do now since it will be carried out on all the sheets.

Go to cell A2 of any other sheet that is selected.

Simply paste (Press CTRL + V) what we had copied earlier.

You will notice, that the hyperlink to the Index sheet appears on all the sheets now.

Simply deselect all the selected sheets.

For this, you can just click on any of the non-select sheets of the workbook.

The selected sheets will disappear.

Now what?

 

Step 3: Navigate from a sheet to another using the Index sheet as anchor

Now, let us say we are on sheet 2018.

We want to move to sheet 2009.

We can simply click on cell A2 of the sheet 2018.

This will take us back to the Index sheet.

Now, on the Index sheet simply click on the 2009 hyperlink.

And voila, we are now on Sheet 2009!

Notice, how we have been able to navigate between sheets so simply.

You can navigate to any other sheet from here on in a similar way as we have shown.

Think how useful this would be when you have more than 100 sheets in a workbook.

 

Won’t it?

For more such tricks and tips in Excel, join our online course The Data Analyst Skills Training (DAST) in Excel here.

Please log in to provide your comments!