Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 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

Written by Allen Wyatt (last updated December 20, 2022)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, and Excel in Microsoft 365


11

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()
    Sheets("Main").Select
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
        Sheets(1).Activate
        Sheets(2).Activate
    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.

Note:

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 Microsoft 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. ...

MORE FROM ALLEN

Understanding Add-Ins

The primary way to extend what Excel can do is through the use of add-ins. This tip explains what they are and the ...

Discover More

Reading a PDF Newsletter on a Cell Phone

Sometimes creating a Word document is just the first step. Often you'll want to then convert it to a PDF file that can be ...

Discover More

Inserting the Date Your Document Was Last Printed

Word keeps track of each time you print your document, and you can automatically insert the last printing date anywhere ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!

More ExcelTips (ribbon)

Automatically Renaming Worksheets

Excel allows you to easily add and remove worksheets from a workbook. You may want a way to automatically rename all of ...

Discover More

Using Very Long Worksheet Tab Names

Excel places a limit on how many characters you can use in a worksheet name. This tip discusses that limit and provides ...

Discover More

Copying a Single Cell to Multiple Worksheets

If you need to copy a cell from one worksheet to a bunch of other worksheets, the work can quickly get tedious. Make the ...

Discover More
Subscribe

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

View most recent newsletter.

Comments

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}] (all 7 characters, in the sequence shown) 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 2 + 8?

2023-02-08 13:07:56

Allen

The master? Wow. (Thanks for the compliment.)

Yes, the code does not include that, and it should for completeness. However, the code will still work without it, provided you don't use "Option Explicit" in the module. If you do use it, then you'll get an error that is correctable by declaring the variable as you suggest.

Thanks, Rebecca.

-Allen


2023-02-08 12:25:36

Rebecca Healy

While I hate to challenge the master, is it possible that your code above forgot to include: Dim i As Long?


2022-12-23 11:48:26

J. Woolley

Vince and Elliot suggest opening a second window with "Main" in it, but this is not very useful if the purpose of "Main" is for convenient hyperlinks to the other sheets.
The shortcuts to move from sheet to sheet are Ctrl+PageDown and Ctrl+PageUp.
For related discussion, see https://excelribbon.tips.net/T007094_Jumping_to_a_Specific_Worksheet.html
and https://excelribbon.tips.net/T003591_Picking_Worksheets_Quickly.html


2022-12-20 13:19:38

Vince Roland

...OR... Just open a second window and keep "Main" in it. Flip to it any time you want.


2021-11-09 00:01:36

Ronald Kane

Hi Allen. Like Jonathan, in my Excel workbooks I have many tabs (worksheets) and I was able to scroll quickly between these worksheets with the cursor on the tabs and swiping either way with two fingers. Now, for no reason this has simply stopped working and I have to scroll across the tabs (worksheets) one click at a time. It is like I have triggered something in Excel . . . but I don’t know what. The two finger touch pad scrolling was very helpful and saved a lot of time as I could scroll quickly either way to any worksheet. No other changes have been made to my touchpad settings in Windows 10. Hope you may have some ideas. Thank you. Regards, Ron


2019-09-04 18:50:31

Chris

Ctrl-Click the left-arrow in the worksheet selection pane...it will take you to the first worksheet no matter how far you are to the right.
Right-click it and it will show the Activate window.


2019-08-27 09:46:50

Dariusz Wrobel

The proposed macro to be pasted in This Workbook does not work quite well.
It looks like, when we select for example 10th sheet, that it will become first from the left and sheets that were in positions from 1st to 9th will be moved to the end. It debugs for a long time in case of big files.

I suggest below macro for ThisWorkbook option. It simply moves tab "Main" before active sheet.
If your tab is called differently than "Main" it must be changed in the code in line: Worksheets("Main").Move Before:=Sheets(sc)
If you prefer to move tab "Main" to the right side of active sheet instead of the left side simply change "Before" in code to "After".


Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim sc As Long ' ActiveSheet

Application.EnableEvents = False
Application.ScreenUpdating = False

sc = ActiveSheet.Index

Worksheets("Main").Move Before:=Sheets(sc)
Sheets(sc).Activate

Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub


2019-04-29 01:59:49

Nick King

Another solution can be where many of the 50 worksheets in the above example are only used infrequently. If you hide such rarely used worksheets you may be able to leave just the ones you are working with on the tabs row such that Main is always visible. Shortening tab names and the length of the horizontal scrolling bar also will help.


2019-04-28 15:35:46

Daryl D Duren

Another option is to create a command button for the macro below:

Sub showsheets()
Application.CommandBars("workbook tabs").ShowPopup
End Sub


2019-04-28 07:19:47

Alex B

Another option is to create a rangename on the Main sheet and use the drop down box in the Name Box (cell reference box to the left of the formula bar) to go to the Main sheet.
The Name has have a scope of workbook (which seems to be the default anyway). And if you are using range names already put an underscore in front of it so that it appears first eg _MAIN

PS: if you then want to go back to your original sheet F5 then enter will get you back there.


2019-04-27 07:12:11

Elliot

Why not simply open a second window? Jonathan could size and position the two windows side-by-side. Then he could always keep one window on "Main" and roam around freely in the other window. Or am I missing something?


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
Subscribe

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.