Trouble with formulae? Lookups not going so well? The Wizard of Excel can help. Dan Harrison offers practical tips and examples, as well as Excel training and consulting.Often in Excel, you'll want columns or rows to continue to appear when you scroll off to the right or down the page.
You may have a table of employees with names down the left-hand side, with data pertaining the individuals disappearing off the right-hand side of the screen. Scrolling to the right, you'll want the names to continue to show when you hunt across for their NI numbers, stock plans, start dates.
And when you scroll down the page to get to the individual you're interested in, you'll want the column titles to continue to appear, so that you know which column you're looking in.
There are two ways to do this: the right way and the wrong way.
First: the wrong way. A feature has existed in Excel since I can remember called Split. It used to be on the Window menu, and now sits on the View ribbon. As its name suggests, it splits the window. You can either split the screen into two sections (a top and a bottom; or a left and a right); or you can split it into four.
The trouble with this method is that all of the panes are still navigable. So you can scroll up or down, left or right in any of the two or four panes that appear.
Just try it. Try splitting the screen and then do some scrolling in each of the resulting panes. Confused? You betcha.
Another minor annoyance with this technique is that the bars that separate the panes are distressingly ugly.
The Split function does have its uses—it can be useful to show multiple areas of data at the same time, to allow data entry that relies on other data in the worksheet. But more often than not, it's not the right tool.
Now: the right way.
The right way is through Freeze Panes. It can be found right next to the Split function. The main difference between this and the Split function is that with Freeze Panes, you can only navigate in the bottom-right pane. The top section remains frozen, as does the left section. This reduced functionality is a blessing. Trust me on this.
Oh, and the dividers are much more pleasing to the eye.
With either method, you invoke the dividers by navigating to the cell immediately below and immediately to the right of where you want them to appear. With Split, you also have the option of dragging a little divider immediately above the vertical scroll bar, and a similar one immediately to the right of the horizontal one.
Hi Dan,
I totally agree that the Split functionality should be avoided in most situations. There was one time I used it and that was when I had a pivot table with a lot of report filters taking up vertical space. With the split function, I put the top window on row 14 and when I scrolled down (in the bottom window) the table headers stuck around only taking up one row. This is honestly the only time I can think the split feature is useful.
Nice blog and keep it up!
Joseph
Is there a way to combine the two? For example, in Lotus 123 (yes, I still use it and you will see why), I have a worksheet to enter hours worked and vehicle hours used. I use Worksheet/Titles to freeze the left columns and the top rows. I then use Worksheet/Window/ Horizontal to separate the employees from the vehicles. That way I can have the employee and his vehicle on the page at the same time and just use F6 to tab between the two. Is there something similar in Excel?
I don't think there is, Rick. The only way I can think of doing this would be to create a View in Excel where the columns in between are hidden. Select the view and you can see both tables of data next to each other. And create a separate View that displays everything that you can toggle back to.
Not sure whether this helps.
Dan.
You can freeze a certain row AND a certain column at the same time. "To freeze both rows and columns, select the upper left cell in the data you don't want to freeze. Then choose Freeze Panes from the menu."
I've just added a feature to FormulaDesk, my free Excel add-in, that allows you to freeze any combination of columns and/or rows and display them on the right (for frozen columns) or the bottom (for frozen rows). http://www.formuladesk.com
I thought this was an open-source project you were sharing with the community; was it ever?
Looking at it now it is paid software; which I think makes this an advertisement post.
I think it's great that you are learning from users needs to enhance your product; not a fan of marketing back to those users under false pretenses.
I would like to know if it is possible when printing the document after freezing panes, the freezed panes will appear on each paper in case you have a large document. Or is there another tool for this in lotus 123.
There's no way of getting it to respect Freeze Panes, Ben. But if you go into Page Setup (on Page Layout, click the little icon in the bottom right corner of the Page Setup bit).
Click on the Sheet tab, click in Rows to repeat at top and then click back within your spreadsheet to select which ones to repeat.
Dan.
Is it my imagination or has the updated version of Excel now started to show the frozen row below the frozen row ie if you freeze Row 1 for example, then scroll to the top of the spreadsheet you see row 1 twice? I am sure it never used to repeat the frozen cells? It is very annoying when trying to move around fast across a sheet with say 3 row's frozen. Any cures?