Objective: To improve productivity by ensuring that you can see what you want to see when working with Excel.
In an upcoming EZ Guide we will look at some of the techniques available to help you print an Excel workbook in the way that you wanted. This will include switching between the three standard Excel views: Normal, Page Layout and Page Break Preview.
But first, we will look at a range of features that help you make the required content visible when viewing, rather than printing, your Excel workbook.
If you have a worksheet that extends beyond a single screen, you will often want to keep row and column headings visible as you scroll down or to the right. The View Ribbon tab, Window group includes a set of commands for managing what is displayed in the Excel window.
We’ll start with the Freeze Panes dropdown. This includes three options. Two are very straightforward. You can choose to freeze the top row of the window or the leftmost column of the window. Note that this applies to the area of the worksheet currently visible in the window.
If you have scrolled down so that rows 13 downwards are visible, then freezing the top row will freeze row 13 and you will not be able to scroll to display rows 1 to 12 (although you can use the cursor keys to display the contents of individual cells in those rows in the formula bar). If you already have either of the basic freeze Panes options set then applying the other Freeze Pane setting will cancel the existing one and replace it with the new option.
The main Freeze Pane option freezes the columns to the left of the currently selected cell or cells and also the rows above the current selection. If you already have a Freeze Pane option set, you will need to Unfreeze Panes before you can apply the Freeze Pane setting.
It’s worth noting that an Excel Table will, by default, display the Table header row in place of the normal column letters when you scroll down through the Table without the need to Freeze Panes.
At first glance, the Split option might seem to work in a similar way to Freeze Panes in that the window is split at the position of the top left-hand corner of the currently selected cell or cells. However, unlike a frozen pane, you can scroll independently in each of the sections of a split window This would, for example, allow you to compare multiple different sections of your workbook.
Here, we are looking at rows 241 to 250 in the top section and rows 1460 to 1470 in the bottom section, and we have also split our view vertically to show columns H to I beside columns S to V. Note that the vertical and horizontal scroll bars are split into two separate areas.
Click on Split again to remove an existing Window split.
Multiple windows for multiple workbooks
Excel 2013 introduced an important change in the way that Excel works with multiple windows. Prior to the 2013 version, when you already had a workbook open, creating a new workbook, or opening an existing one, would open an additional window within the main Excel window to you had one Ribbon bar but the document window could display multiple windows.
This was known as Multiple Document Interface (MDI). From Excel 2013 onwards, this behaviour was changed to open each document in its own instance of Excel: Single Document Interface (SDI). The main advantage of the SDI approach is the ability to easily display different workbooks on different monitors since each document is displayed in an independent Excel window.
If you do want to display multiple workbooks next to each other on a single monitor, then there are specific options that make this easier. You can use the View Ribbon tab, Window group, Arrange All command to display all open windows either side-by-side (Vertical)or above each other (Horizontal):
Recent versions of Windows include the Snap feature which allows you to do the same in Excel 2013 and later by dragging a window to the left or right to fill that half of the screen, then clicking on the other Excel window that you want to fill the other half of the screen. You can also use the Window key with the left or right arrow key to shrink the current window to the left or right half of the screen respectively.
Multiple windows for multiple worksheets
The procedure for displaying multiple worksheets of the same workbook is similar but requires an additional step. In order to display different sheets in different workbooks, you first need more than one window open for the same workbook.
You can open multiple windows on the same workbook by using the New Window command in the Window group of the View Ribbon tab. The header of the new window will display :2 next to the workbook name. You can then use the Arrange All command to arrange these windows horizontally or vertically and choose which worksheet each displays.
Note that if you have multiple workbooks open but you just want to look at the windows for the current workbook you need to turn on ‘Windows of active workbook’ otherwise all Excel windows, regardless of the workbook, will be squeezed into your screen. Closing an individual window of a workbook will not close the workbook itself if other windows on that workbook are still open.
There is another way to keep very specific details from another worksheet or workbook visible without opening a separate Excel window. The Watch Window command in the Formula Auditing group of the Formulas Ribbon tab opens a dockable window within Excel that allows you to select specific cells or ranges of cells in other worksheets or other open workbooks. Watched cells in other workbooks will only be displayed if that workbook is open.
As well as displaying the content and formula of watched cells, the Watch Window allows you to select any of the cells displayed by double-clicking on them within the Watch Window itself. In this example, we have docked the Watch Window at the left-hand side of the screen and added one cell from another sheet in the same workbook, and a set of cells from a different workbook:
In the previous EZ guide to printing, we looked at the use of the Excel Camera or the equivalent Paste, Linked Picture command to assemble dynamic pictures of ranges of cells from different worksheets onto a single sheet for printing. This technique would also serve as another method of being able to view content from one sheet whilst looking at a different sheet.
EZ guides – where next?
As we embark upon the next set of EZ Guides, it would be very helpful to hear of any particular areas that you would like us to cover. Please add a comment with any suggestions that you may have.
About Simon Hurst
Simon Hurst is the founder of technology training consultancy The Knowledge Base and is a past chairman of the ICAEW's IT Faculty.