When you have lots of data that goes down for rows and rows that don’t fit on one page, you have at least 3 options for getting Excel to keep the column headings in view whilst scrolling up and down:

  • Freeze Panes
  • Split Window
  • Excel Table

Freeze Panes

The Freeze Panes command, ALT + W + F + F, lets you freeze parts of a worksheet, usually column and row headings, so that you can scroll to other parts of the worksheet while the headings stay visible.

The instructions for Freeze Panes in Excel 2007/2010 are:

  1. Position the active cell based on what you want to freeze.
  2. On the Excel Ribbon choose VIEWFREEZE PANES
  3. Select from: (a) Freeze Panes, (b) Freeze Top Row, (c) Freeze First Column
  4. You’ll know the command has worked when you can see a thin black line that separates the different sections.
  5. When you scroll down and/or right you will see that the columns above and rows to the left of where you selected to freeze panes will remain fixed.

To Unfreeze Panes, go to the Excel Ribbon and choose VIEWFREEZE PANESUnfreeze Panes

Note: Usually pressing CTRL + HOME takes you to cell A1. With Freeze Panes active, pressing CTRL + HOME takes you to the cell just below and to the right of the black freeze pane lines. You can use your arrow keys or click your mouse to access cells inside the frozen panes.

Split Window

The Split Window command, ALT + W + S, is similar to freeze panes but gives you more flexibility.

You split the worksheet window into separate panes and scroll the worksheet in each pane to help you compare data from two separate places on the same worksheet.

The instructions for Split Window in Excel 2007/2010 are:

  1. Position the active cell based on where you want to split the window.
  2. On the Excel Ribbon choose VIEWSPLIT, this is a toggle button, so to turn off SPLIT you just click the same button again
  3. You’ll know the command has worked when you can see a think gray divider that separates the different sections

You can make the panes in a workbook window disappear by double-clicking anywhere on the split bar that divides the window. And you can reposition the split bar by dragging it to a new position.

Excel Table

Tables were introduced in Excel 2007 as an extension to Lists, and they are one of the most significant new features in Excel 2007. Unfortunately you can’t use Tables in Excel 2003 or older.

When you select a cell inside a large Table you can scroll up and down, and the column headings are kept in view. They appear in place of the normal Excel column headings (A, B, C, D etc.)

A Table is similar to a range but includes many new features. 

  • Tables will dynamically grow and shrink as you add more rows of data, so you can create a chart from the table and have the source range dynamically grow and shrink without writing any complex formulas.
  • You can quickly customize the table style to include banded rows and banded columns, again without any tricky conditional formatting formulas.
  • Each table has its own set of filters.

You can use the shortcut CTRL + T to insert a Table. Alternatively you can type ALTNT.