Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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: Shortcut to Move between Two Worksheets.

Shortcut to Move between Two Worksheets

by Allen Wyatt
(last updated February 21, 2015)

29

You can easily move between worksheets in a workbook by using Ctrl+Pg Up and Ctrl+Pg Down. What if you want to use a shortcut to move between two specific, non-neighboring worksheets, such as Sheet1 and Sheet4? In this case, it is best to use a macro to do the jumping around.

If desired, you could define two macros that would do the jumping. One macro would jump to Sheet1 and the other to Sheet4. These would be easy enough to create using the macro recorder, and you could assign a shortcut key to each of the macros.

If you are looking for a single shortcut that will toggle between the two worksheets, then you can use a macro such as this:

Sub JumpBetween1()
    If ActiveSheet.Name = "Sheet1" Then
        Worksheets("Sheet4").Activate
    Else
        Worksheets("Sheet1").Activate
    End If
End Sub

The macro simply checks to see which worksheet is currently displayed. If it is Sheet1, then Sheet4 is displayed. In all other instances, Sheet1 is displayed. This is handy, but it means that if you currently have Sheet2 displayed, the shortcut will always display Sheet1. You might not want the macro to do anything unless either Sheet1 or Sheet4 is displayed. In that case, you should use this variation of the macro:

Sub JumpBetween2()
    If ActiveSheet.Name = "Sheet1" Then
        Sheets("Sheet4").Activate
    ElseIf ActiveSheet.Name = "Sheet4" Then
        Sheets("Sheet1").Activate
    End If
End Sub

Note that the only difference between the two macros is that the latter variation uses ElseIf to check if Sheet4 is displayed. This means that if any worksheets other than Sheet1 or Sheet4 is displayed, the macro will do nothing.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (9669) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Shortcut to Move between Two Worksheets.

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

How Word Treats Normal.dot

Templates are at the core of how Word creates and formats documents. From the earliest days of Word, the most basic of ...

Discover More

Saving Document Versions

Documents often go through several versions during development. For this reason, Word provides a feature that allows you to ...

Discover More

Removing Spaces

Need to get rid of spaces in a range of cells? There are two ways you can approach the task, as described here.

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Disabling a Function Key

Function keys are used to perform common tasks in Excel. If you want to disable one of the function keys, it's rather easy to ...

Discover More

Arranging Workbook Windows

If you find yourself working with a number of different workbooks at the same time, you may want to arrange your desktop so ...

Discover More

Status Bar Summing No Longer Available

When you select a range of cells, Excel normally displays the sum of those selected cells on the status bar. If the sum no ...

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}] 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 9?

2017-07-14 18:48:33

James T

For those attempting to switch between sheets via another method than Ctrl+ Pg up / Pg down [me due to my keyboard on my HP Elitebook not including a dedicated button for Pg up/down] i used Mark's code below (thanks Mark!) with a slight modification [activate rather than select in the command] given that Mark's original code was buggy in my version of excel [2016]. Here is the code, which seems to working fine but i will note that ive just installed it so have only tried it out in a few workbooks:

Sub Change_Sheets_Left()
' Keyboard Shortcut: Ctrl+k
Dim SheetNum, CurrentSheet As Integer
SheetNum = Sheets.Count
CurrentSheet = ActiveSheet.Index
If CurrentSheet > 1 Then
Sheets(CurrentSheet - 1).Activate
Else
Sheets(SheetNum).Activate
End If
End Sub

Sub Change_Sheets_Right()
' Keyboard Shortcut: Ctrl+l
Dim SheetNum, CurrentSheet As Integer
SheetNum = Sheets.Count
CurrentSheet = ActiveSheet.Index
If CurrentSheet < SheetNum Then
Sheets(CurrentSheet + 1).Activate
Else
Sheets(1).Select
End If
End Sub


2017-01-18 05:42:09

Dave Smith

@ Dan Gazit

Hi Dan,
You might want to try using a hyperlink.
Use the key combination CRTL and K to start the hyperlink wizard.

I use hyperlinks on a list of names in a weekly draw collection sheet to take me to the individual's own record payment sheet.............just wish I had discovered hyperlinks back in January 1996 when I took over the draw.

Hope this helps?


2017-01-17 05:10:58

Veronica

I want to use two different price lists (selling price and stock price when bought) that are on two different sheets in one workbook. I want to use the same detail list but swap between the price lists prices depending if the manager wants the list or a client without having two lists to keep updated. Is this possible?


2016-10-23 03:28:57

Dan Gazit

I meant to move to another sheet (not cell):

I have on sheet 1 a list of all the sheet`s names. Each one in different cell.
How do I move to a certain sheet just by clicking on the name in sheet 1 ? (It will be, each time, different sheet)


2016-10-23 03:25:43

Dan Gazit

I have on sheet 1 a list of all the sheet`s names. Each one in different sheet.
How do I move to a certain cell just by clicking on the name in sheet 1 ?


2016-04-13 07:02:59

Nidhi

How to switch between different sheets when u have named that sheet with some different names on every sheet,because currently ctrl-pg up not working in excel 2013


2016-04-05 08:58:13

dixit goswami

if we have 50 sheets in worksheet 1,2,3,4,5,6,7,8......50.
and we have to in 2nd sheet and jump to 40 no sheet which short cut use for it without ctrl+pgup pgdn.
directly to the 2 sheet to 40th sheet


2016-02-17 08:25:24

Peter Atherton

Hi Santhosh

You are going to need a sheet with the dates each person uses a particular tool. It could look something like this:

Date Employee Tools
01/2/2016 Bob Tool1
01/2/2016 Fred Tool2
01/2/2016 Ged Tool6
01/2/2016 Jon Tool7
01/2/2016 June Tool4
01/2/2016 May Tool3

Then I would create a pivot table (Insert --> Table --> Pivot Table

Place the Date Field in the Report Filter, the Employee Field in the Column Labels and the Tools in the Row Labels

You can click on the Date Filter to select the dates you want counted.

On the actual List where you make your entries You could use Data Validation to ensure that Employees are not mis-typed. You could also do the same with with the Tools column. However, Data Validation only applies to one list AFAIK. Perhaps you could merge both list elsewhere a then sort them.

If you decide to use Data Validation be sure to click the check box saying appy to all cells with same setting.

HTH


2016-02-16 02:58:09

Santhosh

Hi, am santhosh. I am having 800 tools in sheet 1 600 tools in sheet 2 and number of person is 7 in sheet 3. I need to calculate frequency of this 7 persons that how many times they are using the tools in a month and it should be calculated in sheet 3. Please help me to solve this.


2015-12-15 23:33:08

AyyA

Really Nice Shortcut


2015-10-27 21:47:30

Tommy

Hi, I was good in excel 20 years ago, since then, I haven't work with it until now. I have forgotten so many... back to learning in Excel 2016 ;)

Thanks :)


2015-10-15 08:52:40

Roger B.

@Matt J - you can do that. First, have you tried Ctrl-PgUp and Ctrl-PgDn to move between worksheets? It is the same as using Alt-Tab to move between applications. If you want direct access to the last sheet, you will have to create a macro. I put all my macros in one workbook and open it. Then, as I open and work in a different workbook, the macros are available. Setup a hot key for them and you have quick access and there is no need to put the macro in all the different workbooks.


2015-10-14 16:29:48

Matt J

I appreciate the use of macros, but that also assumes I'll always be working within the same workbook.

I would like to swap to the last active tab (worksheet?) in the same way that I can alt-tab to the last active window/application in the Windows O/S. This should be implemented in the software; users should not have to create a macro or hyperlink with every new workbook - not that you guys are responsible for this. Microsoft needs to better their software.


2015-10-12 06:38:15

Mark

CTL+PgUp/PgDn doesn't work in my version of Excel so I created these Macros comparable to Windows ALT-TAB----

Sub Change_Sheets_Right()
' Keyboard Shortcut: Ctrl+Shift+X
Dim SheetNum, CurrentSheet As Integer
SheetNum = Sheets.Count
CurrentSheet = ActiveSheet.Index
If CurrentSheet < SheetNum Then
Sheets(CurrentSheet + 1).Select
Else
Sheets(1).Select
End If
End Sub

Sub Change_Sheets_Left()
' Keyboard Shortcut: Ctrl+Shift+Z
Dim SheetNum, CurrentSheet As Integer
SheetNum = Sheets.Count
CurrentSheet = ActiveSheet.Index
If CurrentSheet > 1 Then
Sheets(CurrentSheet - 1).Select
Else
Sheets(SheetNum).Select
End If
End Sub


2015-08-25 12:16:10

John Bovill

Thanks for the tip Mike H. Appreciated very much


2015-08-13 08:43:44

Jonathan

Can I create a custom shortcut to change CTRL+PgUp & CTRL+PgDn to ALT+Q and ALT+W, respectively?
Would make it easy to do with one hand, and places this function right next to CTRL+Tab (standard shortcut to switch windows).
Thanks in advance!


2015-06-10 06:05:10

wanwan

Any shortcut key that can jump from sheet to another sheet that have the source of formula.
eg: I've ='Sheet2'!S69 in my Sheet1 cell, I want a shortcut key that can jump to Sheet2 the particular cell. Thks. pls advise


2015-06-09 01:01:12

N Srinivas

Thank you very much, valuable information


2015-06-04 03:10:03

Arcahan

Thank You soo Much Sir


2015-04-22 05:11:02

Dave Smith

I have also discovered in Excel 97 that CRTL/TAB and CTRL/TAB/SHIFT combinations allow me to move back and forth between open work books.


2015-04-22 04:35:15

vipmog

Thank You very much Mike.

Indeed a very very helpful information.

Cheers


2015-03-24 09:44:21

Glenn Case

Roger:

There is, at least in Excel 2010, a built-in way to select from a list of worksheets. Just right-click on the arrows in the lower LH corner of the Excel window (to the left of the worksheet tabs.) A pickable list of worksheets will be generated.


2015-03-24 06:23:24

Dave Smith

Hi Mike H,

I also use hyperlinks to switch between 2 work sheets in separate Excel 97 files.

After using one of the hyperlinks I have found that I can quickly switch back and forth between sheet using the ALT/Left or Right Arrow.

Which saves having to find the mouse, mouse arrow and then click on a hyperlink.


2015-03-24 01:24:22

Deepak D

hi
Mike

FIRST of all thanks a lot,
for solution on moving btwn sheet 1 to sheet 2

This was 1 needed.

Thanks AND keep ur work going.


2015-02-23 10:37:07

Roger B.

I have a excel file that has a main list sheet and then a series of detailed sheets. I want the main list next to the sheet that I'm working on. So I wrote a macro that makes a list of all the worksheets, puts it into a popup menu from which I can select and then moves the main list sheet in front of the worksheet that I select. And since I have a variety of different files some of which have unique sheets, it always displays the sheets of the one that I'm working on. It has saved me a lot of work in moving that main list around.


2015-02-23 06:10:01

Abel Freire

You can also open a new window of the same book and open the other sheet.
Move between windows of the same book using Alt+Tab.


2015-02-22 20:03:52

mandy

thanks , it was helpful


2015-02-22 13:30:52

Mike H

Another easy method is you could always create a hyperlink via (Ctrl + K) Insert Hyperlink and followed by -select "Place in this document" then edit "Text to display" to some appropriate word or wording then type in / change cell address (default A1)and then select sheet to jump to. Then one to get back.

This way you have a simple one left click action to jump between sheets and back again and even to a specific cell / area.

Have used this in a menu sheet to jump to other sheets in same file for ease of navigation for other users I have sent the file to.

Hope you find this of interest and help.

Mike H


2015-02-21 14:35:38

J. Conklin

If the non-adjacent worksheets, just repeat the Ctrl-PgUp or Ctrl-PgDn and you will move to the next worksheet. You don't need to use a macro.


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.