Let’s look at how to freeze a row in Excel.
It can be a real pain when you don't have freeze panes on, and when you start to scroll down the screen you lose the headings at the top of your columns.
In the example below, we've got columns for Cost price, Recommended retail price and Sale price.
If I scrolled down the screen, I will lose sight of the column headings and I will no longer be able to see which column I’m looking at.
So if you have been struggling with this, you're not alone.
Most people have a problem with this, and it's something that when I cover this in my workshops, people go, "Oh my goodness, I just love this."
So, let's sort this out this together.
How to freeze a row in Excel
Now, if you have a worksheet where the headings are in row 1 and you would love to have that row frozen, so you can still see the headings as you scroll down, follow the steps below.
From the View tab, select Freeze Panes (look for the button with a little icicle on it).
From the dropdown menu, shoot right to the middle option, Freeze Top Row.
As soon as you click this, you're going to see a tiny gray line underneath the first row.
Now start to scroll down, and watch as row 1 stays nicely 'locked on' at the top of the screen, which is gold, absolutely brilliant!
How to Unfreeze in Excel
Now at any time if you want to remove your freeze panes, it's quite easy.
Just make your way back to the View tab, select Freeze Panes and then select Unfreeze from the top of the menu.
Now you'll see that we're back to where we started and if you scroll down the top row is no longer locked.
How to freeze a row other then the Top Row in Excel
So, freezing the first row is easy peasy.
Where the challenge comes in is when you’re needing to freeze a row, other than the top row.
In the example below you can see that the column headings are in row 3, not row one. So, of course if we scrolled down, again we will have the same problem where we will lose the column titles, and it's hard to see what's in each column.
And in this example, if we popped on Freeze Top Row, of course it's going to freeze the top row, and only the Annual Sales heading will be frozen, and that's no use at all.
So, if you are wanting to freeze from a set point, you're going to select a cell in column A, directly underneath the row that you're wanting frozen.
Now, from the View tab select Freeze Panes and this time select the top option, Freeze Panes.
Now as soon as you apply this option you will see the tiny gray line above the cell you selected, and as you scroll down, the row above will be locked frozen.
So, that is how you can apply Freeze Panes anywhere where the row is lower than row 1.
How to freeze columns in Excel
So, if wanted to freeze the first column, that's easy.
Of course, it's the bottom option on the Freeze Panes menu.
We use this option when we're scrolling across to the right.
In the example below, if we wanted to see the invoice number as I scrolled across, I can freeze it using the Freeze First Column option.
How to freeze a row and a column at the same time
So the middle and bottom menu options, top row and first column are pretty easy to use.
But what if you actually wanted to have the column headings frozen as you scroll down, and the invoice number AND customer name, both of those showing as you scroll across.
So again, we have a little bit of a challenge.
Let’s look at the example below.
If we’re wanting to put a lock against the first two columns and the top 3 rows, what we can do is select a cell below the row we want locked on, and to the right of the columns we want locked on. In this example we would select cell C4.
So once you've done that, then you go to the View tab, Freeze Panes, and then select the top option, Freeze Panes Keep rows and columns visible while the rest of the worksheet scrolls (based on current selection).
Now take note of the text, based on current selection. Excel is basically saying, “based on the current cell that you have selected, the worksheet will freeze above and to the left of this cell”.
You should now see the little gray lines, and if you scroll down, you will still see your headings, and if you scroll across, you will still have those two columns showing.
Sharyn is an expert trainer. She became the first certified Microsoft® MOUS Authorised Instructor in New Zealand.
She is endorsed by Microsoft® as a qualified Microsoft® Office Specialist and has more than 20 years of experience in the training industry, developing and delivering technology training workshops. Her approach to taking the “techie-speak” out of technology training has placed her as a preferred supplier to many of New Zealand’s leading organisations.
If you enjoyed this you may also enjoy...
Learn how to use Conditional Formatting to identify cells that are of interest. For example, you can apply a Conditional Format that checks to see a cell’s value is greater than $500. If it is the Conditional Format can change the Fill colour of the cell. Therefore making it really easy for you (and others using
Learn how to find, modify and break links to an Excel workbook. Linking is great, until you no longer need it. Then it can be a bit of a pain. A couple of challenges may come your way if you weren’t the creator of the workbook that contains links. One challenge is identifying where the linked data
Learn how to link Link cells in the same or different Excel worksheets. Linking saves a huge amount of time (and a huge amount of mistakes) in that it allows you to create connections from one cell to another.
Use the SUMIF function to total only the cells that match your requirement. For example, if you wanted to know the total sales made by one of your sales team members you can use the SUMIF function to only add to the total the sales made by a certain member. Gold! This function saves you a
Learn how to insert an Excel function into your workbook. Excel comes equipped with a large number of pre-defined formulas. These are known as functions. There is a great amount of functions available in Excel. Each created for a specific purpose. There are functions specific to Maths, Science, Finance, and Engineering to name a few.
Learn how to insert subtotal rows into sorted data without having to spend time doing it manually. Recently I ran a training session for an Accounts Manager and her staff. They spent a lot of time pulling data out of their in-house computer system, sorting it by customer and then inserting a new row at every change