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.

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

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

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

Comments

[Asaf Gilad]

You are smart ...I've read so many books and somehow... the message didn't pass :) thank you very much.

[Yordan Georgiev]

+1 + to combine , if you are on Windows 7 or 8 you could also press WinLog + <<Num>> , where <<Num>> is the number of the pinned application on the taskbar ..

[Yordan Georgiev]

Actually I think your answer is the best one providing one has first named the tables : office.microsoft.com/en-001/excel-help/…. - e.g. select table Alt , JT , A , type the name of the table ... After this Ctrl + G , type <<TableName>> , hit Enter ... - much faster than the Ctrl + PageDown , Ctrl + PgUp

[G-Man Says 'Reinstate Monica']

But (Ctrl)+(Page), (Alt)+(Tab), and (right-click) → "Activate" all remember your current position on the sheet — and even if you have cells selected.  This answer requires that you remember where you were, or at least can specify what cell you want to go to.  This could be very disruptive of work flow.

[Aganju]

I think he said in the question that he knows that and it is not good enough .