In this video, learn how to handle those situations by splitting the worksheet into separate units and freezing columns and headers.
- [Instructor] 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 do you do if you have a data list and you want to keep the headers on the screen regardless of how far you scroll? In this movie, I will show you how to handle those situations by splitting the worksheet into separate units and freezing columns and rows. My sample file is the Freeze workbook and you can find it in the Chapter04 folder of your Exercise Files collection. This workbook contains a single worksheet and in it I have monthly revenue data for eight calendar years.
My month headers are in the first column, column A, and my column headers are in row four. So let's say that I want to be able to scroll left and right and not have the months disappear, as I'm doing here. You see that column A, the month headers, were disappearing because I scrolled away from them. If I want to keep that first column frozen, I can go to the View tab of the ribbon, and then click Freeze First Column.
You'll notice that there is a thin gray line at the right edge of column A, and now if I scroll to the right, you'll see that that column stays in place. And if I scroll back, everything remains. If I want to unfreeze that column, I can click the Unfreeze Panes button here. Also note that if I wanted to, I could freeze the top row. However, freezing the top row would only have the main Monthly Revenue label. It doesn't give me anything else.
So what I would like to do is to freeze rows one through four. To do that, I need to click in column A and the first cell that I want to scroll. So in other words, everything above the cell that I select will be frozen, or at least all the rows. So I'll click cell A5. That means I'm not freezing any columns, I'm just freezing rows, and then I will go up to the View tab of the ribbon again and click Freeze Panes.
And you'll notice that there is now the same thin outline below row four. And now when I scroll up, your mouse pointer needs to be in the scrollable region, now when I scroll up, you'll see that the headers remain in place and the data scrolls beneath it. And as before, if you want to unfreeze, just click Unfreeze Panes. If you want to freeze rows and columns at the same time, then you click the cell below and to the right of the intersection where you want the freeze to take place.
So for example, before I had rows one through four and column A. That means that I need to go one row down, row five, and one column to the right, column B, so that would be cell B5. Click cell B5, click Freeze Panes, and you'll see that I now have two outlines, one along the right side of column A, and one along the bottom edge of row four. So if I put my mouse pointer in the scrollable area and start scrolling, you'll see that both the rows and columns are frozen.
And as always, I can go back and click Unfreeze Panes to undo it. Now say that I have a large worksheet and I want to be able to scroll within a couple of different areas. Let's say, for example, that I want to be able to scroll from column A to column D. To create a scrollable area, I will click cell E1 and then click Split. That's on the View tab of the ribbon. And you'll see that I now have two separate areas within my worksheet and I can scroll fully within each of them.
So I'm currently in the right side of the split. You can see the thicker light gray border there. I can scroll to the right and basically I have a second copy of my worksheet. Scroll there, and I can scroll in the separate window over here. To remove a split, just click the Split button again and it goes away. And, like with freezing panes, if I want to create multiple scrollable or split areas, let's say that I want to do it at cell E10.
I'll select cell E10, and then on the View tab click Split, and I have four scrollable regions. So I can scroll up here, scroll here, and you'll notice that any time I scroll up or scroll to the left or right, then the worksheet behaves in interesting ways. What's happening is that anytime I scroll up and down, then everything on the same horizontal level of the split scrolls along with it.
And anytime I scroll left or right, then everything on the same vertical appears to move with it. So you can see here I'm scrolling up and down, so that means that I get the left and right, the horizontal line. And if I scroll left to right, then the vertical aligned regions scroll there. And also note that you can scroll all the way to the edge of the worksheet. There's no limit on that. When you're done with your split, click the Split button and you're back to your normal worksheet.
- Creating workbooks
- Manipulating cell data
- Sorting, filtering, and managing worksheets
- Using core functions and formulas
- Formatting worksheet elements
- Creating and managing conditional formats
- Working with charts
- Adding images and shapes
- Working with PivotTables
- Exporting workbooks