Save content
Have you found this content useful? Use the button above to save it to your profile.
thinkstock_istock_crunched_ice

Excel tip: Freeze a row and scroll above or below

by
19th Feb 2015
Save content
Have you found this content useful? Use the button above to save it to your profile.

Freezing panes in Excel can be a frustration. Once you’ve mastered the basics, David H Ringstrom demonstrates a useful variant on the technique.

One of my most popular items in the past couple of years attempted to take the pain out of freezing panes.

The technique is fairly simple, and allows you to scroll to the right or down, while frozen columns or rows remain in place so that you can review worksheet headings or the initial columns as you move around the data. When you no longer need the rows or columns locked in place, you can unfreeze them.

If you are still troubled by how to make use of this useful trick, refer back to my original article on AccountingWEB.com (follow the link above). But I got a follow-up question from a member who wanted to know if it was possible to freeze a row in the middle of a sheet but have the rows above it and below it to scroll together.

It is indeed possible, and this article will show you how.

The technique currently only works in the Windows version of Excel and not on Macs. You must also only have one workbook open in Excel as the workaround is based on creating three windows in a single workbook.

Because of the complications, make sure to follow the step-by-step instructions carefully:

  1. Fire up Excel with a single blank workbook visible. The workbook should only have one worksheet as well. This isn't mandatory, but will keep your screen tidy while you attempt the next steps.
  2. On a blank worksheet, click in the Name Box, type A1:A50, and then press Enter to select a range of 50 cells.
  3. Type the words Top Rows, and then press Ctrl-Enter. If you slip up and press Enter out of habit, simply type Top Rows on the next row and then press Ctrl-Enter again. The words Top Rows should now appear in cells A1:A50.
  4. Type A51 in the Name Box and press Enter to select what will become our middle row.
  5. Type the words Middle Row in cell A51 and press Enter. We're only filling one cell here, so there's no need to press Ctrl-Enter.
  6. Click in the Name Box, type A52:A101, and then press Enter to select another block of 50 cells.
  7.  Type the words Bottom Rows, and then press Ctrl-Enter.

Follow these steps to create a simple example as a basis to work with:

Creating three data sheets to construct a frozen middle row view

This procedure has created a set of sample data we will use to "freeze" row 51 on the screen, while simultaneously being able to scroll through rows 1-50 and 52-101, as shown here:

The exercise will freeze row 51 while simultaneously scrolling rows 1-50 and 52-101.

So, how do you get from the test data sample to the scrolling arrangement shown above? Here’s how:

  1. Choose View and click New Window twice as shown below. This presents the worksheets in three separate windows - although just one will be visible at this point. The New Window command appears on the Window menu in Excel 2003.
  2. Either via the View tab or Window menu choose Arrange All. From the Arrange Windows dialogue box select Horizontal, then Windows of Active Workbook, and click OK.
  3. All three windows will appear onscreen. Each title bar will show the file name appended by a window number. If your workbook is named Book 1, then window 1 would be titled Book 1:1. Our ultimate goal is to have:
    • The Top Rows should appear in window 1
    • The row labelled “Middle Row” will be in window 2; and
    • The Bottom Rows are in window 3.

The next steps will view the same worksheet in three separate windows.

To manage the next step, you will need to click within each window and carefully position the cursor following this sequence:

  •  Window 1: Press Ctrl-Home to move the cursor to cell A1.
  • Window 2: Type A100 in the Name Box and press Enter. Next type A51 in theName Box and press Enter to make row 51 become the first row. In order to have row 51 appear as the first row, it's important to first hop to a row that won't appear onscreen the same time as row 52, so in this case I chose cell A100.
  • Window 3: Type A100 in the Name Box and press Enter to move the cursor to row 100. Next type A52 in the Name Box and press Enter to make row 52 become the first row.
  • To synchronize windows 1 and 3, click anywhere within window 1 to activate that window. On the View tab chooseView Side by Side, select window 3 from the dialog box that appears and then click OK. To clarify, if your workbook is named Book1, then you'll choose Book1:3 from the Compare Side by Side dialog box. This is what this step looks like:

Follow these steps to position the cursor to a specific location in each window.

  1. At this point, you should experience one of two scenarios:
    1. A single window fills the screen: In this case choose Arrange All on theView tab. Within the Arrange Windows dialog box choose Horizontal, selectWindows of Active Workbook, and then click OK.
    2. Only windows 1 and 3 are visible onscreen: Click on the title bar of window 3 and hold down your left mouse button as you drag this window to the bottom of the screen, which will reveal window 2.
  2. Once you can see all three windows, the next step is to resize window 2, and then rearrange the windows so that the windows appear in numeric order onscreen in ascending order:

a.       Use your left mouse button to resize window 2. To do so, click any cell within window 2 to activate that window, and then position your cursor at the bottom edge of the window. When a double-headed arrow appears, hold down your left mouse button and resize the window so that only a single row is visible within the window.

b.      Manually rearrange all three windows onscreen by dragging the title bars of the window with your left mouse button until window 1 appears first, window 2 second, and window 3 third. You may wish to make windows 1 and 3 larger so that ultimately all three windows fill the screen.

  1. Click in window 1 to activate it, and then either use your mouse or the arrow keys to scroll the worksheet down. Window 3 should scroll simultaneously, while window 2 remains frozen onscreen.

Windows 1 and 3 now scroll simultaneously, while window 2 remains stationary.

At this point you can save the workbook to preserve this window arrangement. The windows will reappear automatically when you open the workbook. Or, if you only need this window arrangement temporarily, you can press Ctrl-F4 to close any open window, or click the Close button for a given window, which appears as an X in the upper right-hand corner of the window.

Replies (0)

Please login or register to join the discussion.

There are currently no replies, be the first to post a reply.