Community Newsletter: Q&A forum: 8/06/04 Freezing those Excel title rows

by: Lee Koo (ADMIN) August 5, 2004 1:30 PM PDT

Like this

2 people like this thread

Staff pick

8/06/04 Freezing those Excel title rows

by Lee Koo (ADMIN) ModeratorCNET staff - 8/5/04 1:30 PM

Question:

In Excel 2002, I want to lock in my title row so that it remains visible while I scroll down to see the rest of my data. Any ideas?

Submitted by: : Conrad F. of Walnut, California

Answer:

No problem. There are two ways:

1. Freeze Panes (Alt+W, F). Position your cursor on the line below what you want to keep. If you want the entire row, put the cursor in column A. If you want some columns to also be frozen, position the cursor one column to the right. Click Windows (Alt+W). Then click Freeze Panes (F). To undo the freeze, do Alt+W, F again. The position of the cursor doesn't matter when unfreezing.

2. Split (Alt+W, S). Cursor positioning and undo are both exactly the same
as for freezing.

There are several differences between the two.

1. You can reposition the split crossbar as you do windows (position mouse over a bar, right-click and hold, move mouse, unclick). You cannot move the frozen sections without unfreezing/freezing all over again.

2. Freeze actually immobilizes everything above and to the left of the cursor. You can use the arrow keys, page-up, and so on to go up or left as far as you want (watch the Name Box), but you won't see the frozen section move.

3. Split allows you to move the two to four sections almost separately. Click inside one section. Move left/right. Only the two sections that are on top of each other will move, not the other two. Similarly, moving up or down will move only the two side-by-side sections, not the other two.

If you want to do this in a Visual Basic macro:

1) Freeze:
Range("B2409").Select
ActiveWindow.FreezePanes = True
2) Split:
With ActiveWindow
.SplitColumn = 3
.SplitRow = 11
End With


Submitted by: Mark S.

Thank you for a great submission Mark and thank you all that submitted a response this past week! Below I have listed a list of honorable mentions, so please check them out. I encourage all of you who have more solutions or questions on this topic to post below in this thread. This way we can all learn more.

Thanks again everyone!
-Lee Koo
CNET Community

****** HONORABLE MENTIONS**********

Answer:

here are two ways to achieve what you want. First is by using split
command or second is called freeze panes. Both commands are available
from Window menu bar in Excel. However, please note that you cannot
apply both freeze and split at once.

Splitting and freeze panes can be done in three methods,
1. Lock top rows only or
2. Lock left columns only or
3. Lock both top and row simultaneously.

To lock rows, select the row below where you want the split to appear.
To lock columns, select the column to the right of where you want the
split appear.
To lock both rows and columns, click the cell below and the right of
where you want the split to appear.

Next on the Window menu, click Split or Freeze panes.

Unlike freeze panes, split will allow you to resize the split by
dragging the split border. So, if you need to resize freeze panes, you
need to unfreeze the panes first by using Unfreeze panes in Window
menu. If you want to remove split pane, also by using Remove Split in
Window menu.

In your case, you select row below your header by clicking on the row
number. Any rows above the selected row will remain appear eventhough
you scroll down.


Submitted by: Mohamad K. of Kuala Lumpur, Malaysia

**********************************************************************

Answer:


There are actually two ways of accomplishing this in Excel. The Freeze Panes item on your Window menu (in Excel), and the Split item on the same menu.

While the split feature allows some extra versatility, it also comes with some issues, but I believe that the Freeze Panes item is actually what youre looking for.

Assuming that your column titles are all in row 1 and any row titles are in column A, you would make sure both column A and row 1 were visible, click on cell B2, and then choose Freeze Panes from the window menu. This would mean that neither column A, nor row 1 would ever scroll off the sheet. You are not stuck to working with just those ranges, though. Consider the following examples:

-You want the top 5 rows (but dont care about columns)
-make sure those rows were visible at the top of your sheet, click in A6, then freeze the panes. Rows 1-5 will stay visible

-You want the leftmost 5 columns (but dont care about rows)
-make sure those columns are visible at the left of your sheet, click in F1, then freeze the panes. Columns A-E will stay visible

-You want the rows 3 & 4, and columns B & C to stay visible
-scroll your worksheet so that column A drops off the left side, and so that rows 1 & 2 drop off the top. (The upper left most cell should now be B3)
-click in cell D5
-freeze your panes

The important thing to note, is that the freeze panes always freezes the range immediately above and to the left of the active cell.

Split works very similar to freeze panes, except that it doesnt completely lock them. In last example above, you would end up with 4 live windows. The biggest advantage is that you can scroll your header rows (or columns) if you need to, but I find that, more often that not, I forget to click on the pane that I want to work in, and end up scrolling the wrong window! I use this feature almost exclusively for setting my freeze panes range if Im way down the sheet, and dont want to lose where Im at in the data.

Just for reference, the Split feature can also be accessed by dragging the tiny flat bar just over the vertical scroll bar arrow (for rows), and just to the right of the horizontal scroll bar (for columns)

I hope this helps,


Submitted by: Ken P. of Nanaimo, British Columbia, Canada

**********************************************************************

Answer:

This is one of the most useful, easy to implement, and LEAST documented
features of Excel (any version). If you want to keep just the top row
visible, click on the cell that is in the first column & second row (i.e.,
cell A2), then click on the "Window" menu, then select "Freeze
Panes". This will keep the top row visible all the way down the
document. This also works for the first column, by clicking on the cell in
the top row, second column (B1), or for both the top row and first column,
by clicking on the cell in the second row, second column (B2, this is the
setting I use for most of my data sheets). What this feature does is keep
every cell above and to the left of whichever cell you have highlighted
when you click on "Freeze Panes" visible wherever you are on the sheet, so
you can click somewhere in the middle of the sheet if you want to and keep
all the rows above and to the left visible (the only limit is the number of
rows and columns visible at any one time on your screen, which depends on
your screen resolution). This setting stays in place until you click
"Window" menu, then click "Unfreeze Panes" (you can do this from anywhere
in the spreadsheet, you don't have to go back to the cell you turned the
setting on from). If you save a document with "Freeze Panes" on, the
setting will be saved as well (I use this on our client list, to keep the
client name in the left column and the contact information category in the
top row). I hope this helps!

Submitted by: Dean L. of Colorado Springs, CO

**********************************************************************

Answer:

To keep your title row visible while scolling down, highlight the entire row below your title row by clicking the numbered cell on the left. Then on the menu bar, click Window, then click Freeze Panes. To turn this off, click Window, then click Unfreeze Panes. If you don't see the Freeze Panes option, you may need to expand the menu by clicking or hovering over the "Expand" button at the bottom of the menu.

You can also keep a column visibile while scrolling right by highlighting the entire next column instead of a row and click Window, Freeze Panes. If you want both a row and column, click in the cell that is directly below your title row and to the right of your title column and click Window, Freeze Panes. Note: You can only ever have one "frozen pane" at a time.

Examples:
(1) If you want to keep row 1 visible, highlight row 2 and click Window, Freeze Panes.
(2) If you want to keep column A visible, highlight column B and click Window, Freeze Panes.
(3) If you want to keep row 1 and column A visible, click in cell B2 and click Window, Freeze Panes.

Another option altogether is to use the Window, Spilt feature, but this actually shows your rows and columns duplicated and can be confusing.


Submitted by: Kelbo

**********************************************************************

Answer:

All versions of Excel have a Freeze Pane option found under the Window menu. Freeze Pane can be used to freeze titles in a single row or in sequential rows. It can be used as well to freeze titles in a column or sequential columns, or even titles in both row(s) and column(s).

To use Freeze Pane you must a) first select where the scrolling cells begin, then b) from the Window menu select Freeze Pane.

So to create a title row that does not scroll, lets say the titles are in cells A1 through A10, either click the row indicator button 2 to select row 2, or position the Active Cell by clicking cell A2. Then apply the Window - Freeze Pane command. It is the top edge of the selected row or Active Cell that determines the point of separation between the non-scrolling from scrolling cells. All cells above will be non-scrolling cells.

There are two other Freeze Pane possibilities:
a) If titles are in a column, say A1 through A10, then first select the column button B to select the entire B column or click cell B1 to make it the Active Cell. Then use Window - Freeze Pane. In this case all cells to the left of the selected column or Active Cell are non-scrolling.

b) If you have titles in both rows and columns that you do not want to scroll, for example you have titles across in row 1 and more titles down columns A and B then you would select cell C2 and then apply Window Freeze Pane. In this case it is both the both the top and the left sides of the Active Cell that determine all cells above and to the left of the Active Cell will be non-scrolling.

To remove the Freeze Pane: first select the worksheet, and then select Window - Unfreeze Panes. It is not necessary to select any specific cell in the worksheet.

It seems a bit backwards to select the start of the scrolling cells instead of the cells to be non-scrolling when using Freeze Pane, but that is how it works in Excel. If you work with an Access database. it has a Format - Freeze Column command, but here it is the column or columns selected in an Access Table or a form Data Sheet that become the non-scrolling columns.

Be aware:
1. Each worksheet in a workbook can have a different Freeze Pane setting. So when pasting or linking cells the Freeze Pane setting is not included in the paste or link action
2. Freeze Panes only applies to your monitor view. Printing a worksheet includes all cells even if scrolled behind a frozen pane. To select what cells are printed you can use the File Page Setup Sheet tab options and/or Format - Row (Column) - Hide.
3. Watch how you apply cell formatting, for example a border applied to the top of cells immediately below the frozen pane means this border disappears when these cells scroll under the frozen title cells. Fix this by applying border formatting to the bottom of the frozen non-scrolling title cells.

As a bonus, depending on how your data is organized and how you want to view it, Freeze Panes can be combined with other Excel menu commands such as: Window - Split and Data - Filter - AutoFilter.

Submitted by: David S. of North Saanich, BC CANADA


**********************************************************************

Answer:

How to lock title row in Excel
==============================

1. First select the cell immediately below your title eg if your title is on A1 then select A2.

2. Then go to the Windows drop down menu (or press Alt-W) and select Freeze Panes.

3. A bold line will appear on the row that your title is on.

4. When you scroll down your title will remain visible.

You can also freeze columns as well.

If you want to keep data in column A visible as well as your title in row 1 then do the following -

1. Go back to the Windows drop down menu and select Unfreeze Panes to cancel your previous freeze.

2. Now select cell B2 then go to the Windows drop down menu and select Freeze Panes again.

3. Now a bold line will appear vertically as well as horizontally to show that row 1 and column A have been locked.

4. You will be able to scroll horizontally and vertically and your first row and column will always be visible.
=============================================================================================================

Rgds


Submitted by: Steve W. of Cape Town, South Africa


Forum Icon Legend

  • UnreadUnread
  • ReadRead
  • Locked threadLocked thread
  •   
  •   
  •   
  •   
  •   
  •   
  •   
  • ModeratorModerator
  • CNET StaffCNET Staff
  • Samsung StaffSamsung Staff
  • Norton Authorized Support TeamNorton Authorized Support Team
  • AVG StaffAVG Staff
  • avast! Staffavast! Staff
  • Webroot Support TeamWebroot Support Team
  • Acer Customer Experience TeamAcer Customer Experience Team
  • Windows Outreach TeamWindows Outreach Team
  • DISH staffDISH staff
  • Dell StaffDell Staff
  • Intel StaffIntel Staff
  • QuestionQuestion
  • Resolved questionResolved question
  • General discussionGeneral discussion
  • TipTip
  • Alert or warningAlert or warning
  • PraisePraise
  • RantRant

You are e-mailing the following post: Post Subject

Your e-mail address is used only to let the recipient know who sent the e-mail and in case of transmission error. Neither your address nor the recipient's address will be used for any other purpose.

Sorry, there was a problem emailing this post. Please try again.

Submit Email Cancel

Thank you. Sent email to

Close

Thank you. Sent email to

Close

You are reporting the following post: Post Subject

If you believe this post is offensive or violates the CNET Forums' Usage policies, you can report it below (this will not automatically remove the post). Once reported, our moderators will be notified and the post will be reviewed.

Offensive: Sexually explicit or offensive language

Spam: Advertisements or commercial links

Disruptive posting: Flaming or offending other users

Illegal activities: Promote cracked software, or other illegal content

Sorry, there was a problem submitting your post. Please try again.

Submit Report Cancel

Your message has been submitted and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.

Close

Your message has been submitted and will be reviewed by our staff. Thank you for helping us maintain CNET's great community.

Close

You are posting a reply to: Post Subject

The posting of advertisements, profanity, or personal attacks is prohibited. Please refer to the CNET Forums policies for details. All submitted content is subject to CBS Interactive Site Terms of Use.

You are currently tracking this discussion. Click here to manage your tracked discussions.

If you're asking for technical help, please be sure to include all your system info, including operating system, model number, and any other specifics related to the problem. Also please exercise your best judgment when posting in the forums--revealing personal information such as your e-mail address, telephone number, and address is not recommended.

Sorry, there was a problem submitting your post. Please try again.

Sorry, there was a problem generating the preview. Please try again.

Duplicate posts are not allowed in the forums. Please edit your post and submit again.

Submit Reply Preview Cancel

Thank you, , your post has been submitted and will appear on our site shortly.

Close