Smack83 sends in today's question via YouTube. How do you freeze specific rows and columns in Excel and have them appear without retyping. Episode 968 shows you how to you Freeze Panes to keep the headings visible on the screen.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Basically, you start out with massive amount of data.
How we're gonna analyze as well.
Let's fire up a pivot table.
See if we can solve this problem.
Alright! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today question sent in, on YouTube from Smack-83 and wants to know about, basically how to use freeze panes.
So, let's say that we have a quick little worksheet here.
Title up in row 1, headings going across row 3.
Some labels going down the left-hand side and then just lots of data here.
So, just fill in 123 throughout.
Of course, we need to copy the labels down that far as well.
Alright! You get the idea huge data set and maybe we've done a little bit of formatting here.
So, cell styles we choose title for the headings.
We've chosen you know, you'd like to use heading 3.
Heading 4, let's go with that, alright!
So, the question is how do we make sure that we can always see those headings on the first row of every page and this is called Freeze Panes and basically, obviously as you scroll down you lose track of which columns are which.
So, to use freeze panes, we're going to go to the view tab in Excel 2007.
You see that, there is a freeze panes item here.
But I want to open the drop-down, and they've added a couple of new things.
Freeze top row or freeze first column, what if you happen to be lucky enough, that your data is in the first row.
You can use those new options.
However, in this case that's not what we have.
So, it's really important that the cell pointer goes in the first cell that is going to be non frozen.
So, row 4 is allowed to scroll off the screen.
I'm sitting at A4 and I go to freeze panes and choose freeze panes.
And now, I've frozen everything above row 4.
So, as I scroll down, I can see further rows but not lose the headings.
Now, let me go back.
I'm going to unfreeze panes and we'll talk about other ways to use this.
If I would be in cell B4, it's going to freeze everything above B4 but also everything to the left B4.
So, I'm going to freeze column A.
So, here we go freeze panes and now as I scroll to the right.
I can see further columns to the right but still see those labels over in column A.
I can still scroll down.
Now, you'll notice that we have a big title up there and a lot of times we'll have a huge title 3, 4 or 5 lines.
When we don't really need that taking up space on our screen.
So, watch this cool trick.
I'm going to use the [ down arrow ] and get it to that way row 3, is the first row.
That's visible in the worksheet.
Now, I come back here to cell B4 and when I freeze panes.
I've effectively, removed the title from the locked area.
I still have row 3, the important row.
The row shows my headings there and I can scroll down throughout the data set.
Great question. I know, I've covered this before a matter of fact I think it was episode 101 or 102, one of my first episode.
Talked about them to do that, of course.
That was back in the days of Excel 2003.
So, the Smack-83 thanks for sending in this question.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
I'm Bill Jelen.
Basically, you start out with massive amount of data.
How we're gonna analyze as well.
Let's fire up a pivot table.
See if we can solve this problem.
Alright! Welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today question sent in, on YouTube from Smack-83 and wants to know about, basically how to use freeze panes.
So, let's say that we have a quick little worksheet here.
Title up in row 1, headings going across row 3.
Some labels going down the left-hand side and then just lots of data here.
So, just fill in 123 throughout.
Of course, we need to copy the labels down that far as well.
Alright! You get the idea huge data set and maybe we've done a little bit of formatting here.
So, cell styles we choose title for the headings.
We've chosen you know, you'd like to use heading 3.
Heading 4, let's go with that, alright!
So, the question is how do we make sure that we can always see those headings on the first row of every page and this is called Freeze Panes and basically, obviously as you scroll down you lose track of which columns are which.
So, to use freeze panes, we're going to go to the view tab in Excel 2007.
You see that, there is a freeze panes item here.
But I want to open the drop-down, and they've added a couple of new things.
Freeze top row or freeze first column, what if you happen to be lucky enough, that your data is in the first row.
You can use those new options.
However, in this case that's not what we have.
So, it's really important that the cell pointer goes in the first cell that is going to be non frozen.
So, row 4 is allowed to scroll off the screen.
I'm sitting at A4 and I go to freeze panes and choose freeze panes.
And now, I've frozen everything above row 4.
So, as I scroll down, I can see further rows but not lose the headings.
Now, let me go back.
I'm going to unfreeze panes and we'll talk about other ways to use this.
If I would be in cell B4, it's going to freeze everything above B4 but also everything to the left B4.
So, I'm going to freeze column A.
So, here we go freeze panes and now as I scroll to the right.
I can see further columns to the right but still see those labels over in column A.
I can still scroll down.
Now, you'll notice that we have a big title up there and a lot of times we'll have a huge title 3, 4 or 5 lines.
When we don't really need that taking up space on our screen.
So, watch this cool trick.
I'm going to use the [ down arrow ] and get it to that way row 3, is the first row.
That's visible in the worksheet.
Now, I come back here to cell B4 and when I freeze panes.
I've effectively, removed the title from the locked area.
I still have row 3, the important row.
The row shows my headings there and I can scroll down throughout the data set.
Great question. I know, I've covered this before a matter of fact I think it was episode 101 or 102, one of my first episode.
Talked about them to do that, of course.
That was back in the days of Excel 2003.
So, the Smack-83 thanks for sending in this question.
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
