Spreadsheets: Worksheet Toggle Solution Revealed

The "hidden" solution to the puzzle of toggling between worksheets in the same workbook is explained.


Editor’s Note:

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.

Initially, it will look like nothing happened. There is one very subtle clue, though. Look in the title bar at the top of your Excel window. You will notice that there is a “:2″ that appears after the workbook name, as shown in Figure 1. If you look in the Switch Windows dropdown (Figure 2), you will see that there appear to be two versions of your file in the list of open windows. There is a CFO8.xlsm:1 and a CFO8.xlsm:2 in the list.

You do not really have two copies of the file open. Think of it as having two different cameras that are pointing at the same workbook. Switch to the “:1″ version of the file and display the October worksheet. Switch to the “:2″ version of the file and show the Summary worksheet.

Fig. 1

MrExcel 8 01

Fig. 2

MrExcel 8 02

 

 

 

 

 

 

You can now use Ctrl+Tab or Alt+Tab to move from the :1 to :2 view of the file. If you need to toggle between Summary, Oct, and Q4, you could simply click the New Window icon again to add a “:3″ view of the file.

Creative Uses for Two Windows

I occasionally show this trick in my Power Excel seminars, and members of the audience often latch on to the command with several clever ideas.

First, try using Arrange All, then Vertical. You can now see the Summary worksheet in Window 1 on the left and the October worksheet on Window 2 on the right as shown in Figure 3 (below).

Fig. 3

MrExcel 8 03

If you have Excel 2003 or newer, you can use the Synchronous Scrolling option after choosing Compare Side by Side to have the two worksheets scroll in sync.

Closing the Second Window

Once you use New Window, the second view of the file will hang around forever. You can save the workbook, close the workbook, and open it a month later and both the :1 and :2 views of the file will appear. If you want to get rid of the :2 version of the file, use the “Close Window” X as shown in Figure 4 (next page). This will cancel the second view of the file but still leave the workbook open.

Fig. 4

MrExcel 8 04

Bill Jelen is the author of 32 books, including Pivot Table Data Crunching, and is the host of MrExcel.com. Suggest a topic for his next column at CFO.com’s Spreadsheet Community Center (below right) and if your suggestion is chosen, you’ll receive a copy of one of Jelen’s new books.

Discuss

Your email address will not be published. Required fields are marked *