Watching what happens to results when you change inputs
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. These can be used in building a model or in running sensitivities.
To follow along go to 15.3 and 15.4 (this is a repeat of one of the tips)
If YouTube doesn't work, click here
The watch window allows you to monitor what is happening in certain cells in the spreadsheet no matter where you are. It can be found next to the Auditing Toolbar under the Formulas tab.
When you click on it, a separate dialogue box will appear. You can now click on any cell/s and click ‘Add Watch’ and it will be added to the list.
You can now view the effects of any changes you make on the cells that interest you.
One option is to split the panes.
In order to do this, one way (no longer available in Excel 2013 or above) 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).
Another way to split the screen is to click on a row, column or cell and click the SPLIT button under the VIEW tab
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.
It is now possible to see what happens to results in row 45 when you make changes in B13.
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).
If the spreadsheets are linked, you will be able to see what happens to one spreadsheet when you make changes to the other one.
View 2 (or more) sheets within the SAME spreadsheet 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.