Working with multiple worksheets and workbooks

When working on spreadsheets, you often need to work on different parts of the spreadsheet at the same time. Perhaps you are building the capital expenditure requirements but you want to see how revenue is changing to match capex with activity. Or you want to see the consolidated income while working on one of the divisions.

In the video clip we show several ways to see different parts of a spreadsheet at the same time covering:

• Freezing the panes
• Splitting the screen so you can see row 10 while looking at row 1000
• Watching key cells as you move around your spreadsheet
• See two (or more) spreadsheet workbooks next to each other
• See two sheets WITHIN the same spreadsheet at the same time

To follow along go to 1.1

There are a number of ways to view different parts of a spreadsheet at the same time e.g. sales summary is in row 10 but the detail is in row 1000 and you don’t want to scroll up and down.

Splitting Panes

One option is to split the panes. In order to do this, one way (no longer available in Excel 2013) is to hover your mouse just above the scroll bars until the cursor changes to the image below (2 horizontal lines with arrows coming out of them).

Click and drag down and place the ‘split’ on the row you want. Something like the below will appear. Note the thicker line that now splits the screen. On the right you will now notice that there are 2 scrollbars. These are independent and you can move each ‘split’ up and down. Notice in the image below we are showing row 14 and row 39 next to each other.

You can do the same thing for the vertical scroll bar (hover just to the right of the scroll bars).

To remove the splits, double click on the lines and they will be removed.

Another way to split the screen is to click on a row, column or cell and click the SPLIT button under the VIEW tab

Freeze Panes

A slightly different option to split panes is freeze panes.

Where split panes allow you to scroll within both sections, freeze panes ‘freezes’ the top and/ or left section so that it is always viewed but you cannot move within it.

In order to achieve this, click on a row, column or cell and click the Freeze Page option under the VIEW tab

View Multiple Spreadsheets at the same time

If you need to view multiple spreadsheets at the same time, there is an easy way to resize them so that they all fit onto a single screen. If you have 2 or more spreadsheets open, click on VIEW, ARRANGE ALL and then your choice of arrangement (we prefer vertical or horizontal)

View 2 sheets within the same workbook at the same time

A more difficult option is how to view 2 sheets WITHIN a single spreadsheet at the same time. In order to do this you need to create a new window. With the spreadsheet open, click on the NEW WINDOW button under the VIEW tab as shown below

You will notice that it appears as if a new workbook is created and the name of the file now includes a :2 as shown below

This is NOT a new workbook, it is a new view of the SAME workbook. You can now use the Arrange All option to see them at the same time and click on any of the sheets. The nice thing is that if you work on either ‘’view’ the changes appear in both views. As a result, when you are done you have no issue with merging the spreadsheet as ‘they’ are already a single spreadsheet.

To go back to a single view, just close down one of them, preferably the one with a 2 or higher number behind its name.