Excel Optimize

Problem: As shown in Fig. 28, you have a wide spreadsheet. There are headings at the top of the spreadsheet and there are several columns of labels at the left side of the spreadsheet. You also have monthly sales figures that extend far to the right. You need to be able to scroll through the sales fi gures while always seeing both the headings at the top and the labels at the left of the spreadsheet.

Strategy: Use the Freeze Panes command on the Window menu. Remember, to make the Freeze Panes command work, you must place the cell pointer in the correct location before using the command.
In the spreadsheet shown in Fig. 28, you might want row 5 and columns A through F visible at all times. You would then be able to scroll through the monthly fi gures, while always being able to see the customer information in the left columns and the month name information in row 5.

1. First, you want to scroll the worksheet so that cell A5 is in the upper left visible corner. You could try to do this with the scrollbars or the arrows, but this method will always work:
2. Scroll A5 out of view by hitting PgDn a few times and the Tab key a few times.
3. Hit the F5 key to bring up the Go To dialog.
4. Enter A5 in the Reference box and hit Enter. Cell A5 will now be the fi rst visible cell in the window.
5. Place the cell pointer in cell G6. Select Window – Freeze Panes. You will see a solid line between columns F and G and between rows 5 and 6.

Result: As shown in Fig. 29, you can scroll through any numeric data while being able to look at the row and column headings.

Gotcha: In Fig. 29, rows 1 through 4 cannot be accessed. You will have to unfreeze the panes to change those rows.
Gotcha: If you have many columns frozen at the left of the report and then make those columns wider, it is possible that the entire window will be fi lled with the frozen columns. You then have a situation where the arrow keys will move the cell pointer to other cells that you cannot see. The Address Bar and the Formula Bar will show you the active cell,but you cannot see it. You need to either make the frozen columns less wide, or use the Window – Unfreeze Panes command to unfreeze the panes.
Summary: Use the Window – Freeze Panes command to keep certain rows and columns visible at the top of the window as you scroll through the data. It is critical that you place the cell pointer in the fi rst cell that is not to be frozen before invoking the command. Commands Discussed: Window – Freeze Panes; Edit – Go To
Cross Reference: How to See Headings as You Scroll Around a Report