To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips page.
Reader Danny D wins a copy of the book Learn Excel 97-2007 From MrExcel for her question: “How can you toggle between worksheet tabs that are in the same file?”
Using keyboard navigation is definitely faster than using the mouse, so I appreciate the opportunity to share a couple of tricks about fast ways to navigate between worksheets in the same workbook.
Navigating Between Applications, Workbooks
Many people realize that Alt+Tab will move you through all open applications on your computer. For example, pressing Alt+Tab might move from Excel to Outlook.
If you want to navigate between open Excel workbooks, you can use Ctrl+Tab. Pressing Ctrl+Tab will take you to the next open workbook in the Switch Windows dropdown list. Pressing Ctrl+Shift+Tab will take you to the previous open workbook in the Switch Windows dropdown list.
Purists will note that Alt+Tab offers one trick that Ctrl+Tab doesn’t. Say you have 10 workbooks open. If you use Ctrl+Tab to move from Workbook 1 to Workbook 5, pressing Ctrl+Tab again will move you to Workbook 6. Instead, if you use Alt+Tab+Tab+Tab+Tab to move from Workbook 1 to Workbook 5, a single press of Alt+Tab will move you back to the last workbook, which in this case is Workbook 1. In other words, Alt+Tab will always toggle you back to the previous window. More on this later.
Navigating Right and Left Through Worksheet Tabs
If you want to navigate from worksheet to worksheet within the same workbook, you can use Ctrl+PgDn and Ctrl+PgUp. The Ctrl+PgDn command will move to the next workbook to the right. The Ctrl+PgUp command will move to the previous worksheet to the left of the current worksheet.
I use Ctrl+PgDn and Ctrl+PgUp all the time. The problem, though, is when you have many worksheets. Say your workbook contains a Summary sheet and then 12 worksheets named Jan through Dec. If you need to move from Oct to Summary, it requires you to press Ctrl+PgUp ten times or to press and hold Ctrl+PgUp and wait for Excel to scroll up to the first worksheet.
The Hidden Solution: Put the “Other” Worksheet in a “New” Window
I am sensing that Danny wants to use Ctrl+Tab or Alt+Tab to toggle between the Summary worksheet and the October worksheet in the same workbook. There is a very clever way to do this. In Excel 2007 or Excel 2010, go to the View tab and choose New Window. In Excel 2003, open the Window menu and choose New Window.