Excel How-to: Split and Freeze Cells

excel logoThe problem:

  • You have a very large spreadsheet with descriptive headers in the first row and in the first column. As you scroll down or across the spreadsheet you lose sight of those headers, so you can no longer tell what you're looking at.

The solution:

  • Use the split panes and freeze panes feature in Excel to lock the header rows and columns so they stay in view.

The examples shown are what you would see using Excel 2003 running on Windows XP. Different versions of Excel on different operating systems may look slightly different than this, but the basic functionality should be the same.

You can download the sample spreadsheet here, (right click on the link and select 'Save target as or Save link as')

Let's take a look at a typical spreadsheet. In the example below you can see a header row and column giving you descriptions of the data:

 

excel freeze split panes 01

 

Unfortunately, as you scroll down and/or across the page you quickly loose sight of those descriptions, and your data turns into a mass of meaningless numbers:

 

excel freeze split panes 02

 

Fortunately, this is very easily remedied. I'm going to show a couple of ways to do this (unlike most of my examples), because the option you choose will vary depending on what you want to do (or see). First, I'm going to show a very quick and easy way to freeze both the header row and column. Start by selecting the cell that is immediately below and to the right of the headers, in this case cell B3:

 

excel freeze split panes 03

 

Now go to the main menu and select Window -> Freeze panes:

 

excel freeze split panes 04

 

Presto, the rows and columns above and to the left of the cell you selected are now locked. Now when you scroll down or across you will still be able to see those rows. To unfreeze the sheet simply go back up to the menu and select Window -> Unfreeze panes. Go ahead and do that now, so that we can move on to the other example.

The next method allows you to split the screen into horizontal and/or vertical panes. A frozen pane is not adjustable, you freeze the columns/rows where it was initially selected. A split pane is adjustable, so that you can resize the panes as you work. While you can also use the menu to do this, I think it is easier to use the split box that is at the top of the vertical scroll bar, and at the right end of the horizontal scroll bar:

 

excel freeze split panes 05

 

Let's start by splitting the screen horizontally. Go to the top of the vertical scroll bar and click on the split box. While holding down the mouse button drag down to where you want the screen to split:

Tip - if you move your mouse over the spreadsheet while dragging it will 'click' on the lines between rows.

 

excel freeze split panes 06

 

When you let go of the mouse button you will have an adjustable bar where the screen is split. You can adjust this by simply clicking on it and dragging with your mouse. Let's split the screen again, this time vertically. Grab the split box at the far right of the horizontal scroll bar, and drag it over to the line between columns A and B:

 

excel freeze split panes 07

 

When you release the mouse button you will have the same effect as our first example, except this time you can adjust the location of the splits if you want. You can always freeze the panes where you've got them split by going to the main menu and selecting Window -> Freeze panes. You can remove both splits by selecting Window -> Remove split, or you can remove an individual split by clicking on it and dragging it to the edge of the screen.

Bytes: