Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Freezing Worksheet Tabs.

Freezing Worksheet Tabs

by Allen Wyatt
(last updated January 12, 2019)


Jonathan has a workbook that contains over fifty worksheets, one of which is named "Main" and is positioned as the first tab in the workbook. He is constantly having to revert back to the "Main" worksheet. In order to display the worksheet, he must either click back a tab at a time or scroll all the way to the left of the tabs (by clicking on the control at the far left of the tabs) and then select the "Main" tab. This last method is the easiest, but still is time consuming. Jonathan wonders if there is a way, much like freezing a pane, to freeze a worksheet tab. He would like the "Main" tab to always be visible, and the tabs to its right to scroll.

The short answer is no, there is not a way in Excel to freeze the worksheet tabs. That being said, there are several things you can do to get the results you want.

One possible solution is to use hyperlinks in your worksheets. Many people set up a system where their main worksheet functions as a table of contents to the other worksheets in the workbook. Each worksheet is hyperlinked from the main worksheet, and each non-main worksheet has a hyperlink back to the main worksheet. Thus they can navigate very quickly between the main and secondary worksheets just by clicking the hyperlinks.

Another option is to remember that you can right-click on the worksheet tab controls at the left of the tabs at the bottom of the Excel window. When you do, you get a list of the first fifteen worksheet names, and you can easily select the "Main" worksheet.

Still another option is to set up a very simple macro that always displays the "Main" worksheet:

Sub GoToMain()
End Sub

You can assign this macro to either a shortcut key or add it to the Quick Access toolbar so that you could use it very quickly. When run, the worksheet named "Main" is always displayed.

If you absolutely want to always have the "Main" sheet visible in the tabs area, then you must resort to a macro that will continuously reorder the tabs so that "Main" is always visible.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Dim sc As Long ' count of sheets
    Dim NewPos As Long ' index of serlected sheet

    Application.EnableEvents = False
    Application.ScreenUpdating = False

    If ActiveSheet.Index <> 1 Then
        sc = Sheets.Count
        NewPos = ActiveSheet.Index
        For i = 2 To NewPos - 1
            Sheets(2).Move After:=Sheets(sc)
        Next i
    End If

    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

This macro needs to be part of the ThisWorkbook object, so make sure you add it into the proper place in the VBA Editor. The easiest way to do this is to follow these steps:

  1. Press Alt+F11 to display the Visual Basic Editor.
  2. Using the Project window, double-click the ThisWorkbook object. (Make sure you double-click on the ThisWorkbook object associated with the workbook where you want to add the macro.) You'll see a code window for the ThisWorkbook object appear.
  3. Put the above code in the code window.

The macro always moves the worksheets in positions 2 through however many sheets you have so that the desired worksheet is in the second position. This means that the worksheet in the first position (Main) never moves.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (8937) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Freezing Worksheet Tabs.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Adding Captions

Need to add a caption after a document element such as a figure or table? You can add and format one manually, or you can ...

Discover More

Specifying a Backup Location

Backup files created by Word are stored in the same folder in which the document is located. If you want them stored in a ...

Discover More

Understanding the VLOOKUP Function

Functions are at the heart of Excel's power in working with data. One of the most misunderstood functions provided by ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Condensing Multiple Worksheets Into One

Want a quick way to combine your worksheets? This tip features a simple macro to do the task for you.

Discover More

Protecting a Single Worksheet

Excel allows you to protect your worksheets easily—and that includes if you need to protect only a single worksheet ...

Discover More

Finding the Size of Individual Worksheets

Your workbooks can contain many, many worksheets. Which of those worksheets are the largest, however? Here's some ideas ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is nine more than 6?

2018-01-17 07:35:00


I like the VBA in the post but it prohibits copying between pages. It will allow copying where the main page is the destination. Is there a way around this?

2017-09-11 13:17:26

Richard C

I'm surprised I did not see this simple trick: Control-Left-Click on the arrow you use to scroll left through the worksheet tabs. This will automatically scroll all the way to the leftmost worksheet tab, which you can then click in order to select it. This will select your "Main" sheet.

2014-04-13 05:22:19

Michael (Micky) Avidan

Most geeks, usually, read the full(!) tip and find out that the "Wheel was already invented" - and I quote:
"...and each non-main worksheet has a hyperlink back to the main worksheet...
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” MVP – Excel (2009-2014)

2014-04-12 12:28:05

Surendera M. Bhanot

One can also put a Hyperlink named "Main" in each sheet and by licking this the main sheet will become the current sheet. Most geeks do this.

2014-04-12 12:23:04

Surendera M. Bhanot

The easiet way to use the GoTo Command (Ctrl+G). In the Reference Box write the name of worksheet you want to go to. Add Exclamation Sign next to the name of Worksheet (without any apace and then enter the cell reference e.g., Sheet3!A1, and click OK or press enter. The Cell A1 od the sheet3 will be selected and Sheet3 will become the Current Sheet.

If you have renamed your worksheet as June2014, Just enter June2014!A1 in the reference Boxof GoTo command.

Another way to achive this is to name your cells in different worksheets and Select that name by pressing the Cell Reference (or Address) Box (situated on left of the Formula Bar no matter in which sheet you are currently in. This will take you to the relevant worksheet in which that named cell is.

2014-04-11 06:56:46


Maybe you should ask yourself if 50 worksheets is really the way to go? You may be able to use VBA to make it easier to find "Main", but you are still going to be scrolling around all over the place if you need to find, say, sheet 37.

2014-04-10 10:26:37


Maybe of the 50 sheets, if you only need to temporarily freeze them you could hide the ones you don't need.

Then as work through the tabs next to Main, the others can be hidden until you need to see the others again and then unhide the relevant tabs.

That helped me in the scenario of having to build two Main tabs referencing data in 35 other sheets

2013-12-22 02:54:41

nir liberman

The "Sub GoToMain()" will be more effective if you add the "sub goBack()":
In the module:

Public origSheet As String

Sub GoToMain()
End Sub

Sub GoBack()
On Error Resume Next
End Sub

And in ThisWorkbook object:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If ActiveSheet.Name <> "Main" Then origSheet = ActiveSheet.Name
End Sub

Assign shortcut to GoToMain and goBack.

2013-12-21 10:50:43


Or name the range to which you will need to navigate. Choosing the range from the name box will move you there quickly. Range names are arranged alphabetically, so assign a name AAMain or _Main to assure this range is easily found, should there be many named ranges.

2013-12-21 08:18:22


worth using

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.