Switching between Excel Workbooks

Picture of operating sitting at Switchboard

When working with one Excel file (called a workbook) at a time, it is pretty easy to stay organized and move around easily. Opening a second Excel file can complicate things a little bit. Trying to move between multiple Excel worksheets can get even more problematic, especially if you need to look back and forth quickly. Then, make it more difficult by moving within the worksheets within the different workbooks.

An example of this is when you are working on your budget from the last year or two to create your upcoming budget. If you have worksheets from several departments within each budget file, it can get really confusing. You can have all of the workbooks open but just look at one at a time. Or, you are able to view the workbooks side by side, tiled, cascaded, etc.

There are several different ways to move around and switch between files. The great thing about many of these methods is that they can also be used to move between any of the open files on your computer. I have highlighted the most popular ways below.

Switch Windows

One option to switch between the open Excel files is to use the View tab on the ribbon. Go to the View tab and click on the Switch Windows button. Pick the workbook you would like to move to from the list of available open files. There will be a check next to the file that you are currently viewing for easy reference. See Figure 1.

View Ribbon

Figure 1

Personally, I don’t like having to click on the View tab and then a button and then a file from the list. I really wanted a faster way. By adding the Switch Windows button on the Quick Access toolbar, I was able to cut down on my clicks. I cover how to do that next.

Switch Commands – Quick Access toolbar

The Quick Access toolbar is located at the top left of the window by default. It usually has the save, undo, and redo buttons. You can right-click it or right-click anywhere on the ribbon or any of the tabs to get a pop-up menu. Choose “Customize Quick Access Toolbar” from that pop-up menu. See Figure 2.

Customize Quick Access Toolbar

Figure 2

The following dialog box will appear. See Figure 3.

Customize Quick Access Toolbar dialog box

Figure 3

Click the “Choose commands from” drop-down list (near the top center) and select “View Tab”. This will have all of the buttons and more from the View tab that you can add to your Quick Access Toolbar.

In the list, scroll down and find “Switch Windows”. Then click the “Add>>” button in the middle to move that command to the right-hand list. This list (on the right) is what you want to show on the Quick Access Toolbar. If you want to change the location of the button on the toolbar, you can move it up and down. (While you are here, you might want to explore to see if there are other commands you’d like to include.) Click OK when you are done. See Figure 4. See Figure 5 to see what the Quick Access toolbar will now look like.

Updated toolbar

Figure 4

Switch Workbooks button

Figure 5

Switch Windows – Keyboard Shortcut

To move back and forth between any open windows (of all file types and browsers), you can use the combination ALT + TAB. You can hold the ALT key down and press TAB to cycle through all of the files until you get to the one you want.

Side by Side (Synchronous scrolling)

If you have two Excel workbooks open at the same time, you can view them in Side by Side mode. To switch to this mode, click the View tab and then choose the “View Side by Side” button. See Figure 6. It is best if you are at the top of each document when you switch to this mode since the “Synchronous Scrolling” is automatically turned on when you click the “View Side by Side” button.

Synchronous Scrolling can be useful to comparing two files since it moves both files at the same time. It sometimes is more annoying than useful, so you can turn it off by pressing the “Synchronous Scrolling” button again.

Synchronous Scrolling button

Figure 6

Arrange All

After switching over to “View Side by Side” you can choose how the windows are arranged. You change the arrangement by clicking on the “Arrange All” button on the View tab. See Figure 6 above. The Arrange All dialog box opens and letting you choose from Tiled, Horizontal, Vertical, or Cascade. See Figure 7.

Arrange Windows dialog box

Figure 7

Depending on how your Excel workbooks are laid out, Tiled and Horizontal will most likely be useful. Cascade is not very good for comparing or looking at two workbooks at a time. Usually, Tiled and Vertical will give the same layout on your screen.

Switching Sheets

Within each of your Excel workbooks, you may have multiple worksheets (or sheets). To switch from one sheet to another, click on the tab of the sheet you would like to switch to. The tabs are located at the bottom of the screen near the left. If you are on “Sheet 1”, you can click on the tab for “Sheet 2” to view the contents. See Figure 8.

If you would like to switch between sheets using keyboard shortcuts, move through the different worksheets with the CTRL + PAGE UP or CTRL + PAGE DOWN keyboard combinations.

Worksheet tabs

Figure 8

Additional Excel Shortcuts

Here are several other ones also, which you might find useful:
CTRL + ; – Enters the current date into the active cell
CTRL + A – Selects the entire worksheet
ALT+F1 – Creates a chart of the data in the current range
SHIFT+F3 – displays the Insert Function dialog box
SHIFT+F11 – Inserts a new worksheet
CTRL+HOME – Moves to the beginning of a worksheet
CTRL+SPACEBAR – Selects an entire column in a worksheet
SHIFT+SPACEBAR – Selects an entire row in a worksheet
F6 – Move to the next worksheet pane in a worksheet that has been split (by using the Split command on the Window menu)
SHIFT+F6 – Move to the previous worksheet pane in a worksheet that has been split
CTRL+F6 or CTRL+TAB – Move to the next workbook window
CTRL+SHIFT+F6 or CTRL+SHIFT+TAB – Move to the previous workbook window

Image credit: https://www.flickr.com/photos/matman/

Angela Nino - Versitas Editor

Leave a Reply

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