Join Dennis Taylor for an in-depth discussion in this video Split screens and frozen titles in a flash, part of Excel 2010: Tips, Tricks, and Shortcuts.
When you are working with large amounts of data, sometimes what you'd like to be able to do is to see information in part of a worksheet and another part, even though maybe they are couple of hundred rows apart or even thousands of rows apart or columns far apart. Couple of ways to pursue this. If you are in kind of a hurry, would you like to split the screen just easily top/bottom? Click in the middle more or less somewhere. It doesn't make any difference. For example here. One way to do this is above the vertical scroll bar in the right-hand side of your screen, just above the arrow, I am sliding the mouse up here slowly on the right side.
Put the mouse right there. If you double-click right when you see the arrow being a two way up/down arrow, this automatically introduces a split screen above the active cell. And now on the right-hand side of the screen you'll see two scrollbars. So in the lower part, I am scrolling using the mouse wheel, I am scrolling down to row 150, 180, whatever, or maybe I am going to drag that scrollbar. I want to see these people here, these Project & Contract Services. Meanwhile I also want to see these people.
They are quite a few rows apart. That's just fine. And strangely enough we can scroll, you know call it upside down, wherever you want to call it. But we know we are looking at data on the bottom of the screen. Maybe this near row 1 or row 2, that sort of thing. And on the other part of the screen we are looking at data elsewhere so sort of a topsy-turvy kind of approach. Unlikely they use this too often, but we can do this. We can scroll independently on either side of the screen. Any changes we make to the data of course occurs in the actual data. And this particular view has nothing to do with printing.
How do we get rid of this? Simply double-click it. Another way to use this, not necessarily better, perhaps not as fast as, at any time when we do not have a split screen, we can point to the split screen button and drag it to wherever we want and let go. Sometimes it makes sense to split a screen left/right, and we might have titles here, we might not. That's not the issue. And even if we've got merge titles that's not going to get in the way. So how do we introduce a vertical split? In the same way that we can use the horizontal split, we might say well let's split the screen more or less 50-50.
We will click somewhere here in column E. And the vertical split indicator is to the right-hand side of the horizontal scrollbar in the lower right-hand corner. So right there is the button. When you slide the mouse over that and see that double left-right arrow, double-click. And we are about to see a vertical split to the left of the active cell. There it is right there. Here we have two horizontal scrollbars at the bottom of the screen. So on the left side we can scroll left and right, on the right side we can scroll left and right.
And sometimes we come up with some interesting angles where we were viewing the column A data next to the column J data. You have your reasons for doing this. I think it probably makes more sense to suggest that if you have got a worksheet with many, many columns you might want to see data for example in columns A, B, C, D whatever on the left side. And at the same time see data off on the right-hand side in other columns. We get rid of the split by double-clicking. We saw a horizontal split. We saw a vertical split. Can we do both? Sure can, both at the same time.
We could use this technique by dragging down this way or double-clicking, or dragging this or double-clicking. Now we have got a four way split. There are four scrollbars now. If you are interested in chaos and confusion, this may be just the ticket. I think it's a bit weird to be doing both of these at once, but you might have your needs. Just to prove a point here you could be seeing the very last row in this worksheet and the very last column. There it is, XFD 1048576, and you could also be viewing A1 in the upper left-hand corner.
I am not sure if that proves a point, but it does suggest the idea that by using these techniques you could see any two cells in the worksheet at the same time. And when you've got formulas tied together that reflect on one another sometimes this could be useful. But I think it's more useful to have either a horizontal or a vertical split. Now a similar feature and yet different in many respects, and I think for most people most useful is simply to freeze the panes. We like to scroll up and down here and maybe always see rows 1 and 2.
Maybe we'd always like to see column A as well. We can do it different ways. If we simply want to freeze the top row, titles like this are going to get in the way. Let me take this out for the moment. We may want to put it back later. But I am going to delete that, because the feature does work a little bit better. And it's a neat way if we don't have a separate heading like that. On the View tab we can simply do what? Freeze Panes and Freeze Panes, or just what? Freeze Top Row. Now it doesn't make any difference where the active cell is. If we freeze the top row, slight visual change there we can scroll up and down and always see row 1.
In addition to that or instead of that we might even freeze the first column, and we can scroll leftwards and rightward quickly and easily this way as well. If we want to remove the feature, the Undo capability has nothing to do or does not record these actions. So Undo doesn't help here. We have to go back here and unfreeze the panes. If you click on cell B2 and then use this feature and Freeze Panes, it automatically freezes everything above and to the left.
I think that's what a lot of people need in database-type lists. They frequently want to freeze the top row and the left column. So there we are. Fast easy to use tools that give us some perspective on our worksheets and allow us to navigate back and forth pretty quickly and easily to see the data better.
- Creating charts with keyboard shortcuts
- Converting formulas to values by dragging
- Repeating Undo and Redo actions
- Displaying formulas instantly
- Navigating quickly through worksheets and across workbooks
- Formatting numeric, and date/time data in a flash with keystroke shortcuts
- Inserting dates or times instantly
- Grouping rows or columns to create collapsible regions
- Building data-entry shortcuts with Auto-fill
- Displaying unique items from large lists
Skill Level Intermediate
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.