Spreadsheets: Worksheet Toggle Solution Revealed

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

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.


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