Join Curt Frye for an in-depth discussion in this video Splitting and freezing rows and columns , part of Excel for Mac 2011 Essential Training.
- View Offline
Excel worksheets can contain a lot of data, but what do you do if you need to use data in two different parts of the worksheet at the same time? Or, what you do if you have a data list, and you want to keep the headers on the screen, regardless of how far down or to the right you scroll? In this movie, I'll show you how to handle both of those situations by freezing rows and columns, and by splitting worksheets. First, we will talk about freezing. Freezing a row or a column means that column or row stays in place, regardless of where you scroll in the worksheet. For example, I have a column of data here, and it would be very useful, regardless of how far I scroll down, to keep these headers on the screen the entire time.
If you want to do that, go to the Layout tab of the Ribbon, and then in the Window group, click the Freeze button, and then you can select Freeze Top Row. When you do, Excel draws a gray line, indicating that the top row has been frozen, and now when you scroll down, instead of having row number one scroll off the screen, you will see that it remains in place and the rest of the data on the worksheet scrolls. You move down through the rest of the rows. If you scroll back up, it's still there. When you're ready to unfreeze the row, you can go back up to the Freeze button and click Unfreeze, and now the worksheet will scroll normally.
If you want to freeze the first column, you can do that. Just go back to the Freeze button and click Freeze First Column. Now when you scroll to the right, that column stays in place. So unfreeze, click the freeze button, and click Unfreeze. Now let's suppose that you want to freeze a specific area of the worksheet. For example, let's say that you wanted to freeze both the top row and the leftmost column. You can do that, and the way to do so is to click the cell to the right and below where you want to freeze. In another words by clicking cell B2, if I were to freeze the worksheet right now, we'd freeze the top row and the leftmost column, the row above the selected cell, and the column to the left of the selected cell.
Go back over to the freeze button and click Freeze Panes. Now you see we have the gray line, indicating the top row is frozen, and the gray line here - it's a little less visible, but it is there - indicating the leftmost column is frozen. And just to prove that, we will scroll to the right and scroll down. Go back to the left. When you are ready to undo the freeze, click the Freeze button, and click Unfreeze. And now let's say that you have a huge worksheet, and you have data in two separate pieces of it that you want to work with. Unfortunately, those two pieces aren't on the screen at the same time, so instead of scrolling back and forth, you can split the worksheet into two scrollable areas and work with it that way.
To do that, you select the cell below and to the right of where you want to split the worksheet. I'll do it here, the first month of 2006. And then again on the layout tab of the Ribbon in the Window group, you can click the Split button, which is this button right here. When you do, Excel gives you two different scrollable areas. Now both of these areas contain the entire worksheet. So, for example, you will see here, I have row one and all the headers, and this split here starts with 2006 in row 14. However, I can't scroll up if I'm in the bottom pane, all the way up to row number one if I want to, and in the top section I can scroll down.
When you're ready to remove the split from the worksheet, go back up to the Window group on the Layout tab, click the button, and the split will go away. For example, if you were to click cell C14, and then click the split button, you would split the worksheet into four areas. So, for example, you can scroll through here using this scroll bar, scroll through here using this scroll bar, and so on. Again, when you're ready to remove the split, just click the split button, and worksheet returns to normal. Dividing your worksheets into parts by splitting or freezing rows and columns helps you work with large data collections efficiently.
If you're getting frustrated while moving around within a large worksheet, see if one of these techniques makes things easier.
- Customizing the Ribbon
- Formatting worksheets, cells, and cell data
- Sorting and filtering data
- Working with formulas
- Detecting formula errors
- Creating charts
- Importing data
- Inserting objects and graphics
- Using PivotTables
- Recording macros
- Sharing workbooks