Written by Maggie – Sat 29 Dec 2012
In this post, our Excel trainer Maggie runs through using large files in Excel.
In many cases, you might find it helpful to work with different sections of your worksheet at the same time.
For example, you might want to keep the data in row 4 visible while you scroll down to look at information located in row 35. You do this by applying split bars.
If you already know how to use split bars but are struggling with other aspects of Excel, why not try our Advanced Excel Training.
When you apply split bars to a worksheet, Excel creates identical copies of the worksheet side by side. If you apply either a horizontal or vertical split bar, you can scroll within one pane while the other pane remains stationary.
Although the Split command can be accessed from the View Menu, you can also manipulate split bars with the mouse using the split boxes. You can move between the different panes by simply clicking the pane in which you want to work. Because each pane is a view of the same worksheet, a change in one pane means a change to the worksheet.
The VIEW tab contains the option to SPLIT. However, if you do choose to split using this option, Excel will split the Window at the current location, i.e. the cell you are currently in. You will probably end up with a horizontal and vertical split.
To Split a Window horizontally, place the mouse over the Split Symbol and drag it half way down the vertical scroll bar. To Split a Window vertically, place the mouse over the Split Symbol and drag half way across the horizontal scroll bar.
Examples of Split Windows
Notice two horizontal scroll bars are available. In the example above, columns A, B, C can be seen at the same time as columns K-P.
Notice two vertical scroll bars are available. In the example above, rows 1, 2, 3 can be seen at the same time as rows 9-28.
For other beginner's Excel tips and tricks, try our post on 3 Hidden Gems in Excel.
Another way to divide your worksheet into panes is by freezing sections of the worksheet. Freezing panes is useful when you are working with large tables because you can hold horizontal and vertical labels static while you move through the data.
Note that Excel will freeze the Row or Column that is currently visible in the screen. Therefore, if you need to freeze Row 1 or Column A you should move to this area of the Workbook before choosing either of these options.
The most important thing when freezing rows and columns is the current location in the document. In the example below, the first row and first two columns will be frozen. This is based on the current location, highlighted in yellow.
Often, it is useful to view more than one worksheet at a time. You can arrange worksheets on your screen so that you can view them simultaneously. Once you have more than one copy of your worksheet window open, you can select different worksheets to view from each worksheet window, and then arrange the windows to best suit your needs.
All this would be very different on an iphone or ipad. Take a look at our post on the recent announcement of Excel for iOS.
The Arrange Windows dialog box provides options to let you set up your data on the screen. Here these options are summarized:
If you're new to Excel and would like to learn more, try our Beginner's Excel Course.
ITIL®, PRINCE2®, PRINCE2 Agile®, MSP®, M_o_R®, P3O®, MoP®, MoV® courses on this website are offered by The Knowledge Academy, ATO of AXELOS Limited. ITIL®, PRINCE2®, PRINCE2 Agile®, MSP®, M_o_R®, P3O®, MoP®, MoV® are registered trade marks of AXELOS Limited. All rights reserved.