Start learning with our library of video tutorials taught by experts. Get started
Viewers: in countries Watching now:
In Excel for Mac 2011 Essential Training, author Curt Frye gives a comprehensive overview of Excel, the full-featured spreadsheet software from Microsoft. The course covers key skills such as manipulating workbook and cell data, using functions, automating actions, printing worksheets, and collaborating with others. Exercise files accompany the course.
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.
Find answers to the most frequently asked questions about Excel for Mac 2011 Essential Training.
Here are the FAQs that matched your search "":
Sorry, there are no matches for your search ""—to search again, type in another word or phrase and click search.
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.