How to freeze panes in Excel to lock rows and columns

The tutorial demonstrates quick ways to freeze panes in Excel. You will learn how to quickly lock header row or/and the first column. You will also see how to freeze several panes at a time to make Excel always show certain rows or/and columns when you scroll down or right. These tips work in all modern versions of Excel 2016, 2013, 2010 and 2007.

As you probably know, the recent versions of Excel 2016, 2013 and 2010 allow using more than a million rows and over 16,000 columns per sheet. Hardly anyone will ever use them to the limit, but if your worksheet contains tens or hundreds of rows, the column headers in the top row disappear when you are scrolling down to view lower entries. The good news is that you can easily fix that inconvenience by freezing panes in Excel.

In Microsoft Excel terms, to freeze panes means to always show certain rows and/or columns at the top of a spreadsheet when scrolling. Bellow you will find the detailed steps for Excel 2016, 2013, 2010 and lower versions.

How to freeze rows in Excel

Typically, you would want to lock the first row to see the column headers when you scroll down the sheet. But sometimes your spreadsheet may contain important information in a few top rows and you may want to freeze them all. Below you will find the steps for both scenarios.

How to freeze top row (header row) in Excel

To always show the header row, just go to the View tab, and click Freeze Panes > Freeze Top Row. Yep, it's that simple : )
Freezing the top row in Excel

Microsoft Excel gives you a visual clue to identify a frozen row by a bit thicker and darker border below it:
A darker border indicates that the row above it is frozen.

Tips:

  • If you are working with Excel tables rather than ranges, you do not really need to lock the first row, because the table header always stays fixed at the top, no matter how many rows down you scroll in a table.
  • If you are going to print out your table and want to repeat header rows on every page, you may find this tutorial helpful - How to print row and column headers of Excel.

How to lock multiple Excel rows

Do you want to freeze several rows in your spreadsheet? No problem, you can lock as many rows as you want, as long as you always start with the top row.

  1. Start by selecting the row below the last row you want to freeze.

    For example, if you wish to lock the top two rows, place the mouse cursor in cell A3 or select the entire row 3.

  2. Head over to the View tab and click Freeze Panes > Freeze Panes.
    Select the row below the last row you want to freeze and click Freeze Panes.

The result will be similar to what you see in the screenshot below - the top 2 rows in your Excel worksheet are frozen and will always show up.
A darker border indicates that top 2 rows are frozen.

Note. If some of the rows that you wish to lock are out of view when you apply freezing, they won't show up later, nor will you be able to scroll up to those rows. See how to avoid frozen hidden rows in Excel.

How to freeze columns in Excel

You lock columns in Excel in exactly the same way as you lock rows. And again, you can choose to freeze the first column only or multiple columns.

Lock the first column in a worksheet

Freezing the first column is as simple as clicking View > Freeze Panes > Freeze First Column.
Freeze the first column in Excel.

A little darker and thicker border to the right of column A means that the left-most column in the table is frozen.
A darker border means that the column to its left is frozen.

How to freeze multiple columns in Excel

If you want to lock more than one column in a sheet, proceed in this way:

  1. Select the column to the right of the last column you want to freeze. For example, if you want to freeze the first 3 columns (A - C), select the entire column D or cell D1.

    Just remember that frozen columns will always start from the left-most column (A), it's not possible to lock several columns somewhere in the middle of the sheet.

  2. And now, follow the already familiar path, i.e View tab > Freeze panes > and again Freeze panes.
    Freezing several columns in Excel
Note. Please make sure that all the columns you want to lock are visible at the moment of freezing. If some of the columns are out of view, you won't see them later. For more details, please see How to avoid hidden columns in Excel.

How to freeze multiple panes in Excel (rows and columns)

Do you wish to lock multiple rows and columns? No problem, you can do this as well, provided that you always start with the top row and first column.

To lock several rows and columns at a time, select a cell below the last row and to the right of the last column you want to freeze.

For example, to freeze the top row and first column , select cell B2, go to the View tab and click Freeze Panes under Freeze Panes:
Freezing the top row and first column in Excel.

In the same fashion, you can freeze as many Excel panes as you want. For instance, to lock the first 2 rows and 2 columns, you select cell C3; to fix 3 rows and 3 columns, select cell D4 etc. Naturally, the number of locked rows and columns does not necessarily have to be the same. For example, to freeze 2 rows and 3 columns, you select... guess which cell? Right, D3 : )

How to unfreeze panes in Excel

To unfreeze panes, just do the following: go to the View tab, Window group, and click Freeze panes > Unfreeze Panes.

Unfreeze panes in Excel

Excel Freeze Panes tips

As you have just seen, freezing panes in Excel is one of the easiest tasks to perform. However, as is often the case with Microsoft, there is much more beneath the hood. What follows below is a caveat, an artifact and a tip.

Caveat: Prevent hidden rows / columns when freezing Excel panes

When you are locking several rows or columns in a spreadsheet, you may inadvertently hide some of them, and as a result, you won't see those hidden panes later. To avoid this, make sure that all the rows and/or columns you want to lock are within eyesight at the moment of freezing.

For example, you wish to freeze the first three rows, but row 1 is currently out of view, as shown in the screenshot below. As the result, row 1 won't show up later and you won't be able to scroll up to it. Though, you would still be able to get to the cells in a hidden frozen row using the arrow keys.
To prevent hidden frozen rows, make sure all the rows you wish to lock are visible.

Artifact: Excel may freeze panes totally different from what you expected

Don't you believe me? Then try selecting cell A1, or the top visible row, or the leftmost visible column, click Freeze Panes and see what happens.

For example, if you select row 4 while the first 3 rows are out of view (not hidden, just above the scroll) and click Freeze Panes, what would you expect? Most obviously, rows 1 - 3 would get frozen? Nope! Microsoft Excel thinks differently and the screenshot below shows one of many possible outcomes:
Excel may freeze panes in a different way than you expected.

So, please remember, the panes you are going to lock, both rows and columns, should always be in sight.

Tip: How to camouflage the Freeze Panes line

If you are not particularly fond of the dark freeze panes line that Microsoft Excel draws underneath locked rows and to the right of locked columns, you can try disguising it with the help of shapes and a little creativity : )
The Freeze Panes line is camouflaged using Excel shapes.

If you think this is something that might work for you, please see the following article for step-by-step instructions - Dealing with Excel Freeze Panes Line.

And this is all for today, thank you for reading!

58 Responses to "How to freeze panes in Excel to lock rows and columns"

  1. Ravi Lele says:

    Hi Svetlana,

    Your solution under "Artefact: Excel may freeze panes totally different from what you expected" helped me. Thanks a lot.

  2. AnandaBalan says:

    Hi Svetlana,

    I need a help in Excel,I am not sure if this is the right forum that would help.

    I would like to create a percentile bar chart in excel.
    Can I attach my excel example?

    Detailed question:
    A table has 10 values. In these 10 values 6 values are positive and 4 are negative.
    I need to create a chart, that shows percentage of positive values as bar chart.

    If this is not the right forum, please let me know where can I find an answer to such questions.

    Thanks in advance.

    Regards,
    AnandaBalan

    • Hi AnandaBalan,

      I understand your task, but it does not relate to the topic of this article in any way. You can try finding a solution on some Excel forums like excelforum.com or stackoverflow.com.

  3. Roxana says:

    Thanks a lot for this post! Been looking for a detailed procedure for so long now!

  4. Lakshman says:

    Hi,
    Help me freezing the panes..
    I have 3 tables in a single sheet, with headings on different rows and have different colomns.

    How can I custom freeze panes for each particular table on a single sheet ? so that heading on each of the table shows up, while browsing the rows on individual table ?

  5. NANDAN says:

    THX A LOT...THE INFO AND THE EXAMPLES ARE USEFUL AND EASY TO UNDERSTAND.

  6. Abhilash Surendran says:

    G8...Thanks a lot...

  7. Manoj says:

    Hi
    Thanks for the information. It is very useful, it helped me.

  8. Kristi says:

    I am trying to do this but the freeze option is not available

    • JoLo says:

      My problem, also. I have used excel since it came on the market and I switched from Lotus123, if I remember correctly. But every time I post this problem I get the same lecture on how to freeze rows like I was born yesterday. I have other spreadsheets with the top row frozen. But, even in those sheets the "freeze" icon is no longer highlighted. Also, the fonts are huge even though it is set on 8. The worksheets from previous years are the right font 11. If I have caused this problem on my Windows 10 will someone please tell me how to activate my freeze icon? Clicking on "normal" view does nothing.

  9. josephine says:

    so good, and it was so useful

  10. Allen says:

    I need to freeze column and row in same time , is that possible

  11. Harsh Verma says:

    Thnx Problam Solved ! ! !

    Hn....Hn...Hn...

  12. Erin says:

    I don't have a "freeze" button!! Is there another name for it??

  13. Mel says:

    I am successfully able to freeze the top row. I do so and then save. However, each time I open the spreadsheet I have to manually re-freeze the top row. The save will not stick? Is there any way to permanently freeze the top row so I do not have to do so each time I open the sheet? Thanks in advance.

    • Rachel says:

      Hi Mel,

      I had the same problem, it was really frustrating! I couldn't find an answer anywhere! I've found that for some reason the default file type had been changed to an opendocument spreadsheet but when I changed it to save as an excel workbook it worked fine. I've no idea why this happened but at least it works now! I hope this helps!

      • Tina says:

        I am having the same problem since upgrading to MSO 2016. My document is already a .xlsx document. Is what you are talking about somewhere else?

  14. SARAH says:

    Great! Thanks!

  15. Mohsin Raza says:

    I want to freeze the header row and then below the sheet i want to freeze another row. Can excel do that

  16. GULAB MORE says:

    HELLOW...

    HOW CAN TWO DO ROW OR COLUMN FREEZE PANE IN EXCEL SHEET.

  17. Adeela says:

    Hi, I want to freeze first 2 columns and then at the same tym 8th and 9th column and again 13th and 14th column. IS it possible?

  18. Wasp says:

    Добрый день, Светлана.

    Возможно Вы сможете мне помочь.
    Заморозил строчку 1. В столбце А вписываем название месяца и дни. (А2="Январь", А3="1 число", А4="2 число" ... А33="Февраль", А34="1 число" и т.д.)
    Хочу сделать так чтоб прокручивая вниз в А1 отображался текущий месяц который уже прокрутил под замороженную строку.

    Благодарю за совет.

    • Добрый день!

      К сожалению, с помошью формул невозможно отследить скроллирование документа, это можно сделать с помощью VBA кода, но и там нужно будет потрудиться, чтобы отследить все варианты.

  19. Chirag says:

    Thank You For This Great Information

  20. Raja says:

    Is it possible the we freeze custom rows, for e.g freeze row#5, then row#9 then row#15.

  21. Ajoke says:

    Best solution I've found all day!! Thank you so much!!

  22. Sisbee says:

    This step was useful to me. I needed to freeze both rows and columnsat the same time. Thanks.

  23. samchief says:

    Thanks it works

  24. Ramesh says:

    Thanks multiple column freeze panes this step was useful

  25. ASHRAF M V says:

    Thanks for Good Article. I have a doubt in excel i hope you will help me.i have data in excel 2013 i want to learn how to sort it as i wish .

    data is like this

    Ward w rank Name Mark
    1 1 AA 85
    1 2 Ab 80
    1 3 Ac 78
    2 1 BA 82
    2 2 AB 81
    2 3 AD 80

    i want to get result like this

    1 1 AA 85
    2 1 BA 82
    1 2 AB 80
    2 2 AB 81
    1 3
    2 3

  26. Keith says:

    Great help, Thanks!!

  27. Ryan says:

    When i have conditional formatting and freeze panes some of the formatting in the frozen panes does not refresh appropriately, is there a way of resolving this issue?

  28. Mark says:

    Hi, I'm trying to freeze rows and columns at the same time, but can only seem to be able to freeze one or the other.
    Is it possible ot be able to freeze rows 1&2 and columns A B C & D?

  29. Kelly says:

    I want to freeze only the first row and first column. When I select those and freeze them it also freezes the nex 5 rows and 2 other columns. How do I get it to only freeze the first row and first column together?

    • Hello Kelly,

      Please make sure that the first row and first column are within eyesight (above the scroll), select cell B2, and then click Freeze Panes. I've tested this on a few sheets in different Excel versions, and it always works.

  30. Ian says:

    Thanks for this. Microsoft took something that was simple to do in previous versions of Excel with a slider and made it more complicated.

  31. pat says:

    When I select View - Freeze panes there is no extra Freeze panes as indicated in teh instructions to select more than one column/row. Used to work OK - but no longer. Only options that come up from Freeze panes refer to the first row/column ONLY - or unfreeze - sigh.

  32. Rajesh Bahl says:

    Excellent explanation on "How to freeze panes in Excel to lock rows and columns" in this article.

    Thanks a lot!!

  33. Prasad says:

    Great. Thanks!

  34. Neha says:

    Awesome, the examples helped. Thanks!

  35. Umer says:

    i need to freeze 1st three columns how it is possible?

  36. Yasar says:

    Hi,

    Any possibilities to freeze multiple row which are not adjacent?

    Thanks in advance for your understanding.

  37. Aqua says:

    Really thanx a lot for such an easy solution. Stay blessed

  38. Dallas says:

    I have seen spreadsheets where different rows are frozen
    e.g.: The "title" (rows 1:3), followed by sub-titles for individual tables on the same page (row 6:7, 23:24, 41:42, 58:59, 74:75).
    As you scroll down the page, the sheet allows you to scroll to each title then title automatically freezes and the page continues to scroll.
    In the end, you can see all of the frozen titles/rows, and can continue scrolling to infinity.
    How do you achieve this?

  39. RAVIKUMAR says:

    hello, please advise me how to convert DAYS:HOURS:MINUTES to total number of days.
    eg. I have (2/22/16 11:00) in one cell and 3/15/16 20:00
    in another cell, and the exact day difference is 22.375 days. Can you tell a quick formula for calculating this day difference in EXCEL SHEET. I SHALL BE VERY VERY GLAD FOR YOUR KIND SUPPORT... PLEASE...

    THANKS, I AM AWAITING YOUR REPLY

  40. tayyab mirza says:

    how can we freeze column and row in same sheet on our choice not first row nor first column i want to do freeze 3rd row and 4th column in on sheet. please help to do that i waiting your answers. tayyab mirza from pakistan.

  41. MOHSIN says:

    Dear Svetlana,

    We want to use more freeze pen in pivot table like in group one group finish then second group want to stop heading of the group name is it possible

Post a comment



Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
Excel add-ins and Outlook tools - Ablebits.com
33
Ultimate Suite 2018.5 for Excel
33
60+ tools for Excel
December offer: Dec. 8 – Dec. 17