Freezing a Middle Row in Excel While Scrolling Above/Belowby
Reader Fiona posed an interesting question on my article regarding taking the pain out of freezing panes in Excel. She asked: "I want to freeze a row in the middle of a sheet but have the rows above it and below it to scroll together. Is this possible?" It is indeed, which I'll demonstrate in this article.
The technique I'm going to demonstrate for Fiona is nuanced, so take your time and read each step carefully. Unfortunately, this technique currently only works in the Windows version of Excel and not on a Mac. Also, make sure that you only have one workbook open in Excel. The technique involves creating three windows from a single workbook, so if you have other workbooks open at the same time, the process could become confusing.
The first step is to create an example workbook, as shown in Figure 1:
- Open Excel with a single blank workbook open. Ideally, this workbook should only have one worksheet as well. This isn't mandatory, but will keep your screen clean until you master these steps.
- On a blank worksheet, click in the Name Box, type A1:A50, and then press Enter to select a range of 50 cells.
- 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.
- Type A51 in the Name Box and press Enter to select what will become our middle row.
- 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.
- Click in the Name Box, type A52:A101, and then press Enter to select another block of 50 cells.
- Type the words Bottom Rows, and then press Ctrl-Enter.
Figure 1: Follow these steps to create a simple example to follow along with.
At this point we have sample data that will allow us to ultimately "freeze" row 51 on the screen, while we simultaneously scroll rows 1 to 50 and 52 to 101, as illustrated in figure 2.
Figure 2: Our goal is to freeze row 51 while simultaneously scrolling rows 1-50 and 52-101.
Here's how to accomplish the effect shown in Figure 2:
- Choose View, and then click New Window twice, as illustrated in figure 3. This will display the current worksheet in three separate windows, although you will likely only see one window at a time at this point. The New Window command appears on the Window menu in Excel 2003.
- On the View tab (or Window menu) choose Arrange All. Within the Arrange Windows dialog box choose Horizontal, select Windows of Active Workbook, and then click OK.
- At this point all three windows will appear onscreen. Notice that each title bar includes 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 rows labeled Top Rows appear in window 1
- The row labeled Middle Row appear in window 2
- The rows labeled Bottom Rows appear in window 3
Figure 3: Follow these steps to view the same worksheet in three separate windows.
To do so, we'll need to click within each window and carefully position the cursor, as illustrated in figure 4:
- 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 the Name 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.
Figure 4: Follow these steps to position the cursor to a specific location in each window.
- The next step is to synchronize windows 1 and 3, as illustrated in figure 5. To do so, click anywhere within window 1 to activate that window. On the View tab choose View 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.
Figure 5: Follow these steps to view windows 1 and 3 side-by-side.
- At this point, you should experience one of two scenarios:
- A single window fills the screen: In this case choose Arrange All on the View tab. Within the Arrange Windows dialog box choose Horizontal, select Windows of Active Workbook, and then click OK.
- 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.
- 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:
- 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.
- 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.
- 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.
Figure 6: 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.
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.