4

Is there a way to freeze a column(s) or row(s) in place so that when I scroll up or down my worksheet, the column(s) or row(s) remain visible?

2
  • I'm not sure that I understand what you mean by "move across"
    – EBGreen
    Jan 24, 2012 at 18:37
  • Do you mean freeze the column so that scrolling to the right leaves that column in place on the screen where it is? Jan 24, 2012 at 19:08

4 Answers 4

3

Office 2010, do the following:

  1. Make your selection that you want to remain in place
  2. Go to ribbon view - freeze panes
  3. Choose to freeze the top row the first column or your selection
  4. The chosen selection will be frozen in place as you move up or down the sheet
1
  • i figured it out!!!! thank you. freeze panes! Jan 24, 2012 at 21:06
3

You can't do only specific column.

But you can target specific column and that corresponding row using

View Menu --> Freeze Pane

Example: If you are on Cell F5 (that is column F & row 5) and select Freeze Pane from View Menu then

Freezed Column will be, till Column E
Freezed Row will be, till Row 4

EDIT:

For more clarity, refer this screenshot:-

enter image description here

7
  • i have a list of names and my excel sheet is very long horizontally. i loose track of which row the name is in while looking further to the right. is there a way to have the list of names move across the spreadsheet while i look further down Jan 24, 2012 at 21:05
  • yes, you can do by using Freeze Pane. Assuming if your Row 5 contains names in each cell then select the CELL A6 then Click on View Menu --> Freeze Pane. Now try by scrolling vertically, you will see Row 5 as freezed. Jan 25, 2012 at 7:47
  • This answer is misleading. You can freeze a specific column or row, it will just become the top-most or left-most column/row or all rows/columns to the left/top of it will also remain frozen. Feb 8, 2012 at 12:49
  • sorry raystafarian, i think you might not looked properly. I have explained clearly how it works.. there is something, you mistaken my answer.. Feb 8, 2012 at 14:05
  • @Raystafarian: I have updated my answer with screenshot, for your reference. Feb 8, 2012 at 14:11
1

Additional Help

Freeze or Lock Rows and Columns

I found it Very Difficult to figure out how to do this for the Top row and for a couple of Columns. I did figure it out however,

First Split the panes

Excel 2010

and then move the large black Lines to the rows and columns that you want Static (or Fixed)

you can click Freeze panes > Freeze Panes and then click Split or you can just click Split. if you do anything else in the Freeze Panes and then Try to Split it will only Give you One Thick Black Line.

if your focus is in the area that is Static (Fixed/Locked) and try to scroll you will scroll only that pane.

I am using Excel 2010

0

Wow, so much confusion.

Raystafarian and Tog are correct and Siva Charan is utterly wrong in his statement leading off his answer: You can't do only specific column.

To word things better all around, consider the following:

  1. You can freeze only columns if desired. One or more, frozen to the left of the working area. If you select the row 1 cell of the first column to the right of the one you want frozen and freeze, the that column to the left of the selected cell, and all to its left, are in the frozen section, but all rows scroll. This seems to be what the question asked. More though later.

  2. You can freeze only rows if desired. One or more, frozen above the working area. If you select the column A cell of the first row below the one you want frozen and freeze, the that row above the selected cell, and all above it, are in the frozen section, but all columns scroll.

  3. Select some cell "in the grid", perhaps f10, and freeze, and like in 1. and 2. above, rows above it and columns to the left of it freeze. This is what Siva Charan was talking about, but he was flat wrong in saying "You can't do only specific column." More on this too, later.

In each case, rows above and columns to the left of the selected cell were frozen. In 1., above, there were NO rows above, so the effect is of only columns being frozen. In 2., above, there are NO columns to the left, so the effect is of only rows being frozen. So it is the same concept in ALL cases applied to the specifics of your selected cell.

Intriguingly, if one selects A1 and presses Alt-W-F-F, the screen "quarters" with the usual "freeze marks" (a dark black line on the lower and right borders of cells that are frozen makes a cross on the screen). The lower right quadrant is scrollable as if one had selected its upper leftmost cell and frozen panes.

However, and this may be what Siva Charan understood the question to be asking, you cannot have, say, columns A, B, C, D, E, and F as your working area and freeze ONLY column B, say. It would be columns A and B: column B and all columns to its left.

You can, though, move the grid about until column B is the leftmost column viewed on the screen and then freeze it. Both columns A and B would again be frozen, but since ONLY column B appears on the screen, it would seem like you had frozen only column B. So you can get some of the effect of that, but not the actuality. If you want column H, perhaps, frozen with work areas to the left and right, you need to do something else altogether.

So, Malachi's answer comes into play. He discusses SPLITTING the window into two parts. In that case, you have much the same results as with freezing panes, but there are differences. Not enough differences to freeze a column in the middle of the screen and work in both the left of it and the right of it, but differences.

The main purpose of Splitting is to have two different areas you want to work WITH and for them to both be scrollable. Perhaps you have entries on line 4 that relate to entries on line 404 and you want to view both lines at once, be able to move between those rows without going up or down 400 lines each time, and maybe even then scroll down one line in each to do work in lines 5 and 405.

Freezing panes is mostly to keep titles on the screen, so that, unlike almost every website table you ever encounter, you always know what a column is supposed to be even if you scroll four feet down in it. So you work in one area and use the frozen area for reference. Only the work area needs scrolling. Splitting's purpose is more to keep both areas in view and be able to work in both areas. And then to scroll for further work. However, the scrolling is manual. You must do both areas, there being no automatic synchronization.

A third thing that does these kinds of things is in that same piece of the Ribbon Menu system. It is New Window. It opens an entirely new window for the file, complete with its own Ribbon Menu system. The first window title (green bar at the top of the window changes from, say, Book1 - Excel to Book1 - 1 - Excel and the new one has a "2" in the middle. Pay attention to those by the way, as when you close them, you want to close the extra windows (not the "1" window), then close from the remaining one or some oddities can happen. Nothing harmful, just annoying, but still... If you have macros that might close the file, keep this thought in the front of your head.

The fun thing about new windows is that:

  1. Can be set to scroll synchronously, so you can see, say, twenty columns at the left of a table, then a window with columns 97-112 perhaps in the other, ignoring the 77 columns in between without dropping little dukies like Hiding those columns and forgetting to unhide them when saving, and not having access to them either if you need the odd look at something in them. Or not synchronously if preferred for your use.

  2. You can size the windows and arrange them on your monitor or monitors as desired. So if you want column AF to be a single column in the center of your screen with a view of columns to its left and to its right to either side of it, you can just open two windows so you have three altogether and arrange and size the windows to do just that. Column AF's window, tall and narrow, in the middle of your screen and the other two windows on each side of it perhaps filling the monitor's screen. Or maybe you drink Bill(ionaire) Gates' Kool-Aid and love little windows all over your screen. It's all good. If you set the middle window (column AF) to not scroll synchronously, it remains effectively frozen there as you work.

So three solid and oft used features to try to accomplish whatever is truly asked here. By the way, "back in the day" when this was asked (2012) Excel was still MDI so the new windows would have ALL stayed in the program's work area and would not have had their own Ribbon Menu systems taking up space. It was common then to pop out four of them each taking up a quarter of the work area. Or cascaded, but the approaches solved two different problems so one was not "better" than the other even though people sure argued the point!

Last thought since this involves at least tangentially the idea of how to get screen space to do what one needs: IF you have two or more monitors, and set Windows correctly, you can un-Maximize your Excel window (or any program that allows you to control its window size), pull its edges manually to fill the screen again, then take the appropriate border and drag it ONTO THE OTHER MONITOR. So two monitors, left and right, programs load on the left one? Do that, dragging the right edge of the screen-sized window onto the right monitor even all the way to its far edge and you have probably 50+ columns showing. More or less as one's column widths dictate, and of course, as one's monitors dictate. The monitors do not even have to be the same size. So you need to read a three foot wide column? No problema! Just read it on screen! No entering the cell and doing it the hard way. Always needed 65 columns but hated scaling down to 50% magnification? No problema! And it will remember the sizing too (Well, as much as Windows permits... notice your dialog boxes shift about for no obvious reason sometimes? That's Windows jacking you.) and launch that way the next time. I have different size monitors, by the way, so I speak from experience.

I'd tell you how to get more than three lines in the formula editor too (it's incredibly simple and annoying obvious once you know it, hard to imagine you worked 30 years with Excel before noticing it...), but can't figure a way that that would even tangentially touch on the question's subject.

Aw heck, why not? Someone can chop it out if it doth offend their sensibilities.

Clock in the formula editor (or press F2). Click the little arrow on the right to expand it (don't have to, I just always like knowing I can leave it as a single line when exiting it). Look at the column headers underneath it. Notice the VERY ODD fact that the top[ third or so of the gray area they are in is empty? The column division lines don't run fully top to bottom, just fill the bottom two-thirds. The column letter labels are consistent with that. So there's this one-third height of gray that doesn't precisely seem to be part of them, but you don't really notice until you look closely and think "Wha...?"

If you put your cursor in the editing area, then move it slowly down toward the letters, the I-beam pointer suddenly turns into a standard vertical double headed pointer. Just over halfway on the I-beam as it moves down into the gray.

Halt! The moment you have the vertical double headed pointer, you can click on the mouse and drag it down. As many lines in the editor as you like, until you reach the bottom of the screen. My boss has a 60" monitor above two 32" monitors and when I show him, we used the 60" monitor and had 80 or so lines. No more scrolling around in a formula just to view a piece of it!

Helpful too, for viewing lengthy cell content with F2, quicker than the two monitor-wide thing above if one needs it infrequently or doesn't wish to devote two monitors to Excel.

Be SURE to restore it to the standard three lines (or five, my new standard) or it could be a little unexpected when you press F2! As "unexpected" it might even cover something you needed to see for the current edit which would layer in even more annoyance. Otherwise though, especially when using LET() or IFS() the extra space can let you keep easy track of all elements.

I've begun laying out formulas like a programmer can, breaking lines for clarity, making them much more readable. So a simple use might see LET() with the first line containing elements that have references or values in them that might be changed by a user, then another one (or several lines) with further elements derived from them, and I like to do that as line 2 elements deriving directly from the line 1 elements, and line 3 for elements using anything in lines 1 or 2, etc., then a blank line, then the working part of the formula, and finish off with the final ")" on its own line. Indenting at the left to keep everything in proper related groupings. Set up a 25 IF IFS() as one line for each IF and it not only becomes incredibly readable, but if the statements are similar, ANY oddity like a dropped comma stands out. Adding spaces after a parameter's comma in functions makes them much more easily edited or understood as well.

It's hugely freeing to not have to stuff everything right smack up against each other to try to minimize the need to scroll if reading or editing it. Dense packed formulas are incredibly hard to fix for changes and for when a comma is dropped and you have to figure out where.

Well, that's it though, just clearing up the three ways to "freeze" (essentially) parts of your screen for better working with your spreadsheets.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .