Excel only allows you to lock the top rows of a spreadsheet, so everything above your selected row will become locked in place. Click the 'View' tab at the top of the screen. Click the 'Freeze Panes' button in the Window area of the ribbon. Click the 'Freeze Panes' button that then appears in a pop-up menu.
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. 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. Split (Alt+W, S). Cursor positioning and undo are both exactly the same as for freezing.
There are several differences between the two. 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. 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. 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 you?re 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 don?t 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 don?t 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 doesn?t completely lock them. In last example above, you would end up with 4?live?
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 I?m way down the sheet, and don?t want to lose where I?m 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, let?s 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? 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. 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? Sheet tab options and/or Format - Row (Column) - Hide. 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. Then go to the Windows drop down menu (or press Alt-W) and select Freeze Panes. A bold line will appear on the row that your title is on. 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. Now select cell B2 then go to the Windows drop down menu and select Freeze Panes again. Now a bold line will appear vertically as well as horizontally to show that row 1 and column A have been locked. 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.
To freeze or not to freeze With new Excel capacity to hold a million rows and numerous columns with numbers, it is easy to lose the connection between the value and its meaning on line 26935. This is one of the reasons Excel keeps the commonsense 'Freeze' feature for you. It allows you to keep track of the data you are looking at without having to scroll back and forth to see the column or row labels. So, what's the button to click, and what are the pitfalls? Step number one is to look at your data and see if you need to lock just one row with the labels, several top rows, or columns.
How to keep your labels visible If you have a usual table with one header row, the steps are very easy:. Scroll to the top and make sure you see the row with the labels. Otherwise you'll quickly notice one peculiarity - this option does exactly what it promises, it freezes the top visible row. Open the View tab in Excel and find the Freeze Panes option in the Window group. Click on the little arrow next to it to see all the options, and choose to Freeze Top Row (or to freeze the first column, depending on the way your data are organized). Whenever you lock rows or columns, the Freeze Panes option turns into Unfreeze Panes for you to quickly unlock the row or column.
Lock several top rows, get hold of the columns More and more often I come across tables that have several header rows. Those are complex structures, but they help you have some context to better understand the data. The question of freezing several rows can also come up when you compare one set of data to another, a couple of thousands rows away. The 'Freeze top row' feature is obviously not very useful in this case. But the 'Freeze panes' option is! Here is how you do it:.
This moment is the key - select the cell just below the rows you want to freeze, and to the right of such columns if needed. Open the View tab in Excel and find the Freeze Panes option in the Window group. Click on the little arrow next to it to see all the options, and choose to Freeze Panes. As always, this is not the end of the story. Because just like, one day you may find out it simply doesn't work for you. This may happen because you already have the top row locked.
I don't know if I can call it a trick, the simple solution (and one thing to remember) is to unfreeze it first. If any of the rows or columns are locked in the table, you'll see Unfreeze panes in place of the Freeze panes option. Peek at the option name before trying to lock the rows, and everything will work as you need it to. Use this little handy feature to have the headers visible when you scroll and always know what figures you are looking at.
You may also be interested in:. Hello Mila, Thank you for your question.
This issue in Excel doesn't have a simple solution, but there is a workaround you can use. Click on the cell with a comment, go to Data tab and click on Data Validation. You can enter the information from the comment on the 'Input Message' tab and select the check box 'Show input message when cell is selected'. Now whenever you click on the cell in the frozen row, this message will appear. You can move the box to any location you prefer.
There is a nice video that describes the process: I hope you'll find this helpful. I am using Excel 2016 for Mac. I have a workbook set up with 12 monthly columns with a grand total column and an avg per month for each row (employee). I also have company wide totals at the end of each month column and then the avg for each month. The totals are unaffected by re-sorts, as are the row averages, but when I re-sort, my avg on each column gets moved up into the table. I had thought I could move the column avg to the top and then freeze the panes but they still get re-sorted. My header row with the month names is unaffected.
Is there a way to set the column avg row as a header row as well, so they will remain at the top regardless of the sort?