How can I set a keyboard shortcut to switch between Excel tabs?

Question

I find the Ctrl+ PgDn/PgUpnot good enough. When I have a lot of tabs it's very annoying to get to the right tab, or move it and change the order, which is sometimes important.

I need Excel to switch between the current tab and the previous tab I worked with - regardless of their physical order. Just like windows Alt + Tab .

I guess I can build a macro that remembers my last tab and assign a shortcut to it, but I would like to know if Excel already comes with that functionality.

Answer 1

I don't believe Excel has a keyboard shortcut for toggling between sheets. In Excel 2007 & 2010, as a non-VBA workaround, you can create a window for each sheet that you're working on.

How..

Let's say you were working on Sheet1 and Sheet10.

1. Select Sheet10 and press Alt+W, N. This creates another window where Sheet10 is active. Note that at the top of the window, the file name now has a :2 after it, like so:

   

2. Press ALT+Tab to go back to the first window, and highlight Sheet1. Note that the first window now has a :1 after the file name at the top.

   

You can just use good old ALT+Tab to go back and forth between the two sheets.

Note that you're not creating another instance of Excel, just a separate view for each sheet you need. So you can still add cell references, and pressing CTRL+S in either window updates the file. If you want to work on another sheet, either change the active worksheet on one of the windows, or spawn another one.

CTRL+W closes a window.

Answer 2

Pressing CTRL+G will allow you to type in the tab & cell number you want to go to. For example, in Kaze's example spreadsheet above, if you start with the "Sheet10" tab selected, you can press CTRL+G, and in the window that pops up, type the following:

'Sheet9'!A1

Press "OK", and it will bring you to that sheet & cell. After you've done that once, you can simply double-click the recent links you've gone to, instead of typing in the whole link.

Answer 3

Press Ctrl+Page Up or Ctrl+Page Down to navigate between worksheets in Excel.

You can also right-click on the navigation arrows in the lower left-hand corner to display an Activate menu from which you can choose a worksheet.

Answer 4

Thanks for the excellent question and the excellent answers ! I am still amazed of the amount of hidden functionalities accessible only for the truly PRO users ...

Actually My answer is a complimentary one to the answer of Ethan with one major addition. I realize to late after the 5 minutes of the expiration for comments that my comment was not fully explained , thus:

Select first each table in the sheets ( Ctrl + Shift + Down , Ctrl + Shift + Left ) , format it as table tables ( Home , Format as table ) and named it according to this instruction http://office.microsoft.com/en-001/excel-help/rename-an-excel-table-HA010223129.aspx. - e.g select table Alt , JT , A , type the name of the table ...

Now enjoy: Ctrl + G , type <> , hit Enter ..- excel will jump to the <> you have predifined , much faster than the Ctrl + PageDown , Ctrl + PgUp and much more intuitive in my opinion than the "New Window" approach.